vba help - instr function

Status
Not open for further replies.

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
958
Office Version
  1. 2010
Platform
  1. Windows
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.

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

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Well-known Member
Joined
Feb 4, 2009
Messages
958
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
3 different months or could it be the same month 3 times?
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
958
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
Three separate months would be simple, but counting duplicated months is a totally different matter, so it needs a new thread.
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
958
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi Fluff,

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


Thanks
mg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
That would be
VBA Code:
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

Well-known Member
Joined
Feb 4, 2009
Messages
958
Office Version
  1. 2010
Platform
  1. Windows
Hi Fluff,

Liked your code very nice. 🕺 (y)

Thanks
mg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 
Status
Not open for further replies.

Forum statistics

Threads
1,144,158
Messages
5,722,827
Members
422,460
Latest member
VBA_Noob01

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
Top