PDA

View Full Version : VBA findAll Copy & Paste



simora
01-31-2010, 02:28 AM
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
01-31-2010, 03:30 AM
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
01-31-2010, 04:17 AM
Simon Thanks:

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

mdmackillop
01-31-2010, 04:33 AM
Try this. It is coded for Account Number search only. Both books must be open.

simora
01-31-2010, 05:58 AM
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?


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



Suggestions & ideas welcome

Thanks

mdmackillop
01-31-2010, 06:49 AM
You are looking after the first found cell in your loop. Check FindNext in Help for correct looping syntax.

simora
01-31-2010, 07:00 AM
Sorry:

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

Thanks
Attached Fileshttp://www.vbaexpress.com/forum/images/attach/zip.gifVB-Sheets.zip (http://www.vbaexpress.com/forum/attachment.php?attachmentid=2665&d=1264930049)

mdmackillop
01-31-2010, 07:03 AM
Is Post 4 not giving the correct result? If not, can you please show the correct result in Postings.

simora
01-31-2010, 07:17 AM
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
01-31-2010, 07:32 AM
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
01-31-2010, 10:36 AM
Cross-posted: http://www.mrexcel.com/forum/showthread.php?t=445002

mdmackillop
01-31-2010, 10:42 AM
Thanks GTO

GTO
01-31-2010, 12:30 PM
Thanks GTO

You bet Malcom:hi: