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

 

[Skuperman]

No luck on that. I had a similar line already in the code to direct the program back to Sheet1 after creating the Named Range sheet.
Sheets("Sheet1").Select, I think it worked pretty much the same as With ThisWorkbook.Sheets("Sheet1")
Ready to go deep on this
Steve

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.

 

[Jerry Sullivan]

Here's some next steps...

Add a Stop statement to the code as shown below. This will pause the execution of the code and allow you to do some testing.

 With ThisWorkbook.Sheets("Sheet1")
   '--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)
[COLOR="#0000CD"][B]Stop[/B][/COLOR]
   '-step through each row
   For lNdxRow = 1 To lLastRow - lFIRST_ROW + 1

Run your code, and it will stop at that added line. At this point, you can use the Immediate Window in the VB Editor to test the assignment of variables. If the Immediate Window isn't displayed enter Ctrl-G to display it.

In the Immediate Window, type or paste this statement, then hit the Enter key

?rDistributorNames.Address(1,1,xlA1,1)

Excel should return the value of that address property- something like:
'[MyBookName.xlsm]Sheet1'!$H$2:$H100002

First test the range variables in Sheet1:
?rDistributorNames.Address(1,1,xlA1,1)
?rCustomerNames.Address(1,1,xlA1,1)
?rOEM.Address(1,1,xlA1,1)

Then test your Named Range:
?rCustomerLookup.Address(1,1,xlA1,1)

If all those ranges are what you'd expect them to reference, then the problem is probably with the matching.

Try the tests above before we move on to testing the matching.

 

[Skuperman]

Yes, I think they all look correct. The worksheet has 992 rows and my OEMs sheet has 6
?rDistributorNames.Address(1,1,xlA1,1)
[Steve_testWB.xlsm]Sheet1!$H$2:$H$992
?rCustomerNames.Address(1,1,xlA1,1)
[Steve_testWB.xlsm]Sheet1!$J$2:$J$992
?rOEM.Address(1,1,xlA1,1)
[Steve_testWB.xlsm]Sheet1!$T$2:$T$992
?rCustomerLookup.Address(1,1,xlA1,1)
[Steve_testWB.xlsm]OEMs!$A$1:$A$6

 

[Jerry Sullivan]

Ok, let's pursue finding a problem with the matching.

Find an example of a row on Sheet1 that has one of the 6 OEM's in either the Customer or Distributor names field, but that the VBA code wasn't marking with "OEM".

First manually try to compare the values by copy-pasting the cell from the OEM sheet to Sheet1 on the same row as the record that was failing to find a match. Then do a simple formula in a blank cell to test if Excel considers those values equal.

e.g. assuming you've pasted the OEM value into Sheet1!Z123 and it appears to match Sheet1!J123, enter this formula in Sheet1!AA123:
=Z123=J123

If that returns False, then the problem is that those values don't match exactly. You can compare the two values character by character to find the difference.
If True, we can do some more tests to see why the VBA code doesn't find those to be matching.

 

[Skuperman]

The test shows as False. The text looks identical, even checked the font size, type and format. I downloaded a program to look for hidden characters and it does show trailing hidden characters or spaces in the cells of my table. Happy to send you a screen shot but I don't see how on this site. So the question is how to handle them. I can only think to add a wildcard to the end of the text string search ?

 

[Skuperman]

Hey Jerry, before you spend any more time on this, I'm going to download a new version of the data. It may be the garbage got added in when the data was imported or even in just the number of copies of it that I have played with may have corrupted it. I won't be able to get to it until this evening but I'll let you know if that works

 

[Jerry Sullivan]

Hi Steve,

Ok that's fine. If those extra characters are part of the typical download, I think you'll be better off cleaning that data as part of your process, instead of modifying the match with wildcards.

 

[Skuperman]

I dug into the issue. If I use Lens it tells me that columns H & J have 35 characters. Looking at the cells there can be a few letters to 30. I used Trim and it does delete the spaces but the result is then in a different column. Any suggestions on how to correct this in the code?

 

[Jerry Sullivan]

Trim will only remove space characters from the front and end of a text string. Do those strings have any other non-print characters other than spaces? If so, the Clean function, which removes some non-print characters might work better. If the strings are some non-print characters that Clean does not remove, we could make a custom VBA function to remove those character.

Try adding a temporary column on a copy of your workbook to test if using Trim and Clean allows Excel to Match the text in the CUSTOMERS list.

Once we know which function works, or what additional characters need to be removed, we can clean Columns H & J using VBA without the need to add another column.

 

[Skuperman]

Hey Jerry, It looks like just using Trim does the job. Using Len the number matches with the number of characters that I see in the cell. I found a small piece of code online that I run at the start to clean the data and it seems to work pretty well, I just repeated the code to do the second column. I'm sure there is a way to writ it as just doing both columns, but repeating it seems to work

Dim r As Range
Set r = Intersect(Range("H2").EntireColumn, ActiveSheet.UsedRange)
r.Value = Evaluate("IF(ROW(" & r.Address & "),IF(" & r.Address & "<>"""",TRIM(" & r.Address & "),""""))")


Set r = Intersect(Range("J2").EntireColumn, ActiveSheet.UsedRange)
r.Value = Evaluate("IF(ROW(" & r.Address & "),IF(" & r.Address & "<>"""",TRIM(" & r.Address & "),""""))")