Solved

help with some flakiness of the split fuction

Posted on 2003-11-12
9
258 Views
Last Modified: 2010-05-01
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
Comment
Question by:megaphreak
  • 5
  • 4
9 Comments
 
LVL 9

Expert Comment

by:Dang123
ID: 9734534
megaphreak,


Try changing

Dim strMatching(0) As String

to

Dim strMatching() As String


Dang123
0
 
LVL 9

Accepted Solution

by:
Dang123 earned 150 total points
ID: 9734567
And add the line

ReDim Preserve strMatching(numFound)

after the line

numFound = numFound + 1 'increment number found total


0
 

Author Comment

by:megaphreak
ID: 9735889
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
 

Author Comment

by:megaphreak
ID: 9781834
works now, thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 9

Expert Comment

by:Dang123
ID: 9781883
Glad you got it working! What else did you need to do?
0
 

Author Comment

by:megaphreak
ID: 9782002
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
 
LVL 9

Expert Comment

by:Dang123
ID: 9782146
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
 

Author Comment

by:megaphreak
ID: 9782230
its row then column, not vice versa, i'm a little slow today i guess

thanks for a ll of the help
0
 
LVL 9

Expert Comment

by:Dang123
ID: 9782294
Your welcome, glad your up and running.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now