CODEWITHSUNDEEP
vbaexcelfor-loop
user8107531
user8107531

Reputation: 27

For loop with formulas

I have this code below which I want to be used in a loop. However, instead of C5 and D5, I would want this loop to be run on all the cells in column C and column D and not only for C5 and D5.

To summarize, I would want C5 and D5 to be replaced by every cell in Column C and D. Please assist.

For i = 1 To 5
Valuex = Evaluate("=IsNumber(Value(Mid(C5, 2, 1)))")
MsgBox (Valuex)
Valuex1 = Evaluate("=Left(Trim(C5), 1) = ""R""")
MsgBox (Valuex1)

If ((Evaluate("=Left(Trim(C5), 1) = ""R""") = "True") And (Evaluate("=IsNumber(Value(Mid(C5, 2, 1)))") = "True")) Then

Range("D5").Formula = "=VLOOKUP(C5,[old.xls]Sheet1!$D:$V,19,0)"
MsgBox ("if")
Else
Range("D5").Formula = "=VLOOKUP(C5,[old.xls]Sheet1!$E:$V,18,0)"
MsgBox ("else")
End If
Next i

Upvotes: 0

Views: 993

Answers (1)

SJR
SJR

Reputation: 23081

Think this does what you want. It will run from row 1 to the last row in C. Note that you could do all this without VBA.

Sub x()

Dim i As Long, Valuex As Boolean, Valuex1 As Boolean

For i = 1 To Range("C" & Rows.Count).End(xlUp).Row
    Valuex = Evaluate("=IsNumber(Value(Mid(C" & i & ", 2, 1)))")
    MsgBox (Valuex)
    Valuex1 = Evaluate("=Left(Trim(C" & i & "), 1) = ""R""")
    MsgBox (Valuex1)
    If Valuex1 And Valuex Then
        Range("D" & i).Formula = "=VLOOKUP(C" & i & ",[old.xls]Sheet1!$D:$V,19,0)"
        MsgBox ("if")
    Else
        Range("D" & i).Formula = "=VLOOKUP(C" & i & ",[old.xls]Sheet1!$E:$V,18,0)"
        MsgBox ("else")
    End If
Next i

End Sub

I think you can avoid the loop altogether thus

Sub xx()

Dim i As Long

i = Range("C" & Rows.Count).End(xlUp).Row

With Range("D1:D" & i)
    .Formula = "=IF(AND(ISNUMBER(VALUE(MID(C1, 2, 1))),LEFT(TRIM(C1), 1) = ""R""),VLOOKUP(C1,Sheet1!$D:$V,19,0),VLOOKUP(C1,Sheet1!$E:$V,18,0))"
    .Value = .Value
End With

End Sub

Upvotes: 2

Related Questions