Excel VBA Code (Goal Seek) Code Simplification

Question

I'm a total novice to VBA - everything I've "coded" so far, has purely come from the Record Macro function. The macro works, but it is incredibly clunky. Essentially what I'm trying to do is to run a goal seek function, take that answer, and put it into a cell. Next, I'd increment to the next input, and do the process again (and then 38 more times). Here are the first 2 increments:

Range("B11").Select
    ActiveCell.FormulaR1C1 = "=R[-8]C[13]"
    Range("C37:D37").Select
    Range("C37").GoalSeek Goal:=0, ChangingCell:=Range("A33")
    Range("A33:B37").Select
    Selection.Copy
    Range("P3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("R11").Select

Range("B11").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[-7]C[13]"
    Range("C37:D37").Select
    Range("C37").GoalSeek Goal:=0, ChangingCell:=Range("A33")
    Range("A33:B37").Select
    Selection.Copy
    Range("P4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("P5").Select

C37:D37 is my formula for the goal seek, and A33:B37 is the output I then copy and paste into the P column. I'd like to share this code with some colleagues, but it is very unprofessional looking in it's current state. Any ideas?

Thank you!

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). After that you might check out how to use a loop instead of repeating code. — Pᴇʜ, Feb 18 '20 at 12:27

Folacol · Accepted Answer · 2020-02-18T14:31:18.897

4

Pᴇʜ - thank you for your answer; simple, yet effective. That's what I needed. In case anyone in the future stumbles across this, here is what I ended up using.

Sub Calculation()

Dim x As Long

For x = 1 To 40 
ThisWorkbook.Worksheets("Sheet1").Range("B11").Value = ThisWorkbook.Worksheets("Sheet1").Cells(2 + x, 15)
ThisWorkbook.Worksheets("Sheet1").Range("C37").GoalSeek Goal:=0, ChangingCell:=ThisWorkbook.Worksheets("Sheet1").Range("A33")
ThisWorkbook.Worksheets("Sheet1").Cells(2 + x, 16) = ThisWorkbook.Worksheets("Sheet1").Range("A33").Value 

Next

End Sub

edited Feb 18 '20 at 14:31

answered Feb 18 '20 at 13:26

Folacol

51
5

2

Just two more suggestions: ① `Dim x As Long` because Excel has more rows than `Integer` can handle (and [there is no benefit in using Integer at all](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520)). ② Never use `Range` or `Cells` without specifying in which workbook/worksheet they are or you will soon or late run into issues. Always specify eg. `ThisWorkbook.Worksheets("Sheet1").Range(…)` or use a variable `Dim MySheet As Worksheet: Set MySheet = ThisWorkbook.Worksheets("Sheet1")` and use it like `MySheet.Range(…)`. – Pᴇʜ Feb 18 '20 at 13:42
Thank you again! I've updated my answer here for posterity. – Folacol Feb 18 '20 at 14:17
1

Well there are still more `Cells` and `Range` objects that need to be specified. I count 4 more. – Pᴇʜ Feb 18 '20 at 14:23
1

Oops. I caught it in my own macro (which has evolved beyond this one), but not here. I've updated again. Thank you! – Folacol Feb 18 '20 at 14:31

Excel VBA Code (Goal Seek) Code Simplification

1 Answers1