Excel formula fill

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3165916%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EExcel%20formula%20fill%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3165916%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EI%20am%20trying%20to%20do%20a%20formula%20that%20goes%3A%3CBR%20%2F%3ESelect%20G4.%3CBR%20%2F%3E%3Db2%3CBR%20%2F%3ESelect%20G5.%3CBR%20%2F%3E%3DD2%3CBR%20%2F%3E%3CBR%20%2F%3ESelect%20G6%3CBR%20%2F%3E%3DB3%3CBR%20%2F%3E%3CBR%20%2F%3ESelect%20G7%3CBR%20%2F%3E%3CBR%20%2F%3E%3DD3%3CBR%20%2F%3E%3CBR%20%2F%3EI%20need%20to%20get%20this%20to%20row%202%2C000.%20Everytime%20i%20try%20to%20drag%20the%20selected%20grouping%20it%20wants%20to%20skip%20rows%20and%20start%20at%20row%206%20end%20row%207start%20row%2010%20end%20row%2011.%20Any%20ideas%3F%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3165916%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3165968%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3165968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1307136%22%20target%3D%22_blank%22%3E%40Lrmolin8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20way%20you%20could%20link%20those%20cells%20-%20enter%20B2%20in%20G4%2C%20B3%20in%20G5%2C%20select%20G4%20and%20G5%20and%20drag%20down%20as%20far%20as%20needed%20(should%20have%20a%20series%2C%20B2%2C%20B3%2C%20B4%2C%20etc.).%20In%20column%20H%2C%20enter%201%20and%203%2C%20and%20drag%20down%20(series%20-%20so%20you%20should%20have%201%2C3%2C5%2C7%2C9%2C%20etc).%20Then%2C%20do%20the%20same%20thing%20for%20column%20D%2C%20but%20enter%202%2C4%20in%20column%20H%20(so%20you%20have%20a%20series%20of%20even%20numbers).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_2-1644895804267.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348209iDB8EB1B89825803F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_2-1644895804267.png%22%20alt%3D%22JMB17_2-1644895804267.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20sort%20columns%20G%20and%20H%20using%20column%20H%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_3-1644895861415.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348210i612FEF4C8EAE3771%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_3-1644895861415.png%22%20alt%3D%22JMB17_3-1644895861415.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20do%20a%20find%2Freplace%20on%20column%20G%2C%20find%20B%20and%20replace%20with%20%3DB%2C%20find%20D%20and%20replace%20with%20%3DD.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_4-1644895945191.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348211i4CAA02347DF4A52F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_4-1644895945191.png%22%20alt%3D%22JMB17_4-1644895945191.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JMB17_6-1644896304265.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348213iF9CC6BCD96C1A148%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JMB17_6-1644896304265.png%22%20alt%3D%22JMB17_6-1644896304265.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
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.