Forum Discussion
Excel formula or VBA script
I have an excel spreadsheet that I am doing grouping by rows function, then Subtotal function based off of invoice numbers (column A). In the total rows, I am needing more information carried down from the row above the total column. Is there a way to do this?
Example:
| Invoice Number | Client Code | Invoice Date | Total Charges | Total Expenses | Total Amount Due | COMPANY | CUSTOMER | LOCATION | INVC_PREFIX | CUSTOMER NAME |
| 080522-1411594 | UHC-MR | 44778.59141 | 600 | 56.88 | 656.88 | 66 | 1008 | ATL66 | UR | Example customer name |
| 080522-1411594 Total | 13875 | 3537.69 | 17412.69 |
I am needing the Company, Customer, Location, and Invc_Prefix to be in the total row as well.
This spreadsheet has over 100 different invoice numbers, so I would need to do this at each row that has the "Total" after the invoice number in Column A.
Thank you for your assistance.
Alternatively: select G1:J57.
Press Ctrl+G or F5 to activate the Go To dialog.
Click Special...
Select Blanks.
Click OK.
G3 should now be the active cell in the selection.
Enter the formula =G2 and press Ctrl+Enter to propagate the formula to all blank cells in the selection.
12 Replies
Sub fill() Dim i As Long Dim j As Long j = Range("F" & Rows.Count).End(xlUp).Row For i = 1 To j If Cells(i, 7).Value = "" Then Range(Cells(i, 7), Cells(i, 10)).Value = Range(Cells(i - 1, 7), Cells(i - 1, 10)).Value Else End If Next i End SubYou can try these lines of code. In the attached file you can click the button in cell L2 to run the macro.
OliverScheurich - Thank you! I will try this out.
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- https://ascensus.box.com/s/dhniry5epou1own9mn5vaicowcen1zsa
Here is the box drive path for the file. Please let me know if you can get to it or not.
Thank you,No, I can't access the file, sorry. It requires a login.
