Excel VBA - Runtime error '1004'

Question

I have some VBA code where I want to copy a formula into a range of cells using R1C1. Unfortunately I get the following error on the second to last line:
Runtime error '1004': Application-defined or object-defined error.

Dim pct As Single
pct = 1

With ThisWorkbook.Sheets("Data").UsedRange
    Dim lastR As Long
    lastR = .Rows.Count 
    Dim lastC As String
    lastC = col_letter(.Columns.Count)
End With

With ThisWorkbook.Sheets("Calculations")
    .Range("A1:" & lastC & 1).Value = 100
==> .Range("A2:" & lastC & lastR + 1).FormulaR1C1 = "=R[-1]C*(1+" & pct / 100 & "*'Data'!R[-1]C"
End With

What am I doing wrong here?

what are your lastC and lastR current values when error occurs? — Kazimierz Jawor
– Kazimierz Jawor, Commented Jun 10, 2013 at 11:05
do you know the difference between using & and + in VBA? its is probably the path to you file data and extension — user2140173
– user2140173, Commented Jun 10, 2013 at 11:14

JosieP · Accepted Answer · 2013-06-10 10:50:52Z

2

you're missing a parenthesis in the formula-perhaps

"=R[-1]C*(1+" & pct / 100 & ")*'Data'!R[-1]C"

answered Jun 10, 2013 at 10:50

JosieP

3,4101 gold badge15 silver badges16 bronze badges

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

3 Comments

bluebox Over a year ago

You're right, I was missing a parenthesis. But the runtime error is still occurring on the same line...

JosieP Over a year ago

what if you do the calculation in the formula "=R[-1]C*(1+" & pct & "/100)*'Data'!R[-1]C"

JosieP Over a year ago

does the formula return errors if you enter it manually into those cells? (does data!A1 contain a number?)

Kazimierz Jawor · Accepted Answer · 2013-06-10 11:19:43Z

1

I think you are running non-english version of Excel/Office. Therefore there could be a problem of numbers separation mark. It's required to use . (dot) in function defined as string and passed then by formulaR1C1 property to Excel. It will be then converted into , (comma) which is required when putting function in excel cell. Therefore this lines works for me with no error:

....=Replace("=R[-1]C*(1+" & pct / 100 & ")*Data!R[-1]C", ",", ".")

One more thing, I removed '' (single quotation marks) for sheet name.

answered Jun 10, 2013 at 11:19

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

2 Comments

bluebox Over a year ago

Thanks everybody for your help - I think KazJaw's suggestion finally solved the problem.

bluebox Over a year ago

UPDATE: I just changed the regional settings of my operating system to 'United States'. Now I can use any function without problems and don't even have to use replace.

Collectives™ on Stack Overflow

Excel VBA - Runtime error '1004'

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related