I have the below formula array that I need to put into VBA so it can be ran with a button. I need this to start in cell B2 and go down the column until it reaches blanks and stop. From other help, I have realized that I can get it into a string, but the constant $A2 will not change per cell. I need it to change from A2, B2, C2 etc... until the next cell is blank.

=IFERROR(INDEX('Page 1'!J:J,MATCH(1,--(ISNUMBER(SEARCH($A2,'Page 1'!D:D))),0)),"No Match")
Any and all help is greatly appreciated!