Dynamic range offset problem!
 

Greetings.



I have an excel worksheet where details of matches are stored. Each record has 16 columns that are reserved for player names. I need a procedure or a function that will search/find the first occurrence of a given variable (player name) , then the cell pointer ( or ActiveCell) always moves to column B of the row found with the player name to store it in another worksheet.

I used the offset method to go back to column 2 , but the offset could be different in each case, and this is the part that I failed to code.



Here is a simple presentation of the worksheet:



A B C D E F G H

1 SEQ DATE COMP. Opp. Plyr1 ply2 ply3 ply4

2 1 02/11/04 WCQ USA Jones Hall Victor Smith

3 2 04/11/04 WCQ Chile Jones Hall Brown Cobby

4 3 06/11/04 WCQ Mexico Shaw Brown Green Victor

5



Example: Lookup Cobby (in this case at range H3) and the stored variable should be range B3 ) date 04/11/04) which is CellOffset - 6 , but If I lookup "Green" , the CellOffset would be - 5



I hope the problem is clear . Appreciate any help or hint.



Thanks.

Dynamic range offset problem!
 

Majeed,
I didn't find a simple solution, but here's a two-step process that may be off use. First, find the first occurrence of, say, Brown, in each row, then find which column contains the first occurence of Brown.

At the end of your player data, in cells under "WORK", I added the formulas
=HLOOKUP($A$8,E3:H3,1,FALSE)
=HLOOKUP($A$8,E4:H4,1,FALSE)
=HLOOKUP($A$8,E5:H5,1,FALSE)
Cell A8 contains the name of the player to look for.
Cells E3:H5 contain the names of all players.

Cell A9, "1st Date", is, I believe, what you are after. The formula is
=INDEX(B3:B5,MATCH(A8,I3:I5,0))
That's a combination of two formulas:
=MATCH(A8,I3:I5,0)
finds the first occurrence of the desired player in the WORK cells, and returns the relative row number.
The index function returns the date in that row.

Hope that helps.
Chris King

A B C D E F G H
SEQ DATE COMP. Opp. Plyr1 ply2 ply3 ply4 WORK
1 2/11/2004 WCQ USA Jones Hall Victor Smith #N/A
2 4/11/2004 WCQ Chile Jones Hall Brown Cobby Brown
3 6/11/2004 WCQ Mexico Shaw Brown Green Victor Brown

Player 1st Date
Brown 4/11/04


"Majeed" wrote in message ...
Greetings.



I have an excel worksheet where details of matches are stored. Each record has 16 columns that are reserved for player names. I need a procedure or a function that will search/find the first occurrence of a given variable (player name) , then the cell pointer ( or ActiveCell) always moves to column B of the row found with the player name to store it in another worksheet.

I used the offset method to go back to column 2 , but the offset could be different in each case, and this is the part that I failed to code.



Here is a simple presentation of the worksheet:



A B C D E F G H

1 SEQ DATE COMP. Opp. Plyr1 ply2 ply3 ply4

2 1 02/11/04 WCQ USA Jones Hall Victor Smith

3 2 04/11/04 WCQ Chile Jones Hall Brown Cobby

4 3 06/11/04 WCQ Mexico Shaw Brown Green Victor

5



Example: Lookup Cobby (in this case at range H3) and the stored variable should be range B3 ) date 04/11/04) which is CellOffset - 6 , but If I lookup "Green" , the CellOffset would be - 5



I hope the problem is clear . Appreciate any help or hint.



Thanks.

Dynamic range offset problem!
 

Majeed,
This may work for you. It may be a bit dangerous to use a "For Each" loop, since I don't know the order in which the range is searched; presumably it is top to bottom, then left to right, but I don't think we can always count on that being the case.
Chris

Option Explicit

Public Sub Debutdate()
Dim Rng As Range
Dim sPlayerName As String
Dim c As Range
Dim sDebutDate As String
Dim sDebutTeam As String

Set Rng = Worksheets("games").Range("M3:AC450")
sPlayerName = InputBox("Enter Player's Surname")
If sPlayerName = "" Then Exit Sub

