I need a VBA Dictionary code for an invoice sheet that if the customer does not exist in the customer sheet so save the new customer’s info from the invoice sheet to the customer sheet
Invoice sheet:
A11: [customer]
A12: [address]
A13: [city]
A14: [postcode]
A15: [telephone]
Customer sheet
Customer ShopManager Telephone Email Address City Postcode
Invoice sheet:
A11: [customer]
A12: [address]
A13: [city]
A14: [postcode]
A15: [telephone]
Customer sheet
Customer ShopManager Telephone Email Address City Postcode
VBA Code:
Sub newCustomer()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dim sh1, sh2 As Worksheet
Set sh1 = ThisWorkbook.Sheets("Customer")
Set sh4 = ThisWorkbook.Sheets("Invoice")
Dim LastRowSh1,LastRowSh4,arr,arr2 As Long
LastRowSh1 = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row
LastRowSh4 = sh4.Cells(sh4.Rows.Count, "A").End(xlUp).Row
arr = sh4.Range(sh4.Cells(1, 1), sh4.Cells(LastRowSh4, 1)).Value2 'load col A of invoices
arr2 = sh1.Range(sh1.Cells(1, 2), sh1.Cells(LastRowSh1, 7)).Value2 'load col B to G of cust
With dict
'VBA Dictionery Code
End With
End Sub