Excel VBA Dealing with 64 values for one argument -

I have a VBA script that I read an incoming email in an account and then marks the respective cell in it spreadsheet .

  if InStr (itm.subject, "test backup") & gt; There were 9 jobs in my trial, which were looking for in a nested IF statement.  
 0 then j = 2 ALSIF inset (itm.subject, "TESTdchq") & gt; 0 then j = 3 alsèf inf (itm.subject, "TESTdynamics") & gt; 0 then J = 4 ALSIF int (itm.subject, "TEST-VSS-HQ") & gt; 0 then J = 5 ALSIF int (itm.subject, "TESTWSUS01") & gt; 0 then j = 6 ALSIF inset (itm.subject, "test-camera") & gt; 0 then J = 7 ALSIF ins (itm.subject, "test-Vcenter") & gt; 0 then j = 8 ALSIF inset (itm.subject, "test-view connection") & gt; 0 then j = 9 ALSIF inset (itm.subject, "TESTktsrv1") & gt; 0 then J = 10 end if  

Although I have 64 works in a practical application I would like to give me a more efficient way to allocate the value of J on the basis of the keyword in the subject of e-mail Methods are needed. ID assumes that I can do something with an array and then call the array and compare this topic.

If it helps, then the entire exam script is

sub-linkalist (Outlook.MailItem as ITM) Dim xlApp Excel ExcelWkBk Excel.Workbook as aplication will be used to declare the proper job row using the 'integer' J = as the dim dim PathName string as the filename filename pathname = "C: \ user \ Owner \ Dropbox \ backup \ "filename =" TESTReconcileSheet.xlsx "'declare J InStr (it M.subject, "test backup") & gt; 0 then j = 2 ALSIF inset (itm.subject, "TESTdchq") & gt; 0 then j = 3 alsèf inf (itm.subject, "TESTdynamics") & gt; 0 then J = 4 ALSIF int (itm.subject, "TEST-VSS-HQ") & gt; 0 then J = 5 ALSIF int (itm.subject, "TESTWSUS01") & gt; 0 then j = 6 ALSIF inset (itm.subject, "test-camera") & gt; 0 then J = 7 ALSIF ins (itm.subject, "test-Vcenter") & gt; 0 then j = 8 ALSIF inset (itm.subject, "test-view connection") & gt; 0 then j = 9 ALSIF inset (itm.subject, "TESTktsrv1") & gt; 0 then j = 10 end if xlap = apalap ("excel. App") set with xlap Visible = True 'is used for debugging, ExcelWakeback = Xlap. Workbook Open (PathName and FileName) ExcelWkBk 'code for updating workbook with VBA as the day given here, in the form of dimensioned DD, the use of the string' D = 'in the form of an integer slow subject to the appropriate date column Will be announced to date = day (now) D = 'Today's day' Marcuset INSAT (itm.subject, "[Success]") & Gt; 0 then Sheet ("Sheet1"). Cell (J, (D + 2)) value = "S" Sheet ("sheet 1"). Room (J, (D + 2)). Internal. Color = index = 43 ElseIf InStr (itm.subject, "[failed]") & gt; 0 then Sheet ("Sheet1"). Cell (J, (D + 2)) value = "F" Sheet ("Sheet1"). Room (J, (D + 2)). Internal. Color indicator = 3 ALSIF inser (itm. Serbics, "[warning]") & gt; 0 .Set ("Sheet1"). Room (J, (D + 2)) value = "W". Sheet ("Sheet1"). Room (J, (D + 2)). Internal. If the end of the colore index = 27 Save. With expiration end. End of the end with the sub ending

I have a dictionary for such a large number of variables If you need to, you can create a global dictionary, but the following example is done locally:

  Dim dictionary as new scripting DSDi set dict = CreateObject ("Scripting.Dictionary") dict.Add "test backup", 2 dict.Add "TESTdchq", 3 dict.Add "testability", 4 dict.Add "Test-VSS-HQ", 5 dict .Add "TESTWSUS01", 6 dict.Add "test camera", 7 dict "test-less", 8 addictive add. "Test-View Connection", 9 Dec. Add "TestcutsR 1 1", the integer J = DCT (ITM) as 10 dim J. MSBBX "J =" & amp; J  

---

Result: