Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-06-2020, 05:01 PM
leeqiang leeqiang is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Advanced Beginner
I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do

the file:
sum.xlsm


How to use an array to traverse the worksheet and summarize them separately? Why does a dialog box that does not match the type pop up after running the code?
Summarize the number of pieces, quantity, price, and freight in each monthly table. code show as below:


Option Explicit

Sub sumallshs()
Dim i, j, p, q
Dim arr, brr
Dim sh As Worksheet
For Each sh In Worksheets
If Trim(sh.Name) Like "*m" Then
p = sh.Cells.Rows.Count
q = sh.Cells(p, 1).End(xlUp).Row
arr = sh.Range("a2:h" & q)
brr = sh.[a2].Resize(UBound(arr) + 1, UBound(arr, 2) + 1)

If q >= 3 Then
For j = 4 To UBound(brr)
For i = 1 To UBound(brr) - 1
brr(UBound(brr), 3) = "total"
brr(UBound(brr), j) = brr(UBound(brr), j) + brr(i, j)
Next i


Next j
sh.[a2].Resize(UBound(brr), UBound(brr, 2)) = brr
End If
End If
Next sh

end sub




After running the code, the following dialog box pops up
20201007075828.jpg
Reply With Quote
  #2  
Old 10-06-2020, 09:01 PM
Guessed's Avatar
Guessed Guessed is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

You are creating variables without defining what type of data they are. If you define their types, you will be able to more easily spot the errors in your code. Looking at the first time the variables are assigned values, I would deduce the following types.

Dim i as Integer, j as Integer, p as Integer, q as Row

However, you then test q as if it is a number. I expect that would cause the Run-time error you encountered.

Then with the variable arr, you populate it as a Range and then use UBound on it as if it is an array. This might work in Excel's vba but I would not be surprised if it didn't.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 10-06-2020, 09:23 PM
leeqiang leeqiang is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Advanced Beginner
I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Smile

20201007122243.png


go wrong
Reply With Quote
  #4  
Old 10-06-2020, 09:25 PM
leeqiang leeqiang is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Advanced Beginner
I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
You are creating variables without defining what type of data they are. If you define their types, you will be able to more easily spot the errors in your code. Looking at the first time the variables are assigned values, I would deduce the following types.

Dim i as Integer, j as Integer, p as Integer, q as Row

However, you then test q as if it is a number. I expect that would cause the Run-time error you encountered.



thank you!


write the q only,run the code the result as follows:
20201007122431.png
Reply With Quote
  #5  
Old 10-06-2020, 09:54 PM
Guessed's Avatar
Guessed Guessed is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

The overflow is happening because Integer's are not large enough for the total rows on a sheet. Use a larger number type instead. Also the Row was a mistake, that q variable can be a Long too as the line where it is populated returns a number.

Dim i As Long, j As Long, p As Long, q As Long
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #6  
Old 10-06-2020, 10:01 PM
leeqiang leeqiang is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Advanced Beginner
I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

the problem as the same way!

20201007130029.png
Reply With Quote
  #7  
Old 10-06-2020, 10:52 PM
Guessed's Avatar
Guessed Guessed is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

I'm not seeing a type mismatch on my machine so it probably depends on the contents of your worksheets.

Why aren't you using the previously supplied solution of using formulas on the bottom of your tables? See https://www.msofficeforums.com/excel...-each-row.html
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #8  
Old 10-07-2020, 01:42 AM
leeqiang leeqiang is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Advanced Beginner
I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
I'm not seeing a type mismatch on my machine so it probably depends on the contents of your worksheets.

Why aren't you using the previously supplied solution of using formulas on the bottom of your tables? See https://www.msofficeforums.com/excel...-each-row.html





Because the data area of the table is dynamic, the data rows increase at any time. The purpose of writing a program is to automatically summarize the data in the corresponding column to the next row of the data area as the data changes. I don’t know how to rewrite the code in this case for the summation of the previous question?
Reply With Quote
  #9  
Old 10-07-2020, 04:20 AM
Purfleet Purfleet is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Leeqiang

This is why you need to ask the full question at the begining - in your orginal post you said the 15th row and Column K, so i assumed the grid was static.

If you want to work out varible rows and columns try this - there might be neater or nicer ways but you dont need a loop to sum

Sub sumtheall()

Dim LR As Integer
Dim LC As Variant
Dim LRSum As Integer
Dim LCSum As Variant

'Find last row
LR = Cells(Rows.Count, 1).End(xlUp).Row + 1
LRSum = LR - 1

'Find last column
LC = Cells(1, Columns.Count).End(xlToLeft).Column + 1

'Change column to Letter
LCSum = Chr(LC + 64 - 1)
LC = Chr(LC + 64)

'Input Sum
Range("a" & LR & ":" & LC & LR) = "=sum(a1:a" & LRSum & ")"
Range(LC & "1:" & LC & LR) = "=Sum(a1:" & LCSum & "1)"

End Sub
Reply With Quote
  #10  
