CODEWITHSUNDEEP
vbaexcel
Sam Kennedy
Sam Kennedy

Reputation: 3

VBA: I can add formulas to a range of cells, but not to 1 cell

I think I've tried most everything and I always get the dreaded Run-time error '1004': Application-defined or object-defined error.

Dim wsMaster As Workbook
Set wsMaster = ActiveWorkbook
(Other stuff)
With wsMaster.Worksheets(1)
    .Activate
    .Range(Cells(startRow, 20), Cells(endRow, 25)).FormulaR1C1 = "=RC[-12]*RC15/R" & startRow & "C16"
    .Range(Cells(startRow, 26), Cells(startRow, 31)).FormulaR1C1 = "=SUM(RC[-6]:R" & endRow & "C[-6])"
    .Range("R" & startRow & "C16").FormulaR1C1 = "=SUM(R" & startRow & "C[-1]:R" & endRow & "C[-1]"
End With

The interesting part is that the code only throws an error on the third formula assignment line. The other lines function as intended and insert the proper formulas in the specified range. I've tried a myriad of different ways to reference the cell, but I get the error every time.

I checked with the locals screen and startRow is properly set at 2 and endRow is properly set at 8.

I'd appreciate any help with this mysterious issue!

Upvotes: 0

Views: 53

Answers (2)

Busse
Busse

Reputation: 863

Just right off the bat I notice you are missing a : (Colon)

.Range("R" & startRow & "C16") should be

.Range("R" & startRow & ":C16")

The other issues I noticed are with the third formula. You are missing your brackets and a parenthesis:

"=SUM(R" & startRow & "C[-1]:R" & endRow & "C[-1]" should be:

"=SUM(R[" & startRow & "]C[-1]:R[" & endRow & "]C[-1])"

Here is the entire third line, with your same format, with the above referenced corrections.

.Range("R" & startRow & ":C16").FormulaR1C1 = "=SUM(R[" & startRow & "]C[-1]:R[" & endRow & "]C[-1])"

Upvotes: 2

John Wu
John Wu

Reputation: 52270

To reference a single cell, you can use the Cells(row, column) property.

In your code, change

.Range("R" & startRow & "C16").FormulaR1C1 = "=SUM(R" & startRow & "C[-1]:R" & endRow & "C[-1]"

To

.Cells(startRow, 16).FormulaR1C1 = "=SUM(R" & startRow & "C[-1]:R" & endRow & "C[-1]"

Upvotes: 0

Related Questions