Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-27-2019, 02:07 PM
trevorc trevorc is offline search 2 sheets for match Windows 7 32bit search 2 sheets for match Office 2013
Competent Performer
search 2 sheets for match
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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
Reply With Quote
  #2  
Old 06-27-2019, 11:24 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline search 2 sheets for match Windows 7 64bit search 2 sheets for match Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 06-28-2019, 05:14 AM
NoSparks NoSparks is offline search 2 sheets for match Windows 7 64bit search 2 sheets for match Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #4  
Old 07-01-2019, 01:24 PM
trevorc trevorc is offline search 2 sheets for match Windows 7 32bit search 2 sheets for match Office 2013
Competent Performer
search 2 sheets for match
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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
Reply With Quote
  #5  
Old 07-01-2019, 01:25 PM
trevorc trevorc is offline search 2 sheets for match Windows 7 32bit search 2 sheets for match Office 2013
Competent Performer
search 2 sheets for match
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

Just a quick update,
should have said auto-complete in stead of auto-fill
Reply With Quote
  #6  
Old 07-01-2019, 04:14 PM
NoSparks NoSparks is offline search 2 sheets for match Windows 7 64bit search 2 sheets for match Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Not going to guess, post the workbook so I can test the macro.
Reply With Quote
  #7  
Old 07-01-2019, 07:48 PM
trevorc trevorc is offline search 2 sheets for match Windows 7 32bit search 2 sheets for match Office 2013
Competent Performer
search 2 sheets for match
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

Hi Sparks, I should be able to do that by tomorrow morning, bit busy now....
Reply With Quote
  #8  
Old 07-02-2019, 12:25 PM
trevorc trevorc is offline search 2 sheets for match Windows 7 32bit search 2 sheets for match Office 2013
Competent Performer
search 2 sheets for match
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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
Reply With Quote
  #9  
Old 07-07-2019, 01:07 PM
trevorc trevorc is offline search 2 sheets for match Windows 7 32bit search 2 sheets for match Office 2013
Competent Performer
search 2 sheets for match
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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.
Reply With Quote
  #10  
Old 07-08-2019, 12:59 AM
trevorc trevorc is offline search 2 sheets for match Windows 7 32bit search 2 sheets for match Office 2013
Competent Performer
search 2 sheets for match
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

Pecoflyer
Did you read my first post?
I tried
Reply With Quote
Reply

Thread Tools
Display Modes


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
search 2 sheets for match Trying to match values across multiple sheets coxyada Excel 2 12-13-2016 03:44 PM
search 2 sheets for match 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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:37 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft