VBA Code for bulk Goal Seek

[hellokitty19]

I came upon this old thread while having similar questions. VBA Code for bulk Goal Seek

Bulk Goal Seek Task:

Set Cell Range is G2:G1001
Value I want is 12.00%
Change Cell Range is B2:B1001


I don't know how this code solved the OP's question. Do I need to adjust the code? Thanks!

Sub lee()
Dim iRow As Long

For iRow = 2 To 1001
Cells(iRow, "G").GoalSeek Goal:=0.12, ChangingCell:=Cells(iRow, "B")
Next iRow
End Sub

 

[StephenCrump]

You've quoted another question, and the solution to that question.

But what's your question?

 

[hellokitty19]

Thank you for the reply!

I tried again and can replicate the other question's solution now
My question is similar while only swapping rows and columns. I think i got it work as well but just want to confirm.

Bulk Goal Seek Task:

Set Cell Range is B6:CV6
Value I want is 1
Change Cell Range is B2:CV2

Code:
Sub GoalseekC()
Dim iColumn As Long

For iColumn = 2 To 100
Cells("6", iColumn).GoalSeek Goal:=1, ChangingCell:=Cells("2", iColumn)
Next iColumn
End Sub

To Confirm Code for the above task:
Column needs to be referenced by number not letter in the loop statement, right?

Thank you!

 

[StephenCrump]

Yes, based on your description, the code should do what you're wanting.

Have you tried the code?

 

[hellokitty19]

Yes.. It works... Just wanted to confirm the terminology of Column Names as I am super new to VBA.

Some Follow-up Questions:
The above code is simple and does great job. However, it needs manual adjustment if need to run it over different sections.

• Task 2: Cell Input and Output ranges are different from B2:CV2 and B6:CV6
• Task 3: Cell Input and Output ranges are not continuous ranges.

Is there way to improve the automation like a pop-out box like Goal Seek but can input various ranges of cells for Cell Input and Cell Output?

Attached please find the examples of Task 2 and Task 3. Thanks again!

 

[StephenCrump]

You could do something fairly succinct like this:

Sub Test()

    Dim r As Range

    For Each r In Range("B2:P2,B11:P11,B19:D19,J19:P19")
        r.Offset(4).GoalSeek Goal:=0.24, ChangingCell:=r
    Next r

End Sub

But using VBA code give you flexibility to do it many other ways. Depending on your preference, you could, for example, prompt the user to select the relevant ranges. Or you could determine the ranges dynamically, e.g. by finding each "Change cell" row, counting the relevant columns, and populating the formulae appropriately.

Please let us know if you'd like any help with alternatives?

 

[hellokitty19]

Thanks for the reply!

Yes.. I do want to determine the ranges dynamically, e.g. by finding each "Change cell" row, counting the relevant columns..
Ultimately, i want to solve the use case that cell input and output ranges may be dynamic and not continuous ranges.

Thanks very much for the help!~~~