I have a sheet with about 5k rows and a dozen columns. Column A cotains
cusip#, the rest of columns info related to that. I can find the number and
the value of unique cusips, which i put into an array. Now i want to find
each instance of a cusip and the row(I need the row#) it's on, grab what i
need, and see if there is another instance of that cusip and if not search
for the next one. Can someone help me get started with the Find and Find
next, thanks in advance!

Hi,

Based on my understanding you want to search some characters in an excel
sheet and know its row number.
Currently I did not understand the rule/characters of cusip#.
So here I show some about code to search certain character in a excel sheet.

Assume I have a sheet as below.
1
2
3
4
5
6
2
4
6


First I activate cell A1(content 1)
Then run the macro below to search content 2, it will output two number,
2,7 which means it found an result on row 2,7.

Sub Macro1()
Dim c As Range
Set c = Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, MatchByte:=False, SearchFormat:=False)
Debug.Print c.Row
c.Activate
Set c = Cells.FindNext(After:=ActiveCell)
Debug.Print c.Row
End Sub

Please have a try and let me know the result.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Peter,
First, thanks, the cusip is just a unique id, is there any way to alter your
code so that it stops at the first instance of 2, gives me the row# and
allows me to grab other info on that row, then search for the next occurance
of 2, if it does not find it search for the next item in my array and repeat
this procedure, also is there a way of not having to select the cell as it
slows down the code? thanks again!

Mike

""Peter Huang" [MSFT]" wrote:

Hi,

Based on my understanding you want to search some characters in an excel
sheet and know its row number.
Currently I did not understand the rule/characters of cusip#.
So here I show some about code to search certain character in a excel sheet.

Assume I have a sheet as below.
1
2
3
4
5
6
2
4
6


First I activate cell A1(content 1)
Then run the macro below to search content 2, it will output two number,
2,7 which means it found an result on row 2,7.

Sub Macro1()
Dim c As Range
Set c = Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, MatchByte:=False, SearchFormat:=False)
Debug.Print c.Row
c.Activate
Set c = Cells.FindNext(After:=ActiveCell)
Debug.Print c.Row
End Sub

Please have a try and let me know the result.

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Mike,


You may try to check the code below.

1. We find the 2 from the cell A1,
2. If c is Nothing, i.e. not found, we start another search.
3. If c is found we use Cells(rownumber,columnnumber) to output the value
in the same row
4. and then find Next

If you want to search in an array, I think you can adjust the follow sub to
a function and then invoke the function in a loop.

e.g.
Function SearchX(string X) as boolean
'Search code
End Function

Sub SearchinArray()

While condition

'Search( X)...
Wend

End Sub





Sub Test()
Dim c As Range
Set c = Cells.Find(What:="2", After:=Cells(1, 1), LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, MatchByte:=False, SearchFormat:=False)

If c Is Nothing Then
MsgBox "not found"
'Search another keyword
Else
Dim rNumber As Long
rNumber = c.Row

'output other column cell in the row
Debug.Print Cells(rNumber, 1)
Debug.Print Cells(rNumber, 2)

Set c = Cells.FindNext(c)
Debug.Print c.Row
End If

End Sub



Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Mike,

Just want to say Hi, and I was wondering how everything is going.
If anything is unclear, please let me know.
It is my pleasure to be of assistance.


Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.