SOLVED
Home

Repeating Macro until I get an empty row

%3CLINGO-SUB%20id%3D%22lingo-sub-334740%22%20slang%3D%22en-US%22%3ERepeating%20Macro%20until%20I%20get%20an%20empty%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334740%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20totally%20new%20to%20macros%2C%20but%20i%20figured%20out%20how%20to%20record%20one%20and%20it%20works.%3C%2FP%3E%3CP%3ENow%20I%20need%20to%20make%20it%20repeat%20for%20several%20rows%20until%20it%20gets%20to%20an%20empty%20row.%3C%2FP%3E%3CP%3Esee%20below%3C%2FP%3E%3CP%3E%3CSTRONG%3ESub%20Macro5()%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ERange(%22P9%22).GoalSeek%20Goal%3A%3D0.25001%2C%20ChangingCell%3A%3DRange(%22G9%22)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EEnd%20Sub%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20this%20to%20repeat%20for%20each%20row%2C%20for%20example%3A%20row%2010%2C11%2C12...until%20it%20gets%20to%20an%20empty%20cell.%3C%2FP%3E%3CP%3Ecan%20someone%20help%3F%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-334740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-335079%22%20slang%3D%22en-US%22%3ERe%3A%20Repeating%20Macro%20until%20I%20get%20an%20empty%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-335079%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20worked!!%20Thank%20you%20Haytham!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334894%22%20slang%3D%22en-US%22%3ERe%3A%20Repeating%20Macro%20until%20I%20get%20an%20empty%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334894%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20%3CSTRONG%3EFor%20Each%20statement%3C%2FSTRONG%3Eto%20loop%20through%20each%20cell%20in%20the%20selection%20and%20apply%20the%20code%20to%20it.%3C%2FP%3E%3CP%3EBut%20you%20have%20to%20select%20the%20range%20of%20cells%20that%20you%20want%20to%20change%20before%20you%20run%20the%20code.%3C%2FP%3E%3CPRE%3ESub%20Macro5()%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20cell%20In%20Selection%3CBR%20%2F%3E%20%20%20cell.GoalSeek%20Goal%3A%3D0.25001%2C%20ChangingCell%3A%3Dcell.Offset(0%2C%20-9)%3CBR%20%2F%3ENext%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F72796i571D3C87F1D59BAB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22For%20Each.png%22%20title%3D%22For%20Each.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334873%22%20slang%3D%22en-US%22%3ERe%3A%20Repeating%20Macro%20until%20I%20get%20an%20empty%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334873%22%20slang%3D%22en-US%22%3E%3CP%3ESee%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334836%22%20slang%3D%22en-US%22%3ERe%3A%20Repeating%20Macro%20until%20I%20get%20an%20empty%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334836%22%20slang%3D%22en-US%22%3E%3CP%3EDo%20you%20have%20a%20sample%20spreadsheet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
moemaya
New Contributor

Hi totally new to macros, but i figured out how to record one and it works.

Now I need to make it repeat for several rows until it gets to an empty row.

see below

Sub Macro5()
Range("P9").GoalSeek Goal:=0.25001, ChangingCell:=Range("G9")
End Sub

 

I would like this to repeat for each row, for example: row 10,11,12...until it gets to an empty cell.

can someone help? Thanks.

4 Replies

Do you have a sample spreadsheet?

Solution

Hi,

 

You can use the For Each statement to loop through each cell in the selection and apply the code to it.

But you have to select the range of cells that you want to change before you run the code.

Sub Macro5()

For Each cell In Selection
cell.GoalSeek Goal:=0.25001, ChangingCell:=cell.Offset(0, -9)
Next

End Sub

For Each.png

 

Hope that help

It worked!! Thank you Haytham!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
17 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies