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

 

[VeBeYay]

Have you tried adding a msgbox just before the "Set linkfile = ..." line

MsgBox location & monthly_file & function_tb

to see what you are actually trying to set the range to? Might be missing a space or some punctuation?

 

[grexcelman]

Have you tried adding a msgbox just before the "Set linkfile = ..." line

MsgBox location & monthly_file & function_tb

to see what you are actually trying to set the range to? Might be missing a space or some punctuation?

I've tried that, and that works for me, I can verify each string. but it all falls apart when i try to create a range out of it. I'm ultimately trying to plug this concatenated range into a vlookup function but i can't get past this range situation.

 

[grexcelman]

I think the issue is maybe my syntax is incorrect for creating a range that has been concatenated from strings that link to another workbook on my drive. the punctuation looks alright but thus far no luck in my trouble shooting.

 

[shg]

You can't assign a range variable to a range in a closed workbook.

 

[grexcelman]

You can't assign a range variable to a range in a closed workbook.

got it. do you know of a creative way around this problem?

 

[grexcelman]

got it. do you know of a creative way around this problem?

I have the workbook open and it's still producing #VALUE! errors.

 

[shg]

Post the code and the contents of all relevant variables when the code fails.

 

[grexcelman]

Post the code and the contents of all relevant variables when the code fails.

Provided here:

Function grabmonthly(exp As String, functionTitle As String, BD As String) As LongDim function_tb as String
Dim morun As String
Dim functionname As String
Dim linkfile As Range

If functionTitle = "Marketing" Then
    functionname = "Marketing Info"

'location of the month selection
morun = Range("B7").Value

function_tb = "'[Monthly Trend _ " & morun & " Reporting_" & BD & ".xlsx]" & functionname & " Monthly Trend GL'!$A$12:$BJ$63"
 
Set linkfile = Range(function_tb)

variable contents:
function_tb will read: "'[Monthly Trend _ February Reporting_BD6.xlsx]Marketing Monthly Trend"
morun will read: "February"
BD will read: "BD6"
functionname will read "Marketing"
linkfile essentially is the same as function_tb

 

[shg]

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.