Faster code by looping

[jasonleewkd]

Hi all, this must be a really easy question but I'm just getting started with VBA, and coding in general. I wrote a short macro to perform multiple goal seeks and the below is what I got. Now I'm sure the code can be shortened to a few lines by assigning some variables and looping, and I think the For loop can help, but I'm not sure how to get started. Would appreciate if anyone can show me a shorter version of the code below that'll help greatly in my learning!

Sub Goal_Seek()

Dim X As Double

X = 0

    Sheets("Prime Total Returns").Select
    Range("CA10").Select
    Range("CA10").GoalSeek Goal:=X, ChangingCell:=Range("CC10")

    Sheets("Prime Total Returns").Select
    Range("CA11").Select
    Range("CA11").GoalSeek Goal:=X, ChangingCell:=Range("CC11")
          
    Sheets("Prime Total Returns").Select
    Range("CA12").Select
    Range("CA12").GoalSeek Goal:=X, ChangingCell:=Range("CC12")
  
    Sheets("Prime Total Returns").Select
    Range("CA13").Select
    Range("CA13").GoalSeek Goal:=X, ChangingCell:=Range("CC13")
  
    Sheets("Prime Total Returns").Select
    Range("CA14").Select
    Range("CA14").GoalSeek Goal:=X, ChangingCell:=Range("CC14")
  
    Sheets("Prime Total Returns").Select
    Range("CA16").Select
    Range("CA16").GoalSeek Goal:=X, ChangingCell:=Range("CC16")
  
    Sheets("Prime Total Returns").Select
    Range("CA17").Select
    Range("CA17").GoalSeek Goal:=X, ChangingCell:=Range("CC17")
  
    Sheets("Prime Total Returns").Select
    Range("CA18").Select
    Range("CA18").GoalSeek Goal:=X, ChangingCell:=Range("CC18")
  
    Sheets("Prime Total Returns").Select
    Range("CA20").Select
    Range("CA20").GoalSeek Goal:=X, ChangingCell:=Range("CC20")
  
    Sheets("Prime Total Returns").Select
    Range("CA21").Select
    Range("CA21").GoalSeek Goal:=X, ChangingCell:=Range("CC21")
  
    Sheets("Prime Total Returns").Select
    Range("CA10").Select

End Sub

 

[Hui]

I think this should do it

Sub Goal_Seek()
Dim i As Integer
Dim x As Double

x = 0

Sheets("Prime Total Returns").Select

For i = 10 To 21
  Cells(i, 79).GoalSeek Goal:=x, ChangingCell:=Cells(i, 81)
Next

Range("CA10").Select

End Sub

79 is Column CA
81 is Column CC

 

[jasonleewkd]

Thanks Hui, that works. One question, how do I skip rows 15 and 19? There's an Excel =AND formula but VBA's syntax is a little different.

 

[Hui]

Sub Goal_Seek()
Dim i As Integer
Dim x As Double

x = 0

Sheets("Prime Total Returns").Select

For i = 10 To 21
  if i<>15 or i<> 19 then Cells(i, 79).GoalSeek Goal:=x, ChangingCell:=Cells(i, 81)
Next

Range("CA10").Select

End Sub

 

[GCExcel]

Hi,

Avoid using .Select to make your code run faster.
And this should probably be an AND instead of an OR to skip rows 15 and 19 :

Sub Goal_Seek()
    Dim i As Integer
    Dim x As Double

    x = 0
    With Sheets("Prime Total Returns")
        For i = 10 To 21
            If i <> 15 And i <> 19 Then .Cells(i, 79).GoalSeek Goal:=x, ChangingCell:=.Cells(i, 81)
        Next
    End With

End Sub

 

[SirJB7]

@GCExcel
Hi!
Two comments.
a) Had looked carefully at the 1st post you should have noticed that only rows 15 and 19 were omitted, so @Hui's OR is right and not your AND.
b) What an amazing change and improvement achieved with your code instead of Hui's previously posted! I think that something like... like... one select method less?... wow!
Regards!

 

[GCExcel]

@SirJB7
a) If you want to skip rows 15 and 19, I believe you need to use AND not OR.
Try this simple code, and see by yourself :

Sub test()
    Dim i As Integer
   
    'Want to skip 5 and 9
   
    'This works :
    For i = 1 To 10
        If i <> 5 And i <> 9 Then Debug.Print i
    Next i
   
    'Not this
    For i = 1 To 10
        If i <> 5 Or i <> 9 Then Debug.Print i
    Next i

End Sub

b) Yes, it's only one select method less but this is coding best practice. It may not make a difference in this case but when you are working with bigger loops, selecting sheets WILL slow down your code.

Again, try this simple codes and see by yourself which one is faster.

Sub Demonstration()
    Dim dStart As Double
    Dim i As Integer

    dStart = Timer
    For i = 1 To 1000
        With Sheets(1)
            Debug.Print Sheets(1).Name
        End With
    Next i

    MsgBox Timer - dStart
End Sub

Sub Demonstration_with_Select()
    Dim dStart As Double
    Dim i As Integer

    dStart = Timer
    For i = 1 To 1000
        Sheets(1).Select
        Debug.Print Sheets(1).Name
    Next i

    MsgBox Timer - dStart
End Sub

 

[SirJB7]

@GCExcel
Hi!
You're right, my bad. Thanks for the catch of the catch, I don't know what would I be thinking on when I wrote that, but AND rules
Regards!

 

[Luke M]

@SirJB7
We'll cut you some slack, since it looks like you've been away for a bit. An over abundance on the intake of Carlsbergs? Nah, that can't be it.

 

[SirJB7]

@Luke M
Hi, my friend!
Nice to read you again, BTW. And just a minor remark: Groucho once said that military intelligence was a contradiction in terms; well so happens with over abundance and Carlsberg!
Easter holidays on April 17-20, job travel abroad on April 21-25 + weekend, labor day on May 1st extended to 2nd in this country (yes, tell our politicians that they shouldn't), so why working on April 28-30?, and then a required relax week on May 4-11... and coming back this week.
Regards!