CODEWITHSUNDEEP
excelvba
CustomX
CustomX

Reputation: 10113

Adding formula to a cell and referencing to variables

I am trying to get a formula into a cell with variables from VBA, but I'm getting another Application or Object defined error. The error is triggered on the first row op Range("R" & i).FormulaR1C1 = "=Q" & i & "/" & DollarRate. I have tried using just Formula instead of FormulaR1C1.

Columns Q and R are empty. Column Q will contain values in € and Column R will show the value in $.

Dim LastRow As Integer, i As Integer
Dim DollarRate As Double

DollarRate = InputBox("Enter dollar rate:", "Dollar rate")

Range("Q1").Value = "$ POS"
Range("R1").Value = "€ POS"

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To LastRow
    Range("R" & i).FormulaR1C1 = "=Q" & i & "/" & DollarRate
Next i

Upvotes: 0

Views: 51

Answers (1)

Rory
Rory

Reputation: 34045

Try replacing this:

For i = 2 To LastRow
    Range("R" & i).FormulaR1C1 = "=Q" & i & "/" & DollarRate
Next i

with this:

Range("R2:R" & lastrow).FormulaLocal = "=Q2/" & DollarRate

Upvotes: 1

Related Questions