Triming trailing spaces before using Match on columns of numbers withleading zeroes formatted as text
 

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.

Triming trailing spaces before using Match on columns of numbers with leading zeroes formatted as text
 

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.