studentlearner
New Member
- Joined
- Oct 7, 2021
- Messages
- 30
- Office Version
- 365
- Platform
- Windows
Hi Guys, so i recorded a formula using macros, currently the macro will be within a fixed range, but i require it to find the last cell inside the table. Question is How do modify it so that it'll find the last cell of the table?
this is the formula i'm working with:
=SORTBY(SEQUENCE(ROWS(L2:L37)),RANDARRAY(ROWS(L2:L37)))
with
=INDEX(L2:L37,M2#,1)
and this the recorded macro when i apply the formula that require modification:
Range("M2").Select
ActiveCell.Formula2R1C1 = _
"=SORTBY(SEQUENCE(ROWS(RC[-1]:R[37]C[-1])),RANDARRAY(ROWS(RC[-1]:R[37]C[-1])))"
Range("N2").Select
ActiveCell.Formula2R1C1 = "=INDEX(RC[-2]:R[37]C[-2],RC[-1]#,1)"
As you can see from the recorded macro it'll only stop at Row 37 but the function i'm working with will be constantly updating and so i'm not sure how do i integrate 'End(xlDown)' or 'LR' onto the formula the macro recorded. or some other stuff.
Any help would be great thanks!!
this is the formula i'm working with:
=SORTBY(SEQUENCE(ROWS(L2:L37)),RANDARRAY(ROWS(L2:L37)))
with
=INDEX(L2:L37,M2#,1)
and this the recorded macro when i apply the formula that require modification:
Range("M2").Select
ActiveCell.Formula2R1C1 = _
"=SORTBY(SEQUENCE(ROWS(RC[-1]:R[37]C[-1])),RANDARRAY(ROWS(RC[-1]:R[37]C[-1])))"
Range("N2").Select
ActiveCell.Formula2R1C1 = "=INDEX(RC[-2]:R[37]C[-2],RC[-1]#,1)"
As you can see from the recorded macro it'll only stop at Row 37 but the function i'm working with will be constantly updating and so i'm not sure how do i integrate 'End(xlDown)' or 'LR' onto the formula the macro recorded. or some other stuff.
Any help would be great thanks!!