VBA to insert XLOOOKUP in multiple columns

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
Hi, Firstly thanks to Maabadi who has given me great code for my project. Here is a OneDrive link to my workbook example file2.xlsm

I receive a weekly data download from an EPOS system as shown in columns A:L in the first tab. The items purchased are shown in column L. Mabaadi has given me code that splits the string in column L for each type of item purchased and has shown the first item purchased in column M and the quantity of items purchased in column N. Subsequent items purchased and their quantity are shown in steps of five columns. This leaves three blank cells to the right of the sequence beginning with column N, which are named Category, Unit Price and Total Price.

I'm looking for code please to insert a XLOOKUP function in each of the first two blank columns and a simple multiplication of the first two columns in the third column. I've highlighted the columns O:Q where I have manually inserted the function and this shows what it should look like.

The split string code allows for up to 50 separations from data in column L. The lookup table is in the second tab and the data below the headings in yellow is the product table output from the EPOS system. Typically, this output format doesn't match the receipts report and so, by an agricultural method, I've produced the identical description match in column Q of the lookup table. If you also know of a way to simplify this, I would be grateful.

Many thanks for your help.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,324
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
End Sub
Sub jeffdolton()
   Dim UsdRws As Long, UsdCols As Long, i As Long
   
   UsdRws = Range("A" & Rows.count).End(xlUp).Row
   UsdCols = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
   For i = 15 To UsdCols Step 5
      With Cells(2, i).Resize(UsdRws - 1, 2)
         .Formula2 = "=XLOOKUP(" & Cells(2, i - 2).Address(0, 1) & ",'Product LookUp'!$Q$2:$Q$158,'Product LookUp'!R$2:R$158,"""",0)"
         .Offset(, 2).Resize(, 1).Formula2R1C1 = "=iferror(rc[-3]*rc[-1],"""")"
      End With
   Next i
End Sub
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
How about
VBA Code:
End Sub
Sub jeffdolton()
   Dim UsdRws As Long, UsdCols As Long, i As Long
  
   UsdRws = Range("A" & Rows.count).End(xlUp).Row
   UsdCols = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
   For i = 15 To UsdCols Step 5
      With Cells(2, i).Resize(UsdRws - 1, 2)
         .Formula2 = "=XLOOKUP(" & Cells(2, i - 2).Address(0, 1) & ",'Product LookUp'!$Q$2:$Q$158,'Product LookUp'!R$2:R$158,"""",0)"
         .Offset(, 2).Resize(, 1).Formula2R1C1 = "=iferror(rc[-3]*rc[-1],"""")"
      End With
   Next i
End Sub
Thanks so much, works a treat. Are you able to set the results to £ please. Also, I've noticed the code to separate the string (Module SeparateString) in row 7 as an example hasn't displayed the final product in the description. I was told that up to 50 separations could be made using this code but I cannot see in the code where this appears or what to change. Again your help will be gratefully received.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,324
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub jeffdolton()
   Dim UsdRws As Long, UsdCols As Long, i As Long
   
   UsdRws = Range("A" & Rows.count).End(xlUp).Row
   UsdCols = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
   For i = 15 To UsdCols Step 5
      With Cells(2, i).Resize(UsdRws - 1, 2)
         .Formula2 = "=XLOOKUP(" & Cells(2, i - 2).Address(0, 1) & ",'Product LookUp'!$Q$2:$Q$158,'Product LookUp'!R$2:R$158,"""",0)"
         .Offset(, 2).Resize(, 1).Formula2R1C1 = "=iferror(rc[-3]*rc[-1],"""")"
         .Offset(, 1).NumberFormat = "$#,##0.00"
      End With
   Next i
End Sub
You would need to post back to your previous thread if that code isn't working.
 
Solution

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
How about
VBA Code:
Sub jeffdolton()
   Dim UsdRws As Long, UsdCols As Long, i As Long
  
   UsdRws = Range("A" & Rows.count).End(xlUp).Row
   UsdCols = Cells.Find("*", , , , xlByColumns, xlPrevious, , , False).Column
   For i = 15 To UsdCols Step 5
      With Cells(2, i).Resize(UsdRws - 1, 2)
         .Formula2 = "=XLOOKUP(" & Cells(2, i - 2).Address(0, 1) & ",'Product LookUp'!$Q$2:$Q$158,'Product LookUp'!R$2:R$158,"""",0)"
         .Offset(, 2).Resize(, 1).Formula2R1C1 = "=iferror(rc[-3]*rc[-1],"""")"
         .Offset(, 1).NumberFormat = "$#,##0.00"
      End With
   Next i
End Sub
You would need to post back to your previous thread if that code isn't working.
Absolutely brilliant, thanks. Undertstand about last thread but I've solved since we last spoke.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,324
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,587
Members
414,460
Latest member
uctc

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