Enter a Formula in a filtered range using VBA

[typody]

I will try and explain this as best I can. I need a way to get a cumbersome formula into a cell using VBA. The formula basically does an xlookup against multiple values and reports a number back into the batch column. For various reasons I may overwrite that value with a manual value, and then set it back to the default formula. The Live Data sheet will contain '000 of users and I have a macro that sets filters the users and manually sets a value. I am trying to write a macro to revert these changes based on the same user filter.

Forum Extract.xlsx
	A	B	C	D
1	Id	Primary Name (First Name)	Primary Name (Last Name)	Batch
2	User ID	FirstName	LastName	1
Live Data

Cell Formulas
Range	Formula
D2	D2	=XLOOKUP(A2 & B2 & C2,'Test Schedule'!$B$1:$B$800 &'Test Schedule'!$C$1:$C$800 & 'Test Schedule'!$D$1:$D$800,'Test Schedule'!$AP$1:$AP$800)

The challenge is that the cells referenced in the start of the formula needs to reference the row for each user and as the list is filtered that isn't as easy as starting at A1 and copying down.

I think I am close, but I just can't get the formula to take in the macro. Below is my current attempt. I have read that you don't need to use $A$1 (for example) when using FormulaR1C1 but I am not sure how to reference the right cell in the second sheet. The & between the elements of the xlookup need updating with CHR(38) I think. But again, I could be way off.

With Range(Range is entered here).SpecialCells(xlCellTypeVisible)

.Formula2R1C1 = "=XLOOKUP(RC[-3]" & CHR(38) & "RC[-2]" & CHR(38) & "RC[-1],'Test Schedule'!$B$1:$B$800 &'Test Schedule'!$C$1:$C$800 & 'Test Schedule'!$D$1:$D$800,'Test Schedule'!$AP$1:$AP$800)"

End With

I could be way off as I am rapidly reaching the ends of my Excel knowledge so no judgement if I am way out in my thinking

 

[psgoldberg]

There may be a way to fix your effort but it eludes me. The safer way to go is to use table references and using the @sign in the formula will always refer to the current row which I believe is your challenge. The formula below looks pretty ugly with the [[[ ... ]]] but that is mostly because the headings include spaces and special characters.

So, if your result table is named tblResult and has the column headings you show and the lookup table is named tblLookup with somewhat shortened headings to be clearer (and Column AP named Batch), then you could use the formula: =XLOOKUP(CONCAT(tblResult[@[Id]:[Primary Name (Last Name)]]),tblLookup[ID]&tblLookup[First]&tblLookup[Last],tblLookup[Batch])

For clarity, if the headings were just First and Last then the formula is: =XLOOKUP(CONCAT(tblResult[@[Id]:[Last]]),tblLookup[ID]&tblLookup[First]&tblLookup[Last],tblLookup[Batch])

That will be easier to get working in the macro.

 

[RoryA]

It would be:

With Range(Range is entered here).SpecialCells(xlCellTypeVisible)

.Formula2R1C1 = "=XLOOKUP(RC[-3]&RC[-2]&RC[-1],'Test Schedule'!R1C2:R800C2 &'Test Schedule'!R1C3:R800C3 & 'Test Schedule'!R1C4:R800C4,'Test Schedule'!R1C42:R800C42)"

End With

 

[typody]

Thanks, I will give both a shot tomorrow and see if I can get it working.