Searching using a Named Range

[Skuperman]

I have a spreadsheet with rows of customer data. Each Row has a column with Distributor Name (Column H) and a column with Customer name (Column J) I have another sheet in my Workbook named “OEMs” which has a Named Range of “CUSTOMERS”
I want to search through all the rows in my spreadsheet (Sheet1) and see if either Column H or J matches a name in CUSTOMERS (named range) and if it matches change the string in Column T to say OEM. If Columns H or J do not match then just go to the next row until it reaches the end of the rows
I have been messing with this for a couple of weeks and just cannot find a way that works. Any help would really be appreciated

 

[Jerry Sullivan]

Hi Steve,

If you want a formula approach and you don't have any other data being entered into column T, you could use this formula.

Enter into Cell T2, then copy down...

=IF(OR(ISNUMBER(MATCH($H2,CUSTOMERS,0)),ISNUMBER(MATCH($J2,CUSTOMERS,0))),"OEM","")

 

[Skuperman]

Thanks Jerry, I had tried something similar but could not get it to work as well as your version.
I do have some text already in column T that I only want to overwrite when I need to change it to OEM.
I'll also need to add it into my VBA.
I know I can write a Macro to run it on the data but then I would need another routine to go back and add the text back into the cells that it erased. Unfortunately that would not be easy as the text is linked into a catalog system.
When I started this it did not sound that difficult but it really is much more of a challenge than I expected.

Hi Steve,

If you want a formula approach and you don't have any other data being entered into column T, you could use this formula.

Enter into Cell T2, then copy down...

=IF(OR(ISNUMBER(MATCH($H2,CUSTOMERS,0)),ISNUMBER(MATCH($J2,CUSTOMERS,0))),"OEM","")

 

[Jerry Sullivan]

You could use a macro that places "OEM" on rows that the criteria is met, and leaves unchanged the other rows.

Just ask if you want some help coding that.

 

[Skuperman]

You could use a macro that places "OEM" on rows that the criteria is met, and leaves unchanged the other rows.

Just ask if you want some help coding that.

I was thinking pretty much the same. Write a Macro to use the formula and have it put the OEM into say Column Z, then a macro that if Column Z has OEM copy it to T or something like that.
I'm sure you have a more effecient idea in mind
I can use all the help I can get on this it has been dragging on for a few weeks and is getting painful

 

[Jerry Sullivan]

Here's some code that you can try. There's more efficient ways to do this, but if you are relatively new to VBA, I think this is easier to follow.

Sub MarkOEM()
 '--adds "OEM" to field in rows where criteria is met
 
 Dim lLastRow As Long, lNdxRow As Long
 Dim rCustomerLookup As Range, rDistributorNames As Range
 Dim rCustomerNames As Range, rOEM As Range
 
 Const lFIRST_ROW As Long = 2
 
 With ActiveSheet
   '--define ranges to read and write
   lLastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
   Set rCustomerLookup = ThisWorkbook.Names("CUSTOMERS").RefersToRange
   Set rDistributorNames = .Range("H" & lFIRST_ROW & ":H" & lLastRow)
   Set rCustomerNames = .Range("J" & lFIRST_ROW & ":J" & lLastRow)
   Set rOEM = .Range("T" & lFIRST_ROW & ":T" & lLastRow)
   
   '-step through each row
   For lNdxRow = 1 To lLastRow - lFIRST_ROW + 1
      If IsNumeric(Application.Match(rDistributorNames(lNdxRow).Value, rCustomerLookup, 0)) Or _
         IsNumeric(Application.Match(rCustomerNames(lNdxRow).Value, rCustomerLookup, 0)) Then
         '--criteria is met- write OEM marker
         rOEM(lNdxRow).Value = "OEM"
      End If
   Next lNdxRow
   
 End With
End Sub

 

[Skuperman]

Thanks, I'm traveling for business this week but when I get a chance in a hotel I'll give it a try and get back to you.
Thanks for the help
Steve

Here's some code that you can try. There's more efficient ways to do this, but if you are relatively new to VBA, I think this is easier to follow.

Sub MarkOEM()
 '--adds "OEM" to field in rows where criteria is met
 
 Dim lLastRow As Long, lNdxRow As Long
 Dim rCustomerLookup As Range, rDistributorNames As Range
 Dim rCustomerNames As Range, rOEM As Range
 
 Const lFIRST_ROW As Long = 2
 
 With ActiveSheet
   '--define ranges to read and write
   lLastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
   Set rCustomerLookup = ThisWorkbook.Names("CUSTOMERS").RefersToRange
   Set rDistributorNames = .Range("H" & lFIRST_ROW & ":H" & lLastRow)
   Set rCustomerNames = .Range("J" & lFIRST_ROW & ":J" & lLastRow)
   Set rOEM = .Range("T" & lFIRST_ROW & ":T" & lLastRow)
   
   '-step through each row
   For lNdxRow = 1 To lLastRow - lFIRST_ROW + 1
      If IsNumeric(Application.Match(rDistributorNames(lNdxRow).Value, rCustomerLookup, 0)) Or _
         IsNumeric(Application.Match(rCustomerNames(lNdxRow).Value, rCustomerLookup, 0)) Then
         '--criteria is met- write OEM marker
         rOEM(lNdxRow).Value = "OEM"
      End If
   Next lNdxRow
   
 End With
End Sub

 

[Skuperman]

Hey Jerry, the code worked perfectly! Thanks so much for your help. This thing had me stumped but your code did the trick. I am extremely grateful for your help
Steve

Thanks, I'm traveling for business this week but when I get a chance in a hotel I'll give it a try and get back to you.
Thanks for the help
Steve

 

[Skuperman]

Hey Jerry, so I thought it looked good in some brief testing. My demo spreadsheet had about 50 rows of data and it looked good. I tried it on a new spreadsheet with the same data but 100,000 rows and it missed most of the names when searching.
My Named Range is written into the VBA and when I run the program it creates a new sheet with the list and then makes it a Named Range and changes the name on the sheet, the VBA then goes back to Sheet1 and runs the search.
I found it only works if I copy the customer name from the row and then paste it into the VBA. If I type it into VBA it will not find it in the search. My thought is that maybe it’s picking up hidden text codes but I cannot see it. I did try to copy the name from the VBA and write it over the customer name but that also does not work. maybe try using wildcards in the search? Any suggestions on this?

Hey Jerry, the code worked perfectly! Thanks so much for your help. This thing had me stumped but your code did the trick. I am extremely grateful for your help
Steve

 

[Jerry Sullivan]

Hi Steve,

You've got a lot of moving parts there!

There's several places in the process that might not be working as intended. Those fall into the categories of the ranges not being assigned as intended or the match not working.

Before giving you instructions on how to do some more methodical testing, let's check one of the more likely causes.

In the code I suggested, I used ActiveSheet since I didn't know the name of the sheet that you've now identified as "Sheet1". Since you've combined this with your other code, it's possible that Sheet1 isn't the ActiveSheet when this code is executed.

Try replacing...

With ActiveSheet

with...

With ThisWorkbook.Sheets("Sheet1")

If that doesn't fix things, I'll suggest some other steps.