finding a value thats present more than once in a sheet

[monika]

hi

In one of the sheets I am finding COlumn "Q1 2004". THese
words are found in column "U" and Column "CG". How can I
differentiate which one i need to pick???

WHat i am currently doing is right inthe beginning when i
activate this sheet i select the first cell of the sheet:
cells(1,1).select

and since i know theoritically that I am looking for "Q1
2004" at its 1st occurance, hence i fetch the address of
that cell where these words are 1st found.

IS there a solution to this thing?
thanks
Monika

 

[Tom Ogilvy]

Dim rng as Range
set rng = Cells.Find(What:="Q1 2004", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.Select
End if


Using xlByColumns should find the cell from the lowest numbered column.

 

[Alan Beban]

Not sure what you mean by "I fetch" the address, but if the functions in
the freely downloadable file at http://home.pacbell.net/beban are
available to your workbook, the following will select the cell on the
active sheet that contains the nth occurrence of Q1 2004:

Sub test3001()
Dim rng As Range,n as long
n=1
On Error Resume Next
Set rng = Range(ArrayMatch("Q1 2004", Range("A1:E5"), "A")(n, 1))
If Not rng Is Nothing Then rng.Select
End Sub

Alan Beban