Find command for multiple occurances and display

[scott micklo]

Hello again

I am looking to see if its possible in vba to use FIND code to locate multiple occurances in a sheet, and display all of them in a message box on the screen.

I have come across several where they display the results in another worksheet, but looking for a msgbox display. I am wondering if I would use an array to house the address of each occurance I find and then show them all.

thanks in advance.

[Gatti]

What "occurance" you need to display?

Please Login or Register  to view this content.

This way I'm displaying EVERY cell that has values on UsedRange at sheet.

If you need more specific occurance, tell me

[scott micklo]

Thanks. Here is the code I already have written, and it will show just one occurance. There may be up to 30 or more occurances

Please Login or Register  to view this content.

So, I am looking to integrate your code into my code, but I am not quite sure how to marry them.

Thanks

[scott micklo]

Gatti

Thanks for the assistance. I have been working on this and I think I am close. I was able to intergrate the 2 codes almost. Right now, I get a message box full of blank lines (representing all cells that are not blank). I am onyt attempting to return the location of the part numbers that are found. I was attempting to attach my whole project so you could see it, but I am unable (I think) to attach any file on these replies. Here is the code I am up to:

[CODE][/
Private Sub cmdFind_Click()



Application.ScreenUpdating = False

Dim PrtNo As String
Dim srchRslt As Range
Dim strMsgBox As String
Dim Locate As Range

strMsgBox = ""

PrtNo = InputBox("What part number are you looking for?")

Set srchRslt = Worksheets("Overview").Range("a1:zz10000").Find(What:=PrtNo, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

Set srchRslt = srchRslt.Offset(0, -2)

While srchRslt <> "WITKO Rcvd Date"
Set srchRslt = srchRslt.Offset(-1, 0)
Wend

Set Locate = srchRslt.Offset(-1, 0)

For Each Locate In ActiveSheet.UsedRange

If (Locate.Interior.ColorIndex = 37 Or 48) Then strMsgBox = strMsgBox & vbCrLf & Locate
Next Locate



MsgBox strMsgBox

End Sub

CODE]

I was going to try and use the cell hilighted color for its location, but that isnt working the way I had anticipated.

Thanks for all your assistance.

Scott

[LOKLUK]

hello

what is "WITKO Rcvd Date" ?name of colomne or values ?
if is colomn do you want to step on it ?

for the loction you can simply use :

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[scott micklo]

hello

what is "WITKO Rcvd Date" ?name of colomne or values ?

Its the name of the column.


Example of one


N101C1 ********* Location - For example this one is in cell A2 (merged cell A2->D2)
WITKO Rcvd Date Description Part # Pallet Tag ********* These are column headers. The table is 20 rows, and "WITKO Rcvd Date" is in cell A3

00151 ************ This is one occurnence of the search for part number. Its in cell C7




















if is colomn do you want to step on it ?

for the loction you can simply use :

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

So, code finds the part number searched for (in this case, C7). Steps back using offset to cell A7, and works back up to locate the cell with "WITKO Rcvd Date". Once there, the "Location" is found with an offset up 1 row. In this case, Location = N101C1.

What i am hoping to retun in a message box is the Location, N101C1.

I can get one occurance of it.My probelm is now the part number searched for may be in up to 30 (or more) "Locations". And I need to show where they all are to the user.

Does that help?

[LOKLUK]

hello
NO, Please give us a sample of your project as workbook attachment, small workbook ,table with anonymizes values
And please riposte Your question (a new post) to be sure that author members will participate