Autofill and function not working Need HELP!

I have the following function code

=IF(MAX(IF(ISNUMBER(SEARCH($A2,Sheet2!$A$2:$E$5000)),ROW(Sheet2!$A$2:$E$5000))),INDEX(Sheet2!M:M,MAX(IF(ISNUMBER(SEARCH($A2,Sheet2!$A$2:$E$5000)),ROW(Sheet2!$A$2:$E$5000)))),"")

listed in cell V2 on sheet 1. After that is placed i then run the following macro for an autofill.

Sub Autofill()
    Range("V2").Autofill Range("V2:V" & Range("A" & Rows.Count).End(xlUp).Row)
End Sub

The problem I'm running into is that the pasting of the function and the autofill are not working correctly and I do not know why. The corresponding cells that are suppose to increase with the autofill are not increasing. It should be A2 for row 2 and A3 for row 3 and so on. Any suggestions are greatly appreciated.

Maybe:

Sub trevor2524zzzz()
Range("V2:V" & Range("A" & Rows.count).End(3)(1).Row).Formula = "=YOUR FORMULA GOES HERE"
End Sub

Originally Posted by HaHoBe

Hi, trevor2524,

just combine the codes instead of writing the formula first and then using Autofill:

Sub Autofill()
    Range("V2:V" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(MAX(IF(ISNUMBER(SEARCH($A2,Sheet2!$A$2:$E$5000)),ROW(Sheet2!$A$2:$E$5000))),INDEX(Sheet2!M:M,MAX(IF(ISNUMBER(SEARCH($A2,Sheet2!$A$2:$E$5000)),ROW(Sheet2!$A$2:$E$5000)))),"""")"
End Sub

Ciao,
Holger

Thanks that one works the only thing it is needing now is how do i add the crtl+shift+enter to that macro so that each cell after the function it does that.

I think i see what your saying. Ok i'll try and figure out another way to go about the whole process and see if there is someway I can manipulate it. I'll post if I have any more questions. Thanks again for your help. It helped out alot.