VBA for Vlookup in another workbook and replace data

[excel01noob]

I need to combine the customer ID from one report received (table 1) with a table where I need to find the respective Account reference (table 2).
Issues I have:

-Customer ID is formatted as "number" in table 1. If that ID is => to 6 characters I need to take the first 3 digits from the customer ID (from the left);

Table 1

id	sales	Customer ID
chair	1000	1008000
table	1000	1045070
wardrobe	15000	12345
chair	34000	123
table	43000	654321
wardrobe	12000	1051
chair	450003	7071

-with those 3 digits, I need to search in the customer ID on table 2 (which is not formatted as number but as text). When that is matched, I need to take the Acc ref

-then paste it in another sheet Column G (I add this sheet named "Final data" in the same file from where I am getting the table 1 raw data.

Table 2

Customer ID	country	Acc ref
108	PRT	PT10081990
145	LUX	X10082004
294	ESP	S10082017
389	FRA	F10082017
589	GER	G10092016
988	UK	UK10091975
230	SLO	SO10092000

How to set up this in VBA?

 

[excel01noob]

Hi

I have changed this line in your code
wsFinalData.Range("G" & wsFinalData.Cells(Rows.Count, "G").End(xlUp).Row + 1) = IDcell
to
wsFinalData.Range("G" & wsFinalData.Cells(Rows.Count, "G").End(xlUp).Row + 1) = cell.Offset(0,2)

but yes, I am also getting the 4 results (should be only 3, the "988" should not appear as it has less than 6 characters so no need to compare with the Template.xlsm

(currently using company's laptop, not allowed to install the Add-in)

 

[Zot]

Hi

I have changed this line in your code
wsFinalData.Range("G" & wsFinalData.Cells(Rows.Count, "G").End(xlUp).Row + 1) = IDcell
to
wsFinalData.Range("G" & wsFinalData.Cells(Rows.Count, "G").End(xlUp).Row + 1) = cell.Offset(0,2)

but yes, I am also getting the 4 results (should be only 3, the "988" should not appear as it has less than 6 characters so no need to compare with the Template.xlsm

(currently using company's laptop, not allowed to install the Add-in)

? You need Account Ref, not Customer ID. My brain short circuited already. Now I understood that you need to read the Cust ID with 6 digit or more only. Then just add the Len condition.

I did some clean-up on code and tested it. I hope this will do.

Sub Search_ID_and_Copy_Acc()

Dim cell As Range, IDcell As Range, rngID As Range, rngTemplate As Range
Dim wsTemplate As Worksheet, wsRawData As Worksheet, wsFinalData As Worksheet
Dim wbTemplate As Workbook, wbRawData As Workbook

Application.ScreenUpdating = False

' Define this Workbook as wbTemplate
Set wbTemplate = ActiveWorkbook
' Define working sheet in wbTemplate. Change sheet name accordingly
Set wsTemplate = wbTemplate.Sheets("Sheet2")

' Define opened Workbook as wbRawData
Set wbRawData = Workbooks("wbRawData.xlsx")
' Define working sheet in wbRawData. Change sheet name accordingly
Set wsRawData = wbRawData.Sheets("RawData")
Set wsFinalData = wbRawData.Sheets("FinalData")

' Define Customer ID range in RawData and Template ID
Set rngID = wsRawData.Range("C2", wsRawData.Cells(Rows.Count, "C").End(xlUp))
Set rngTemplate = wsTemplate.Range("A2", wsTemplate.Cells(Rows.Count, "A").End(xlUp))

' Go through Template list and find match in RawData by looping
For Each IDcell In rngID
    For Each cell In rngTemplate
        If Len(IDcell) < 6 Then Exit For
        If Left(IDcell, 3) = cell Then
            wsFinalData.Range("G" & wsFinalData.Cells(Rows.Count, "G").End(xlUp).Row + 1) = cell.Offset(0, 2)
        End If
    Next
Next

End Sub