Index Match using VBA for Multi-sheet workbook

Status
Not open for further replies.

Rymare

New Member
Joined
Apr 20, 2018
Messages
37
I am trying to do the following, here's some images explaining it:

image.png


2:
image.png


3:
image.png


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:

Code:
=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:

Code:
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:

Code:
=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':
Code:
=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!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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!
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top