0

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)

Pᴇʜ
  • 53,845
  • 9
  • 46
  • 68
Peter Deane
  • 1

1 Answers1

1

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
SJR
  • 22,652
  • 6
  • 17
  • 25