Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable to select a cell on a worksheet..."Subscript out orrange"
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable to select a cell on a worksheet..."Subscript outor range"
On May 28, 10:00*am, Zarlot wrote:
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable to select a cell on a worksheet..."Subscript out or range"
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable to select a cell on a worksheet..."Subscript outor range"
On May 28, 10:25*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: 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" wrote in message ... 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.- Hide quoted text - - 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable to select a cell on a worksheet..."Subscript outor range"
On May 28, 10:22*am, JW wrote:
On May 28, 10:00*am, Zarlot wrote: 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)- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Range("C100:D200").Select with variable names | Excel Programming | |||
Format Cell to show "-1" in subscript | Excel Discussion (Misc queries) | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
"Subscript out of range" error for: Workbooks("Test1.xls").Save | Excel Programming |