Setting a range equal to a group of variables

[grexcelman]

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.

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

 

[grexcelman]

When you assign a range, you do not (cannot) use the path:

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:

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

 

[shg]

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?

 

[grexcelman]

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.

 

[shg]

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?

 

[grexcelman]

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.

 

[shg]

Does the code break, i.e., stop with that line highlighted?

What happens when you step through the code?

 

[grexcelman]

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.

 

[shg]

So does the range get set properly, which is where we've been spinning our wheels?

Time to spend an hour reading Debugging VBA

 

[grexcelman]

thanks, will definitely spend some time on this and come back later.

 

[grexcelman]

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.

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