CODEWITHSUNDEEP
excelvba
KarenDP
KarenDP

Reputation: 57

Object Variable or With Block Variable not Set when setting range

I have a linked cell that changes value depending on a scroll bar.

I want to get the value of the cell in column E and the row # will depend on my scroll bar's linked cell value.

Example: If the linked cell value is 2, I want to get the value of cell E3.

I am getting

Object Variable or With Block Variable not Set

I read threads here but none helped.

Sub getweekrowindexrange()
Application.ScreenUpdating = False
Set wsCalculations = Sheets("Calculations")
'Set wsDashboard = Sheets("Dashboard")
Dim weekrowindex As Long
Dim Dashboardweekrange As Range

'this line works fine, gives me the value i need    
weekrowindex = WorksheetFunction.Sum(wsCalculations.Range("BS1").Value, 1)

'this gives me the 'Object variable or With block variable not set' error.
'i tried adding 'Set' before the Dashboardweekrange variable
' but then i get an 'Object required' message
Dashboardweekrange = wsCalculations.Range("E" & weekrowindex).Value

MsgBox weekrowindex 'ignore this line, i just use it to test the index value
End Sub

Screenshot of my data. I hid columns to avoid anyone getting confused.
worksheet

Upvotes: 0

Views: 57

Answers (2)

KarenDP
KarenDP

Reputation: 57

The wsCalculations.Range("E" & weekrowindex).Value gets the cell value of the range not the range itself. So Set Dashboardweekrange = wsCalculations.Range("E" & weekrowindex). –

thank you so mych @AxelRichter!!!!

Upvotes: 0

Error 1004
Error 1004

Reputation: 8230

Try:

Sub test()

    'Dashboardweekrange is a range
    Dim Dashboardweekrange As Range

    Set Dashboardweekrange = wsCalculations.Range("E" & weekrowindex)

    'Dashboardweekrange is a long
    Dim Dashboardweekrange As Long

    Dashboardweekrange = wsCalculations.Range("E" & weekrowindex).Value

End Sub

Upvotes: 1

Related Questions