DavyJones90
Board Regular
- Joined
- Feb 23, 2022
- Messages
- 62
- Office Version
- 365
- Platform
- Windows
Hello Everyone, thanks in advance for your help
Here is my code
'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
Here is my code
VBA 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
VBA Code:
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