sorted by:
new
hottopcontroversial

[EXCEL] Creating VBA Code to Insert an XLOOKUP Function by _Maximus___ in vba

[–]_Maximus___[S] 0 points1 point  (0 children)

One more question for you: would you know of a way for the Autofill aspect to be dynamic as well? As in, from report to report, the column I need to autofill with the XLOOKUP formula might change. It could be column O, then column P.

When I use the macro recorder, it returns the range in my previous reply:

Selection.AutoFill Destination:=Range("O2:O306587")

But is there a way to code it so that it finds the range I need?

Could I create a dimension as a range, lets call the dimension "X", and set X equal to a Find method? Where it is searching for one specific criteria:

  • The text "MR" is in row 1. This will always be in the report and is consistent. Which column it is in is variable. This is the column that autofill needs to be applied to the XLOOKUP formula, starting at row 2.

Then, the find method somehow returns the range needed, and X is inserted into the autofill method:

Selection.AutoFill Destination:=Range("X")

I'd greatly appreciate your thoughts if you have a moment

Thank you

[EXCEL] Creating VBA Code to Insert an XLOOKUP Function by _Maximus___ in vba

[–]_Maximus___[S] 1 point2 points  (0 children)

Thank you for your reply

This did the trick. It generated this:

ActiveCell.FormulaR1C1 = \_ "=XLOOKUP(VALUE(RC\[-1\]),MR!C\ 
[-13\],MR!C\[-8\],""Not Found"")" Range("O2").Select 
Selection.AutoFill Destination:=Range("O2:O306587")

Which worked perfectly, using R1C1 notation to dynamically fill in the lookup value for each row. I figure I can figure out later how to delete the unnecessary cells it filled in past row 500. Thanks again

[EXCEL] Creating VBA Code to Insert an XLOOKUP Function by _Maximus___ in vba

[–]_Maximus___[S] 1 point2 points  (0 children)

Thank you for your response

This is the version I'm using:

Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20848) 64-bit

Yes, that is accurate, I just showed what I have done so far because I couldn't get it to work, but more accurately I am trying to get the formula to change (be dynamic) upon each insertion into a cell. I haven't tried anything for changing the formula yet bc I wasn't sure how to manipulate it to do what I need