Solved Macro or Formula to insert a row based on information given

April 13, 2015 at 12:53:27
Specs: Windows 8
Hi, I have read many threads in regards to writing a macro to insert a row. I have tried some of them that I was hoping would apply and I could build on them, but I have not had any luck. I am working on a macro workbook that will take a CSV file with way to much information in it and turn it into a very simple file that can be imported into another software program for payroll processing. I have one issue that is out of my expertise and I am not sure if it is even possible. When importing into the payroll software the excel sheet must be set-up a specific way. I have created a macro that does this. Column F is an earnings/deduction column and must have an E or D depending on the output from column G. Column G is an earnings code column. This is a numeric code given by the payroll company which tells the software where the hours it is importing should be placed on the payroll check (i.e. 40 regular hours would be listed as "E" "01" respectively in these two columns). I also have three columns; AY, AZ, BA, which provide the hours for the earning code in G. Again each column is broken down by the type of payment; regular, overtime, holiday. Now here comes the tricky part. I am only allowed to use one row per earning code which is fine when only one type of hours are provided; however, I am stuck when there are more than one type of hours (i.e. regular and overtime hours in the same row). I was hoping to insert a row for the instance that multiple types of hours are present and copy the formulas down. Again I am not sure if this is even possible or if there is an easier way of looking at this through Excel eyes that I am not thinking of. Any help/advise would be greatly appreciated.

See More: Macro or Formula to insert a row based on information given

Report •

#1
April 13, 2015 at 15:08:56
✔ Best Answer
I'm making a couple of assumptions here...

1 - In a given row, you will either have 1 or 2 entries in Columns AY:BA, never 3.
2 - You want to eliminate the extra data in the pair of rows once the original row is copied. In other words, let's say you have data in AY and BA. Once the row is copied, the first of the 2 rows should retain the data from AY, but not BA, and the second row should retain the data from BA, but not AY.

If those assumptions are correct, try this code. What it does is use COUNTA to determine if AY:BA contains more than 1 entry. If that is true, then it copies the row. Once the row is copied, it determines where those 2 pieces of data reside and deletes the extra data from each row.

Sub InsertRwForMultiHours()
'Turn off ScreenUpdating while macro runs
 Application.ScreenUpdating = False
'Determine last row with data in Column A
  lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Rows in reverse order
   For srcRw = lastRw To 2 Step -1
'If AY:BA in current Row contain more than one value, Copy/Insert Row
    If WorksheetFunction.CountA(Range("AY" & srcRw & ":BA" & srcRw)) > 1 Then
      Range("A" & srcRw).EntireRow.Copy
       Range("A" & srcRw).Insert
'Erase the extra Data in the Rows with multiple values
       If Range("AY" & srcRw) <> "" Then
          Range("AZ" & srcRw & ":BA" & srcRw).ClearContents
          Range("AY" & srcRw + 1).ClearContents
       Else:
          Range("BA" & srcRw).ClearContents
          Range("AZ" & srcRw + 1).ClearContents
       End If
     End If
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
April 14, 2015 at 12:10:04
Thank you, thank you, thank you. I was really hoping you would be the one to respond. Your answers are the threads I mentioned that I was working with to try to remedy this before I posted a new thread. I found your solutions the easiest to follow along and understand. This way could really work with a few minor changes. They are my fault completely as I don't think I listed them in my original question. Column BA (holiday hours) will never share hours with columns AY/AZ. So the need for the row insertion/copy only comes into play when columns AY & AZ both has data in it. I have already created a formula so that AY-BA will show accurate hours no matter what data is provided. Meaning that if BA shows 8 hours for holiday than AY/AZ will show nothing in their columns. The current code is inserting a new row even when there is only one type of hours present (i.e. when only one of the three columns has data in it). Is there a way to tweak it slightly so that AY is over 1 and there is no data present in AZ it will do nothing; or vice-versa depending where the hours fall? Also, Column G will be changing based on what hours are provided. So if AY has hours in it column G will show 1, if AZ has hours in it column G will show 3, if BA has hours in it column G will show 4. This formula is based on only one type of hours being paid. So additionally I was curious to see if there is the possibility that if a new row is inserted that column G will automatically list a 3 in it based on that row being inserted because of the data from column AZ?. Sorry I know that this is complex and again I really appreciate your assistance.

message edited by bookkeepinggirl


Report •

#3
April 14, 2015 at 13:05:39
re: ""The current code is inserting a new row even when there is only one type of hours present (i.e. when only one of the three columns has data in it).

If that is the case, then the CSV file (or something else) is putting some type of data in the cells. I have tested the code multiple times on 2 different systems and it does not insert a row if there is data in only one column.

Per the Excel Help files "The COUNTA function counts the number of cells that are not empty in a range." If the code is inserting a row, then there is data in more than one cell.

I've changed CountA to Count since the COUNT function only counts cell that contain numbers. I can't guarantee that that will work since I don't know what data is in the cells that appear to be empty. If there are numbers in the cells that for some reason aren't displaying, Count will find them. Try the code below. If it still inserts rows where you don't think it should, we'll have to dig deeper.

I have also modified the code to only deal with Columns AY:AZ. Let me know how it works for you.

Sub InsertRwForMultiHours()
Application.ScreenUpdating = False
'Determine last row with data in Column A
  lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Rows in reverse order
   For srcRw = lastRw To 2 Step -1
'If AY:BA in current Row contain more than one value, Copy/Insert Row
    If WorksheetFunction.Count(Range("AY" & srcRw & ":AZ" & srcRw)) > 1 Then
      Range("A" & srcRw).EntireRow.Copy
       Range("A" & srcRw).Insert
'Erase the extra Data in the Rows with multiple values
          Range("AZ" & srcRw).ClearContents
          Range("AY" & srcRw + 1).ClearContents
     End If
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 16, 2015 at 08:32:05
Sorry for the delay as I am working on a couple of different projects at once. You were absolutely correct; it worked perfectly! I goofed and saved the file in Excel instead of CSV so the code was working overtime but I have now fixed this. Thanks again for all the help. I sincerely appreciate your time and expertise. Make it a great day!!!

Report •

Ask Question