excel Index function in VBA using variables instead of target column number

[evenzuryossi]

trying to make this index formula in vba

ActiveCell.Formula = "=" & "@INDEX(oss,MATCH(A2,OSS!AJ:AJ,0),@COL)"
where col is an integer variable. if I put a number its working fine, also checked the value in the debug and there is a number over there.
yelp

 

[Joe4]

Welcome to the Board!

Try this:

ActiveCell.Formula = "=" & "@INDEX(oss,MATCH(A2,OSS!AJ:AJ,0)," & COL & ")"

 

[evenzuryossi]

Welcome to the Board!

Try this:

ActiveCell.Formula = "=" & "@INDEX(oss,MATCH(A2,OSS!AJ:AJ,0)," & COL & ")"

Of course!!!!! So obvious...

 

[evenzuryossi]

Of course!!!!! So obvious...

Thanks a lot

 

[Joe4]

You are welcome.

 

[smozgur]

Of course!!!!! So obvious...

So, the Oscar goes to the answer post #2!

@evenzuryossi - Welcome to the board!
Just FYI - I changed the marked solution by selecting the post that actually answered your question, so it will help future readers. That would be great if you could do the same in your future questions.

 

[evenzuryossi]

I'm a newbie in VBA still struggling and only after "seeing" the solution wanted to kick myself.