PDA

View Full Version : [SOLVED] How do I get the total from all array elements



elmnas
09-21-2015, 02:03 AM
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
09-21-2015, 02:28 AM
msgbox application.sum(intersect(sheet1.usedrange,sheet1.columns(14).resize(,11)))

elmnas
09-21-2015, 02:44 AM
msgbox application.sum(intersect(sheet1.usedrange,sheet1.columns(14).resize(,11)))

Doesn't work.
Object required.

elmnas
09-21-2015, 02:49 AM
I used instead but it still wrong result

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

pike
09-21-2015, 04:51 AM
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
09-21-2015, 05:55 AM
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
09-21-2015, 05:56 AM
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
09-21-2015, 06:33 AM
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
09-21-2015, 07:12 AM
Hi

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

Thanks

SamT
09-21-2015, 07:39 AM
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
09-21-2015, 07:42 AM
thanks Samt! Posted the question hope one of you genius's can answer it

elmnas
09-22-2015, 12:15 AM
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
09-22-2015, 12:19 AM
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
09-22-2015, 12:47 AM
Please Elmnas, do something yourself: testing, analysing, varying something in the code.

pike
09-22-2015, 01:24 AM
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
09-22-2015, 02:21 AM
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
09-22-2015, 02:46 AM
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
09-22-2015, 02:55 AM
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
09-22-2015, 03:23 AM
What is the use of the empty element in array ???

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

elmnas
09-22-2015, 04:55 AM
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
09-22-2015, 05:47 AM
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
09-22-2015, 06:27 AM
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
09-22-2015, 09:30 AM
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
09-22-2015, 01:37 PM
???????????

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
09-22-2015, 02:05 PM
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
09-22-2015, 10:43 PM
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
09-22-2015, 11:35 PM
???????????

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
09-22-2015, 11:54 PM
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.