Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
add valuew to new sheet depens on month
I have a sheet that look like this:
col a col b col c col d invoice No day Amount 1 205 2-apr-03 23.44 2 206 2-apr-03 55.18 3 207 5-Απρ-03 12.45 4 208 5-Απρ-03 22.89 5 209 5-Απρ-03 33.76 6 210 5-Απρ-03 55.43 7 211 5-Απρ-03 33.22 8 212 5-Απρ-03 19.76 9 213 9-Απρ-03 22.45 10 214 9-Απρ-03 55.88 11 215 9-Απρ-03 55.77 And i want to transfer to a next sheet the total amound per day and the invoice No (from No - to No) For example starting from row 1 at new sheet the result must be like this col a col b col c col d col e from to day Amount 1 205 206 2-apr-03 78,62 2 207 212 5-apr-03 177,51 3 213 215 9-Απρ-03 134,10 The macro will be fired from a userform button After select the month from a userform.combobox. (This part of code is ready) I have made the form and the combobox with months but i am stuck with the first part i describe in the beginning. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add valuew to new sheet depens on month
The following worked for me after changing "Απρ" to "Apr".
HTH, Merjet Sub Macro1() Dim iHi As Integer Dim iLo As Integer Dim iRow1 As Integer Dim iRow2 As Integer Dim dAmt As Double Dim dtDay As Date Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = Sheets("Sheet1") Set ws2 = Sheets("Sheet2") ws2.Cells.Clear ws2.Cells(1, 2) = "From" ws2.Cells(1, 3) = "To" ws2.Cells(1, 4) = "Day" ws2.Cells(1, 5) = "Amount" iRow1 = 2 iRow2 = 2 Do Until ws1.Cells(iRow1, 1) = "" dtDay = ws1.Cells(iRow1, 3) iLo = ws1.Cells(iRow1, 2) Do Until ws1.Cells(iRow1, 3) < dtDay iHi = ws1.Cells(iRow1, 2) dAmt = dAmt + ws1.Cells(iRow1, 4) iRow1 = iRow1 + 1 Loop ws2.Cells(iRow2, 1) = iRow2 - 1 ws2.Cells(iRow2, 2) = iLo ws2.Cells(iRow2, 3) = iHi ws2.Cells(iRow2, 4) = dtDay ws2.Cells(iRow2, 5) = dAmt dAmt = 0 iRow2 = iRow2 + 1 Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel inventory Sheet for workday month | Excel Worksheet Functions | |||
update sheet name in conditional sum every month | Excel Discussion (Misc queries) | |||
formatting end of month on a monthly sheet | Excel Worksheet Functions | |||
how do I print 12 month calender in one sheet | Excel Discussion (Misc queries) | |||
transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) |