CODEWITHSUNDEEP
vbaexcel
Cindy
Cindy

Reputation: 27

VBA Concatenate & Sum

I need help with writing a macro that can concatenate numbers on SHEET2 (that may look like this):

         A     B     C     D
 1       4     3     2     1
 2       5     6
 3       7     8     0

And come out with a sum on SHEET1 like this (4321+56+780=5157):

         A
 1     5157

I have no macro written so far, so any help will be greatly appreciated!

Upvotes: 0

Views: 644

Answers (6)

Nitish
Nitish

Reputation: 341

Sub concadinate_sum()
Dim LstRow As Long
Dim LstCol As Long, c As Variant
Dim i As Long
Dim j As Long

Sheets("Sheet2").Activate

LstRow = Sheets(2).Range("A1", Range("A1").End(xlDown).Address).Count

For i = 1 To LstRow
    LstCol = Sheets(2).Range("A" & i, Range("A" & i).End(xlToRight).Address).Count
    For j = 0 To LstCol
       c = c & Sheets("Sheet2").Range("A" & i).Offset(0, j).Value
    Next j
    Sheets("Sheet1").Range("A1").Value = CInt(c) + CInt(Sheets("Sheet1").Range("A1").Value)
    c = ""
Next i
End Sub

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166755

Use Ctrl+Shift+Enter:

=SUM(CONCATENATE(A1:A3,B1:B3,C1:C3,D1:D3)*1)

Ignoring errors:

=SUM(IFERROR(CONCATENATE(B2:B4,C2:C4,D2:D4,E2:E4)*1,0))

Upvotes: 3

andrewgu
andrewgu

Reputation: 1642

This should be exactly what you asked for. An Excel VBA script that concatenates all the values (forever) in each row in Sheet2, adds the row totals together, and displays them in Sheet1. I have tested it with your data set, and it works correctly.

Sub concatSum()
    Dim row As Integer
    Dim rowVal As String
    Dim col As Integer
    Dim colVal As String
    row = 1
    col = 1
    totalVal = 0
    rowVal = ""
    With Worksheets("Sheet2")
        Do While Len(.Cells(row, 1).Value) > 0
            colVal = .Cells(row, col).Value
            Do While Len(colVal) > 0
                rowVal = rowVal & colVal
                col = col + 1
                colVal = .Cells(row, col).Value
            Loop
            col = 1
            row = row + 1
            totalVal = totalVal + rowVal
            rowVal = ""
        Loop
    End With
    Worksheets("Sheet1").Cells(1, 1).Value = totalVal
End Sub

Upvotes: 3

Chicago Excel User
Chicago Excel User

Reputation: 263

Here is VBA solution

Sub AddThem()

Dim sh2 As Worksheet
Set sh2 = ThisWorkbook.Worksheets("Sheet2")

lr = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row
tot = 0
For i = 1 To lr

tot = tot + (Cells(i, 1) & Cells(i, 2) & Cells(i, 3) & Cells(i, 4)) + 0

Next i

Range("A1") = tot

End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96771

No VBA needed, use:

=VALUE(CONCATENATE(A1,B1,C1,D1))+VALUE(CONCATENATE(A2,B2,C2,D2))+VALUE(CONCATENATE(A3,B3,C3,D3))

and change the references to any sheet you like.

enter image description here

Upvotes: 1

Chicago Excel User
Chicago Excel User

Reputation: 263

You know you can do this without a macro, right?

=SUMPRODUCT((A1:A3&B1:B3&C1:C3&D1:D3)+0)

Upvotes: 7

Related Questions