(Solved) – VBA: Object Variable or With Block Variable not Set when setting range


i have a cell(linked cell) that changes value depending on a scroll bar. i want my code 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 link cell value is 2, i want my code to get the value of cell E3. ive created a code and i cant figure out why i am getting the Object Variable or With Block Variable not Set message. ive read threads in here about the same error but none helped(or maybe i just dont understand the answers)

here’s my code

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

weekrowindex = WorksheetFunction.Sum(wsCalculations.Range("BS1").Value, 1) ''this line works fine, gives me the value i need
Dashboardweekrange = wsCalculations.Range("E" & weekrowindex).Value '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
MsgBox weekrowindex 'ignore this line, i just use it to test the index value
End Sub

Here’s a screenshot of my data. i intentionally hid lots of columns to avoid anyone from getting confused
can someone please tell me what im doing wrong?

Leave a Reply

Your email address will not be published. Required fields are marked *