I'm trying to figure out where the issues with this code are and can't seem to figure it out. Any help would be appreciated. Here's the issue at hand.
In case the image doesn't work, here's the layout:
A B C D E F G H I J
1 ... 2003 2014 ... ... ... ... ... ... List
2 =B2 =B1+1 =C1 =D1 =E1 =F1 =G1 =H1 =I1 =J1 Text
What I would like to do is: Assuming A1 is your active cell, If C1 is greater than B1 (years), then copy the entire row (which goes to Col. AV) and insert it in the row below. Then I want the copied cell in column A to equal the copied cell in column B. The cell in column B needs to be one year greater than the one it was copied from. The rest of the cells would stay in their copied state until Col. J. Col. J is a dropdown list through validation. For the copied cell underneath, I would like all the subsequent years to change when the original cell drop down is chosen.
Basically, I've been given data in a start and end year format, and I need to build out an extensive database with each year being its own line, so in this example, there would be 11 additional copied lines since each year after 2003 up until 2014 would need to be added.
I need this to loop through the sequence until it hits a blank row.
Here's the code I have so far:
Sub AddYearsToModel()
'
'AddYearsToModel Macro
'
'Keyboard Shortcut: Ctrl+g
'
Do
If ActiveCell(1, 3).Value > ActiveCell(1, 2).Value Then
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=R[0]C[1]"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
ActiveCell.Offset(0, 8).Select
ActiveCell.Validation.Delete
ActiveCell.FormulaR1C1 = "=R[-1]C"
ActiveCell.Offset(0, -9).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = ""
End Sub
By taking out the Do/Loop, it will perform correctly a single time. When I run it a second time, (which would now be row 4) Cells A3:b$ all equal "#VALUE!" Of those four cells, A3 and A4 both equal their corresponding cells in Col. B. Cell B4 does exactly what it is supposed to do, and calls from B3+1. However somehow it is going back through and changing cell B3 to pull B1+1 when it shouldn't even be visiting that cell anymore and the cell should still pull B2+1.
Suggestions?
Aucun commentaire:
Enregistrer un commentaire