vba help - instr function

[Mallesh23]

Hi Team,
I am Reading text line using instr functions.
Text file is unstructured data, I want to Read those lines which contains month name in it.
(JAN,Feb,mar,apr,may,jun,jul aug,sep,oct,nov,dec)


Product currency princial Trade_Date Settlement_Date Maturity Date Interest Rate
ABC USD 145,356 25Jun2020 25Jul2020 18AUG2020 0.6



Below is my attempted code, is there any alternate way
or can we shorten the code.

also I am getting error when breaking the lines into multiple.

Sub Read_String()
Dim s As String
Dim dict as new scripting.dictionary
s = "ABC     USD     145,356        25Jun2020                  25Jul2020        18AUG2020           0.6 "

if instr(s,"JAN")>0 & _
    OR instr(s,"JAN")>0 & _
    OR instr(s,"FEB")>0 & _
    OR instr(s,"MAR")>0 & _
    OR instr(s,"APR")>0 & _
    OR instr(s,"MAY")>0 & _
    OR instr(s,"JUN")>0 & _
    OR instr(s,"JUL")>0 & _
    OR instr(s,"AUG")>0 & _
    OR instr(s,"SEP")>0 & _
    OR instr(s,"OCT")>0 & _
    OR instr(s,"NOV")>0 & _
    OR instr(s,"DEC")>0 THEN
dict.Add s, s

End Sub

Thanks
mg

 

[Fluff]

How about

Sub Read_String()
Dim s As String
Dim i As Long
Dim dict As New Scripting.Dictionary
s = "ABC     USD     145,356        25Jun2020                  25Jul2020        18AUG2020           0.6 "

For i = 1 To 12
   If InStr(1, s, MonthName(i, 1), vbTextCompare) > 0 Then
      dict.Add s, s
      Exit For
   End If
Next i

End Sub

 

[Mallesh23]

Hi fluff,

Nice ! Thanks for your help,
If I want minimum 3 months in a string. consider those line I am interested.
What will be code here.


Thanks
mg

 

[Fluff]

3 different months or could it be the same month 3 times?

 

[Mallesh23]

Hi fluff,

Any 3 months , if month repeat also consider. Repeat ok, duplicate also ok.

(jan jan jan = 3 count) read value
(jan jan August = 3 count) read value


Thanks

 

[Fluff]

Three separate months would be simple, but counting duplicated months is a totally different matter, so it needs a new thread.

 

[Mallesh23]

Hi Fluff,

I am ok for different months.
suggest the code please.


Thanks
mg

 

[Fluff]

That would be

Sub Read_String()
Dim s As String
Dim i As Long, j As Long
Dim dict As New Scripting.Dictionary
s = "ABC     USD     145,356        25Jun2020                  25Jul2020        18AUG2020           0.6 "

For i = 1 To 12
   If InStr(1, s, MonthName(i, 1), vbTextCompare) > 0 Then
      j = j + 1
      If j = 3 Then
         dict.Add s, s
         Exit For
      End If
   End If
Next i

End Sub

 

[Mallesh23]

Hi Fluff,

Liked your code very nice. ?

Thanks
mg

 

[Fluff]

Glad to help & thanks for the feedback.