Macro VBA INDEX MATCH LOOKUP

[XuanXuan]

Sub FindBeginEnd()

    Dim m As Long
    Dim Lastrow As Long
    Dim ws As Worksheet
    
    Set ws = Workbooks("TYPE A").Worksheets("Aimei Cheah Output")  
    Lastrow = Sheets(1).Cells(Rows.count, "A").End(xlUp).Row

    Range("AT4").Value = "Shortage Begin"
    Range("AU4").Value = "Shortage End"

    For m = 5 To Lastrow
        Cells(m, 46).Value = WorksheetFunction.Index(ws.Range("J3:V3"), WorksheetFunction.Match(True, ws.Range("J" & m & ":" & "V" & m) < 0, 0))
        Cells(m, 47).Value = WorksheetFunction.Lookup(2, 1 / (ws.Range("J" & m & ":" & "V" & m)), ws.Range("J" & m & ":" & "V" & m))
    Next m
    
End Sub

I tried many method to do this but can't work , anyone can help?

 

[jasonb75]

It is better if you tell us what you're trying to do. Code that doesn't work, doesn't always give an indication of your intention.

What is your code doing wrong? If you get an error message, what is that message and which line of the code causes it?

This is just a guess for the INDEX line

Cells(m, 46).FormulaArray = "=INDEX('" & ws.Name & "'!J3:V3,MATCH(True,'" & ws.Name &"'!J" & m & ":" & "V" & m & "<0,0))"

Only typed it in here, so syntax not checked in vba editor.