How do I use variables when inserting an Excel formula using VBA?

Question

I am trying to get an Excel If formula to apply to all rows with content in a sheet. If the cell value entered after the macro is applied is y, then something appears in the next column, if it is n something else appears. It looks like this:

For Row = 2 To LastRow
        Cells(Row, 8).Formula = "=IF(""H"" & Row & ""="" ""Y"",""Included"",IF(""H"" & Row & ""="" ""N"",""SYS"", """"))"
    Next

I believe the issue is with where I putting the quotes and how many, but I can't figure that out for the life of me. I was able to get the formula to work when I chose a specific cell, ie. H2, but the issue began when I tried to use a variable.

Any help or tips or references would be appreciated!

Tim Williams · Accepted Answer · 2020-08-04 20:15:35Z

1

I find it easier to replace tokens than to use concatenation, as it's easier to manage the quotes that way.

Const FRM As String  = "=IF(H<r>=""Y"",""Included"",IF(H<r>=""N"",""SYS"", """"))"
For Row = 2 To LastRow
    Cells(Row, 8).Formula = Replace(FRM, "<r>", Row
Next

edited Aug 4, 2020 at 20:15

answered Aug 4, 2020 at 19:33

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

magician103 Over a year ago

GAMECHANGER. Thank you!

Collectives™ on Stack Overflow

How do I use variables when inserting an Excel formula using VBA?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related