#1
|
|||
|
|||
search 2 sheets for match
Hi All,
I'm trying to search 2 sheets for a customer name and if found copy/paste some of the cells into the same row. This works great when I'm searching on the same sheet and returns the data correctly, how can I add to this code so if it can't find a match in the current sheet it looks in another sheet for the data there, and if found copy the same range of cells into the current sheet. Any help is appreciated, even just a point in the right direction would be great. P.S. I tried to wrap my code in code tags but it wouldn't let me do it for some reason Private Sub Worksheet_Change(ByVal Target As Range) Dim C As Range 'limit to single cell If Target.Count > 1 Then Exit Sub With Sheets("Clipsal Customer") 'monitor specific range If Intersect(Target, .Range("H4:H5000")) Is Nothing Then Exit Sub 'dealing with the entry If Target.Value <> "" Then '.Find has some persistence Set C = .Range("H4:H" & Target.Row - 1).Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) If Not C Is Nothing Then 'copy from C.row to Target.row Application.EnableEvents = False .Range("I" & Target.Row).Resize(, 4).Value = .Range("I" & C.Row).Resize(, 4).Value .Range("Y" & Target.Row).Resize(, 9).Value = .Range("Y" & C.Row).Resize(, 9).Value Application.EnableEvents = True End If End If End With End Sub |
#2
|
||||
|
||||
Please wrap your code with code tags (#button)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
untested, but maybe something like
Code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim C As Range 'limit to single cell If Target.Count > 1 Then Exit Sub 'monitor specific range If Intersect(Target, Range("H4:H5000")) Is Nothing Then Exit Sub 'dealing with the entry If Target.Value <> "" Then 'search this sheet With Sheets("Clipsal Customer") Set C = .Range("H4:H" & Target.Row - 1).Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) End With If C Is Nothing Then ' meaning it was NOT found, so... ' search the other sheet With Sheets("whatever its name is") Set C = .Range("whatever the range is").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) End With End If If Not C Is Nothing Then 'copy from C.row to Target.row Application.EnableEvents = False Target.Offset(, 1).Resize(, 4).Value = C.Offset(, 1).Resize(, 4).Value Target.Offset(, 17).Resize(, 9).Value = C.Offset(, 17).Resize(, 9).Value Application.EnableEvents = True Else MsgBox Target.Value & " was not found." End If End If End Sub |
#4
|
|||
|
|||
Thanks for the reply Sparks,
unfortunately I cannot get it to work the way I need it to. A bit more of an explanation may help, I am trying to archive of some records of customer repairs to another sheet. When I enter a customers name that is already in the main sheet it will auto-fill with that name, the worksheet.change event fires after hitting return and gets the data from the row were the auto-fill found the match. This only works on the current sheet. As the auto-fill only works on the current sheet/row I can't figure out how to also look at the archive sheet for the auto-fill data. Is this even possible to do? regards Trevor |
#5
|
|||
|
|||
Just a quick update,
should have said auto-complete in stead of auto-fill |
#6
|
|||
|
|||
Not going to guess, post the workbook so I can test the macro.
|
#7
|
|||
|
|||
Hi Sparks, I should be able to do that by tomorrow morning, bit busy now....
|
#8
|
|||
|
|||
Hi again,
After doing some research it looks like it can't be done, I'll have to think of another way to do what I need. Thanks for your help anyway. regards Trevor |
#9
|
|||
|
|||
Sort of got it going,
Well is seems that after trying various methods I have a result that is sort of OK. using the above code I also had the create a validation list on a separate sheet, and copy all records to that sheet, I can now hide my closed records and they are still found by autocomplete and the fields then updated by the VBA code that finds the match in the extra table. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Search and Paste for accounts on 2 sheets | Brian13 | Excel Programming | 1 | 06-05-2017 09:59 PM |
Trying to match values across multiple sheets | coxyada | Excel | 2 | 12-13-2016 03:44 PM |
Using If function in VBA to match data from two sheets | cinstanl | Excel Programming | 1 | 12-06-2016 10:37 AM |
compare, match and count cell contents between sheets | bobsone1 | Excel | 11 | 08-07-2014 10:34 PM |
If two geographical data match in two sheets, copy unique id/code found in one sheet | alliage | Excel | 1 | 09-01-2011 05:23 AM |