Vert.Zoeken via VBA

[Jemp]

Waarom slaag ik er niet in om via VBA een formule (die Vert.Zoeken bevat) in een cel te plaatsen?

Ik heb het probleem geschetst in een dwaas vereenvoudigd voorbeeld.

Blad1 bevat een aantal waarden in kolom A en B. De waardes in kolom C worden berekend via een formule. Het domein "AllHer" is benoemd en omvat de gegevens van Blad2.
Met de formules is duidelijk niks verkeerd.

Wanneer ik echter dezelfde formule in een cel van kolom D wil plaatsen via VBA (zie knop in Blad 3) loopt het fout.

Voor specialisten zal dit wel een dwaas probleem zijn, maar voor mij .....
Bekijk bijlage Test VertZoeken.xlsm
Groetjes
JP

 

[SjonR]

vba herkent geen Nederlands

 

[gast0660]

  Worksheets("Blad1").Range("D1").FormulaR1C1 = "=VLOOKUP(RC[-2],AllHer,4,FALSE)"

 

[edmoor]

vba herkent geen Nederlands

Wel als je niet FormulaR1C1 gebruikt maar FormulaLocal:

Worksheets("Blad1").Range("D1").FormulaLocal = "=VERT.ZOEKEN(A1;AllHer;4;ONWAAR)"

 

[Jemp]

Dat van de taal heb ik goed begrepen en dat werkt ook. Waarvoor dank allen.

De antwoorden maken dat ik weer wat verder kan.
Hieronder een aangepast bestand. Ik probeer nu een lus te maken in VBA zodat op blad1 formules terecht komen zoals aangeduid in de groene zone.
De oplossing die ik wilde invoeren (maar niet werkt) heb in de regel VBA als Rem geplaatst.

Iemand een idee?

Bekijk bijlage Test VertZoeken.xlsm

Groetjes
JP

 

[edmoor]

Dat hoeft niet in een loop:

Private Sub CommandButton1_Click()
    counter = Worksheets("Basis").Range("A" & Rows.Count).End(xlUp).Row
    Worksheets("Basis").Range("J1:J" & counter).FormulaLocal = "=Vert.Zoeken(A1;AllHer;4;ONWAAR)"
End Sub

 

[Jemp]

Beste Edmoor

Het probleem is dat niet enkel kolom C van blad1 moet ingevuld worden maar ook D en E (gele zone).
De formules die ik via de knop in deze zone wil plaatsen heb ik nu voor de duidelijkheid rechtstreeks in de cellen ingevuld.
Dus in de formule "Vert.Zoeken(A1;AllHer;4;ONWAAR)" moeten ook "A1" en "4" variabel zijn (aangepast aan de rij en kolom waar het over gaat).

Bekijk bijlage Test VertZoeken.xlsm

Groetjes

 

[VenA]

Probeer het zo eens

Sub VenA()
  Sheets("Basis").Columns(1).SpecialCells(2).Offset(, 2).Resize(, 3).FormulaR1C1 = "=VLOOKUP(RC1,AllHer,COLUMN(),FALSE)"
End Sub

 

[Jemp]

Hoi VenA

Werkt perfect, waarvoor dank.
Wanneer ik echter onderstaande voorwaarde invoer loopt het weer mis (zie bijgevoegd voorbeeld):
"=if(ISBLANK(VLookUp(RC1,AllHer,Column(),False)),"",VLookUp(RC1,AllHer,Column(),FALSE)"

Het loopt ergens mis bij de vertaling. De "", om het veld blanco te laten, wordt niet aanvaard/herkend.

Bekijk bijlage Test VertZoeken.xlsm

Groetjes

 

[edmoor]

Logisch, dat helpt de syntax om zeep.
Gebruik daar niet "" (2) maar """" (4) dubbele quotes.

 

[snb]

Als je eenmaal met VBA begint heb je (bijna) geen Excel formules meer nodig.

Private Sub CommandButton1_Click()
   with sheets("Basis").cells(rows.count,1).end(xlup)
     .offset(,8)=["AllHer"].find(.value,,,1).offset(,3).value
   end with
End Sub

De interaktie met het werkblad is moeizaam en overbodig.

Als je van het benoemde bereik (een hulpmiddel voor gebruikers zonder VBA-kennis) ook nog een vrolijke array maakt, wordt de code nog een stuk sneller.

 

[Jemp]

Hoi snb

Moet ik nog iets aanpassen aan je voorgestelde code ?
Momenteel werkt hij niet.

Bekijk bijlage Test VertZoeken.xlsm

Groetjes

 

[snb]

Zo dus:

Private Sub CommandButton1_Click()
  sn = Sheets("her").Cells(1).CurrentRegion
  
  With Sheets("Basis").Cells(Rows.Count, 1).End(xlUp)
     For j = 1 To UBound(sn)
      If sn(j, 1) = .Value Then Exit For
     Next
    .Offset(, 2).Resize(, 3) = Array(sn(j, 3), sn(j, 4), sn(j, 5))
  End With
End Sub

 

[Jemp]

Met dank voor de voorgestelde oplossingen.