Variables in formula

Question

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)

SJR · Accepted Answer · 2019-01-28 10:55:46Z

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

answered Jan 28, 2019 at 10:55

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Deane Over a year ago

Thanks so much. I though the & was just for spaces in text. You learn every day. Tested and works.Thanks SJR.

Collectives™ on Stack Overflow

Variables in formula

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related