Excel macro vlookup

[yankiessg]

Hi,

I have an excel where i want to lookup column header "Country" and vlookup the row to find if it's United States, Europe or Asia from a reference sheet. As the Country header might not always be on the same column from the spreadsheet i received from user. It might be in Column B today and Column Z the next day. So i have a macro that search for this header name. That works fine until the vlookup statement which fails with runtime error.

i = lastCol - ColCtry --->(i got the answer as -20)

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[i],'Sheet1'!RC[-3]:RC[-4],2,0)"


The error lies with this code: VLOOKUP(RC[i]
It does not recognise "i". Macro will works if i directly enter it as -20

Need help to advise what is wrong with my code.

Thanks

[Tinbendr]

Anything between the quotes is a literal. Because I is a variable, the interpreter is trying to insert the value, but throws the error.

You have to move the variable outside the quotes.

Please Login or Register  to view this content.

[dangelor]

Nevermind...