finding a value thats present more than once in a sheet

  • Thread starter Thread starter monika
  • Start date Start date
M

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
 
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.
 
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
 
Back
Top