Some background if it helps - this is a tree inventory dataset, and each tree should have been measured 4 times in 4 different years. Many were not, either because they had fallen down or were erroneously ignored. Each row is a different measurement "round". I need to create new rows where they were missed, for each tree, to aid in regression analysis.
I've been able to successfully insert new rows where sequential values are missing in Column D (denoting Measurement Round) using a code adapted from this thread: http://www.ozgrid.com/forum/sh…00184&p=772716#post772716
I would like to fill in values each time a new row is created - filling down the values from Columns A, B, & E in the previous row, filling Column D with the sequential value that was identified as missing, and filling Column C with a year associated with that value (i.e. 1=1997, 2=2002, 3=2008, 4=2016).
What I want to fill in Column L might be more complex.. (so no worries if it would take too much time) The value should equal "100" unless it is in the middle (Measurement Round 2 or 3), and in subsequent years the cell value equals "99" for that tree. In that case, Column L should equal "99" (100=fallen down, 99=dead but standing).
Here is what I've got:
Sub test() i = 1 Do While Cells(i, 4) <> "" j = Cells(i + 1, 4).Value - Cells(i, 4).Value - 1 If j < 0 Then j = 3 - Cells(i, 4).Value + Cells(i + 1, 4).Value For k = 1 To j Rows(i + k).EntireRow.Insert 'If j = 2 Then Cells(k, 3).Value = "2002" 'If j = 2 Then Cells(k, 3).Value = "2008" 'If j = 2 Then Cells(k, 3).Value = "2016" 'This is how I was attempting to fill in the year, but it doesn't work. Might not even be using the right variables. Next k i = i + k Loop End Sub
And here is a small portion of the dataset:
Please let me know if I can provide other info.