For Each c In Rng
If c = sPlayerName Then
sDebutDate = Rng.Cells(c.Row - Rng.Row + 1, -2).Text
sDebutTeam = Rng.Cells(c.Row - Rng.Row + 1, -1).Text
MsgBox "Debut date is " & sDebutDate & vbCr & _
"Debut team =" & sDebutTeam
Exit Sub
End If
Next c

End Sub

"Majeed" wrote in message ...
Hi Chris

Thanks alot for your effort. I am trying your solution , but it is giving me #N/A in both columns. It is a bit late in the morning so, I might be doing something ovbiousley wrong.
My own solutiion was using a VBA code like this:


Public Sub Debutdate()
Dim counter As Integer
Dim Debutdate As Date
Dim DebutTeam As String

Set Rng = Worksheets("games").Range("M3:AC450")
PlayerName = InputBox("Enter Player's Surname")
If PlayerName = False Then Exit Sub

For Each c In Rng
If c = PlayerName Then
' MsgBox "Address of c =" & c.Address
Range(Cells(c.Row, 2)).Select
DebutTeam = Cells(c.Row, 3).Value ' Error occurs here Run-time error '1004' Applications-defined or object defined-error
' Range(c.Address).Offset(0, -12).Value = Debutdate ' This one works if the player is in Column N , which is the begining column for players name.
MsgBox "Debut team =" & DebutTeam
Exit Sub
End If
Next c

End Sub


Note that range Rng is the cells containing the players names in worksheet "games"


There is something wrong with th cell reference that I just can't figure out. Can you see where the problem lies here.

Thanks a million.

Majeed



"Christopher King" wrote in message ...
Majeed,
I didn't find a simple solution, but here's a two-step process that may be off use. First, find the first occurrence of, say, Brown, in each row, then find which column contains the first occurence of Brown.

At the end of your player data, in cells under "WORK", I added the formulas
=HLOOKUP($A$8,E3:H3,1,FALSE)
=HLOOKUP($A$8,E4:H4,1,FALSE)
=HLOOKUP($A$8,E5:H5,1,FALSE)
Cell A8 contains the name of the player to look for.
Cells E3:H5 contain the names of all players.

Cell A9, "1st Date", is, I believe, what you are after. The formula is
=INDEX(B3:B5,MATCH(A8,I3:I5,0))
That's a combination of two formulas:
=MATCH(A8,I3:I5,0)
finds the first occurrence of the desired player in the WORK cells, and returns the relative row number.
The index function returns the date in that row.

Hope that helps.
Chris King

A B C D E F G H
SEQ DATE COMP. Opp. Plyr1 ply2 ply3 ply4 WORK
1 2/11/2004 WCQ USA Jones Hall Victor Smith #N/A
2 4/11/2004 WCQ Chile Jones Hall Brown Cobby Brown
3 6/11/2004 WCQ Mexico Shaw Brown Green Victor Brown

Player 1st Date
Brown 4/11/04


"Majeed" wrote in message ...
Greetings.



I have an excel worksheet where details of matches are stored. Each record has 16 columns that are reserved for player names. I need a procedure or a function that will search/find the first occurrence of a given variable (player name) , then the cell pointer ( or ActiveCell) always moves to column B of the row found with the player name to store it in another worksheet.

I used the offset method to go back to column 2 , but the offset could be different in each case, and this is the part that I failed to code.



Here is a simple presentation of the worksheet:



A B C D E F G H

1 SEQ DATE COMP. Opp. Plyr1 ply2 ply3 ply4

2 1 02/11/04 WCQ USA Jones Hall Victor Smith

3 2 04/11/04 WCQ Chile Jones Hall Brown Cobby

4 3 06/11/04 WCQ Mexico Shaw Brown Green Victor

5



Example: Lookup Cobby (in this case at range H3) and the stored variable should be range B3 ) date 04/11/04) which is CellOffset - 6 , but If I lookup "Green" , the CellOffset would be - 5



I hope the problem is clear . Appreciate any help or hint.



Thanks.