XLOOKUP not working correctly in macro

[Kekda]

Hello!
I'm trying to create a table of data on several products which I will be updating with a new column to the right on a monthly basis. See link: sample.xlsx

To make this easier I've tried to record a simple macro which pulls data from another sheet and puts the data in position for me to review. The steps go as follows:

1. Right click table, enter new column to right
2. In the new 'F' column I have created I enter my xlookup formula which pulls data across nicely, for month September
3. Change rounding up/down as necessary
Then I stop recording and after deleting the data just entered, I run the macro and it works nicely, entering everything I just recorded. However when I run it again straightaway, it creates a new column but the xlookup formula is still stuck in the 'F' column, and so it just recopies the data for September on top. What I'm after is an xlookup formula that shifts over to the new column I've created so I have static data for each month lined up in columns.

Hope this makes sense.
Thanks in advance

 

[Kekda]

I've tried using 'Use Relative References' but it doesn't work.
Any suggestions appreciated.

 

[Kekda]

Any ideas?

 

[Joe4]

Many people are either unwilling or unable to download files from the internet. So to increase your odds of getting an anwer, if might be best to post a section of your workbook here directly. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, it would be helpful if you posted the VBA code you recorded/created. See the following link for how to post your VBA code: How to Post Your VBA Code

 

[Kekda]

Hello, thanks for the reply. I'm unable to download XL2BB as a message comes up saying 'This file type is not supported in Protected View' (I'm not in protected view)

 

[Fluff]

'This file type is not supported in Protected View'

Have a look here XL2BB Icons greyed out

 

[Kekda]

It works Thanks!

This is the worksheet in question:

sample.xlsx
	A	B	C	D	E	F	G	H
1	STOCK CODE	May	Jun	Jul	Aug		Current	Sparkline
2	A	39000	33000	29000	40000		40000
3	B	500	500	100	510		510
4	C	720	500	490	530		530
5	D	850	150	250	330		330
Sheet1

VBA Code used:

Sub monthlyreview()
'
' monthlyreview Macro
'
' Keyboard Shortcut: Ctrl+k
'
    Range("E2").Select
    Selection.ListObject.ListColumns.Add
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(XLOOKUP([@[STOCK CODE]], 'Data Entry'!C[-5], 'Data Entry'!C[1]), "" "")"
    Range("F3:F1742").Select
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0"
    Range("R1").Select
End Sub

Thanks!

 

[Joe4]

It looks like your formula is referencing a sheet named "Data Entry".
Can you show us what that data looks like?

 

[Kekda]

Yes the data entry sheet is just a sheet which I manually update with data every month.

I use an xlookup formula to pull data from column G

Book1
	A	B	C	D	E	F	G
1	STOCK CODE	PURCHASE	OPEN	CLOSE	REF	#	NO
2	A	50	60	92	76	1	40000
3	B	4	18	12	15	2	510
4	C	4	24	7	15.5	3	530
5	D	1	5	1	3	4	330
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=IFERROR((C2+D2) / 2, " ")

Hope this helps

 

[Joe4]

Try this:

Sub monthlyreview()
'
' monthlyreview Macro
'
' Keyboard Shortcut: Ctrl+k

    Dim lc As Long
    
'   Find last column in row 2
    lc = Range("A2").End(xlToRight).Column

'   Insert new column after last column
    Cells(2, lc).ListObject.ListColumns.Add
    Cells(2, lc + 1).FormulaR1C1 = _
        "=IFERROR(XLOOKUP([@[STOCK CODE]], 'Data Entry'!C1, 'Data Entry'!C7), "" "")"
    Range(Cells(2, lc + 1), Cells(1742, lc + 1)).NumberFormat = "0"
    
    Range("R1").Select

End Sub

The key is when using R1C1 notation, you use the square brackets to denote relative position, i.e.
C[-5] means 5 columns to the left of the current column the formula is being placed in.

To do absolute columns, you don't use square brackets, i.e.
C7 means the 7th column (column G).