Array & variable

[Guest]

Hi Group,
I have gone through a worksheet that had ID numbers in it and put those IDs
in a variable ID1, ID2, ID3...ID10. I am then going over to another worksheet
and doing a Find with in a For...Next construct

For Q = 1 To 10
Selection.Find(What:=("ID"& Q), After:.........
Next

Above does not work, although a hard code instead of =("ID"& Q), does work.
I have tried varous variations and concantinations to bring in the varible,
but have not been able to. ID1, the varaible, contains the number 4, but I
can not get that into the search.

Thanks,

 

[Guest]

David,
Put your IDs into an array e.g ID(10) and then use:

Selection.Find(What:= ID(Q),After:.........

 

[Guest]

Hi Troppers,

I tried to make it simple, thinking it would be able to accomplish the find.
I am reallt pulling in the ID, another variable i am calling W, one called L
and finally Pt. These are currently pulled in by wallking down a column and
and pulling values via an offset. So I have ID1 through ID10, W1 through W10,
L1 through L10 and Pt1 through Pt10.

I am not very good at setting up arrays and getting the values into the the
array. I believe this makes it a 10x4 array, so some thing like Dim
PlayerData(10,4), will set up the dimention, but I am not sure what to do
next, as I walk down the sheet with a simple Activecell.Offset(1,0).Select.

Thanks,

 

[Guest]

David,
Assuming your array is PlayerDaya(10,4), you could populate it
as follows:


Dim PlayerData(10,4) as Variant (or Integer/Long if all values are numeric)


..........
PlayerData(n,1)="ID data" e.g. ActiveCell.value for ID data
PlayerData(n,2)="W data"
PlayerData(n,3)="L Data"
PlayerData(n,4)="Pt Data"
n=n+1

where n has values in range 1 to 10.

So ID1 is in Player(1,1), W1 is in PlayerData(1,2) .... ID10 is in
PlayerData(10,1).

You know the value of n as you effectively use it to assigning ID1, W1 etc .
Set n=0 initially and increment by 1 when you have assigned the last value
for that index.

You can replace ID(Q) in the FIND statement by PlayerData(Q,i) where i has a
value of 1 to 4 corresponding to ID,W,L and Pt.

HTH

 

[Bob Phillips]

Assuming the data is in a worksheet, with columns for ID, W, L and Pt.

Why don't you name the Id Range, ay rngID, and then use something like

For Q = 1 To 10
With Range("rngID")
myVal = .Offset(0Q,0) & .Offset(Q,1) & .Offset(Q,2) &
..Offset(Q,3)
End With
Selection.Find(What:=("ID"& Q), After:.........
Next



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you Toppers, arrays always get me and I had made this vastly more
complicated than I needed to. I thought I needed set value and stuff like
that, but what you laid out worked great. Thanks again.

Sincerely,