VBA : Sum Every 22 Rows in a Sheet with Data Not Fully Number

[muhammad susanto]

I am trying to Show MsgBox Sum every 22 rows in column C in a single sheet, starting from C21,C43,C65,C87, etc....till the last cells used.
this problem modify from this post Sum Data Contains String/Mark from Multiple Sheet into Same Cell
my data not fully contains number
this below code not fully working

Sub TextToNumbersSum()
Dim w As Worksheet, S As Double
For Each w In ActiveWorkbook.Worksheets
    For Each c In w.Range("C21....")
        S = S + Replace(Replace(Replace(c.Value, "=", ""), ".", ""), ",", "") + 0
    Next c
Next w
MsgBox Format(S, "#,##0")
End Sub

the problem in range and format number (bold) not correct (i'm use Indonesian format)

here my sample

sum every 22 rows.xlsx

drive.google.com

thank for your helping..

john m

 

[muhammad susanto]

note :
data number can like e.g 788.000 or 7.800.000 or 78.000.000 or 780.000.000 or 7.800.000.000 or 78.000.000.000
the problem in :

Range("C21....")

and

Format(S, "#,##0")

 

[Michael M]

I may be misunderstanding, but does this do what you want on the active sheet...you did ask for "on a single sheet"

Sub TextToNumbersSum()
Dim w As Worksheet, S As Double, lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
S = 0
    For r = 21 To lr Step 22
        Cells(r, 3).Value = Replace(Replace(Replace(Cells(r, 3).Value, "=", ""), ".", ""), ",", "")
        S = S + Cells(r, 3).Value
    Next r
MsgBox S
End Sub

 

[muhammad susanto]

I may be misunderstanding, but does this do what you want on the active sheet...you did ask for "on a single sheet"

Sub TextToNumbersSum()
Dim w As Worksheet, S As Double, lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
S = 0
    For r = 21 To lr Step 22
        Cells(r, 3).Value = Replace(Replace(Replace(Cells(r, 3).Value, "=", ""), ".", ""), ",", "")
        S = S + Cells(r, 3).Value
    Next r
MsgBox S
End Sub

hi michael after run your code something happen:
1. my number exactly in cell every 22 rows i mean in cell C21, etc...have gone mark=,.. i want mark =,. still keep display
2. the result summing is not correct, 23754000000 (over 3 digits 0) should be 237.540.000, i want use format number, please see my problem about format number

 

[muhammad susanto]

i have try to modify this code below

Sub TextToNumbersSum()
Dim w As Worksheet, S As Double
lr = Cells(Rows.Count, "C").End(xlUp).Row
S = 0
    For r = 21 To lr Step 22
        Cells(r, 3).Value = Replace(Replace(Replace(Cells(r, 3).Value, "=", ""), ".", ""), ",", "")
        S = S + Cells(r, 3).Value
    Next r
MsgBox Format(S, "#,##0")
End Sub

for summing is correct but the mark of number is gone like mark =,comma,dot, not dissapear again
please, see my attach photo(before and after run macro)..
i want the mark is not gone

 

[muhammad susanto]

this code work properly. how to adapt this code can work to SUM every 22 rows in col. C and total sum Show MsgBox
i confused about this.

Sub test()
Dim R As Range, Col As Range, c As Range, S As Double, D As Double
Set R = Range("G5:L8")
Application.ScreenUpdating = False
For Each Col In R.Columns
    For Each c In Col.Cells
        If InStrRev(c.Value, ",") = Len(c.Value) - 2 Then ' there's a decimal amount
            D = D + Right(c.Value, 2) + 0
            S = S + Replace(Replace(Replace(Left(c.Value, Len(c.Value) - 3), "=", ""), ".", ""), ",", "") + 0
        Else
            S = S + Replace(Replace(Replace(c.Value, "=", ""), ".", ""), ",", "") + 0
        End If
    Next c
    With Cells(Rows.Count, Col.Column).End(xlUp).Offset(1, 0)
        .Value = S + D / 100
        .NumberFormat = "#,##0.00"
    End With
D = 0: S = 0
Next Col
Application.ScreenUpdating = True
End Sub

 

[JoeMo]

this code work properly. how to adapt this code can work to SUM every 22 rows in col. C and total sum Show MsgBox
i confused about this.

Sub test()
Dim R As Range, Col As Range, c As Range, S As Double, D As Double
Set R = Range("G5:L8")
Application.ScreenUpdating = False
For Each Col In R.Columns
    For Each c In Col.Cells
        If InStrRev(c.Value, ",") = Len(c.Value) - 2 Then ' there's a decimal amount
            D = D + Right(c.Value, 2) + 0
            S = S + Replace(Replace(Replace(Left(c.Value, Len(c.Value) - 3), "=", ""), ".", ""), ",", "") + 0
        Else
            S = S + Replace(Replace(Replace(c.Value, "=", ""), ".", ""), ",", "") + 0
        End If
    Next c
    With Cells(Rows.Count, Col.Column).End(xlUp).Offset(1, 0)
        .Value = S + D / 100
        .NumberFormat = "#,##0.00"
    End With
D = 0: S = 0
Next Col
Application.ScreenUpdating = True
End Sub

muhammad, I believe you got this code from me here: Sum Data Contains String/Mark from Multiple Sheet into Same Cell

Why not ask for help adapting it in that thread instead of posting it here with no attribution??

 

[muhammad susanto]

muhammad, I believe you got this code from me here: Sum Data Contains String/Mark from Multiple Sheet into Same Cell

Why not ask for help adapting it in that thread instead of posting it here with no attribution??

hi JoeMo, you're right but i just think your code related in this title so this main actually problem. and i hope someone would help me cause i confused it,
about for this i'm so sorry, thank for your helping
related this posting Sum Data Contains String/Mark from Multiple Sheet into Same Cell

 

[muhammad susanto]

across posting too from Showing Only MsgBox "Total=...???

 

[JoeMo]

hi JoeMo, you're right but i just think your code related in this title so this main actually problem. and i hope someone would help me cause i confused it,
about for this i'm so sorry, thank for your helping
related this posting Sum Data Contains String/Mark from Multiple Sheet into Same Cell

Thanks for your apology. I don't have any data to test this on so try it on a copy of your data. It sums every 22nd row in column C and returns the sum one cell below the last data cell in column C.

Sub TextToNumbersSum1()
Dim w As Worksheet, S As Double, D As Double
lr = Cells(Rows.Count, "C").End(xlUp).Row
S = 0
    For r = 21 To lr Step 22
        If InStrRev(Cells(r, "C").Value, ",") = Len(Cells(r, "C").Value) - 2 Then ' there's a decimal amount
            D = D + Right(Cells(r, "C").Value, 2) + 0
            S = S + Replace(Replace(Replace(Left(Cells(r, "C").Value, Len(Cells(r, "C").Value) - 3), "=", ""), ".", ""), ",", "") + 0
        Else
            S = S + Replace(Replace(Replace(Cells(r, "C").Value, "=", ""), ".", ""), ",", "") + 0
        End If
    Next r
    With Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
        .Value = S + D / 100
        .NumberFormat = "#,##0.00"
    End With
End Sub