Setting a range equal to a group of variables

grexcelman

Board Regular
Joined
Mar 1, 2015
Messages
50
Not sure where my syntax is wrong here. I've been trying to concat a few variables into a range based off the location in a shared folder for my coworkers to use when updating a sheet. Hopefully someone can point me in the right direction. When I've checked each variable by using msgbox within the function, each one checked out ok until I get to the range varaible and that's where the function stops and I get a #Value! error with no msgbox popup.

Code:
function test()
Dim location As String
Dim monthly_file As String
Dim function_tb As String
Dim linkfile As Range

location = "My drive address"
monthly_file = "My current month"
function_tb = "charity'!A10:X25"
Set linkfile = Range(location & monthly_file & function_tb)
msgbox linkfile
 
When you assign a range, you do not (cannot) use the path:

Code:
Set someRange = Workbooks("someWorkbook").Worksheets("someWorksheet").Range("someRangeString")

BTW, if you close the workbook, the range variable will be destroyed; a range variable can never reference a closed workbook.

Thank you for that. I revised my code for the range but I'm still unable to pull in the data. Can I not insert variables in place for my file and worksheet names? My updated range code is as follows:

Code:
Set linkfile = Workbooks("Monthly Trend _ " & morun & " Reporting_" & BD & ".xlsx").Worksheets(functionname).Range("$A$12:$BJ$63")
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I can't see your workbook from here, and can't read your mind. What error occurs? Subscript?

What's the workbook name? What's the result of "Monthly Trend _ " & morun & " Reporting_" & BD & ".xlsx"?

What's the worksheet name? What's the value of functionname?
 
Last edited:
Upvote 0
the error i'm getting is: #VALUE!

workbook name is: Monthly Trend _ February Reporting_BD6.xlsx
the result of "Monthly Trend _ " & morun & " Reporting_" & BD & ".xlsx" is: Monthly Trend _ February Reporting_BD6.xlsx
worksheet name is: Marketing Monthly Trend GL
functionname value is: "Marketing Monthly Trend GL"

thanks again for your patience and help.
 
Upvote 0
You sure that's the line that generates the error? That's an odd error for assigning a range variable.

Does the workbook name really have a space on either side of the underscore?
 
Upvote 0
i'm not 100% sure but i've inserted a message box after each string to see what's coming back and each one returned a popup with its expected value except for the range variable which never popped, the function just ends with the error result. Given the possibility my eyes were tricking me I removed the spaces around the underscore but that didn't make a difference. Still getting just a #VALUE! error.
 
Upvote 0
Does the code break, i.e., stop with that line highlighted?

What happens when you step through the code?
 
Upvote 0
it doesn't break. it runs all the way through and returns that #value. not able to step through the code and find the line causing the error.
 
Upvote 0
So does the range get set properly, which is where we've been spinning our wheels?

Time to spend an hour reading Debugging VBA
 
Upvote 0
thanks, will definitely spend some time on this and come back later.

So I placed a breakpoint at the line in question and the variables inside the range variable all had values but the linkfile range variable was equal to "Nothing". Guessing it's not getting set properly.

Code:
Set linkfile = Workbooks("Monthly Trend_" & morun & " Reporting_" & BD & ".xlsx").Worksheets(functionname).Range("$A$12:$BJ$63")
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top