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ᴇʜ
  • 56,719
  • 10
  • 49
  • 73
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,986
  • 6
  • 18
  • 26