Index Match using VBA for Multi-sheet workbook

[Rymare]

I am trying to do the following, here's some images explaining it:

2:

3:

The WO# never changes and never repeats. There can be 1,000s of w.o. numbers and I avoid defining definitive ranges in VBA because data is constantly added and deleted. To help solve this problem, I used this formula to get what I want:

=IFS(A2="","",
A2="Kusanagi",IF(INDEX(Kusanagi!R:R,MATCH('2018'!G5,Kusanagi!G:G,0))=0, "", INDEX(Kusanagi!R:R,MATCH('2018'!G5,Kusanagi!G:G,0))),
A2="Dumbledore",IF(INDEX(Dumbledore!R:R,MATCH('2018'!G5,Dumbledore!G:G,0))=0, "", INDEX(Dumbledore!R:R,MATCH('2018'!G5,Dumbledore!G:G,0))),
A2="Violet",IF(INDEX(Violet!R:R,MATCH('2018'!G5,Violet!G:G,0))=0, "", INDEX(Violet!R:R,MATCH('2018'!G5,Violet!G:G,0))),
A2="Logan",IF(INDEX(Logan!R:R,MATCH('2018'!G5,Logan!G:G,0))=0, "", INDEX(Logan!R:R,MATCH('2018'!G5,Logan!G:G,0))))

and I just drag it down. The problem is, this formula is not very dynamic--what if a new inspector gets hired on? Though I would know how to change it, this excel needs to be designed for those who know nothing about excel. This includes how to drag formulas down to populate new cells in a column. I want to just put a button up that does this formula for them, so that they get the most updated information each time they click.


I would really like it if I could fold the code into either the end or the beginning of this code (because I have a button for this code already)--but I am also 100% ok with just making another module and adding an extra button to the excel:

Sub mailing()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim lastRow As Long
    Dim ws As Worksheet




        lastRow = Worksheets("2018").Cells(Rows.Count, "T").End(xlUp).Row
        Dim rg As Range
        Set ws = Worksheets("2018")


        With ws
            lastRow = .Cells(Rows.Count, "T").End(xlUp).Row
            Set rg = Range(.Cells(1, "T"), .Cells(lastRow, "T"))
        End With
 
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    
    For Each cell In rg
        Set OutMail = OutApp.CreateItem(0)
        If cell.Value Like "?*@?*.?*" Then      'try with less conditions first
            With OutMail
                .To = Cells(cell.Row, "T").Value
                .Subject = "Work Order: " & Cells(cell.Row, "G").Value & " assigned"
                .Body = "Work Order: " & Cells(cell.Row, "G").Value & _
                    " has been assigned to you." & _
                    vbNewLine & vbNewLine & _
                    "Region: " & Cells(cell.Row, "B").Value & vbNewLine & _
                    "District: " & Cells(cell.Row, "C").Value & vbNewLine & _
                    "City: " & Cells(cell.Row, "D").Value & vbNewLine & _
                    "Atlas: " & Cells(cell.Row, "E").Value & vbNewLine & _
                    "Notification Number: " & Cells(cell.Row, "F").Value & vbNewLine
                .ReadReceiptRequested = True
                .Send
            End With
            Cells(cell.Row, "u").Value = "sent"
            Cells(cell.Row, "w").Value = Now
            Set OutMail = Nothing
        End If


        Next cell


    'Set OutApp = Nothing                        'it will be Nothing after End Sub
    Application.ScreenUpdating = True


End Sub

p.s. I also have this code in the 'Email for Macro' column:

=IFS(U2="sent", "", A2="","", MATCH(A2,Inspectors!A:A,0)=0, "", INDEX(Inspectors!B:B, MATCH(A2,Inspectors!A:A,0))=0, "", MATCH(A2,Inspectors!A:A,0)>0, INDEX(Inspectors!B:B, MATCH(A2,Inspectors!A:A,0)))

That is needed because the vba code above pulls the email address from that column, but if there's a way to convert that into code that goes into VBA I would be eternally greatful. It's adaptive enough I'm not too worried, it's just I have the formula dragged all the way down to 10000 rows but like I said, the excel is designed for people who don't know how to do that.



And this one in the 'Email Address email was sent to':

=IFS(A2="","", MATCH(A2,Inspectors!A:A,0)=0, "", INDEX(Inspectors!B:B, MATCH(A2,Inspectors!A:A,0))=0, "", MATCH(A2,Inspectors!A:A,0)>0, INDEX(Inspectors!B:B, MATCH(A2,Inspectors!A:A,0)))

If I could fold all these formulas into VBA it would ease my mind. I've looked high and low but I just don't understand some of the examples, or they're very limited to defined ranges of 10 rows or something. I tried writing it myself--it was a disaster.

Let me know if this makes sense or not, I'm happy to provide any clarification needed and appreciate any help!

 

[Rymare]

Update: I did try to use the 'record macro' to get the code--it didn't really work. I got that answer from googling other peoples attempts to translate INDEX/MATCH to VBA.

The only examples I'm able to find that make sense to me are ones that change the formatting of cells--I don't need that. I need it to paste a value from a 3rd column.

If anyone has any other answers--much appreciated!

 

[Fluff]

Replace with https://www.mrexcel.com/forum/excel...-copy-paste-wont-until-after-certain-row.html