Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a worksheet
Is there any way of getting data from a WorkSheet
without actually selecting it. an extract of my code is:- Worksheets("Data").Activate Dim MyArray(40, 40) For i = 2 To Cells(Cells.Rows.Count, "A").End(xlUp).Row - 1 X = Range("A" & i).Value If Mid(Range("C" & i), 5, 1) = "(" Then Y = Left(Range("C" & i), 7) GoTo Line1 End If Y = Left(Range("C" & i), 3) Line1: MyArray(i - 2, 0) = X MyArray(i - 2, 1) = Y Next i ListBox1.List() = MyArray What I'm trying to avoid is the screen changing from a particular WorkSheet to the "Data" worksheet, when the code is run donwb |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a worksheet
Sure, either use a "with" statement or use the worksheet reference
each time the macro access a cell on the Data Sheet or use the worksheet reference in each sheet. I prefer to use a variable to hold the worksheet reference, but it can also be done with Worksheets ("Data"),Range("A" & .... See my two examples. Sub aaa() 'Note the leading dots in the range and cells statements which ' indicate the reference to Worksheets("Data") Dim MyArray(40, 40) With Worksheets("Data") For i = 2 To .Cells(.Cells.Rows.Count, "A").End(xlUp).Row - 1 X = .Range("A" & i).Value If Mid(.Range("C" & i), 5, 1) = "(" Then Y = Left(.Range("C" & i), 7) GoTo Line1 End If Y = Left(.Range("C" & i), 3) Line1: MyArray(i - 2, 0) = X MyArray(i - 2, 1) = Y Next i End With ListBox1.List() = MyArray End Sub Sub bbb() Dim ws As Worksheet Dim MyArray(40, 40) Set ws = Worksheets("Data") For i = 2 To ws.Cells(ws.Cells.Rows.Count, "A").End(xlUp).Row - 1 X = ws.Range("A" & i).Value If Mid(ws.Range("C" & i), 5, 1) = "(" Then Y = Left(ws.Range("C" & i), 7) GoTo Line1 End If Y = Left(ws.Range("C" & i), 3) Line1: MyArray(i - 2, 0) = X MyArray(i - 2, 1) = Y Next i End With ListBox1.List() = MyArray End Sub Hopes this helps. --- Per On 12 Apr., 00:09, "donwb" wrote: Is there any way of getting data from a WorkSheet without actually selecting it. an extract of my code is:- Worksheets("Data").Activate Dim MyArray(40, 40) For i = 2 To Cells(Cells.Rows.Count, "A").End(xlUp).Row - 1 * * X = Range("A" & i).Value * * If Mid(Range("C" & i), 5, 1) = "(" Then * * Y = Left(Range("C" & i), 7) * * GoTo Line1 * * End If * * Y = Left(Range("C" & i), 3) Line1: * * MyArray(i - 2, 0) = X * * MyArray(i - 2, 1) = Y Next i ListBox1.List() = MyArray What I'm trying to avoid is the screen changing from a particular WorkSheet to the "Data" worksheet, when the code is run donwb |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a worksheet
Many thanks Barb & Per.
Both solutions worked for me & solved the problem. donwb "donwb" wrote in message ... Is there any way of getting data from a WorkSheet without actually selecting it. an extract of my code is:- Worksheets("Data").Activate Dim MyArray(40, 40) For i = 2 To Cells(Cells.Rows.Count, "A").End(xlUp).Row - 1 X = Range("A" & i).Value If Mid(Range("C" & i), 5, 1) = "(" Then Y = Left(Range("C" & i), 7) GoTo Line1 End If Y = Left(Range("C" & i), 3) Line1: MyArray(i - 2, 0) = X MyArray(i - 2, 1) = Y Next i ListBox1.List() = MyArray What I'm trying to avoid is the screen changing from a particular WorkSheet to the "Data" worksheet, when the code is run donwb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
Copy data in multiple worksheet tabs into one worksheet | Excel Programming | |||
Scan data on worksheet 2 and display a summary on Worksheet 1 | Excel Programming | |||
Using a column of data from 1 worksheet to extract data from another worksheet | Excel Worksheet Functions | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |