Setting a Sheet based on a cell value in a different sheet

[DavyJones90]

Hello Everyone, thanks in advance for your help

Here is my code

Sub AutoArrowsLeft()
   
  
    Dim Cl As Range
    Dim Dic As Object
    Dim sys As Worksheet
    'Dim arrow As Worksheet
    Dim Sarrows As Worksheet
       
   

    Set sys = ThisWorkbook.Sheets("$Auto")
   ' Set arrow = Workbooks("CURRENCIES  W-D System.xlsm").Worksheets("Final ARROWS")
   
       
    If sys.Range("H3:Y3").Text = "Monthly" Then
    Set Sarrows = ThisWorkbook.Sheets("$MW")
    ElseIf sys.Range("H3:Y3").Text = "Weekly" Then
    Set Sarrows = ThisWorkbook.Sheets("$WD")
    ElseIf sys.Range("H3:Y3").Text = "Daily" Then
    Set Sarrows = ThisWorkbook.Sheets("$DH")
    Else
    End If
      
       
   
    Set Dic = CreateObject("scripting.dictionary")
   
   
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False

'Dow Patern
   
    With sys
        For Each Cl In .Range("A5", .Range("A" & Rows.Count).End(xlUp)) 'I am getting an error here stating Object Variable of with variable not set
            Dic(Cl.Value) = Cl.Offset(, 7).Value
        Next Cl
    End With
    With Sarrows
       
        For Each Cl In .Range("F6", .Range("F" & Rows.Count).End(xlUp))

'I am getting an error stating Object Variable of with variable not set, any idea how to restructure so it works?

It must have somehting to do with this part, because it works if I only set one worksheet instead of 3 through an if

If sys.Range("H3:Y3").Text = "Monthly" Then
    Set Sarrows = ThisWorkbook.Sheets("$MW")
    ElseIf sys.Range("H3:Y3").Text = "Weekly" Then
    Set Sarrows = ThisWorkbook.Sheets("$WD")
    ElseIf sys.Range("H3:Y3").Text = "Daily" Then
    Set Sarrows = ThisWorkbook.Sheets("$DH")
    Else
    End If

 

[bebo021999]

If sys.Range("H3:Y3").Text = "Monthly" Then

a range can not be compared like that.

should be:

If Worksheetfunction.Countif(sys.Range("H3:Y3"), "Monthly") then

Similar to Weekly and Daily.

 

[DavyJones90]

Thanks man, works like a charm, can you tell me why that worked and mine didn't?

 

[bebo021999]

To test if a value is in a range, not:

If sys.Range("H3:Y3").Text = "Monthly" Then

but two ways:

1) Loop

For each cell in sys.Range("H3:Y3")
    If cell.value = "Monthly" then <do some thing>
Next

2) to test if value exist or not:

If Worksheetfunction.Countif(sys.Range("H3:Y3"), "Monthly") then