Solved Excel Macro to match set of values to another through edits

Microsoft Excel 2013 32/64-bit - license...
April 12, 2017 at 13:47:38
Specs: Windows 7
Hi folks! I've tried my hand with some macros and done OK but what I want to do here is beyond me and need your help! What I'd like to do is search through a selection on Sheet1 of cells with catalog numbers in them (let's say A1:A10) and try and match each of the values in those 10 cells to the values in another tab on Sheet2 which is essentially a price file with catalog numbers....let's say the catalog numbers are all down column A on Sheet2.
Here's the tricky part....I would like to modify each of the values in Sheet1 a little bit, search for them in Sheet2, if still no match, then modify it differently, search again, if still no match, then modify it differently again, if still no search, move on to the next cell. If there IS actually a match, replace the cell in Sheet1 with the value of the match in Sheet2.

So if let's say Sheet1 A1 is a catalog number P100-100. I want to search Sheet 2 column A for this catalog number. If an exact match is not found, I want to remove the first character of Sheet1 A1 and then search again. If an exact match is not found, I want to remove the final character of Sheet1 A1 and search again. If an exact match is not found, I want to remove all dashes and search again. If an exact match is not found, move on to the next catalog number A2 on Sheet1 and continue the search. If an exact match is actually found, I want to replace A1 with the match on Sheet2.

Hopefully this make sense and I would be indebted to anyone that can figure this one out for me! I realize I haven't been overly specific but if you can send me the meat of it, I can fill it in with the ranges and selections to make it point to what I've got going on in my spreadsheets.


See More: Excel Macro to match set of values to another through edits

Reply ↓  Report •

✔ Best Answer
April 12, 2017 at 18:51:42
Try this:

Sub FindEdits()
'Loop through Sheet 1 Column A
  For Each cell In Sheets(1).Range("A1:A10")
   With Sheets(2).Columns(1)
'Search for original value on Sheet 2 Column A
    Set e = .Find(cell, lookat:=xlWhole)
'If not found, search for modifications
     If e Is Nothing Then
'Search for original value with first character removed
      Set e1 = .Find(Right(cell, Len(cell) - 1), _
                lookat:=xlWhole)
'If found, copy value from Sheet 2
       If Not e1 Is Nothing Then
        Range(cell.Address) = e1
       Else
'If not found, Search for original value with last character removed
        Set e2 = .Find(Left(cell, Len(cell) - 1), _
                  lookat:=xlWhole)
'If found, copy value from Sheet 2
         If Not e2 Is Nothing Then
          Range(cell.Address) = e2
         Else
'If not found, Search for original value with hyphens removed
           Set e3 = .Find(WorksheetFunction.Substitute(cell, "-", ""), _
                     lookat:=xlWhole)
'If found, copy value from Sheet 2
            If Not e3 Is Nothing Then
             Range(cell.Address) = e3
            End If
         End If
       End If
     End If
   End With
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
April 12, 2017 at 15:28:02
I think I understand what you want to do except for 2 things:

1 - Your wording could be taken different ways, at least by me:

Original: P100-100
Remove the first character: 100-100
Remove the final character: P100-10 or 100-10?
Remove all dashes: P100100 or P10010 or 10010?

In other words, are the removals additive or individual modifications to the original string?

2 - This isn't really a question that matters as far the code goes, just something that sounds a little strange.

Let's say an exact match for P100-100 is found. It appears that you don't want to do anything to that entry so we move on.

Now let's says there is no match for P100-200 or any of its modified versions. You say the code should "move on to the next catalog number". That means that after the code is run there won't be any distinction between values that had an exact match and those that don't have any match at all. Is that what you want or did I miss something?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
April 12, 2017 at 16:03:29
Good questions Derby, sorry for not being clear. It's always hard to explain what i mean through words. So I don't mean additive, these would be individual modifications to the original. For your 2nd question, that is correct. The code wouldn't need to do anything after the code is run to make any distinctions between what was matched and what wasn't. The reason being is I have multiple columns of data off to the right that are vlookups off this column A. If the vlookup finds a match to that price file in Sheet2, it populates the rest of the detail behind that catalog number automatically. If it doesn't find a match, it just basically says "no match found" and we can move on.
Thank you!

Reply ↓  Report •

#3
April 12, 2017 at 16:31:29
Sorry, one more thing. Just to make things slightly more complicated, the catalog numbers in Sheet1 in column A won't always just be 10 going down. Sometimes it will be 10, sometimes it will be 2,000 rows down. Sorry I didn't make this clear in my initial message.

Reply ↓  Report •

Related Solutions

#4
April 12, 2017 at 18:03:20
OK, we may have an issue.

Since I needed some data to test against, I set up Sheet 1 and Sheet 2 as shown below You'll notice that I have many matches on Sheet2, but I also have 1 case of each of the other possibilities:

Removed first Character
Removed last Character
Removed Hyphen

Note: The order of the data on Sheet 1 and Sheet 2 does not matter, it's only "lined up" to make it easy to explain the situation.

      Sheet 1             Sheet 2
         A                   A
1     P100-100            P100-100
2     P100-101            P101-101
3     P100-102            P100-102
4     P100-103            100-103
5     P100-104            P100-104
6     P100-105            P100-10
7     P100-106            P100-106
8     P100-107            P100107
9     P100-108            P100-108
10    P100-109            P100-109

Here's the problem with that data set:

One of the Sheet 2 values is P100-10. All of the Sheet 1 data contains P100-10. Therefore, if there isn't an exact match (e.g. P100-103, P100-105, P100-107) then the code is going to search Sheet 2 for those values with the last character removed. i.e. P100-10. That means that if more than one value on Sheet1 has the same set of 7 first characters, they will all "match" the P100-10 on Sheet 2.

The same holds true for Sheet 1 values like:

P400-601 and X400-601

If Sheet 2 contains 400-601, both of those will be a "match".

It that a possibility? If so, I am not sure how we would differentiate between those values.

Do you see that as an issue with your data set or are you confident that there will never be more than one "modified" value from Sheet 1 with a match on Sheet 2?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#5
April 12, 2017 at 18:19:39
Actually I think that might be an unintended positive consequence. Just some background on my data...So it's healthcare medical device and medical/surgical product catalog numbers. What happens is the people in their supply chain department enter in the catalog numbers slightly differently throughout the country. Some folks will add hyphens where they actually exist and some folks will just type the catalog number in without the hyphens. I'm trying to search for them multiple different ways quickly and automatically. Hence the macro. And typically these catalog numbers are veery unique. SO...if we have P400-601 in Sheet1 and there happens to be an X400-601 in Sheet2, then chances are these are the same product...the employee just entered it in incorrectly putting a P where the X should have been. Does that make sense?

Reply ↓  Report •

#6
April 12, 2017 at 18:51:42
✔ Best Answer
Try this:

Sub FindEdits()
'Loop through Sheet 1 Column A
  For Each cell In Sheets(1).Range("A1:A10")
   With Sheets(2).Columns(1)
'Search for original value on Sheet 2 Column A
    Set e = .Find(cell, lookat:=xlWhole)
'If not found, search for modifications
     If e Is Nothing Then
'Search for original value with first character removed
      Set e1 = .Find(Right(cell, Len(cell) - 1), _
                lookat:=xlWhole)
'If found, copy value from Sheet 2
       If Not e1 Is Nothing Then
        Range(cell.Address) = e1
       Else
'If not found, Search for original value with last character removed
        Set e2 = .Find(Left(cell, Len(cell) - 1), _
                  lookat:=xlWhole)
'If found, copy value from Sheet 2
         If Not e2 Is Nothing Then
          Range(cell.Address) = e2
         Else
'If not found, Search for original value with hyphens removed
           Set e3 = .Find(WorksheetFunction.Substitute(cell, "-", ""), _
                     lookat:=xlWhole)
'If found, copy value from Sheet 2
            If Not e3 Is Nothing Then
             Range(cell.Address) = e3
            End If
         End If
       End If
     End If
   End With
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#7
April 13, 2017 at 06:03:18
Hmm, it doesn't seem to be doing anything at all when I initiate the macro. I've got 10 rows worth of catalog numbers in Sheet 1 Column A and a bunch of catalog numbers going down column A in Sheet 2. It doesn't seem to be going down the line from A1:A10 doing anything, the cursor doesn't move, etc.

I included 4 catalog numbers in Sheet1 that, if the last character was removed, it would find a match to a catalog number on Sheet2. But still nothing is happening.

I must be doing something wrong :(


Reply ↓  Report •

#8
April 13, 2017 at 06:20:44
Hold the press, it is working. It's just not quite working the way I thought it would, and that's OK.

I think I just need to get my hands dirty and figure out how to modify the sections of the code where it's modifying the cell before it searches. I was thinking you said in your earlier response that if there is a cell in Sheet1 that has a catalog number of U87295, the macro will eventually remove the 5 and search Sheet2 for U8729. Now, if there is a catalog number in Sheet2 of U87294, even though there is a 4 at the end, it should still treat that as a match and replace the cell in Sheet1 with the U87294. That's how I want it to work. Is this possible? Sort of like, even if it searches for U87, it will still find a match for the first catalog number in Sheet2 that starts with U87. Does that make sense?

I think this will work though, it's a great starting point for me and I will tweak it to get what I need. I couldn't have gotten this far without you, thank you sir.


Reply ↓  Report •

#9
April 13, 2017 at 07:33:50
Update again: It's working like I wanted it to, thank you sir! I only want to make one change. Can we highlight the cell yellow if the match was found? I tried adding
ActiveCell.Interior.ColorIndex = 28 but it doesn't seem to be working. I must not be adding it in the right section of the code.

Reply ↓  Report •

#10
April 13, 2017 at 08:29:54
First, I don't know if I have told you about this tutorial before. These debugging techniques might help you tweak the code:

https://www.computing.net/howtos/sh...

As for your latest questions...

re: I was thinking you said in your earlier response that if there is a cell in Sheet1 that has a catalog number of U87295, the macro will eventually remove the 5 and search Sheet2 for U8729.

Yes, that is correct.

re: Now, if there is a catalog number in Sheet2 of U87294, even though there is a 4 at the end, it should still treat that as a match and replace the cell in Sheet1 with the U87294.

No, that is not correct, at least not as the code is currently written.

The code is only modifying the values in Sheet 1 and then looking for a exact match of that modified value in the list on Sheet 2. It is doing nothing to the values on Sheet 2, so U87294 is not a match for when searching for U8729 - as the code is currently written.

There was nothing in your original requirements that indicated that you want something like U87295 replaced with U87294. In fact, you specifically asked for exact matches of the modified values.

re: Sort of like, even if it searches for U87, it will still find a match for the first catalog number in Sheet2 that starts with U87.

The VBA .Find method uses the Find feature in Excel. One of the options of the Find feature is "Match entire cell contents". In VBA, that is handled by setting the "lookat:=" argument to xlWhole.

lookat:=xlWhole

You could try changing all 4 occurrences (or whichever ones you think are appropriate) of xlWhole to:

lookat:=xlPart

If xlPart is used, then U87 would be a match for any value that contains U87 anywhere in the cell, e.g.

U87295

P100-U874

Take the U87 exit

If you only want U87 found at the beginning of the cells being searched, things get a bit more complicated. I don't think that you can use .Find in the manner we currently are. You would need to parse out the first 3 characters of each value on Sheet 2 and compare them to the first 3 characters of the search string. That would need to be done on a cell by cell basis or with a helper column or via something other than a simple .Find against the existing Sheet 2 list.

*****

The following is not really related to your issue, but just a fun fact.

You may have noticed that when you write VBA code, the editor will capitalize any built in function or argument. For example, if you type this and then hit enter...

if not e2 is nothing then

the VBA editor will change it to:

If Not e2 Is Nothing Then

The VBA editor knows that e2 is a user created variable and that all the other words "belong" to VBA.

OK, now enter this in VBA and press enter.

set e2 = .find("Bob", lookin:=xlvalues, lookat:=xlwhole)

Look carefully at what did not get capitalized, other than e2. That bug has been part of VBA for as long as I can remember. 20+ years.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#11
April 13, 2017 at 08:38:40
You are correct on all accounts. I tried xlPart instead on the ones where it removes the first and last characters and it worked. Thanks again!! You are one smart dude. So the 2nd "lookat" didn't get capitalized for some reason. Like you said, a bug. Not many of those in VBA I assume but you found one.

Reply ↓  Report •

#12
April 13, 2017 at 08:44:44
Sorry for another follow up...but would it be difficult for you to update the code so that it searches through all the cells in Sheet1 Column 1? Right now we have it saying A1:A10 but I might have 1000 catalog numbers in there or just 5. I'm assuming it's an easy fix (for you).

Reply ↓  Report •

#13
April 13, 2017 at 08:50:48
Sub FindEdits()
'Determine last Row with data in Sheet 1 Column A
  lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through Sheet 1 Column A
   For Each cell In Sheets(1).Range("A1:A" & lastRw)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#14
April 13, 2017 at 08:58:09
re: So the 2nd "lookat" didn't get capitalized for some reason.

Actually, it's the only "lookat". The first argument is LookIn which does get capitalized.

It's always been just the "lookat" that never gets capitalized, like maybe it was left off of some list deep in the bowels of the VBA Editor's code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#15
April 17, 2017 at 15:29:19
Hi Derby. I was hoping you could help me with one sort of minor tweak to the code. Where you've got the macro searching through column Q in the sheet named "Unkns Catalog # Search Macro", can we instead search through the first column named "Manufacturer Catalog Number"? I've tried searching the web for code to paste in here and modify a bit on my end but I can't figure it out! I was able to figure it out on the other part of the macro that searches through "SCA Cross" and now it sets the value of the column number to myCol but I couldn't figure it out on the other part. See below, and thank you in advance.

Sub FindCatalogNumbs()


myCol = Sheets("SCA Cross").Rows(1).Find(what:="Product Number", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column

'Determine last Row with data in Sheet Catalog # Search Column Q

lastRw = Sheets("Unkns Catalog # Search Macro").Range("Q" & Rows.Count).End(xlUp).Row


'Loop through Sheet Catalog # Search Column Q

For Each cell In Sheets("Unkns Catalog # Search Macro").Range("Q1:Q" & lastRw)

With Sheets("SCA Cross").Columns(myCol)
'Search for original value on Sheet 2 Column A
Set e = .Find(cell, lookat:=xlWhole)
'If not found, search for modifications
If Len(cell.Value) < 4 Then


Else

If e Is Nothing Then

'Search for original value with first character removed
Set e1 = .Find(Right(cell, Len(cell) - 1), _
lookat:=xlPart)
'If found, copy value from Sheet 2
If Not e1 Is Nothing Then
Range(cell.Address).Interior.ColorIndex = 6
Range(cell.Address) = e1

Else
'If not found, Search for original value with last character removed
Set e2 = .Find(Left(cell, Len(cell) - 1), _
lookat:=xlPart)
'If found, copy value from Sheet 2
If Not e2 Is Nothing Then
Range(cell.Address).Interior.ColorIndex = 6
Range(cell.Address) = e2

Else
'If not found, Search for original value with hyphens removed
Set e3 = .Find(WorksheetFunction.Substitute(cell, "-", ""), _
lookat:=xlPart)
'If found, copy value from Sheet 2
If Not e3 Is Nothing Then
Range(cell.Address).Interior.ColorIndex = 6
Range(cell.Address) = e3

Else
'If not found, Search for original value with first 2 characters removed
Set e5 = .Find(Right(cell, Len(cell) - 2), _
lookat:=xlPart)
'If found, copy value from Sheet 2
If Not e5 Is Nothing Then
Range(cell.Address).Interior.ColorIndex = 6
Range(cell.Address) = e5
Else



End If
End If
End If
End If
End If
End If
End With
Next

MsgBox ("Macro Finished")


End Sub


Reply ↓  Report •

#16
April 17, 2017 at 17:52:19
If you repost that code using the pre tags, I'll take a look at it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#17
April 17, 2017 at 18:35:24
Hi Derby. I was hoping you could help me with one sort of minor tweak to the code. Where you've got the macro searching through column Q in the sheet named "Unkns Catalog # Search Macro", can we instead search through the first column named "Manufacturer Catalog Number"? I've tried searching the web for code to paste in here and modify a bit on my end but I can't figure it out! I was able to figure it out on the other part of the macro that searches through "SCA Cross" and now it sets the value of the column number to myCol but I couldn't figure it out on the other part. See below, and thank you in advance.


Sub FindCatalogNumbs()

myCol = Sheets("SCA Cross").Rows(1).Find(what:="Product Number", _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column
'Determine last Row with data in Sheet Catalog # Search Column Q
lastRw = _
Sheets("Unkns Catalog # Search Macro").Range("Q" & Rows.Count).End(xlUp).Row


'Loop through Sheet Catalog # Search Column Q

For Each cell In Sheets("Unkns Catalog # Search Macro").Range("Q1:Q" & lastRw)

With Sheets("SCA Cross").Columns(myCol)
'Search for original value on Sheet 2 Column A
Set e = .Find(cell, lookat:=xlWhole)
'If not found, search for modifications
If Len(cell.Value) < 4 Then


Else

If e Is Nothing Then

'Search for original value with first character removed
Set e1 = .Find(Right(cell, Len(cell) - 1), _
lookat:=xlPart)
'If found, copy value from Sheet 2
If Not e1 Is Nothing Then
Range(cell.Address).Interior.ColorIndex = 6
Range(cell.Address) = e1

Else
'If not found, Search for original value with last character removed
Set e2 = .Find(Left(cell, Len(cell) - 1), _
lookat:=xlPart)
'If found, copy value from Sheet 2
If Not e2 Is Nothing Then
Range(cell.Address).Interior.ColorIndex = 6
Range(cell.Address) = e2

Else
'If not found, Search for original value with hyphens removed
Set e3 = .Find(WorksheetFunction.Substitute(cell, "-", ""), _
lookat:=xlPart)
'If found, copy value from Sheet 2
If Not e3 Is Nothing Then
Range(cell.Address).Interior.ColorIndex = 6
Range(cell.Address) = e3

Else
'If not found, Search for original value with first 2 characters removed
Set e5 = .Find(Right(cell, Len(cell) - 2), _
lookat:=xlPart)
'If found, copy value from Sheet 2
If Not e5 Is Nothing Then
Range(cell.Address).Interior.ColorIndex = 6
Range(cell.Address) = e5
Else


End If
End If
End If
End If
End If
End If
End With
Next

MsgBox ("Macro Finished")


End Sub


Reply ↓  Report •

#18
April 17, 2017 at 19:00:07
I'm pretty sure that the code I posted had indents. Yep, they there are in Response #6.

Using indents in VBA code makes it easier to follow and debug by creating "sections" of code that have a distinguishable opening and closing.

Which of these do you find easier to read?

Sub Ugly()
For rwNum = 1 To 10
If Cells(rwNum, 1) = 4 Then
Cells(reNum, 1) = 6
End If
Next
End Sub

or

Sub Tidy()
  For rwNum = 1 To 10
    If Cells(rwNum, 1) = 4 Then
       Cells(reNum, 1) = 6
    End If
  Next
End Sub

When I asked that you repost the code with the pre tags, I meant that I would like to see the code reposted with the pre tags so that the indents were maintained as they appear in the VBA editor.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#19
April 18, 2017 at 06:06:58
Sorry Derby, this is maybe the 2nd time I've posted in here so I appreciate the guidance. I tried to do what you mentioned in the below posted code. I hope it works better!

Sub FindCatalogNumbs()

myCol = Sheets("SCA Cross").Rows(1).Find(what:="Product Number", 
          LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column
 
'Determine last Row with data in Sheet Catalog # Search Column Q
    lastRw = Sheets("Unkns Catalog # Search Macro").Range("Q" & Rows.Count).End(xlUp).Row

'Loop through Sheet Catalog # Search Column Q
        For Each cell In Sheets("Unkns Catalog # Search Macro").Range("Q1:Q" & lastRw)
        With Sheets("SCA Cross").Columns(myCol)
 
'Search for original value on Sheet 2 Column A
       Set e = .Find(cell, lookat:=xlWhole)
 
'If not found, search for modifications
      If Len(cell.Value) < 4 Then
     Else
     If e Is Nothing Then

'Search for original value with first character removed
     Set e1 = .Find(Right(cell, Len(cell) - 1), _
              lookat:=xlPart)

'If found, copy value from Sheet 2
    If Not e1 Is Nothing Then
      Range(cell.Address).Interior.ColorIndex = 6
        Range(cell.Address) = e1
      Else

'If not found, Search for original value with last character removed
      Set e2 = .Find(Left(cell, Len(cell) - 1), _
       lookat:=xlPart)

'If found, copy value from Sheet 2
     If Not e2 Is Nothing Then
      Range(cell.Address).Interior.ColorIndex = 6
      Range(cell.Address) = e2
    Else

'If not found, Search for original value with hyphens removed
       Set e3 = .Find(WorksheetFunction.Substitute(cell, "-", ""), _
       lookat:=xlPart)

'If found, copy value from Sheet 2
       If Not e3 Is Nothing Then
       Range(cell.Address).Interior.ColorIndex = 6
       Range(cell.Address) = e3
    Else

'If not found, Search for original value with first 2 characters removed
       Set e5 = .Find(Right(cell, Len(cell) - 2), _
         lookat:=xlPart)

'If found, copy value from Sheet 2
       If Not e5 Is Nothing Then
      Range(cell.Address).Interior.ColorIndex = 6
      Range(cell.Address) = e5
     Else


               End If
            End If
          End If
         End If
       End If
     End If
   End With
 Next

MsgBox ("Macro Finished")


End Sub


Reply ↓  Report •

#20
April 18, 2017 at 07:34:14
Perfect! Thanks.

OK, a question first:

Regarding this instruction:

myCol = Sheets("SCA Cross").Rows(1).Find(what:="Product Number", 
          LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column

Why do you need to .Find the column with the macro? Does it move? Have you considered using a Named Range for the columns that you are searching for?

If you Name the column Product_Number you can then refer to the column by name in your code:

Range("Product_Number")

If you move the column, the Name will follow it and the code (as well as any formulas) will still find it. In addition, there is a technique known as Dynamic Named Ranges which grow and shrink based on the amount of data in the range. You wouldn't even need to find the lastRw in the column because the Range Name would contain that information.

See this site for instructions on how to create a Dynamic Named Range or DAGS for more info.

http://www.excel-easy.com/examples/...

For example, if you create a Dynamic Named Range called Product_Number and then run this code against it, you will see that it only Selects the portion of the column that contains data. If you add or delete values at the bottom of the range and run the code again, it will change the Selection to match the length of the data.

Sub TestDNR()
 Range("Product_Number").Select
End Sub


Note: A Range Name must be one continuous string e.g. Product_Number, ProductNumber, etc. i.e. no spaces and some special characters are also prohibited.

If Named Ranges won't work for you, then maybe this is what you are looking for, assuming I understand your latest question correctly.

'Determine Column Numbers For Specific Headers 
  myPN_col = Sheets("SCA Cross").Rows(1). _ 
             Find(what:="Product Number", _ 
             LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column 

  myMCN_col = Sheets("Unkns Catalog # Search Macro").Rows(1). _ 
              Find(what:="Manufacturer Catalog Number", _ 
              LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column 


'Determine last Row with data in myMCN_Col 
     lastRw = Sheets("Unkns Catalog # Search Macro"). _ 
              Cells(Rows.Count, myMCN_col).End(xlUp).Row 

'Loop through Sheet "UC#SM", Search Column Manufacturer Catalog Number 
    For Each cell In Sheets("Unkns Catalog # Search Macro"). _ 
                     Range(Cells(1, myMCN_col), Cells(lastRw, myMCN_col)) 
      'Instructions for Loop go here
    Next 

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#21
April 18, 2017 at 08:11:37
Thanks !! Well, I think I have to use the code at the end that you provided because my intent for this macro eventually will be to give to my colleagues and they are going to literally copy and paste their data into the sheet "Unkns Catalog # Search Macro" and their data is going to be setup very differently than mine, different columns, etc. But one thing will remain constant - they will always have a column named "Manufacturer Catalog Number" somewhere in their data set. I guess I'm not sure what would happen to those named ranges or the Dynamic Range if they copy and paste over everything. Does that affect it in some way? I have done a little research on those in the past and they are definitely neat and will be using them at some point. I really like how they can be used in Data Validation. This video has a good summary of how they can be used in that capacity in case you haven't explored that yet:
https://www.youtube.com/watch?v=18M...

Reply ↓  Report •

#22
April 18, 2017 at 08:54:14
Thanks for the video.

It's hard to say what will happen when the users paste new data over old. I doubt that it will play well with the Named Ranges, so the .Find method may be the best option.

This concerns me to some extent:

"they are going to literally copy and paste their data into the sheet "Unkns Catalog # Search Macro" and their data is going to be setup very differently than mine, different columns, etc."

You might want to add some error checking so that the code fails in a controlled manner vs. just crashing and throwing up a VBA related error. You say that they will always have a column with a specific name, but you don't really know that. e.g.

"That column is way too wide. I think I'll change it to be Man Cat Num."

That is going to produce a pretty ugly error when the code tries to set myMCN_col and can't find "Manufacturer Catalog Number".

Writing code to simplify things for your users can be a very satisfying exercise. Writing user-proof code can be a very frustrating exercise. The problem is that there are users involved and it can be difficult to anticipate and/or control their actions.

Heck, I've written macros for myself that fail years later because I decided to make a change to the sheet, forgetting that the code will be impacted. That is one of the reasons I use a lot of comments in my code. There is no way that I can remember why I wrote the code the way I wrote when it suddenly fails weeks, months or even years later. The comments remind of what I was thinking and help me modify the code to (hopefully) make it work again.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#23
April 18, 2017 at 09:02:36
I totally agree with the notes/comments, I think they are the only way to go for the reasons you stated plus the fact that i can't remember what I ate for breakfast yesterday. I will see what is out there in terms of error checking that I can throw into the macro. I think you're right, if i'm going to send it to others, it needs to at least have a couple of them...one being around both of those named columns I need them to have in their data. I plan on also including a first tab that just has instructions, just some bullet points to help them remember the key elements. Thanks for your help with this Derby

Reply ↓  Report •

Ask Question