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

 

[shg]

What happend if you replace the variables with literals?

Set linkfile = Workbooks(" Monthly Trend _ February Reporting_BD6.xlsx").Worksheets("Marketing Monthly Trend GL").Range("$A$12:$BJ$63")

 

[grexcelman]

same thing. linkfile = nothing

 

[shg]

In what module does the code appear?

In the Immediate window, enter

? Workbooks("Monthly Trend _ February Reporting_BD6.xlsx").Name

What do you see?

 

[grexcelman]

In what module does the code appear?

In the Immediate window, enter

? Workbooks("Monthly Trend _ February Reporting_BD6.xlsx").Name

What do you see?

Run-time error '9':
Subscript out of range

from what i can tell the name definitions all look correct and my workbook I'm pointing to is still open.

 

[Spintzerb2]

I apologize if this has already been answered in another post, but I have a question that may be relatively simple for someone on here:

Can someone provide the formula that will place a specific dollar amount dependent on the range of another cell.
For example, if F16 is between $10000-$15000, then E19 equals $700.

 

[shg]

@Spintzer: Please start your own thread.

@grexcelman: That means there is no workbook open with that exact name (aside from case). Computers are annoyingly literal.

 

[grexcelman]

It printed in the immediate window fine. The worksheet range is likely the issue. whats the appropriate way to print that? same syntax
in that window as for workbooks?

this produced an error:

? Worksheets("Marketing Monthly Trend GL").range("$A$12:$BJ$63").Name

 

[grexcelman]

It printed in the immediate window fine. The worksheet range is likely the issue. whats the appropriate way to print that? same syntax
in that window as for workbooks?

this produced an error:

? Worksheets("Marketing Monthly Trend GL").range("$A$12:$BJ$63").Name

I also tried testing the full line in the immediate window:

? Workbooks("Monthly Trend _ February Reporting_BD7.xlsx").Worksheets("Marketing Monthly Trend GL").Range("$A$12:$BJ$63").Name

it kicked out this error:

Run-time error '1004': Application-defined or object-defined error.

 

[shg]

? Workbooks("Monthly Trend _ February Reporting_BD7.xlsx").Worksheets("Marketing Monthly Trend GL").Name

 

[grexcelman]

? Workbooks("Monthly Trend _ February Reporting_BD7.xlsx").Worksheets("Marketing Monthly Trend GL").Name

that worked as well. kicked out the worksheet name without error.