Coding a Formula in Macro

Hi, everyone! I hope you have a good day.

I have this problem that I have to put a formula in a button in VBA, and I've tried all of the ways here https://www.automateexcel.com/vba/formula-formular1c1 but nothing works and always give me the error from Macro.

My original code is:

Please Login or Register  to view this content.

maxMarker is a variable that could change, hence why I put it as "C" & (maxMarker + 4).

I want to get the value from cell C2 and I got that code from Record Macro, but since [-20] means 20 from the current active cell, and so whenever the values change, the generated formula from the button changes and it is no longer from C2.

The formula I want should be =IF(ISBLANK(C2),"",VLOOKUP(C2,ASetup,10,0)) but every time I put it there it's always giving me errors. I've tried the Range("C" & (maxMarker + 4)).Formula, the R1C1 one and it's always giving me errors. Is there a another way to put the =IF(ISBLANK(C2),"",VLOOKUP(C2,ASetup,10,0)) there?

I'd really, really appreciate it if anyone would kindly help me on this. Thank you so much, stay safe always!

If it should always look at C2 try

Please Login or Register  to view this content.

Hi, Fluff13! Thank you for answering!

Ah, I've tried that, but it locks the C column... whereas I don't want to lock it so that it could still be C2 because the formula needs to be dragged to other cells...

Is there any other way? Thank you!

Is there any other way?

Yes there is, but it will need more information.
Can you attach a file

Thanks for that, unfortunately I cannot test this as your code fails, but try

Please Login or Register  to view this content.

Hi, Fluff13, yes, it works!

Thank you so much for your replies! I hope you have a good day!

Glad to help & thanks for the feedback.