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


14438

[Kenneth Hobs]

You lost me elmnas.

To help us help you the most, it is better to attach a file rather than a screen shot. Click the Go Advanced button in lower right of a reply box and then click the paperclip icon in the toolbar and browse to your file.

You summed valued in AB for each row include column Q values. Your array does not include "Q". Not using VBA to skip Q for AB2 =SUM(N2:P2,R2:Y2). Fill or copy down as needed. To sum AB then, =Sum(AB:AB)

[elmnas]

You lost me elmnas.

To help us help you the most, it is better to attach a file rather than a screen shot. Click the Go Advanced button in lower right of a reply box and then click the paperclip icon in the toolbar and browse to your file.

You summed valued in AB for each row include column Q values. Your array does not include "Q". Not using VBA to skip Q for AB2 =SUM(N2:P2,R2:Y2). Fill or copy down as needed. To sum AB then, =Sum(AB:AB)


I finally made a part what I am looking for I need know to sum the total for each cell


Sub myAr()


Dim mystring As Integer

For x = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).row
Dim j As Integer
For j = 14 To 25

mystring = Cells(x, j).Value

Next j

' I for each cell above I get a value I need now to sum all the values to a total and msgbox here for each row.
Next x


End Sub



could you help me? :)

[Paul_Hossler]

Do you want

a. The sum of each individual column below that column, AND

b. The sum of each individual row to the right of that row, AND

c. The sum of the column with row totals from b below the row total column?

[jolivanes]

???????????

Sub Like_This_Maybe()
Dim lr As Long, ttl As Double, i As Long, j As Long, colArr
lr = Cells(Rows.Count, "N").End(xlUp).Row
colArr = Array("N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y")
For i = 2 To lr
ttl = 0
For j = LBound(colArr) To UBound(colArr)
ttl = ttl + Range(colArr(j) & i).Value
Next j
Range("AB" & i) = ttl
Next i
End Sub

[Kenneth Hobs]

Sub ken()
Dim rn As Long, i As Long
rn = Intersect(ActiveSheet.UsedRange, Columns("N:Y")).Rows.Count
For i = 2 To rn
With Range("AB" & i)
.Formula = "=Sum(N" & i & ":Y" & i & ")"
MsgBox .Address & .Formula & "=" & .Value2
End With
Next i
End Sub

[jolivanes]

elmnas
In your Post #4
If instead of changing snb's solution to this

MsgBox Application.Sum(Intersect(ActiveSheet.UsedRange, ActiveSheet.UsedRange.Columns(14).Resize(, 11)))
I think the following would have given you the desired result.

MsgBox Application.Sum(Intersect(Sheet1.UsedRange, Sheet1.Rows(2).Columns(14).Resize(, 11)))


So for all rows, you could use something like this if you want to continue with that code.



Sub snb_modified()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "N").End(xlUp).Row
For i = 2 To lr
Range("AB" & i) = Application.Sum(Intersect(ActiveSheet.UsedRange, ActiveSheet.Rows(i).Columns(14).Resize(, 11)))
Next i
End Sub

[elmnas]

???????????

Sub Like_This_Maybe()
Dim lr As Long, ttl As Double, i As Long, j As Long, colArr
lr = Cells(Rows.Count, "N").End(xlUp).Row
colArr = Array("N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y")
For i = 2 To lr
ttl = 0
For j = LBound(colArr) To UBound(colArr)
ttl = ttl + Range(colArr(j) & i).Value
Next j
Range("AB" & i) = ttl
Next i
End Sub

finally Jolivanes a big thanks to you!

this is exactly what I am was trying to make.

[jolivanes]

There are quite a few other solutions or near solutions.
Anyway, as long as you got what you needed, we're all happy.
Good luck.