ActiveCell.Formula Run-time error 1004

Question

I am trying to get the sum of columns G:26 to 1 above the "Total Service Fees" cell. I am running into a Run-time error '1004'. Application-defined or object-defined error. Does anyone know what this could be from?

Worksheets(1).Select
Dim rng1 As Range
Dim strSearch As String
strSearch = "Total Service Fees"
Set rng1 = Range("F15:F100").Find(strSearch, , xlValues, xlWhole)
If Not rng1 Is Nothing Then
    rng1.Offset(0, 1).Select
    ActiveCell.Formula = "=SUM(G26:" & ActiveCell.Offset(-1, 0).Select & ")"
Else
    MsgBox strSearch & " not found"
End If

I get the right answer, but I still get this error.

You don't want to use the `Select` method in that formula, I bet. To elaborate: open the immediate window (CTRL+G) and type this -- including the `?` , then press enter: `?"=SUM(G26:" & ActiveCell.Offset(-1, 0).Select & ")"` You should see that this is not a valid formula. — Gaffi, Apr 29 '13 at 19:42
What would you suggest instead? It is giving me =SUM(G26:True) — njsokol, Apr 29 '13 at 19:45
That `=SUM(G26:True)` is what you are telling the code to assign as the formula. This is not valid, so you'll need to supply a valid `A1`-style range identifier. — Gaffi, Apr 29 '13 at 19:48
+ 1 Classic case of why you shouldn't use `ActiveCell`, `Select` — Siddharth Rout, Apr 29 '13 at 19:52

score 2 · Accepted Answer · edited May 23 '17 at 10:25

2

Avoid the use of SELECT. You may want to see THIS

When you have identified the cell which had the search text then simply retrieve the row of that cell and use that. See this example (UNTESTED).

Dim rng1 As Range
Dim strSearch As String
Dim r As Long

With Worksheets(1)
    strSearch = "Total Service Fees"
    Set rng1 = .Range("F15:F100").Find(strSearch, , xlValues, xlWhole)
    If Not rng1 Is Nothing Then
        r = rng1.Row
        .Range("G" & r).Formula = "=SUM(G26:G" & r & ")"
    Else
        MsgBox strSearch & " not found"
    End If
End With

Note: I have not done any error handling. You will have to take care of that. For example, what if the search text if found on F26?

edited May 23 '17 at 10:25

Community

1
1

answered Apr 29 '13 at 19:44

Siddharth Rout

147,039
17
206
250

This gives me the inclusive row of TotalServiceFees when doing the SUM – njsokol Apr 29 '13 at 19:50
Is this what you want then? `r = rng1.Row -1` – Siddharth Rout Apr 29 '13 at 19:50
And if you want the formula in G cell next to F cell where yyou have found the text then don't change the above. but change `.Range("G" & r).Formula = "=SUM(G26:G" & r & ")"` to `.Range("G" & r).Formula = "=SUM(G26:G" & (r-1) & ")"` – Siddharth Rout Apr 29 '13 at 19:56
Yeah, I had to make that little change but it works now. Thanks for the quick help! – njsokol Apr 29 '13 at 19:59

score 0 · Answer 2 · answered Apr 29 '13 at 19:47

Set rng1 = Range("F15:F100").Find(strSearch, , xlValues, xlWhole)

I think your problem lies here. Try declaring the worksheet or setting a variable to the appropriate sheet.

Dim WS as Worksheet
Set WS = Activeworkbook.Sheets("Enter Sheet Name")
Set rng1 = WS.Range("F15:F100").Find(strSearch, , xlValues, xlWhole)

ActiveCell.Formula Run-time error 1004

2 Answers2