VBA findAll Copy & Paste

[simora]

On a Userform, How do I structure a search so that it finds all the values in a selected row that matches the criteria entered in a textbox and a ComboBox, and copies them to another sheet.

Attached are the worksheets & a more detailed explaination.

Thanks

[Simon Lloyd]

This should give you and idea on how to achieve it, there is a worked example of choosing a name (click the button and type either a month in this fashion Jan or a day like this Mon), selecting all occurences and displaying them in a textbox, the cell addresses are then clickable, you should be able to manipulate this to suit.

[simora]

Simon Thanks:

I'll need to re-think the way I was looping.

[mdmackillop]

Try this. It is coded for Account Number search only. Both books must be open.

[simora]

Hi:

Still having a problem getting the code to get the first value in the range as the first selected value. My code is a modification of this http://www.cpearson.com/excel/FindAll.aspx

Any idea why Its selecting the first value in the Range as the last found cell?

[VBA]
With Sheets("Sheet1").Range("D3:P3")
' Set col = Range("D3:P3")
Set LastCell = .Cells(.Cells.count)
End With
Set FoundCell = Range("D3:P3").Find(what:=ComboBox1.Value, after:=LastCell)

If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
End If
Do Until FoundCell Is Nothing
Debug.Print FoundCell.Address
Set FoundCell = Range("D3:P3").FindNext(after:=FoundCell)

With FoundCell
rsCol = FoundCell.Column

End With

Cells(rsRow, rsCol).Select
MsgBox ActiveCell.Address & " " & " Cell Amount is " & " " & ActiveCell.Value

If FoundCell.Address = FirstAddr Then
Exit Do
End If
Loop
[/VBA]


Suggestions & ideas welcome

Thanks

[mdmackillop]

You are looking after the first found cell in your loop. Check FindNext in Help for correct looping syntax.

[simora]

Sorry:

Same worksheet as provided earlier;
Attached are the worksheets & a more detailed explaination.

Thanks
Attached FilesVB-Sheets.zip

[mdmackillop]

Is Post 4 not giving the correct result? If not, can you please show the correct result in Postings.

[simora]

mdmackillop :

I dont get it. Did the help search etc..etc.. I tried every variation and I'm still either finding the 3rd value first, or the 2nd value.

How can I fix this?

[simora]

mdmackillop :
Post 4 did give the correct results, however, I'm trying to understand the code that I presented as I'm using the process for multiple operations. I did not see you post for some reason, but understanding is part of the learning process. How can I fix this? I really appreciated your help.

[GTO]

Cross-posted: http://www.mrexcel.com/forum/showthread.php?t=445002

[mdmackillop]

Thanks GTO

[GTO]

Thanks GTO

You bet Malcom