Comments by Member 13861149

Member 13861149 14-Jun-18 5:07am View

Thank you

Member 13861149 14-Jun-18 4:59am View

Thank you. Its working now without any errors...

Member 13861149 13-Jun-18 23:25pm View

The new code of ConvertTo() is not returning the exact value. rng currently holds only the range like 6:10 but not taking the characters such as D,E,F etc.(i.e, Previously the value in range was D6:D10,E6:E10 etc but now its 6:10). So the macro entirely goes wrong.

Member 13861149 13-Jun-18 23:10pm View

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

Member 13861149 13-Jun-18 5:19am View

I checked the values each variable is holding. The code works smoothly till column AZ. But when col=53 (i.e BA), rng should be BA6:BA7 but i am getting the value "A[6:A[7" which follows the run time error

Member 13861149 13-Jun-18 5:16am View

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

Member 13861149 13-Jun-18 4:47am View

rng is having values A6 to A10 which is wrong. But all the other variables are holding the values as expected.

Tried the two given code lines also. But still showing the same error.
All these works fine for a single sheet workbook.

Member 13861149 12-Jun-18 22:00pm View

Thank you.... It is working.... I modified the code in order to get the sum from D6, E6 etc in the D3,E3,F3 and so on... While running the macro I am getting Error 1004 and it is pointing to the ActiveSheet.Cells(3, col).Formula = "=SUM(" & rng & ")" line. Please find the code below and 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 G 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
ActiveSheet.Cells(3, col).Formula = "=SUM(" & rng & ")"
End If
Next

Member 13861149 7-Jun-18 5:56am View

Thank you for your reply....
I am getting the sum. But sum of one row is added extra(i.e, i am getting the sum of 11 rows(from G3 to G13, but it should be G3 to G12) if i enter 10 in cell G1).

Comments by Member 13861149 (Top 9 by date)