Excel formula fill

Copper Contributor
I am trying to do a formula that goes:
Select G4.
=b2
Select G5.
=D2

Select G6
=B3

Select G7

=D3

I need to get this to row 2,000. Everytime i try to drag the selected grouping it wants to skip rows and start at row 6 end row 7start row 10 end row 11. Any ideas?
2 Replies

@Lrmolin8 

 

One way you could link those cells - enter B2 in G4, B3 in G5, select G4 and G5 and drag down as far as needed (should have a series, B2, B3, B4, etc.). In column H, enter 1 and 3, and drag down (series - so you should have 1,3,5,7,9, etc). Then, do the same thing for column D, but enter 2,4 in column H (so you have a series of even numbers).

 

JMB17_2-1644895804267.png

 

Then, sort columns G and H using column H:

JMB17_3-1644895861415.png

 

Then, do a find/replace on column G, find B and replace with =B, find D and replace with =D.

 

JMB17_4-1644895945191.png

 

JMB17_6-1644896304265.png

 

 

 

 

 

 

@Lrmolin8 

Sub ExpandFormula()

Dim i As Integer
Dim j As Integer

j = 2
For i = 4 To 4000 Step 2

Cells(i, 7).Value = Cells(j, 2).Value
Cells(i + 1, 7).Value = Cells(j, 4).Value

j = j + 1


Next i


End Sub

 

Maybe with these lines of code. Click the button in cell I2 to start the macro.