• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro for copy-and-paste into blank cells from cells directly below

Andrew Kedem

New Member
My name is Andrew. It’s been about 5 years since I’ve had a question for this awesome user group. Since then, I’ve moved from the Middle East and landed in the Pacific Northwest in the USA. And from high tech to a firm helping the elderly with their finances.

I pull an invoice report in Excel from proprietary software that I need to manipulate before importing into QuickBooks.

Please help me create a macro to do the following (sample file attached):

For any row in which cells in column E, L and M are blank, I need to copy the data from the cells in the row directly below, also of cells in column E, L and M.


Looking at the attached file, Rows 2, 11, 26, 37, 87 and 107 have no data in cells in columns E, L or M.

These rows where those cells are empty are highlighted in (blue/gray).


Example:

Row 2, column E should have Row 3, column E’s data: Crowe, Misae

Row 2, column L should have Row 3, column L’s data: 26

Row 2, column M should have Row 3, column M’s data: Administrative – Client – time


Who could write a macro that will copy and paste the data in the empty cells (column E, L and M) into the empty cells directly above?

More detail:
If 2E (Row 2, column E) is blank, then copy 3E and paste in 2E
If 2L is blank, then copy 3L and paste in 2L
If 2M is blank, then copy 3M and paste in 2M
etc., doing the copy-and-pasting for all blank cells in columns E, L and M.


I appreciate any and all help.
Warmly,
Andrew


Short Example:
 

Attachments

Fluff13

Active Member
How about
Code:
Sub AndrewKedem()
  Dim Lr As Long
 
  Lr = Range("A" & Rows.Count).End(xlUp).Row
  With Intersect(Rows("1:" & Lr), Range("E:E,L:M"))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      .Value = .Value
  End With
End Sub
 

Andrew Kedem

New Member
Thank you for you quick and detailed reply.
I forgot to mention I'm new to creating macros.
How do I enter the macro you wrote in Excel?
Where do I type each part/aspect of the macro?
 

Andrew Kedem

New Member
I've opened the macro editor and pasted the macro you wrote.
I'm getting syntax errors.

Do I copy from "Sub AndrewKedem()" through "End Sub"?

Do I change the name "AndrewKedem()" to the file name?
 

Andrew Kedem

New Member
I'd been in LibreOffice Calc. I just entered the Macro you wrote into Excel.
When I ran the macro, it replaced all cells in columns E & M with the actual full name of client.

Only column E should have the full name.

Column L was populated with "Full Name", the Column Title for Column E.
Column L, where blank, should be populated with data (number) from cell in Column L just below blank cell.

Column M should be populated with data (item name) from cell in Column M just below blank cell.
 

Fluff13

Active Member
Ok, how about
Code:
Sub AndrewKedem()
  Dim Lr As Long
 
  Lr = Range("A" & Rows.Count).End(xlUp).Row
  With Range("E2:E" & Lr)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      .Value = .Value
  End With
  With Range("L2:M" & Lr)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      .Value = .Value
  End With
End Sub
 

Fluff13

Active Member
Having seen your other post https://chandoo.org/forum/threads/what-formula-to-use-find-cells-w-specific-word-and-copy-paste-word-in-other-cell.41212/

I'm not sure the code will do what you want, try this instead
Code:
Sub AndrewKedem()
  Dim Lr As Long
 
  Lr = Range("A" & Rows.Count).End(xlUp).Row
  With Range("M2:M" & Lr)
      .Value = Evaluate(Replace("if(@<>"""",@,if(isnumber(search(""total""," & .Offset(, -12).Address & ")),""Total"",""""))", "@", .Address))
  End With
  With Range("L2:L" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 1).Address & "=""Total"",""=r[-1]c"",""""))", "@", .Address))
      .Value = .Value
  End With
  With Range("E2:E" & Lr)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      .Value = .Value
  End With
  With Range("L2:M" & Lr)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      .Value = .Value
  End With
End Sub
 

Andrew Kedem

New Member
Absolutely amazing! You did it! Thank you so much! You addressed the two major issues I had in your one macro.

Is it acceptable to give cash tips in this forum? If so, please give me your contact info to andrew at 21stCenturyCareSolutions dot com.
Have a great day.
Andrew
 

Andrew Kedem

New Member
Having seen your other post https://chandoo.org/forum/threads/what-formula-to-use-find-cells-w-specific-word-and-copy-paste-word-in-other-cell.41212/

I'm not sure the code will do what you want, try this instead
Code:
Sub AndrewKedem()
  Dim Lr As Long

  Lr = Range("A" & Rows.Count).End(xlUp).Row
  With Range("M2:M" & Lr)
      .Value = Evaluate(Replace("if(@<>"""",@,if(isnumber(search(""total""," & .Offset(, -12).Address & ")),""Total"",""""))", "@", .Address))
  End With
  With Range("L2:L" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 1).Address & "=""Total"",""=r[-1]c"",""""))", "@", .Address))
      .Value = .Value
  End With
  With Range("E2:E" & Lr)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      .Value = .Value
  End With
  With Range("L2:M" & Lr)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
      .Value = .Value
  End With
