CODEWITHSUNDEEP
excelvbaif-statement
noobatexcel
noobatexcel

Reputation: 41

How can I convert this IF statement to use in VBA

I have the following IF statement =IF(LEN(F2)>LEN(H2),F2,H2)

This just checks which is longer between F2 and H2 and populates I2 with the longest. When I put this in VBA it comes out as =IF(LEN(G1048558)>LEN(I1048558),G1048558,I1048558)in my spreadsheet

How could I fix this?

Sub PopulateI()

Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEN(R[-20]C[-2])>LEN(R[-20]C),R[-20]C[-2],R[-20]C)"
Selection.AutoFill Destination:=Range("I2:I" & Range("F" & Rows.Count).End(xlUp).Row)

End Sub

Upvotes: 1

Views: 55

Answers (1)

BigBen
BigBen

Reputation: 50007

Skip the AutoFill and write the formula to the entire range in one step.

If it's easier, you can use the A1-style formula instead of the current R1C1-style formula.

Dim lastRow As Long
lastRow = Range("F" & Rows.Count).End(xlUp).Row

Range("I2:I" & lastRow).Formula = "=IF(LEN(F2)>LEN(H2),F2,H2)"

Or as noted by @Scott Craner, you can fix the R1C1-style formula (which is the real issue):

Range("I2:I" & lastRow).FormulaR1C1 = "=IF(LEN(RC[-3])>LEN(RC[-1]),RC[-3],RC[-1])"

Upvotes: 1

Related Questions