mardi 21 mars 2017

Excel VBA --> If, Greater Than, Copy/Paste, ActiveCell.Value, Do Loop

A1:J3

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