CODEWITHSUNDEEP
excelvba
tpthatsme
tpthatsme

Reputation: 45

VBA Formula in a cell referencing another sheet

I am trying to write the following formula in a cell with VBA, but it fails and I am out of ideas. Can someone help out with this? I want the following formula in a cell:

=IF(F3='PLC ADMIN'!B3,"",'PLC ADMIN'!B3)

My VBA looks like this:

Worksheets("PLC").Range("L3").Formula = "=IF(F3='PLC ADMIN'!B3,"""",'PLC ADMIN'!B3)"

The actual results in Sheet PLC cell L3 is this:

=IF(F3='PLC ADMIN'!#REF!,"",'PLC ADMIN'!#REF!)

Thank you in advance!

Upvotes: 0

Views: 232

Answers (1)

DiYage
DiYage

Reputation: 53

I use a shortcut and type the formula I want out in the excel sheet first to make sure it's working correctly. Once I confirm it is, I start a macro recording, double click in the cell with the formula I created (like I'm going to edit it) and then hit enter (like I've completed editing it). Then stop the recording.

Once complete, navigate to the VBA window and find the macro that was recorded. it'll have the formula written correctly in VBA and you can copy/paste it where you need it in your code.

This method is especially helpful for complicated/long formulas.

Upvotes: 0

Related Questions