help with some flakiness of the split fuction

This is the code for what is supposed to go from a drop-down selected major, to search through each company in the database to see if they are hiring for this major, and if so export company name to another (results) sheet.  It is giving -1 as the ubound() of tmpString() after the split, so i don't think the split is filling the array correctly.  Any help would be appreciated.

megaphreak

Public Sub SearchList()
    'this is supposed to go through each row of the xls, and
    ' tokenize the majors desired feel, then fill "results"
    ' with a list of the companies desiring the (drop-down)
    ' selected major
   
    Range("D1").Select 'get selected major
   
    Dim i As Integer
    Dim j As Integer
    Dim dummy As Integer
    Dim strMajor As String
    Dim strMatching(0) As String
    strMajor = ActiveCell
   
    Range("D2").Select 'start at top of list
    Dim tmpstring
    Dim numFound As Integer
   
   
    'Dim tmp2 As Integer
    Do
        tmpstring = Split(ActiveCell.Text, ", ")
        dummy = MsgBox(tmpstring(0))
        For i = 0 To UBound(tmpstring)
        dummy = MsgBox(tmpstring(i))
            If tmpstring(i) = strMajor Then
               
                numFound = numFound + 1 'increment number found total
                ActiveCell.Offset(0, -2).Select 'get name of matching company
                strMatching(numFound) = ActiveCell
                ActiveCell.Offset(2, 0).Select 'get back to Major column
            End If
        Next
        ActiveCell.Offset(0, 1).Select
    Loop While ActiveCell.Text <> Null

    'now output results
    Sheets("ResultPage").Select 'go to correct sheet
    Range("A1").Select
    For j = 0 To UBound(strMatching)
        ActiveCell = strMatching(j)
    Next
    'all done
End Sub
megaphreakAsked:
Who is Participating?
 
Dang123Commented:
And add the line

ReDim Preserve strMatching(numFound)

after the line

numFound = numFound + 1 'increment number found total


0
 
Dang123Commented:
megaphreak,


Try changing

Dim strMatching(0) As String

to

Dim strMatching() As String


Dang123
0
 
megaphreakAuthor Commented:
That stuff was wrong, I really usually just use C++ and I'm not that good anyway.  The Split is still not working, the cell (D2) contains "Eman, MechE", and it won't split it.

megaphreak
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
megaphreakAuthor Commented:
works now, thanks
0
 
Dang123Commented:
Glad you got it working! What else did you need to do?
0
 
megaphreakAuthor Commented:
not really sure, just started working now, sort of:

Public Sub SearchList()
    'this is supposed to go through each row of the xls, and
    ' tokenize the majors desired feel, then fill "results"
    ' with a list of the companies desiring the (drop-down)
    ' selected major
   
    Sheets("SearchPage").Select 'go to correct sheet
    Range("D1").Select 'get selected major
   
    Dim i As Integer
    Dim j As Integer
    Dim dummy As Integer
    Dim strMajor As String
    Dim strMatching() As String
    strMajor = ActiveCell
   
    Range("D2").Select 'start at top of list
    Dim tmpstring
    Dim numFound As Integer
   
    'Dim tmp2 As Integer
    Do
        tmpstring = Split(ActiveCell.Text, ", ")
        'dummy = MsgBox(tmpstring(0))
        For i = 0 To UBound(tmpstring)
            dummy = MsgBox(tmpstring(i))
            If tmpstring(i) = strMajor Then
                numFound = numFound + 1 'increment number found total
                ReDim Preserve strMatching(numFound)
                ActiveCell.Offset(-2, 0).Select 'get name of matching company
                strMatching(numFound) = ActiveCell.Text
                ActiveCell.Offset(2, 0).Select 'get back to Major column
            End If
        Next
        ActiveCell.Offset(0, 1).Select
    Loop While ActiveCell.Text <> ""

    'now output results
    Sheets("ResultPage").Select 'go to correct sheet
    Range("A1").Select
    For j = 0 To UBound(strMatching)
        ActiveCell = strMatching(j)
    Next
    'all done
End Sub

any idea why i get a 1004 from "ActiveCell.Offset(-2, 0).Select 'get name..."?
0
 
Dang123Commented:
Try putting

Debug.Print ActiveCell.Row

just before the line. Since you are refering to row offset -2, I am guessing you may be getting an invalid address. If you get a 2 or less on this dubug, that is the problem.

(I am basing this on how  http://www.vb2themax.com/Item.asp?PageID=CodeBank&Cat=650&ID=297  works with that error value.)

Let me know how you make out.
0
 
megaphreakAuthor Commented:
its row then column, not vice versa, i'm a little slow today i guess

thanks for a ll of the help
0
 
Dang123Commented:
Your welcome, glad your up and running.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.