New TWIST on LOOKUP Match n' Copy to diff sheet -- using font color as part of the required criteria

[ChrisOK]

Ideas on vba to do the following: On Sheet1, starting w/ Column AC, Row 4 take the first cell (that has red font) and try to locate a match on Sheet2 within Column C, starting with Row 2 going down.
=If match found, turn background of that cell on Sheet1 to blue.
=If not found, move to next red font cell within Sheet1, Col AC and repeat search.

*Important: if it comes across a cell in Col AC that has any other font color, ignore it and only search using cells with red font.

 

[ChrisOK]

nope, I went ahead and deleted the space just to make sure, but there was only one char space and that's exactly how the sheet is named.. OH WAIT!!!! The sheet is named "LOCKEED_IPV"!! The sheet itself does not show an underscore UNLESS that sheet is active. Once I clicked on it, it became evident! *You are good!*

However, the code still errors, now with a "Run time error 6, Overflow' on this row:

Set Found = rngSearch.Find(CLng(c.Value), LookIn:=xlValues, LookAt:=xlWhole)

Any idea what might be causing this?

 

[Rick Rothstein]

nope, I went ahead and deleted the space just to make sure, but there was only one char space and that's exactly how the sheet is named.. OH WAIT!!!! The sheet is named "LOCKEED_IPV"!! The sheet itself does not show an underscore UNLESS that sheet is active. Once I clicked on it, it became evident! *You are good!*

The Subscript out of range error means the index value you specified for an array or a collection is not valid. For an array, that would mean the number is either less than the lower bound or greater than the upper bound for the array... for a collection (which Sheets is), it means either the position number is greater than the number of elements in the collection (if a number was specified for the index) or the "key" is spelled wrong in some way... that is why we focused on the spelling of the word "Sheet2" in your generic code and "LOCKED IPV" in your real code.

However, the code still errors, now with a "Run time error 6, Overflow' on this row:

 Set Found = rngSearch.Find(CLng(c.Value), LookIn:=xlValues, LookAt:=xlWhole)

Any idea what might be causing this?

What value is in the cell the range variable 'c' is referencing at the time of the error. My guess is there is a very large number in it (greater than 2147483647 which is the maximum Long can be) and the CLng function is choking on it.

 

[ChrisOK]

WOW, I'm learning something today! Thanks for the expansion..
Yes, INDEED the value in "C" is a long number (a national stock #) NSN... it is 13 to 15 characters long however, when I've done the =ISNUMBER( I get false) and when I do =ISTEXT( I get true on both columns being compared against one another)....
So it needs to look at it as text for what it is... and (not choke) LOL -- how do we make it not choke when trying to compare the NSN's of the column "AC" to the NSNs in column "C" for matches....

If the number of chars is an an issue, I'm okay with it comparing the first 13 of each rather than leaving it open to compare actual to actual... if you think that might help it NOT CHOKE!??? (btw, I've got family from Jersey =-) )

 

[Rick Rothstein]

WOW, I'm learning something today! Thanks for the expansion..
Yes, INDEED the value in "C" is a long number (a national stock #) NSN... it is 13 to 15 characters long however, when I've done the =ISNUMBER( I get false) and when I do =ISTEXT( I get true on both columns being compared against one another)....
So it needs to look at it as text for what it is... and (not choke) LOL -- how do we make it not choke when trying to compare the NSN's of the column "AC" to the NSNs in column "C" for matches....

If the number of chars is an an issue, I'm okay with it comparing the first 13 of each rather than leaving it open to compare actual to actual... if you think that might help it NOT CHOKE!??? (btw, I've got family from Jersey =-) )

Without having access to your actual data, the best we can do is guess. My guess is to try removing the CLng function call so that Find will search for the contents of the cell referenced by the variable 'c' as is...

Set Found = rngSearch.Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)

 

[ChrisOK]

Well, the good news is that I got no errors this time, however, the bad news is, I'm not seeing anything turning blue and was expecting at least one match as there are hundreds of rows of data.

Want to make sure I did exactly what you wanted... is this what you meant? (totally removed that one line)

    'Loop through cells of Source Range
    For Each c In rngSource
        'If Font Color is 'Red'
        If c.Font.Color = 255 Then
            'Test for numeric or not; submit search to set Found
            Select Case IsNumeric(c)
            Case Is = False
            Set Found = rngSearch.Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
            Case Else
            'Set Found = rngSearch.Find(CLng(c.Value), LookIn:=xlValues, LookAt:=xlWhole)
            End Select


            'If Search Result then set interior fill color Source cell
            If Not Found Is Nothing Then
                c.Interior.Color = vbBlue
                Set Found = Nothing
            End If
        End If
    Next c  'cell in search range
End Sub

GIANTS or JETS?
YANKEES or METS?

 

[Rick Rothstein]

Well, the good news is that I got no errors this time, however, the bad news is, I'm not seeing anything turning blue and was expecting at least one match as there are hundreds of rows of data.

Want to make sure I did exactly what you wanted... is this what you meant? (totally removed that one line)

    'Loop through cells of Source Range
    For Each c In rngSource
        'If Font Color is 'Red'
        If c.Font.Color = 255 Then
            'Test for numeric or not; submit search to set Found
            Select Case IsNumeric(c)
            Case Is = False
            Set Found = rngSearch.Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
            Case Else
            'Set Found = rngSearch.Find(CLng(c.Value), LookIn:=xlValues, LookAt:=xlWhole)
            End Select


            'If Search Result then set interior fill color Source cell
            If Not Found Is Nothing Then
                c.Interior.Color = vbBlue
                Set Found = Nothing
            End If
        End If
    Next c  'cell in search range
End Sub

'
VBA's IsNumeric function works completely different from the ISNUMBER worksheet function... IsNumeric will return True for a long text number, for example, this returns True...

MsgBox IsNumeric("12345678901234567890")

so your Select Case statement is failing you.

 

[tweedle]

It's still testing for IsNumeric
Take out the Select Case statement.

    'Loop through cells of Source Range
    For Each c In rngSource
        'If Font Color is 'Red'
        If c.Font.Color = 255 Then
        Set Found = rngSearch.Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)


            'If Search Result then set interior fill color Source cell
            If Not Found Is Nothing Then
                c.Interior.Color = vbBlue
                Set Found = Nothing
            End If
        End If
    Next c  'cell in search range
End Sub

 

[ChrisOK]

Oh - I didn't realize that --- still learning...
What is the proper way to correct it? Is it as simple as changing it to say: "IsText"

Select Case IsText(c)

hmm tried "IsText" that and it gave a compile error