Click here to Skip to main content
14,695,617 members
Please Sign up or sign in to vote.
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.

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
Updated 14-Jun-18 0:05am
v2
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"

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:
Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
   
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.
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.[^]
   
Comments
Member 13861149 14-Jun-18 5:07am
   
Thank you
Maciej Los 14-Jun-18 5:08am
   
You're very welcome.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900