The following code given to me previously by Marcus here worked fine
until I appliedd it, to the real case .
Not obvious to the eye, for apparently same visual content, the
target cells to be Matched to the
Reference cells had 2 trailing spaces. Reference cells had a Length of
8 vs 10 for the Target cells
..
The question is :
In my For Each RngCell loop, what is the syntax to replace the Target
value with a Trim of that Target value
before I use Application.Match.
..
Sub FlagMatchingRecords()
Dim RngCell As Range
Dim IsMatch() As Variant
Dim res As Variant
Dim lw As Long
Dim lr As Long
Dim X As Range
Dim wb As Workbook
Dim ws As Worksheet
lr = Range("B" & Rows.Count).End(xlUp).Row
Set wb = Workbooks("B.xls")
Set ws = wb.Sheets("Sheet1")
IsMatch() = Range("B1:B" & lr).Value
lw = ws.Range("A" & Rows.Count).End(xlUp).Row


Set X = ws.Range("A1:A" & lw)
For Each RngCell In X
MsgBox RngCell.Value
res = Application.Match(RngCell.Value, IsMatch, 0)
If IsError(res) Then
'No Match
Else ' Match
RngCell.Interior.Color = vbYellow
End If
Next RngCell
End Sub
..
Help appreciated,

J.P.

Do it while executing Application.Match:

res = Application.Match(Trim(RngCell.Value), IsMatch, 0)




"u473" wrote in message
...
The following code given to me previously by Marcus here worked fine
until I appliedd it, to the real case .
Not obvious to the eye, for apparently same visual content, the
target cells to be Matched to the
Reference cells had 2 trailing spaces. Reference cells had a Length of
8 vs 10 for the Target cells
.
The question is :
In my For Each RngCell loop, what is the syntax to replace the Target
value with a Trim of that Target value
before I use Application.Match.
.
Sub FlagMatchingRecords()
Dim RngCell As Range
Dim IsMatch() As Variant
Dim res As Variant
Dim lw As Long
Dim lr As Long
Dim X As Range
Dim wb As Workbook
Dim ws As Worksheet
lr = Range("B" & Rows.Count).End(xlUp).Row
Set wb = Workbooks("B.xls")
Set ws = wb.Sheets("Sheet1")
IsMatch() = Range("B1:B" & lr).Value
lw = ws.Range("A" & Rows.Count).End(xlUp).Row


Set X = ws.Range("A1:A" & lw)
For Each RngCell In X
MsgBox RngCell.Value
res = Application.Match(RngCell.Value, IsMatch, 0)
If IsError(res) Then
'No Match
Else ' Match
RngCell.Interior.Color = vbYellow
End If
Next RngCell
End Sub
.
Help appreciated,

J.P.