Getting data from a worksheet

[donwb]

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

 

[Per Jessen]

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.

 

[donwb]

Many thanks Barb & Per.
Both solutions worked for me & solved the problem.
donwb