Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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
0
megaphreak
Asked:
megaphreak
  • 5
  • 4
1 Solution
 
Dang123Commented:
megaphreak,


Try changing

Dim strMatching(0) As String

to

Dim strMatching() As String


Dang123
0
 
Dang123Commented:
And add the line

ReDim Preserve strMatching(numFound)

after the line

numFound = numFound + 1 'increment number found total


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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now