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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Zarlot - 28 May 2008 15:00 GMT

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.

Reply to this Message

JW - 28 May 2008 15:22 GMT

> 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
[quoted text clipped - 12 lines]
>
> 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)

Reply to this Message

Zarlot - 28 May 2008 19:27 GMT

> > 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
[quoted text clipped - 27 lines]
>
> - 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

Reply to this Message

Bernie Deitrick - 28 May 2008 15:25 GMT

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

Reply to this Message

Zarlot - 28 May 2008 15:57 GMT

On May 28, 10:25 am, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:

> Zarlot,
>
[quoted text clipped - 32 lines]
>
> - 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.

Reply to this Message