CODEWITHSUNDEEP
excelvba
Peter Deane
Peter Deane

Reputation: 1

Variables in formula

I've defined x & Y and both return the required values but the field only populates the formula with text x & Y

I've checked against defining variables and its what I'm doing

Sub FindLast()

Dim x As Integer
Dim y As Integer

x = InStrRev(Range("H2"), ")")
y = 999


Range("I2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],x,y)"
ActiveCell.AutoFill Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1))

End Sub

I get the formula =MID(H2,x,y) I Should get =mid(H2,77,999)

Upvotes: 0

Views: 47

Answers (1)

SJR
SJR

Reputation: 23081

Anything you put in quotes will appear as written so you need to take the variables outside. Also, you can remove the Select/ActiveCell bit.

Sub FindLast()

Dim x As Integer, y As Integer

x = InStrRev(Range("H2"), ")")
y = 999

Range("I2").FormulaR1C1 = "=MID(RC[-1]," & x & "," & y & ")"

End Sub

Upvotes: 1

Related Questions