Solved Find multiple words/ values in excel and highlight them

September 16, 2014 at 11:05:29
Specs: Windows 7
Hello,

I have a list of about 4,000 lines of Reference codes that need to be looked up at once from a separate sheet and then highlighted. I have this code currently

Option Explicit
Sub DocFinder()
Dim srchLen, DocName As Integer
Dim g As Range
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column E, Highlight it
With Sheets(1).Columns("E")
For DocName = 1 To srchLen
Set g = .Find(Sheets(3).Range("A" & DocName), lookat:=xlWhole)
If Not g Is Nothing Then
g.Interior.ColorIndex = 5
End If
Next
End With
End Sub


If it looks familiar it is because I had modified it from a similar post. My problem is that it only finds the first hits and highlights it. I have many repeated reference codes that all need to be highlighted. I have tried implementing a for each but the program will then cease to run.

Any help will do.

Thanks

message edited by JonaRey


See More: Find multiple words/ values in excel and highlight them

Report •


✔ Best Answer
September 17, 2014 at 18:03:53
Just for future reference, it would be easier for us to help you if you would post your complete macro, not just a small portion. When we want to test your code, we want to be able to Copy it directly into the VBA editor and run it. When you only post a section, we have to '"rebuild" it to get it to run. If you make us do too much work, we might get bored and walk away. ;-)


As for your error, think about what g is. g is the object that gets set when the .Find method finds what it is searching for.

As I explained in my previous post, your If statement is testing to see if g is Not Nothing. Only when g Is Not Nothing does it have properties such as .Row, .Column, .Value, etc.

The code will only fall through to the Else section if g is Nothing and if g Is Nothing you can't reference its Row, you can't copy it, you can't do anything with it. After all, it's Nothing, and just like in real life, you can't do anything with Nothing. ;-)

OK, so why was g set to Nothing? Because the current search string wasn't found. What is it that you are trying to copy? The unfound search string, right? So, don't try to copy g, copy the current search string which wasn't found.

I'll be nice and tell you upfront that even if you try to copy your current search string, you are still going to get an error. Range(”A”) is not a valid destination Range. You have only referenced a Column without a Row. VBA will not like that. You also need to keep in mind that you have to increment that row each time you want to Copy another unfound string. You should know how to do that since you used the technique earlier in your code.

Note: I'm purposely being vague here because I want you to try and figure it out on your own. I'll help if need be.

Isn't this fun?

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



#1
September 16, 2014 at 11:21:17
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code so that it is easier for us to read. By using the pre tags, the indents that are present in the VBA editor will be maintained.

As for your question, this site will explain the use of the .FindNext method to find all occurrences of the value you are searching for and perform an action on each occurrence.

http://msdn.microsoft.com/en-us/lib...

e.g.

With Worksheets(1).Range("a1:a500") 
    Set c = .Find(2, lookin:=xlValues) 
    If Not c Is Nothing Then 
        firstAddress = c.Address 
        Do 
            c.Value = 5 
            Set c = .FindNext(c) 
        Loop While Not c Is Nothing And c.Address <> firstAddress 
    End If 
End With

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


Report •

#2
September 16, 2014 at 11:59:00
Thank you for the posting tip, the speedy reply, and the link. My code is working now. I posted it here so you can do a quick check to see if you foresee any issues with it.

Thanks Again DerbyDad03

Option Explicit
Sub DocFinder()
Dim srchLen, DocName As Integer
Dim g As Range
'Determine length of Search Column from Sheet3
   srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column E, Highlight it
  With Sheets(1).Columns("E")
    For DocName = 1 To srchLen
      Set g = .Find(Sheets(3).Range("A" & DocName), lookat:=xlWhole)
        If Not g Is Nothing Then
          srchLen = g.Address
          Do
            g.Interior.ColorIndex = 5
            Set g = .FindNext(g)
            Loop While Not g Is Nothing And g.Address <> srchLen
        End If
    Next
  End With
End Sub


Report •

#3
September 16, 2014 at 13:17:11
The only issue I see is one that may not cause the code to fail, but it makes the code a little hard to understand. In your case, the "error" is essentially transparent to the end user, but in other cases it could cause the code to behave badly.

It's typically considered a bad coding practice to use the same variable name for 2 different purposes.

In your code, you first use srchLen to determine the length of your search column, using it as an Integer:

srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row

Later, you use the same variable name to store a String that represents the address of the first occurrence of the found value:

srchLen = g.Address

The reason that your code still works is that once the For-Next loop is set up using the value found via...

srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row

...VBA has already stored that Row value and doesn't need srchLen for the For-Next loop anymore. Therefore when you re-use it to store the Address of the first found value, the code doesn't really care. However, in other cases, using the same variable name for multiple purposes could be a serious issue, either causing the code to fail or (worse yet) not causing the code to fail but causing it to produce erroneous results, which you may not notice.

The reason the code at the MS site uses...

firstAddress = c.Address

and

Loop While Not c Is Nothing And c.Address <> firstAddress 

...is because the variable name firstAddress is intuitive - it's the address of the first occurrence of the searched for item. Deep within the bowels of Excel, the .FindNext method loops through the search range looking for each occurrence of the search string and we want it to stop looking when it has looped back around to the first occurrence that it found.

I suggest that you replace srchLen with firstAddress (or anything other than srchLen) within the If-Then loop to avoid confusion. It is a much better coding practice than you are currently using.

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


Report •

Related Solutions

#4
September 16, 2014 at 14:06:56
Again thank you. I am new to coding and have just recently picked it up a week ago, but I see what you are saying. Here is the updated version taking your words into consideration.

Option Explicit
Sub DocFinder()
Dim srchLen, DocName As Integer
Dim g As Range
Dim FirstAddress As String
'Determine length of Search Column from Sheet3
   srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column E, Highlight it
  With Sheets(1).Columns("E")
    For DocName = 1 To srchLen
      Set g = .Find(Sheets(3).Range("A" & DocName), lookat:=xlWhole)
        If Not g Is Nothing Then
          FirstAddress = g.Address
          Do
            g.Interior.ColorIndex = 5
            Set g = .FindNext(g)
            Loop While Not g Is Nothing And g.Address <> FirstAddress
        End If
    Next
  End With
End Sub

I do have another question though. I noticed that if sheet 3 column A has a cell that does not appear on sheet 1 column E it simply ignores it. I would like to expand and make a message box appear saying what cells where not found in sheet 1 from sheet 3 or simply copy those not found onto sheet 2. I'm assuming an if statement with a true or false will do the trick. Will I be able to find this info in the Microsoft link as well?


Report •

#5
September 16, 2014 at 16:16:47
If you are new to coding, this tutorial might help.

http://www.computing.net/howtos/sho...

One minor point: I would remove some of the indent in the Loop instruction so it lines up with the Do. That helps the reader find the beginning and end of the Do Loop more easily.

As for your question, first a little background...

When using .Find, the method will set the variable g to Nothing when the search string is not found, thus the use of the Not function in this line:

If Not g Is Nothing Then

It's sort of backwards, but since the .Find method is typically used do something after the search is found, the Not g Is Nothing syntax is the most common method for telling VBA that the search string was found.

So, since "Not Nothing" is what your If is testing for, what you need to do is tell VBA what to do if g is Nothing using If...Then...Else

If Not g Is Nothing Then
    'Do your highlighting
Else
    'Do something different
End If

Do you want to play around with that or do you want me to supply some specific code for the "something different"?

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


Report •

#6
September 17, 2014 at 14:37:11
Well I gave it a shot and I ended up getting a run-time error.I tried different times but it never worked. If every cell value in sheet 3 matches up with the master list in sheet 1 it runs with no error. But if there is an entry in sheet 3 that is not on the master it still runs and highlights but gives a run-time error message. Here is what I came up with.

 With Sheets(1).Columns("E")
    For DocName = 1 To srchLen
      Set g = .Find(Sheets(3).Range("A" & DocName), lookat:=xlWhole)
        If Not g Is Nothing Then
          FirstAddress = g.Address
          Do
            g.Interior.ColorIndex = 5
            Set g = .FindNext(g)
          Loop While Not g Is Nothing And g.Address <> FirstAddress
        Else
            g.EntireRow.Copy Destination:=Sheets(2).Range("A")
        End If


Report •

#7
September 17, 2014 at 18:03:53
✔ Best Answer
Just for future reference, it would be easier for us to help you if you would post your complete macro, not just a small portion. When we want to test your code, we want to be able to Copy it directly into the VBA editor and run it. When you only post a section, we have to '"rebuild" it to get it to run. If you make us do too much work, we might get bored and walk away. ;-)


As for your error, think about what g is. g is the object that gets set when the .Find method finds what it is searching for.

As I explained in my previous post, your If statement is testing to see if g is Not Nothing. Only when g Is Not Nothing does it have properties such as .Row, .Column, .Value, etc.

The code will only fall through to the Else section if g is Nothing and if g Is Nothing you can't reference its Row, you can't copy it, you can't do anything with it. After all, it's Nothing, and just like in real life, you can't do anything with Nothing. ;-)

OK, so why was g set to Nothing? Because the current search string wasn't found. What is it that you are trying to copy? The unfound search string, right? So, don't try to copy g, copy the current search string which wasn't found.