Old 10-07-2020, 08:20 PM
leeqiang leeqiang is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Advanced Beginner
I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
Leeqiang

This is why you need to ask the full question at the begining - in your orginal post you said the 15th row and Column K, so i assumed the grid was static.

If you want to work out varible rows and columns try this - there might be neater or nicer ways but you dont need a loop to sum

Sub sumtheall()

Dim LR As Integer
Dim LC As Variant
Dim LRSum As Integer
Dim LCSum As Variant

'Find last row
LR = Cells(Rows.Count, 1).End(xlUp).Row + 1
LRSum = LR - 1

'Find last column
LC = Cells(1, Columns.Count).End(xlToLeft).Column + 1

'Change column to Letter
LCSum = Chr(LC + 64 - 1)
LC = Chr(LC + 64)

'Input Sum
Range("a" & LR & ":" & LC & LR) = "=sum(a1:a" & LRSum & ")"
Range(LC & "1:" & LC & LR) = "=Sum(a1:" & LCSum & "1)"

End Sub



Thank you very much for your help!


run the code have same wrong results in the photo as follows:
20201008111050.png




1.the result don't sum the columns of price, quantity,freight


2.when click the button many times,the result produce under the result before.
I want to produce the result under the data range whatever how many times clicked the button.


3.advanced,how to use the code to get rid of each worksheets's name like *m in this workbook


thx again!
Reply With Quote
  #11  
Old 10-07-2020, 08:49 PM
Purfleet Purfleet is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Sorry i didnt see the Sum worksheet embedded in the first post (its normally at the bottom of the post)

So you just want the sum of Price quantity and freight?

First tip - dont merge cells, they will play havoc with formulas and vba
Reply With Quote
  #12  
Old 10-07-2020, 09:18 PM
leeqiang leeqiang is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Advanced Beginner
I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
Sorry i didnt see the Sum worksheet embedded in the first post (its normally at the bottom of the post)

So you just want the sum of Price quantity and freight?

First tip - dont merge cells, they will play havoc with formulas and vba



yes,sum of Price, quantity and freight.
merge cells in order to input the title of table.
Reply With Quote
  #13  
Old 10-07-2020, 10:42 PM
Purfleet Purfleet is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

i know why you merge cells, but they will only cause problems - it is far better to 'center accross selection'

Code:
Sub SumCols()

Dim LR As Integer 'change to Long if your rows can go over 32000

LR = Cells(Rows.Count, 1).End(xlUp).Row ' Last row calculated on the first column

Range("d" & LR + 1) = "=Sum(d2:d" & LR & ")"
Range("f" & LR + 1) = "=Sum(f2:f" & LR & ")"
Range("g" & LR + 1) = "=Sum(g2:g" & LR & ")"


End Sub

1 - This will sum the 3 columns (you sure you want to sum the price?)
2 - The last row is based on column 1, so will always sum to the last row unless you put something like Total in row 13 then the previous total would be included in the next sum
3 - Can you clarify this part?
Attached Files
File Type: xlsm sum_Purfleet.xlsm (62.0 KB, 5 views)
Reply With Quote
  #14  
Old 10-08-2020, 08:58 PM
leeqiang leeqiang is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Advanced Beginner
I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
i know why you merge cells, but they will only cause problems - it is far better to 'center accross selection'

Code:
Sub SumCols()

Dim LR As Integer 'change to Long if your rows can go over 32000

LR = Cells(Rows.Count, 1).End(xlUp).Row ' Last row calculated on the first column

Range("d" & LR + 1) = "=Sum(d2:d" & LR & ")"
Range("f" & LR + 1) = "=Sum(f2:f" & LR & ")"
Range("g" & LR + 1) = "=Sum(g2:g" & LR & ")"


End Sub
1 - This will sum the 3 columns (you sure you want to sum the price?)
2 - The last row is based on column 1, so will always sum to the last row unless you put something like Total in row 13 then the previous total would be included in the next sum
3 - Can you clarify this part?

thx very much,the result is very good!


1.and sum the freight,the date of last columns.
2.can add a cells name is "total",automating add at the same row of sum ,such as column A of the sum row
3.clarify
Reply With Quote
  #15  
Old 10-12-2020, 05:18 PM
leeqiang leeqiang is offline I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Windows 10 I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do Office 2019
Advanced Beginner
I use an array to traverse the monthly worksheet summaries in the workbook, why the display types do
 
Join Date: Aug 2020
Posts: 53
leeqiang is on a distinguished road
Default

thank you! Purfleet and Guessed
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I copy a worksheet and paste values to another new workbook ? DBenz Excel 1 01-26-2019 07:03 AM
Sharing a Workbook that contains array formula link to other workbook Ahmad.rage22@gmail.com Excel 0 03-12-2018 10:15 PM
Outlook 2016 calendar monthly display Herbert Outlook 0 09-22-2017 04:41 AM
How do I populate Worksheets using information entirely from another Worksheet in the same Workbook? BrieDanielle Excel 1 06-18-2016 10:50 AM
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook JaxV Excel 9 11-14-2014 12:25 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:08 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft