Running Different Macros Based on Values in Range

Question

Im busy trying to write code that calls different macros based on the value of a cell across an entire range on VBA.

So for the example I have a range S20:S21.

When the value in cell S20 is equal to 1, I want to call macro1. When the value in cell S21 is equal to 2, I want to call macro2.

When there is no match I have a time that it will test it in the next 5 seconds again where it will start from the top checking if cell S20 is 1 or of S20 is 2.

This is currently the code I have.

    Dim TimeToRun

Sub auto_open()
    Call ScheduleCopyPriceOver
End Sub


Sub ScheduleCopyPriceOver()
    TimeToRun = Now + TimeValue("00:00:05")
    Application.OnTime TimeToRun, "CopyPriceOver"
End Sub

Sub CopyPriceOver()
Application.ScreenUpdating = False

    Number = Range("S20:S22").Value

    If Number = 1 Then

    Call Macro1
    Call ScheduleCopyPriceOver

    ElseIf Number = 2 Then

    Call Macro2
    Call ScheduleCopyPriceOver

    Else

    Call ScheduleCopyPriceOver
    End If


End Sub

Sub auto_close()
    On Error Resume Next
    Application.OnTime TimeToRun, "CopyPriceOver", , False
End Sub

When `S21` = 2 what would the value of `S20` be? and vica versa when `S20` = 1? — CallumDA, Mar 29 '17 at 10:25

score 0 · Accepted Answer · edited May 23 '17 at 11:46

Your CopyPriceOver sub has some issues, I've pointed them out in comments below

Sub CopyPriceOver()
    Application.ScreenUpdating = False
    ' Number = Range("S20:S22").Value
    ' You're trying to assign 3 cells' values to one number!
    ' You should declare Number first, declaring it as an integer would quickly flag to you
    ' the error caused by assigning 3 cells to it. I've opted to not use the
    ' variable at all, since you're checking different values anyway...

    ' You should full qualify ranges to a specific sheet, this example uses a With block:
    With ThisWorkbook.Sheets("SheetNameHere")
        ' Test for individual cells' values, you specified values for S20 and S21...
        If .Range("S20").Value = 1 Then    
            ' You don't need the "Call" command, see my comment after the code
            Macro1    
        ElseIf .Range("S21").Value = 2 Then    
            Macro2    
        End If
    End With

    ' You called ScheduleCopyPriceOver in every If condition, simply call it afterwards
    ScheduleCopyPriceOver   

    ' You disabled screen updating and never re-enabled it
    Application.ScreenUpdating = True
End Sub

With respect to the Call command, see this SO question: Should I use Call keyword in VB/VBA?

Your catch-all use of On Error Resume Next also raises red flags in auto_close, you should diagnose and fix errors rather than hide them!

score 0 · Answer 2 · answered Mar 29 '17 at 10:51

0

use the below code

If range("S20") = 1 Then

Call Macro1
Call ScheduleCopyPriceOver

ElseIf range("S21") = 2 Then

Call Macro2
Call ScheduleCopyPriceOver

Else
Call ScheduleCopyPriceOver
End If

answered Mar 29 '17 at 10:51

Biju John

1
5

Running Different Macros Based on Values in Range

2 Answers2

Linked