Formule invullen met macro met variabel celbereik

[stimmie]

Hallo, ik ben nieuw op dit forum dus hopelijk doe ik het zo goed.

Ik heb een vraag, ik wil in excel een macro maken dat er in een cel een formule wordt geplaatst (VLOOKUP). Ik wil dit op deze manier doen, omdat het maar bij een aantal cellen gedaan dient te worden en ik niet wil dat excel iedere keer alle VLOOKUPS gaat doorrekenen.

Het probleem is dat de cel waar hij naar verwijst dus variabel is, maar doordat ik de macro heb opgenomen gaat hij elke keer uit van de opgenomen cel.

Kan iemand mij helpen? Ik heb de volgende code, die mij vrij simpel lijkt.

---
ActiveCell.Formula = "=VLOOKUP(U303,'Inbound containers'!C:E,3,FALSE)"
ActiveCell.Select
---

De U303 moet dus iedere keer de 1 cel links van de actieve cel zijn waar ik de macro in start.

Ik hoop dat ik de vraag duidelijk heb omschreven? Kan iemand mij helpen? Alvast bedankt.

Gr. Tim

 

[Cobbe]

Zou dit een (niet getest) oplossingvoor uw probleem kunnen zijn?

ActiveCell.Formula = "=VLOOKUP(ActiveCell.Offset(0, -1).Address,'Inbound containers'!C:E,3,FALSE)"

 

[stimmie]

Zoiets dacht ik ook al ja, maar het werkt niet. Hij geeft een foutcode 1004. Helaas.
Andere ideeen wellicht?

 

[Cobbe]

Klopt, er moet ook gezocht worden naar een waarde en niet naar een adres:

ActiveCell = WorksheetFunction.VLookup(Range(ActiveCell.Offset(0, -1).AddressLocal).Value, Range("C:E"), 3, 0)

 

[stimmie]

Het ziet er al een stuk vernuftiger uit, maar het werkt niet. Ik krijg de volgende melding:
"Unable to get the Vlookup property of the WorksheetFunction class"

Geen idee wat dat inhoudt, kan het liggen aan het feit dat ik Excel 2003 gebruik?

Bedankt voor de hulp tot dusver in ieder geval.

 

[Mark xl]

Hoi stimmie,

Ik zal je vertellen wat de oplossing van het probleem is.

Je kunt een relatieve verwijzing gebruiken in je Formule mbv de zogenaamde R1C1
verwijzing.

als je dat doet, moet je ervoor zorgen dat ALLE verwijzingen in je formule relatief zijn

Ik denk dat deze formule is wat je zoekt

'(een cel in de kolom "V")
Activecell.Formula = "=VLOOKUP(RC[-1],'Inbound containers'!C3:C5,3,FALSE)"

Uitleg:

C3:C5 verwijzen naar KOLOM 3 t/m KOLOM 5, dat wordt vertaald naar $C:$E

RC[-1] verwijst naar de cel op de zelfde rij, één kolom naar links (R zonder positieaanduiding tussen blokhaken en C met [-1])

bijvoorbeeld met Cel V303 geselecteerd verandert de formule in "=VLOOKUP(U303,'Inbound containers'!C:E,3,FALSE)"

Succes.

 

[stimmie]

MARK!

Bedankt, that did the trick!
Een vraagje nog zodat ik de formule wat beter snap, waar staat dat RC dan voor?
Iets van: "1 kolom links van de actieve cel" ?? Bij "RC[1] had hij kolom W gepakt?
Begrijp ik dat goed?

Bedankt voor de hulp in ieder geval! werkt super!

Misschien een uitbreidend vraagje hierop:
Kan ik dit ook gelijk op een of andere manier op meerdere selecties toepassen?

Dus ik selecteer in plaats van 1 cel, 4 cellen en dan moet de formule in alle 4 de cellen 1 kolom eerder neergezet worden.. maar wel ieder refererend naar zijn eigen regel..

 

[Mark xl]

Dat begrijp je goed. lees de uitleg hierboven nogmaals

Succes en tot later!

 

[Mark xl]

Reactie op je tweede vraag:

Begrijp ik je goed als je formules wilt doorkopiëren?

als je vier cellen op eeen willekeurige plek hebt geselecteerd, en de cel rechts daarnaast is leeg dan doe je:

selection.offset(,1) = Selection

houd dan wel rekening mee dat er "$" op de juiste posities staan.\


en doorwerkend op je vorige vraag
als je deze formule: "=VLOOKUP(RC[-1],'Inbound containers'!C3:C5,3,FALSE)" op meerdere cellen in kolom V wilt hebben,
Selecteer je een paar cellen in kolom u, en gebruik je in de macro

    Selection.Offset(,1) ="=VLOOKUP(RC[-1],'Inbound containers'!C3:C5,3,FALSE)"

 

[stimmie]

Dikke ****! jij bent hier goed in haha

Het moest echter wel Selection.Offset(,0) zijn aangezien het in de actieve cell terecht moest komen.

Maar heel erg bedankt, ik ben zeer tevreden!

 

[Mark xl]

haha. dan nog een tip.
Offset([rij],[kolom]) geeft aan hoeveel rijen/kolommen verschuiving van de actieve cel

Activecell.offset(0,0) == Activecell, oftewel deze zijn gelijk.
dus laat dat offset maar achterwege dan.

Veel plezier met je vba ontdekkingstocht.