macro to search on another workbook and replace data

[excel01noob]

Hi

I have two table, table 1 is the registered invoices for a particular month where I usually have:
- old customer accounts (more than 5 digits - column ACCT_ID - table 1);
- newer accounts have only 5 digits as account number (column ACCT_ID - table 1).

I need to prepare report where I combine both account numbers.
For the old accounts, I need to get a REGIST number that comes from table 2 (this table is saved on another workbook which I don't want to open)

Under column F "result" I want to have the accounts with this format:

-new accounts: starting with N and then the 5-digits account and three zeros (example: acc 27193 is N27193000);
-old accounts: I need to pick the first 4 digits from the left of the account under ACCT_ID. Then I need to match that 4-digit number with ACCT_ID from table 2 (example: both accounts 7268999 and 7268111 will have the same first 4 digits which as per table 2 gives REGIST F72682001

RANS_TYP	ARTIC	VALUEDT	ACCT_ID	AMMT	RESULT?		MANDT	ACCT_ID	LEG_ACCT	REGIST
INVCAS	3225TF	2021-05-03T00:00:00.000000	7268999	22564			010	7268	000	F72682001
INVCHE	1174WE	2021-05-04T00:00:00.000000	4043111	1005.99			010	4043	002	F40431985
INVCHE	459DE	2021-05-08T00:00:00.000000	7268111	1985.43			010	4482	283	F44821989
INVCAS	287SY	2021-05-11T00:00:00.000000	4482999	34.5			010	7004	204	F70041978
INVCAS	1174WE	2021-05-11T00:00:00.000000	26725	2569.52			010	1009	000	F10091993
INVACC	133LO	2021-05-13T00:00:00.000000	7268000	875.44			010	1005	080	F10091993
INVACC	459DE	2021-05-13T00:00:00.000000	27194	224.55			010	1005	203	F10091993
INVCHE	117RE	2021-05-28T00:00:00.000000	7004111	103298.5
INVCHE	287SY	2021-05-28T00:00:00.000000	27193	1433.99
		TABLE 1							TABLE 2

How can use a macro that used both workbooks and fill in column F on table 1?
Thank you so much!

 

[excel01noob]

I've managed to work part of the sub routine but I am having difficulty completing it

Option Explicit

Sub accounts_old_and_new()

Dim LastRow As Long

LastRow = Range("D" & Rows.Count).End(xlUp).Row

Dim i As Long

For i = 2 To LastRow

If Len(Range("D" & i)) = 5 Then
Range("F" & i).Value = "C" & Range("D" & i)

'Else

Range("F" & i).Value = ???

Else

End If
Next i

End Sub

 

[excel01noob]

is this something hard to put in place?

 

[Marc L]

Hi,​

​

according to « this table is saved on another workbook which I don't want to open » so if this table is smart enough designed you should use a VLOOKUP formula …​