End Sub

Hi. I've been working with my source Excel file and your macro for the past couple of weeks. Would you please help me solve the following issues I'm facing?
In order to use the file, in all rows with data, I need values to appear in columns E, L and M.
I think the only differences with this file are that I deleted the very last row of the spreadsheet, which showed a total sum of all invoice amounts, and added additional columns. The final “totals” row was unnecessary and negatively affected my import.
I deleted “Totals” rows, i.e., rows with invoice totals. Now we only have line items, invoice lines each with a unique SKU (item), and we have SKU (item) totals, which are really Subtotals lines.
When I run the macro you wrote on the sample, attached file, in the subtotal rows (for example, Row 6), in Column M, the word "Total" appears. I only want the word "Total" to appear in Invoice totals (for example, Row 33).
My main issue all seems to relate to Subtotal rows (for example, on “Data for Macro” spreadsheet, rows 4, 7, 11, 14, etc.). Here are my issues with Subtotal rows:
1) I need Subtotal rows to have “Subtotal” appear in the SKU (item), Column M.
2) Could you populate Column R, “Description,” with the word “Subtotal” and the SKU from the line above? For example, Row 11 in the “Data for Macro” spreadsheet is a subtotal row. The row above is “Care Management/Medical - time”. Could you have the following appear in Column R of the subtotal row?
Subtotal Care Management/Medical - time
(That would be “subtotal” + the SKU from line above.)

3) SKU (Item) header rows, such as rows 2, 5, 8, etc., populate Column M with data (item name) from cell in Column M just below blank cell. That is correct. However, Subtotal rows need to populate Column M with data from cell in Column M just above the blank cell. Above, not below.
The attached worksheet, “Data for Macro”, is the raw spreadsheet that I’ll use with your macro.
The other worksheet, “Desired Output”, is a sample of how I want the original to appear after running your macro.
 

Attachments

Fluff13

Active Member
How about
Code:
Sub AndrewKedem()
   Dim Lr As Long
  
   Lr = Range("A" & Rows.Count).End(xlUp).Row
   With Range("M2:M" & Lr)
      .Value = Evaluate(Replace("if(@<>"""",@,if(isnumber(search(""total""," & .Offset(, -12).Address & ")),""Subtotal"",""""))", "@", .Address))
   End With
   With Range("L2:L" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 1).Address & "=""Subtotal"",""=r[-1]c"",""""))", "@", .Address))
      .Value = .Value
   End With
   With Range("R2:R" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, -5).Address & "=""Subtotal"",""=rc[-5]&"""" """"&r[-1]c[-5]"",""""))", "@", .Address))
      .Value = .Value
   End With
   With Range("E2:E" & Lr)
       .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
       .Value = .Value
   End With
   With Range("L2:M" & Lr)
       .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
       .Value = .Value
   End With
End Sub
 

Andrew Kedem

New Member
Hi. Your revised macro is amazing - it fills in full name, invoice number and SKU(item), enters "subtotal" in the SKU field for subtotal rows, and even, delightfully, puts the subtotal+SKU in the description line. WOW!

There's one issue that I'd appreciate you tweak in another revision of the macro:
In the "Full Name" field, Column E, in each last subtotal line for each invoice, the Full Name is drawn from the next invoice (row below) instead of the current invoice for which it is a subtotal row for (row above). This results in the wrong name appearing on the last subtotal lines.

Please let me know if I'm not being clear. If I can't give you a cash tip, how about a gift certificate for something special as a token of gratitude?
 

Fluff13

Active Member
Try
Code:
Sub AndrewKedem()
   Dim Lr As Long
  
   Lr = Range("A" & Rows.Count).End(xlUp).Row
   With Range("M2:M" & Lr)
      .Value = Evaluate(Replace("if(@<>"""",@,if(isnumber(search(""total""," & .Offset(, -12).Address & ")),""Subtotal"",""""))", "@", .Address))
   End With
   With Range("L2:L" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 1).Address & "=""Subtotal"",""=r[-1]c"",""""))", "@", .Address))
      .Value = .Value
   End With
   With Range("R2:R" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, -5).Address & "=""Subtotal"",""=rc[-5]&"""" """"&r[-1]c[-5]"",""""))", "@", .Address))
      .Value = .Value
   End With
   With Range("E2:E" & Lr)
       .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 8).Address & "=""Subtotal"",""=r[-1]c"",""=r[1]c""))", "@", .Address))
       .Value = .Value
   End With
   With Range("L2:M" & Lr)
       .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
       .Value = .Value
   End With
End Sub
 

Andrew Kedem

