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



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,



Do it while executing Application.Match:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question