Solved

help with some flakiness of the split fuction

Posted on 2003-11-12
9
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 

Author Comment

by:megaphreak
ID: 9781834
works now, thanks
0
 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

632 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