|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
|
#4
|
|||
|
|||
Quote:
thank you! write the q only,run the code the result as follows: 20201007122431.png |
#5
|
||||
|
||||
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 |
#6
|
|||
|
|||
|
#7
|
||||
|
||||
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 |
#8
|
|||
|
|||
Quote:
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? |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
Quote:
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! |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Quote:
yes,sum of Price, quantity and freight. merge cells in order to input the title of table. |
#13
|
|||
|
|||
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? |
#14
|
|||
|
|||
Quote:
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 |
|
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 |