CODEWITHSUNDEEP
excelvbaexcel-formula
John
John

Reputation: 341

Loop Variable inside a Formula Function

I am looping thru a column in which i need to add one to the loop variable within the FORMULA
My problem is how to write the correct FORMULA to go to the next cell using the lrow variable

Attached is a snippet of my code

 Dim LastRow As Long
 For lrow = 1 To 20

 If Worksheets("cars").Range("P" & lrow) = "1" Then 

 Worksheets("cars").Range("a" & lrow).Formula = _
               "=RIGHT(h & lrow ,FIND(""."",h & lrow))"

How do i concatenate the lrow variable within the formula ? I have also tried "=RIGHT("h" & lrow ,FIND(""."","h" & lrow))"

Upvotes: 0

Views: 127

Answers (2)

AJD
AJD

Reputation: 2438

You can build the string, inserting the variables where necessary.

Worksheets("cars").Range("a" & lrow).Formula = _
               "=RIGHT(h" & lrow & ",FIND(""."",h" & lrow & "))"

Note the extra quotation marks and (ampersands) that I have placed to expose the variable from the rest of the string.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166835

Sometimes using Replace() can avoid a lot of concatenation and quote-balancing:

Worksheets("cars").Cells(lrow, "A").Formula = _
       Replace("=RIGHT(H<rw>,FIND(""."",H<rw>))", "<rw>", lrow)

Upvotes: 1

Related Questions