View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Alan Beban[_4_] Alan Beban[_4_] is offline
external usenet poster
 
Posts: 171
Default finding a value thats present more than once in a sheet

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

monika wrote:
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