How to match first 6 characters of a cell instead of the whole cell-CodePudding

Focusing on:

rows = w2.Columns("A:A").Find(What:=aCell, LookAt:=xlWhole).Row

Instead of looking at the whole cell to match (xlwhole), I'm trying to only match the first 6 characters of the cell. I've looked into xlpart and a few other options but have been unsuccessful.

Sub test()
Dim aCell
Dim A, B As Long, rows As Long
Dim w1, w2 As Worksheet
Dim cell As Range

Set w1 = Workbooks("Book1").Sheets("Sheet1")
Set w2 = Workbooks("Book2").Sheets("Sheet1")

A = w1.Cells(w1.Rows.Count, 1).End(xlUp).Row
B = w2.Cells(w2.Rows.Count, 1).End(xlUp).Row

For Each aCell In w1.Range("A2:A" & A)
On Error Resume Next
rows = w2.Columns("A:A").Find(What:=aCell, LookAt:=xlWhole).Row
On Error GoTo 0

If rows = 0 Then

Else
    w2.Range("B" & rows).Value = aCell.Offset(0, 1).Value
End If
rows = 0
Next

End Sub

CodePudding user response：

Find supports wildcards *. Use Left$ to get the first 6 characters of the cell value.

For Each aCell In w1.Range("A2:A" & A)
   Dim rng As Range
   Set rng = w2.Columns("A:A").Find(What:=Left$(aCell.Value, 6) & "*", LookAt:=xlWhole)

   ' First test if the find succeeded
   If Not rng Is Nothing Then
       rng.Offset(,1).Value = aCell.Offset(,1).Value
   End If
Next