VBA for Vlookup in another workbook and replace data

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
87
Office Version
  1. 365
  2. 2016
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
idsalesCustomer ID
chair10001008000
table10001045070
wardrobe1500012345
chair34000123
table43000654321
wardrobe120001051
chair4500037071

-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 IDcountryAcc ref
108PRTPT10081990
145LUXX10082004
294ESPS10082017
389FRAF10082017
589GERG10092016
988UKUK10091975
230SLOSO10092000

How to set up this in VBA?
 

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
87
Office Version
  1. 365
  2. 2016
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)
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,883
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Forum statistics

Threads
1,148,369
Messages
5,746,295
Members
424,006
Latest member
Metal_warrior

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top