Range Method to Set Cell Formulas with LastRow

[OilEconomist]

Thanks in advance for anyone who is assisting. I will test any recommendations and will let you know the outcome.

Item:

I've found the last row in my data ("LastRow"), and now I'm trying to set a formula in cells A5 to "LastRow" to a formula, but I'm getting errors.

The First issue seems to be with the use of "A5:A"&"LastRow" in the code and then I'm also having issues with the formula as I've tested trying to use it by just setting "A5:A"&"LastRow" to "A5:A6".

Once again thanks in advance.

ActiveSheet.Range("A5:A" & "LastRow").Formula = "=IF( OR(B5 = "", B4 = "Rem."), "Delete", IF( AND( N1 = "", LEFT( B1, 4) = "Date"), "Total", IF( AND( N1 <> "", LEFT( B1, 4) = "Date"), N1, IF(AND(B5 <> "", A4 = "delete"), B2, A4 ))))"

 

[Fluff]

You need to remove the quotes from LastRow and at the same time you need to double-up on the quotes inside the formula.

 

[Scott Huish]

2 things
LastRow is a variable and should not be in quotes.
You also to double up on the quotes in the formula, otherwise VBA will see the " as the end of the string:

ActiveSheet.Range("A5:A" & LastRow).Formula = "=IF(OR(B5 ="""",B4=""Rem.""),""Delete"",IF(AND(N1="""",LEFT(B1,4)=""Date""),""Total"",IF(AND(N1<>"""",LEFT(B1,4)=""Date""),N1,IF(AND(B5<> """",A4=""delete""),B2,A4))))"

 

[OilEconomist]

That worked!

Thanks so much to both of you - "Fluff" and "Scott Huish", for fixing my issue and the quick response!

 

[Fluff]

Glad we could help & thanks for the feedback