Opgelost vlookup (excel 2016 NL) met beveiligde rijen

[Remco1962]

Goede middag. Het probleem dat ik heb is dat klanten mij prijsaanvragen sturen in excel met een beveiligde spreadsheet. Met vlookup kan ik dan de formule niet doortrekken naar beneden. Bijgesloten voorbeeld. Als ik het onmogelijke wil, dan hoor ik dat ook graag. Bijgesloten voorbeeld. Dank alvast voor jullie feedback.

 

[AD1957]

Leg het probleem uit aan jouw klant en vraag om een bestand zonder beveiliging.

 

[Remco1962]

Uiteraard al geprobeerd. De klanten zijn grote bedrijven die niet afwijken van hun policy en zijn helaas niet bereid de spreadsheets onbeveiligd te sturen. Ik kan natuurlijk hun tabblad kopieren en zo van de beveiliging afhalen, maar zij willen de offerte in hun beveiligde spreadsheet retour..

 

[AD1957]

Vreemd verhaal.
Hoe wil je dan de prijzen wijzigen ?
Als alleen de gele cellen zijn beveilig zou je 2 kolommen kunnen toevoegen.
nieuwe prijs en nieuw totaal.

 

[Remco1962]

Het is in onze branch gebruikelijk van klanten (wereldwijde scheepsrederijen) aanvragen te ontvangen in hun beveiligde excel formaat. Het enige dat onbeveiligd is zijn de cellen waar wij de prijzen in kunnen vullen. De prijzen heb ik in een eigen spreadsheet staan, maar het doortrekken van de vertical lookup formule werkt niet omdat de kolom met cellen waar wij prijzen moeten invullen wordt onderbroken door beveiligde regels/cellen.
In mijn voorbeeld zijn de gele cellen juist NIET beveiligd.

 

[AD1957]

draai deze code eens. (de range moet je zelf maar aanpassen)

Sub formule()
For Each ct In Range("F1:F22")
    If ct.Locked = False Then ct.FormulaR1C1 = "=VLOOKUP(RC[-5],PRICES!R[-4]C[-5]:R[9]C[-2],4,FALSE)"
Next
End Sub

 

[Remco1962]

Hi Albert. Dank je wel, de formule werkt !

 

[AD1957]

Volgens mij gaat het hiermee niet lukken.
Als je het bestand met de formules terugstuurt naar de klant, waarschijnlijk zonder blad PRICES, gaat het fout.
Zal eens kijken voor een andere oplossing.

 

[AD1957]

probeer deze eens.

Sub formule()
i = Range("E" & Rows.Count).End(xlUp).Row
For Each ct In Range("A5:A" & i)

    Set c = Sheets("PRICES").Cells(5, 1).CurrentRegion.Find(ct, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            ct.Offset(, 5) = c.Offset(, 3)
        End If
Next
End Sub

 

[Remco1962]

Dank je wel. Formule werkt goed. Betekent het dat ik de spreadsheet van de klant eerst als xlsm moet opslaan alvorens ik hem terug kan sturen?

 

[AD1957]

Gewoon uitproberen, draai de macro en sla het bestand eens op als xlsx