Using a variable to select a cell on a worksheet..."Subscript out orrange"

[Zarlot]

See code below. I know this is pretty simple, but what I'm trying to
do is pull data from a cell (in the test.xls spreadsheet) that has a
varying (based upon what someone writes in the cell) text input. I
then want the macro I run to reference this cell to select which
worksheet it is going to select in the spreadsheet in which I'm
running the macro.




Dim rng As RangeDim ws1 As Worksheet
Dim WhichSheetCell As String
WhichSheetCell = Workbooks.Open("Z:\Invoices
\test.xls").Worksheets("Sheet1").Cells(3, 2)
Set ws1 = Sheets("WhichSheetCell")


Set rng = ws1.Range("A1:N" & Rows.Count)

I get a "subscript out of range" error when I run it.

 

[JW]

See code below.  I know this is pretty simple, but what I'm trying to
do is pull data from a cell (in the test.xls spreadsheet) that has a
varying (based upon what someone writes in the cell) text input.  I
then want the macro I run to reference this cell to select which
worksheet it is going to select in the spreadsheet in which I'm
running the macro.

Dim rng As RangeDim ws1 As Worksheet
 Dim WhichSheetCell As String
  WhichSheetCell = Workbooks.Open("Z:\Invoices
\test.xls").Worksheets("Sheet1").Cells(3, 2)
    Set ws1 = Sheets("WhichSheetCell")

Set rng = ws1.Range("A1:N" & Rows.Count)

I get a "subscript out of range" error when I run it.

2 things. You have ws1 declared as a Worksheet, but you are trying to
assign a range to it (.Cells(3,2) from the WhichSheetCell variable).
WhichSheetCell is a variable, but you have quotes around it in your
Set ws1 statement.

I'm not exactly sure where the Cells(3,2) is coming into play, but
here is a snippet you can use for an example.

Dim ws1 As Worksheet, rng As Range
Set ws1 = Workbooks.Open("Z:\Invoices\test.xls") _
.Worksheets("Sheet1")
Set rng = ws1.Range("A1:N" & Rows.Count)

 

[Bernie Deitrick]

Zarlot,

Change

Set ws1 = Sheets("WhichSheetCell")

to

Set ws1 = Sheets(WhichSheetCell)

(Remove the double quotes.)

Still, you will probably need some error checking in there...

HTH,
Bernie
MS Excel MVP

 

[Zarlot]

Zarlot,

Change

Set ws1 = Sheets("WhichSheetCell")

to

Set ws1 = Sheets(WhichSheetCell)

(Remove the double quotes.)

Still, you will probably need some error checking in there...

HTH,
Bernie
MS Excel MVP









- Show quoted text -

Yeah, I think one of my problems is that I'm referencing the cell when
I really want to reference the contents of the cell...the text in the
cell. But what I will try this.

 

[Zarlot]

2 things.  You have ws1 declared as a Worksheet, but you are trying to
assign a range to it (.Cells(3,2) from the WhichSheetCell variable).
WhichSheetCell is a variable, but you have quotes around it in your
Set ws1 statement.

I'm not exactly sure where the Cells(3,2) is coming into play, but
here is a snippet you can use for an example.

    Dim ws1 As Worksheet, rng As Range
    Set ws1 = Workbooks.Open("Z:\Invoices\test.xls") _
        .Worksheets("Sheet1")
    Set rng = ws1.Range("A1:N" & Rows.Count)- Hide quoted text -

- Show quoted text -

Thanks for the help. Cell(3, 2) comes into play because it's the cell
which has in it the worksheet name (it's types in there) that the
macro is supposed to use to find on another spreadsheet and then
select that worksheet to do all of its work on.

Kind of like... I enter on the main spreadsheet what worksheet I want
it to look for on the other, and I put it in Cell(3, 2).

Really... the more I think about it... the more really a Message/Input
Box would be a lot easier and using the input to the messagebox as the
variable... I am really a novice though but I'll try to figure this
out.

Thanks