New Member
Try
Code:
Sub AndrewKedem()
   Dim Lr As Long
 
   Lr = Range("A" & Rows.Count).End(xlUp).Row
   With Range("M2:M" & Lr)
      .Value = Evaluate(Replace("if(@<>"""",@,if(isnumber(search(""total""," & .Offset(, -12).Address & ")),""Subtotal"",""""))", "@", .Address))
   End With
   With Range("L2:L" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 1).Address & "=""Subtotal"",""=r[-1]c"",""""))", "@", .Address))
      .Value = .Value
   End With
   With Range("R2:R" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, -5).Address & "=""Subtotal"",""=rc[-5]&"""" """"&r[-1]c[-5]"",""""))", "@", .Address))
      .Value = .Value
   End With
   With Range("E2:E" & Lr)
       .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 8).Address & "=""Subtotal"",""=r[-1]c"",""=r[1]c""))", "@", .Address))
       .Value = .Value
   End With
   With Range("L2:M" & Lr)
       .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
       .Value = .Value
   End With
End Sub

You are so good at this! What you coded works magnificently. In going through running the macro and importing the spreadsheet into another program (QuickBooks), I saw that I didn't think of asking for the following:

On the header rows (such as row 2, 5, 8, 13, etc.), in addition to your populating the SKU (item) in Column M, which you now have the macro correctly do from the row below, could you also populate the Description in Column R also with the same SKU?

For example, Row 2, the macro copies "Care Management/Medical - time" from Row 3, Column M and pastes it in Row 2 Column M. Could you have the same, duplicated info, "Care Management/Medical - time", paste also in Row 2, Column R? And so on?

Let me know if I'm not clear.


 

Fluff13

Active Member
Try
Code:
Sub AndrewKedem()
   Dim Lr As Long
   
   Lr = Range("A" & Rows.Count).End(xlUp).Row
   With Range("M2:M" & Lr)
      .Value = Evaluate(Replace("if(@<>"""",@,if(isnumber(search(""total""," & .Offset(, -12).Address & ")),""Subtotal"",""""))", "@", .Address))
   End With
   With Range("L2:L" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 1).Address & "=""Subtotal"",""=r[-1]c"",""""))", "@", .Address))
      .Value = .Value
   End With
   With Range("R2:R" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, -5).Address & "=""Subtotal"",""=rc[-5]&"""" """"&r[-1]c[-5]"",""=r[1]c[-5]""))", "@", .Address))
      .Value = .Value
   End With
   With Range("E2:E" & Lr)
       .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 8).Address & "=""Subtotal"",""=r[-1]c"",""=r[1]c""))", "@", .Address))
       .Value = .Value
   End With
   With Range("L2:M" & Lr)
       .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
       .Value = .Value
   End With
End Sub
 

Andrew Kedem

New Member
Success! Thank you. I don't even know your name. Thank you for being so generous and effective. Please know that I'd at least treat you to a wonderful dinner if I only knew how to. Have a great week. Warmly, Andrew
 

Andrew Kedem

New Member
Try
Code:
Sub AndrewKedem()
   Dim Lr As Long
  
   Lr = Range("A" & Rows.Count).End(xlUp).Row
   With Range("M2:M" & Lr)
      .Value = Evaluate(Replace("if(@<>"""",@,if(isnumber(search(""total""," & .Offset(, -12).Address & ")),""Subtotal"",""""))", "@", .Address))
   End With
   With Range("L2:L" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 1).Address & "=""Subtotal"",""=r[-1]c"",""""))", "@", .Address))
      .Value = .Value
   End With
   With Range("R2:R" & Lr)
      .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, -5).Address & "=""Subtotal"",""=rc[-5]&"""" """"&r[-1]c[-5]"",""=r[1]c[-5]""))", "@", .Address))
      .Value = .Value
   End With
   With Range("E2:E" & Lr)
       .FormulaR1C1 = Evaluate(Replace("if(@<>"""",@,if(" & .Offset(, 8).Address & "=""Subtotal"",""=r[-1]c"",""=r[1]c""))", "@", .Address))
       .Value = .Value
   End With
   With Range("L2:M" & Lr)
       .SpecialCells(xlBlanks).FormulaR1C1 = "=r[1]c"
       .Value = .Value
   End With
End Sub

Hi Fluff13,

How are you doing?

The macro you wrote for me works great. However, the Excel-to-QuickBooks import S/W was limiting me on importing blank lines. So I've had to do lots of manually editing of the imported data. Finally, the S/W company has finally given me code so I can import the invoices in a usable way. That means there's an additional line I need to add before each SKU/Item header row.

Would you please do your macro magic so that I can automate adding 1 row above each SKU/Item header row with cells in Columns E (Full Name) and L (Invoice #) populated with data from 2 rows below? (2 rows below, because the SKU/Item header rows are blank until they draw data from 1 line below).

I attached 2 files.
1) "Macro Sample Source File": This is how the file will be BEFORE running the macro
2) "Ideal Result Sample File": This is how I'm hoping the file will be AFTER running the macro. The following rows are the added ones: Rows 2, 16, 21, 26, 33, 37, 50, 84, 88 and 92.

Please let me know if you have questions. Good luck.

Warmly,

Andrew
 

Attachments

Top