Solved Macro to Add rows based on the infrmation in another cell

May 27, 2014 at 19:10:54
Specs: Windows 7
Example of what has to be done by the macro
I need a macro which should add below each row the quantity of rows shown in the column named "TO-FROM".
Then, the same information of the original row should be copy and paste in the new rows that were added. However, anther column should be added named "Valid Date". The first row should contain the date included in the column named "Expense From/On Date" and the last row should contain the date included in the column named "Expense To Date". The rows between these two rows should contain the dates that form a continuous from the first to the last date in question.

Then, the same should apply for te following rows.

The attachement shows further information of what the excel contains.

message edited by Lukiano16


See More: Macro to Add rows based on the infrmation in another cell

Report •

#1
May 28, 2014 at 05:28:06
Thanks for letting us know what you "need" and what the macro "should" do.

When you are ready to ask for help with that, just let us know.

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


Report •

#2
May 28, 2014 at 05:49:49
Hi! Sorry I wrote this late yesterday after a hard day of work! (sorry it was tough how it was written) I am starter in macros so I beg for help on this! :)

Would you mind helping me? This will be really important!

Thanks you very much!!


Report •

#3
May 28, 2014 at 08:27:25
✔ Best Answer
First, if you are trying to learn how to write macros, I suggest that you read through this How-To. It will teach you some basic steps on debugging VBA code. Not only will these debugging steps be a great help in determining what is going with code that you have written, but (maybe more importantly) they can help you figure out what is going on with code that others have written. Once you see how others are doing things, you can start to incorporate those techniques into your own code.

http://www.computing.net/howtos/sho...

As for the code you've requested, give this a try. Note: I can't see the Row numbers in the video you posted, so I assumed that your Columns Headings are in Row 1.

 Sub Insert_By_ColX_Value()
  Application.ScreenUpdating = False
'Create Valid Date Column
    Cells(1, "X").EntireColumn.Insert
    Cells(1, "X").Value = "Valid Date"
'Determine last Row with data in Column Y
      lastRw = Cells(Rows.Count, "Y").End(xlUp).Row
'Loop through rows in reverse order
    For rw = lastRw To 2 Step -1
'Set variable for Date calculation
     addDates = Cells(rw, "Y")
'Insert Rows
       For newRw = 1 To Cells(rw, "Y")
         Cells(rw, "Y").EntireRow.Copy
         Cells(rw, "Y").EntireRow.Insert shift:=xlDown
'Insert dates in Valid Date column
         Cells(rw + 1, "X") = Cells(rw + 1, "V") + addDates
'Decrement Date variable to be added
           addDates = addDates - 1
       Next
'Place first date in original row
         Cells(rw, "X") = Cells(rw, "V")
    Next
 Application.ScreenUpdating = True
End Sub

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

message edited by DerbyDad03


Report •

Related Solutions

#4
May 28, 2014 at 18:44:34
Genius!!!! I just tried it and everything seems to work ok!!!!

You are the best!!!! I will follow your tips thanks!

I will test this further tomorrow but as far as I see no issue was detected!

Thanks!!


Report •

Ask Question