I'll be nice and tell you upfront that even if you try to copy your current search string, you are still going to get an error. Range(”A”) is not a valid destination Range. You have only referenced a Column without a Row. VBA will not like that. You also need to keep in mind that you have to increment that row each time you want to Copy another unfound string. You should know how to do that since you used the technique earlier in your code.

Note: I'm purposely being vague here because I want you to try and figure it out on your own. I'll help if need be.

Isn't this fun?

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


Report •

#8
September 17, 2014 at 20:16:39
So this is what I was able to come up with but it returns FirstAddress as an invalid qualifier. I'm assuming it is not within the current scope, but my question is why isn't it. It is the only string I have or should create another string? and yes this is fun, but painful. As learning always is.

Option Explicit
Sub DocFinder()
Dim srchLen, DocName, NxtRw As Integer
Dim g As Range
Dim FirstAddress As String
'Determine length of Search Column A from Sheet3
   srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column E, Highlight it
  With Sheets(1).Columns("E")
    For DocName = 1 To srchLen
      Set g = .Find(Sheets(3).Range("A" & DocName), lookat:=xlWhole)
        If Not g Is Nothing Then
          FirstAddress = g.Address
          Do
            g.Interior.ColorIndex = 6
            Set g = .FindNext(g)
          Loop While Not g Is Nothing And g.Address <> FirstAddress
        Else
            NxtRw = Sheets(2).Range("E" & Rows.Count).End(xlUp).Row + 1
            FirstAddress.Copy Destination:=Sheets(2).Rang("A" & NxtRw)
        End If
    Next
  End With
End Sub

message edited by JonaRey


Report •

#9
September 18, 2014 at 04:09:06
Without testing anything, I see that there us a syntax error in your Destination line. I assume that that is the line that is giving you the error. If so, the error isn't related to the FirstAddress variable, it's related to the syntax error.

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


Report •

#10
September 18, 2014 at 05:47:51
Since my last post, I had a chance to test the code. While the syntax error in the Destination instruction still needs to be fixed, there is indeed an issue with your use of FirstAddress.

What is the variable FirstAddress used for? As I explained earlier, it is used to store the address of the first cell where the search string is found. That is not what you want to copy to Sheet2. What you want to copy is the latest search string when it is not found.

Where is your search string? It's in:

Sheets(3).Range("A" & DocName)

That's what you want to copy.

One other issue that I see: You have used the correct syntax for the instruction to determine the Row number for the Destination of your copied data for Sheet2, but look closely at what Column you are checking vs. what Column you are pasting your data into. You are going to determine the next available Row in Column E and then use that number to paste your data into Column A. My guess is that that is not what you want to do.

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


Report •

#11
September 18, 2014 at 06:50:35
Thank you so much for all of your help. You have taught so much (and thank you for making me do the hard work myself). Your explanations have all been very detailed and helpful. This code is doing everything I need it to be doing but as always, here it is. Do you think I will be running into any issues?

Option Explicit
Sub DocFinder()
Dim srchLen, DocName, NxtRw As Integer
Dim g As Range
Dim FirstAddress As String
'Determine length of Search Column A from Sheet3
   srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column E, Highlight it
  With Sheets(1).Columns("E")
    For DocName = 1 To srchLen
      Set g = .Find(Sheets(3).Range("A" & DocName), lookat:=xlWhole)
        If Not g Is Nothing Then
          FirstAddress = g.Address
          Do
            g.Interior.ColorIndex = 6
            Set g = .FindNext(g)
          Loop While Not g Is Nothing And g.Address <> FirstAddress
        Else
            NxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
            Sheets(3).Range("A" & DocName).Copy Destination:=Sheets(2).Range("A" & NxtRw)
        End If
    Next
  End With
End Sub


Report •

#12
September 18, 2014 at 09:54:36
It looks like it should work just fine. Just for clarity, you could add a comment before the Else section to explain what those two instructions are doing. You have comments related to what will happen when a value is found, but not what will happen if not.

Now would be a perfect time to refer to the techniques in the tutorial I mentioned earlier. By using those techniques, especially the Watch Window and the Single Step technique, you can get a feeling for how those techniques might help you debug troublesome code in the future.

http://www.computing.net/howtos/sho...

As we discussed earlier, code that fails outright with an error message is never a good thing, but often easier to fix. It's the code that runs without errors but doesn't do what we want it to do that is a real pain. That's when slowing down the code (Single Step) and keeping track of the variables (Watch Window) can really help.

Becoming familiar with the debugging techniques when you have a piece of working code means that you can use those techniques more comfortably when you actually have a problem.

Have fun!

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


Report •

Ask Question