vba help - instr function

Status
Not open for further replies.

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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
 
Upvote 0
3 different months or could it be the same month 3 times?
 
Upvote 0
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
 
Upvote 0
Three separate months would be simple, but counting duplicated months is a totally different matter, so it needs a new thread.
 
Upvote 0
Hi Fluff,

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


Thanks
mg
 
Upvote 0
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
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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