Re: Countif for a string

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks for both Bob and Harald for the quick response. You both basically
displayed the same solution. This will work great!

Mike

"Harald Staff" wrote:

> Hi Mike
>
> Sub test()
> MsgBox ShouldImport("-1234, 4321, 5.1232, 92.1")
> MsgBox ShouldImport("90310, 110.0, -1.0")
> MsgBox ShouldImport("90311, 105.1 -1.0")
> MsgBox ShouldImport("-90312, 125.4, 12")
> MsgBox ShouldImport("B2931, 94.1, 0.32")
> End Sub
>
> Function ShouldImport(ByRef S As String) As Boolean
> Dim L1 As Long, L2 As Long, L3 As Long
> If Val(S) <> 0 Then
> If Len(S) - Len(Replace$(S, ",", "")) = 2 Then ShouldImport = True
> End If
> End Function
>
> HTH. Best wishes Harald
>
> "crazybass2" <crazybass2@xxxxxxxxxxxxxxxxxxxxxxxxx> skrev i melding
> news:FCA32793-23C5-4ACA-9FAB-F6D15AD70595@xxxxxxxxxxxxxxxx
> > I am importing a text file into excel line by line. I only want to import
> > lines where the first non-space character is a number or negative sign AND
> > only those that have two commas in the string. I have the first part
> > working, I just need to know how to do the AND part (strings with only two
> > commas).
> >
> > This would be easy if I were looking at a string in a cell by using
> countif,
> > but this is a string from a text file. Is there any way of doing this
> > without importing the entire file and then using countif on the imported
> > cells?
> >
> > This is what I have so far:
> >
> > Sub Mikesub()
> > ...
> > Open Filename For Input As #FileNum
> > Counter = 1
> > keywords = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "-")
> > Do While Seek(FileNum) <= LOF(FileNum)
> > Line Input #FileNum, ResultStr
> > For i = LBound(keywords) To UBound(keywords)
> > fstword = LTrim(ResultStr)
> > If Left(fstword, 1) = keywords(i) Then
> > bfound = True
> > Exit For
> > End If
> > Next
> > If bfound Then
> > ActiveCell.Value = Trim(ResultStr)
> > ActiveCell.Offset(1, 0).Select
> > bfound = False
> > End If
> > Counter = Counter + 1
> > Loop
> > ...
> > End Sub
> >
> >
> > For example with the following data I would only like the second and
> fourth
> > lines imported.
> >
> > -1234, 4321, 5.1232, 92.1
> > 90310, 110.0, -1.0
> > 90311, 105.1 -1.0
> > -90312, 125.4, 12
> > B2931, 94.1, 0.32
> >
> >
> > Thanks in advance for your help.
> >
> > Mike
> >
>
>
>
.

Relevant Pages

  • Re: Countif for a string
    ... > Hi Mike ... > End Sub ... > Function ShouldImport(ByRef S As String) As Boolean ... >> I am importing a text file into excel line by line. ...
    (microsoft.public.excel.programming)
  • Re: IP range to CIDR list VB6 utility?
    ... >> Mike, is UnpackDWord something new with VB.Net? ... string address and convert it to a binary equivalent enclosed within a long, ... But I still have to use CIDR notation, which requires looking at an address ... using netmask to generate that exact same network range list: ...
    (microsoft.public.vb.general.discussion)
  • Re: Multiselect Listbox as qualifier for query
    ... That's correct, Mike. ... You can pass that kind of string in the ... Or you can re-write the SQL ... > other qualifiers within the individual query definitions. ...
    (microsoft.public.access.queries)
  • Re: Problem with AND &
    ... Mike wrote: ... I did not catch that since I changed the user records. ... But you're still passing a string as the first ... varbinary is a character type - basically the difference between that and varchar in MySQL is the binary types are case sensitive. ...
    (comp.lang.php)
  • Re: Problem with AND &
    ... Mike wrote: ... I did not catch that since I changed the user records. ... But you're still passing a string as the first ... before the signature lines or delete them all together. ...
    (comp.lang.php)