Search Left

[helpexcel]

Hi - I'm using this code as a vlookup. Is there a way to alter the code so that i'm only looking up the first 3 digits of the value in Column 6 on Sheet6?

Dim LastRow, LR1, LR2, LR3, i As LongDim Searchfor, j, inarr As Variant


Set wsRS = Sheet6 
Set wsDT = Sheet2 


With wsDT
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    inarr = .Range(.Cells(2, 1), .Cells(LastRow, 2))
End With


With wsRS
    LR1 = .Cells(Rows.Count, "A").End(xlUp).Row
    searcharr = .Range(.Cells(2, 6), .Cells(LR1, 6))
    outarr = .Range(.Cells(2, 15), .Cells(LR1, 15))
End With


On Error Resume Next
For i = 1 To LR1
For j = 1 To LastRow
Searchfor = searcharr(i, 1)


If inarr(j, 1) = Searchfor Then
    For kk = 2 To 2
        outarr(i, kk - 1) = inarr(j, kk)
    Next kk
    Exit For
End If


Next j
Next i


With wsRS
    .Range(.Cells(2, 15), .Cells(LR1, 15)) = outarr
        .Range("D7:D31").HorizontalAlignment = xlCenter
End With

 

[Fluff]

Do you mean that sheet2 col A has 3digit values & you want to find them in col 6 of sheet1?

 

[helpexcel]

yes, sheet2 has 3 digit values in column A. So i want to lookup the 1st 3 digits from column F on sheet6. thanks!

 

[Fluff]

Ok, try

Sub helpexcel()
   Dim Ary As Variant, Nary As Variant, Oary As Variant
   Dim i As Long, UsdRws As Long

   With Sheet2
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 1)).Value2
   End With
   With Sheet6
      UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
      Nary = .Range("F2:F" & UsdRws).Value2
      Oary = .Range("O2:O" & UsdRws).Value2
   End With

   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For i = 1 To UBound(Ary)
         .Item(Ary(i, 1)) = Ary(i, 2)
      Next i
      For i = 1 To UBound(Nary)
         Oary(i, 1) = .Item(Left(Nary(i, 1), 3))
      Next i
   End With
   With Sheet6
      .Range("O2:O" & UsdRws).Value = Oary
      .Range("D7:D31").HorizontalAlignment = xlCenter
   End With
End Sub