Error 1004 in VBA while running a macro

Question

0.00/5 (No votes)

See more:

I have a workbook with 4 sheets. I have added a macro in Sheet 3 named Amt to find the sum. But while running the macro, I am getting run time error 1004. While VBA debugging it is pointing to ActiveSheet.Cells(3, col).Formula = "=SUM(" & rng & ")"

Note : The value is accepted in D1,E1..... and based on the value in the cell I am calculating the sum of values based on the input from D6,E6....and total is displayed in D3,E3....

What I have tried:

The code is given below. Please help me to resolve this.

VB

Dim lastCol As Integer
    lastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count

    Dim col As Integer
    For col = 4 To lastCol  'From D until finished
        Dim numRows As Long
        numRows = ActiveSheet.Cells(1, col).Value2
        
        Dim rng As String
        If numRows > 0 Then
          rng = ConvertToLetter(col) & "6:" & ConvertToLetter(col) & CStr(numRows + 2)
            
            'Get the appropriate total in D3,E3,F3.....
            ActiveSheet.Cells(3, col).Formula = "=SUM(" & rng & ")"
        End If
    Next

Posted 12-Jun-18 16:30pm

Member 13861149

Updated 13-Jun-18 23:05pm

Maciej Los

v2

Add a Solution

Comments

Maciej Los 13-Jun-18 2:32am

Have you tried to find out what value stores a rng variable?
Note, there's a constant: "6", so it can't be "D3, E3, F3", but "D6:F3"

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2018-06-12T17:02:00

Solution 1

Quote:
I am getting run time error 1004. While VBA debugging ...

While in debugger, open the 'Local Variables' window and inspect the values of those variables.
It will tell you what your code was doing.
Since your code depend on cells values, we can only guess what is going on without knowing the variables values.

[Update]
The function ConvertToLetter you use is wrong, it need to be rewritten, or use available functions.
Try to use:

VB

Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

Posted 12-Jun-18 17:02pm

Patrice T

Updated 13-Jun-18 22:35pm

v4

Comments

Member 13861149 13-Jun-18 5:16am

The values are all fine except rng. The range is taking correctly till column AZ(col=52). Obviously the next should be taken as BA(col=53), but the value in range is "A[6:A[7" which is wrong. The actual value in rng should be BA6:BA7

Patrice T 13-Jun-18 10:17am

Then you know where is the problem !
What is your code for ConvertToLetter ?

Member 13861149 13-Jun-18 23:10pm

Function ConvertToLetter(iCol As Integer) As String
'https://support.microsoft.com/en-gb/help/833402/how-to-convert-excel-column-numbers-into-alphabetical-characters
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function

Patrice T 14-Jun-18 2:57am

Use Improve question to update your question.
So that everyone can pay attention to this information.

Maciej Los · Answer 2 · 2018-06-13T23:07:00

Solution 2

I'd strongly recommend to read my answer to your previous question: I want a VBA code to find the sum of numbers in a column.[^]

Posted 13-Jun-18 23:07pm

Maciej Los

Comments

Member 13861149 14-Jun-18 5:07am

Thank you

Maciej Los 14-Jun-18 5:08am

You're very welcome.