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.
Cross-posted: http://www.mrexcel.com/forum/showthread.php?t=445002
mdmackillop
01-31-2010, 10:42 AM
Thanks GTO
Thanks GTO
You bet Malcom:hi:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.