Search string containing two specific text

[reubanrao93]

Evening gurus,

I'm trying to run a formula on a adjacent cell column if the source cell contains two specific text which are defined. Formula 1 is to straight forward and will not be using InSTR function, however for Formula 2, the two specific text are "MODRETSUBUNIT" and "OPONEMS". A sample of the source cell is as below:

MMLCommand:MODRETSUBUNIT:DEVICENO=13,SUBUNITNO=1,TILT=0,OPONEMS="JMANOJK",IPOFEMSWS="172.26.84.98";

I've been using InSTR but it doesnt fetch the correct formula. For the souce cell above instead of Formula 2, the destination cell will contain Formula 1 instead. Is there something I'm missing here?

Sheets("OUTPUT").Range("N:N").Select
ActiveCell.Formula = "=MID(J1,(LEN(J1)-(LEN(LEFT(J1,LEN(J1)-SEARCH(""ILT="",J1)))-4)),((LEN(LEFT(J1,LEN(J1)-SEARCH(""ILT="",J1)))-4)-(LEN(LEFT(J1,LEN(J1)-SEARCH("";"",J1))))))" <--- Formula 1
LRw = Range("J" & Rows.Count).End(xlUp).Row
Range(ActiveCell, Cells(LRw, ActiveCell.Column)).FillDown

Application.ScreenUpdating = True

Sheets("OUTPUT").Range("N:N").Select
If InStr(1, (Range("J1").Value), "MODRETSUBUNIT:") > 0 & InStr(1, (Range("J1").Value), ",OPONEMS=") > 0 Then 'MODBTSRETSUBUNIT standalone & OPMODE [ISSUE]
ActiveCell.Formula = "=MID(J1,(LEN(J1)-(LEN(LEFT(J1,LEN(J1)-SEARCH(""TNO="",J1)))-4)),((LEN(LEFT(J1,LEN(J1)-SEARCH(""ILT="",J1)))-4)-LEN(LEFT(J1,LEN(J1)-SEARCH(""ILT="",J1)))))" <--- Formula 2
End If
LRw = Range("J" & Rows.Count).End(xlUp).Row
Range(ActiveCell, Cells(LRw, ActiveCell.Column)).FillDown

 

[Fluff]

How about

With Sheets("OUTPUT")
   lRw = .Range("J" & Rows.Count).End(xlUp).Row
   If InStr(1, .Range("J1").Value, "MODRETSUBUNIT:") > 0 And InStr(1, .Range("J1").Value, ",OPONEMS=") > 0 Then
      .Range("N1:N" & lRw).Formula = "=MID(J1,(LEN(J1)-(LEN(LEFT(J1,LEN(J1)-SEARCH(""TNO="",J1)))-4)),((LEN(LEFT(J1,LEN(J1)-SEARCH(""ILT="",J1)))-4)-LEN(LEFT(J1,LEN(J1)-SEARCH(""ILT="",J1)))))"
   Else
      .Range("N1:N" & lRw).Formula = "=MID(J1,(LEN(J1)-(LEN(LEFT(J1,LEN(J1)-SEARCH(""ILT="",J1)))-4)),((LEN(LEFT(J1,LEN(J1)-SEARCH(""ILT="",J1)))-4)-(LEN(LEFT(J1,LEN(J1)-SEARCH("";"",J1))))))"
   End If
End With