CODEWITHSUNDEEP
excelvbaif-statementformula
Mike - SMT
Mike - SMT

Reputation: 15226

VBA IF formula not working?

I have some VBA code that is working between a few workbooks

I am running into a problem where I cannot get this IF formula to apply. I am not sure how I am getting Subscript out of range error.

I have tried to use .Range("O"), .Range("O:O"), .Range("O", i) but all give the same 1004 error.

Am I using .Range() wrong? I was able to use range on a different formula so not sure why this wont work.

Sub if_after()

    Dim co_detail As Workbook
    Set co_detail = ThisWorkbook

        With co_detail.Sheets("Detail")

            For i = 2 To 100
                .Range("O2").Formula = "=IF(L" & i & "=N" & i & ",'good','update')"
            Next i

        End With

End Sub

Upvotes: 1

Views: 728

Answers (3)

AlwaysBeProgramming
AlwaysBeProgramming

Reputation: 23

Instead of using .Range("o" & i).Formula = … try using .Range("o" & i).FormulaR1C1 …. I always use FormulaR1C1 instead of Formula.

Upvotes: 0

BigBen
BigBen

Reputation: 49998

You could simplify this entire code to the following - no need to loop at all, you can just Resize:

Sub If_after()
    ThisWorkbook.Sheets("Detail").Range("O2").Resize(99).Formula = "=IF(L2=N2,""good"",""update"")"
End Sub

Upvotes: 2

user4039065
user4039065

Reputation:

That's a zero not an oh and full quotes are used for text. Quotes must also be doubled up within a quoted string.

.Range("o" & i).Formula = "=IF(L" & i & "=N" & i & ", ""good"", ""update"")"

Upvotes: 3

Related Questions