Cleaner way of writing multiple goal seeking formulas?

[shotgun1]

Hi

I'm quite new to VBA coding and wanted to ask is there a shorter/cleaner form of writing out the following goal seek formulas for every 4th row from EJ9:EJ53?

Sub GSeek1()



    Range("FD8").GoalSeek Goal:=Worksheets("Price Setting").Range("BL8").Value, ChangingCell:=Range("EJ9")
    Range("FD12").GoalSeek Goal:=Worksheets("Price Setting").Range("BL12").Value, ChangingCell:=Range("EJ13")
    Range("FD16").GoalSeek Goal:=Worksheets("Price Setting").Range("BL16").Value, ChangingCell:=Range("EJ17")
    Range("FD20").GoalSeek Goal:=Worksheets("Price Setting").Range("BL20").Value, ChangingCell:=Range("EJ21")
   
End Sub

 

[Luke M]

Sure thing. We can setup a loop counter, and we can control how much the counter increments like so

Sub GSeek1()

Dim i As Long
Application.ScreenUpdating = False
For i = 8 To 52 Step 4
    Cells(i, "FD").GoalSeek Goal:=Worksheets("Price Setting").Cells(i, "BL").Value, ChangingCell:=Cells(i + 1, "EJ")
Next i
Application.ScreenUpdating = True
       
End Sub

Note that I used the Cells object rather than Range as it's a little easier to work with variables.

 

[Stevie]

Hmm.
How about something like:

Sub gSeek1()
    Dim rNum As Integer
    For rNum = 9 To 53 Step 4
        With Worksheets("Sheet1")
            .Range("FD" & rNum - 1).GoalSeek _
            goal:=Sheets("Price Setting").Range("BL" & rNum - 1).Value, _
            ChangingCell:=.Range("EJ" & rNum)
        End With
    Next
End Sub

Edit: ah, looks like I was too slow on this one, should have refreshed first. Mine is pretty much the same as Luke's, but using 'range'instead of 'cells'and laid out slightly differently. Either should work.
GJ Luke

 

[shotgun1]

Many thanks Luke and Stevie.

What would be the best way to exit the loop if the data does not run until the last instance of the loop? Would a Do Until statement in the code fix that?

 

[Stevie]

You can use:

Exit For

to exit the loop or

Exit sub

to exit the sub.
In Luke's example, you will want to do

application.screenupdating = true
Exit sub

to make sure you have turned screen updating back on if you take that route.

However, you are probably better off changing:

For rNum = 9 To 53 Step 4

to

dim endNum as integer
endNum = sheets("Sheet1").range("A1").value
For rNum = 9 To endNum Step 4

where
A1 on sheet1 contains a formula such as:

=counta('Price Setting'!BL8:BL100) +7

or something similar in order to dynamically define the range first. There are several ways to do this, and you can determine the number of rows of data within the macro too if you prefer.

If this was helpful, please click like!

 

[Luke M]

Ditto what Stevie said. Better to define your end point, and then use that end point in the loop.