How do I get the total from all array elements

[elmnas]

Hello people,

I have made following code it is an array that loop through certain columns and each row,

How do I sum all all cells result to an total?

Sub myABArr()




Dim myStrArray As Variant




myStrArray = Array("N", "O", "P", "R", "S", "T", , "U", "V", "W", "X", "Y")
    
    For i = LBound(myStrArray) To UBound(myStrArray)
    
    myCol = myStrArray(i)
        For x = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
            
        
              '  MsgBox Cells(x, mycol).Value ' I can get any numbers example 134
  
         
        Next x
    
    Next i




End Sub

thank you in advance.

[snb]

msgbox application.sum(intersect(sheet1.usedrange,sheet1.columns(14).resize(,11)))

[elmnas]

msgbox application.sum(intersect(sheet1.usedrange,sheet1.columns(14).resize(,11)))

Doesn't work.
Object required.

[elmnas]

I used instead but it still wrong result

MsgBox Application.Sum(Intersect(ActiveSheet.UsedRange, ActiveSheet.UsedRange.Columns(14).Resize(, 11)))

[pike]

Hi elmnas,
with your code use

Sub myABArr() 
    Dim myStrArray As Variant 
    myStrArray = Array("N", "O", "P", "R", "S", "T", , "U", "V", "W", "X", "Y") 
    For i = LBound(myStrArray) To UBound(myStrArray) 
        myCol = myStrArray(i) 
        For x = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row 
           mysum =mysum+ Cells(x, mycol).Value ' I can get any numbers example 134
        Next x 
    Next i 
    msgbox mysum 
 End Sub

[Kenneth Hobs]

I don't see why you needed an extra null value in your array.

As you found out using snb's code, changing Sheet1 to ActiveSheet fixed the object issue. If you need that formula on all your sheets, ActiveSheet object is best.

To compute the "correct" result, we can see that snb missed the exclusion of column Q. We can simply substract it. Using his method, you should also note that if you have any value in those column's first row, an "incorrect" value will be found as well.

Sub ken()  
  With ActiveSheet
    MsgBox Application.Sum(Intersect(.UsedRange, .Columns(14).Resize(, 11))) _
      - Application.Sum(Intersect(.UsedRange, .Columns(17)))  '17=Column Q
     End With
End Sub

[Teeroy]

Drop the UsedRange in the second region of the intersect. So;

MsgBox Application.Sum(Intersect(ActiveSheet.UsedRange, ActiveSheet.UsedRange.Columns(14).Resize(, 11)))

becomes

MsgBox Application.Sum(Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(14).Resize(, 11)))

[SamT]

Sub SamT()

Dim myStrArray As Variant
Dim Col As String
Dim LR As Long
Dim Result As Double

  myStrArray = Array("N", "O", "P", "R", "S", "T", , "U", "V", "W", "X", "Y")
  LR = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
  
  For i = LBound(myStrArray) To UBound(myStrArray)
    Col = myStrArray(i)
    If Not Col = "" Then 'Handles Null Value in array
      Result = Result + Sum(Range(Col & "2" & ":" & Col & LR))
    End If
  Next
  
  MsgBox Result
End Sub

[freetime]

Hi

I cannot post a new thread how do I do this?

Thanks

[SamT]

Select the forum about the application, (Excel, Word, Access, etc,) or that best fits your question, (Introductions, SQL, Project Assistance, etc.) At the bottom of the Threads list, clivk on +Post New Thread

[freetime]

thanks Samt! Posted the question hope one of you genius's can answer it

[elmnas]

Hi elmnas,
with your code use

Sub myABArr() 
    Dim myStrArray As Variant 
    myStrArray = Array("N", "O", "P", "R", "S", "T", , "U", "V", "W", "X", "Y") 
    For i = LBound(myStrArray) To UBound(myStrArray) 
        myCol = myStrArray(i) 
        For x = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row 
           mysum =mysum+ Cells(x, mycol).Value ' I can get any numbers example 134
        Next x 
    Next i 
    msgbox mysum 
 End Sub

incompatible types error 13

Doesn't work.

[elmnas]

Sub SamT()

Dim myStrArray As Variant
Dim Col As String
Dim LR As Long
Dim Result As Double

  myStrArray = Array("N", "O", "P", "R", "S", "T", , "U", "V", "W", "X", "Y")
  LR = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
  
  For i = LBound(myStrArray) To UBound(myStrArray)
    Col = myStrArray(i)
    If Not Col = "" Then 'Handles Null Value in array
      Result = Result + Sum(Range(Col & "2" & ":" & Col & LR))
    End If
  Next
  
  MsgBox Result
End Sub

sub or the function is not defined.

[snb]

Please Elmnas, do something yourself: testing, analysing, varying something in the code.

[pike]

Hello elmnas,
Not knowing the content of the cells

Option Explicit
Sub Test()
    Dim myStrArray As Variant
    Dim Col As String
    Dim xRow As Long
    Dim Result As Double
    Dim iCount As Long
    
    myStrArray = Array("N", "O", "P", "R", "S", "T", , "U", "V", "W", "X", "Y")
    For iCount = LBound(myStrArray) To UBound(myStrArray)
        If Not IsError(myStrArray(iCount)) Then
            For xRow = 2 To ActiveSheet.Cells(Rows.Count, myStrArray(iCount)).End(xlUp).Row
                Result = Result + IIf(IsNumeric(Cells(xRow, myStrArray(iCount)).Value), Cells(xRow, myStrArray(iCount)).Value, 0)
            Next xRow
        End If
    Next iCount
    MsgBox Result
End Sub

[elmnas]

Hello elmnas,
Not knowing the content of the cells

Option Explicit
Sub Test()
    Dim myStrArray As Variant
    Dim Col As String
    Dim xRow As Long
    Dim Result As Double
    Dim iCount As Long
    
    myStrArray = Array("N", "O", "P", "R", "S", "T", , "U", "V", "W", "X", "Y")
    For iCount = LBound(myStrArray) To UBound(myStrArray)
        If Not IsError(myStrArray(iCount)) Then
            For xRow = 2 To ActiveSheet.Cells(Rows.Count, myStrArray(iCount)).End(xlUp).Row
                Result = Result + IIf(IsNumeric(Cells(xRow, myStrArray(iCount)).Value), Cells(xRow, myStrArray(iCount)).Value, 0)
            Next xRow
        End If
    Next iCount
    MsgBox Result
End Sub

I believe I have explained wrong cause now it loops each row and sum all cells for the column then next column

I want the function to take every column and the cells same row and make a total then go to next row and to the same

[pike]

no problem ..try

Option Explicit
Sub Test()
    Dim myStrArray As Variant
    Dim Col As String
    Dim xRow As Long
    Dim Result As Double
    Dim iCount As Long
    
    myStrArray = Array("N", "O", "P", "R", "S", "T", , "U", "V", "W", "X", "Y")
    For iCount = LBound(myStrArray) To UBound(myStrArray)
        If Not IsError(myStrArray(iCount)) Then
            For xRow = 2 To ActiveSheet.Cells(Rows.Count, myStrArray(iCount)).End(xlUp).Row
                Result = Result + IIf(IsNumeric(Cells(xRow, myStrArray(iCount)).Value), Cells(xRow, myStrArray(iCount)).Value, 0)
            Next xRow
        End If
    MsgBox Result
    Result = 0
    Next iCount
End Sub

[pike]

or

Option Explicit
Sub Test()
    Dim myStrArray As Variant
    Dim Col As String
    Dim xRow As Long
    Dim Result As Double
    
    For Each myStrArray In Array("N", "O", "P", "R", "S", "T", , "U", "V", "W", "X", "Y")
        If Not IsError(myStrArray) Then
            For xRow = 2 To ActiveSheet.Cells(Rows.Count, myStrArray).End(xlUp).Row
                Result = Result + IIf(IsNumeric(Cells(xRow, myStrArray).Value), Cells(xRow, myStrArray).Value, 0)
            Next xRow
        End If
    MsgBox Result
    Result = 0
    Next
    
End Sub

[snb]

What is the use of the empty element in array ???

Array("N", "O", "P", "R", "S", "T", , "U", "V", "W", "X", "Y")

[elmnas]

Here is a result I want,

sum all cells for each row and following columns("N", "O", "P", "R", "S", "T", , "U", "V", "W", "X", "Y") in AB


yqAdIhY.jpg