+ Reply to Thread
Results 1 to 4 of 4

Dynamic arrays and type mismatches

  1. #1
    Registered User
    Join Date
    10-13-2004
    Posts
    26

    Question Dynamic arrays and type mismatches

    Hey folks, hope you're all good tonight. I'm having a problem with a type mismatch first and foremost, and need a little guidance on dynamic arrays. This is a search macro. I want to find an item number, have it display any values for the item (which will always be in columns B:G), and then ask if any values need to be added or changed (haven't gotten to that part yet). I was doing some preliminary testing, and the macro stops in error at
    Please Login or Register  to view this content.
    citing a type mismatch as the problem. I tried a few ways around this using the Rows and Columns properties, trying Range, but I get errors ranging from that to global class failures. I'm somewhat new to this, using arrays and ifs anyway, so this is perplexing me and I'm sure its something simple. Any suggestions?

    PHP Code: 
    Sub Search()
     
        
    Dim TNum(), Term2IputIput2 As String
        Dim j
    As Integer
        j 
    0
            
        Iput 
    InputBox("Please enter item:"'Input
        Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _
                searchorder:=xlByRows).Activate '
    Find it
                
        
    For 2 To 7
        
        
    If Worksheets(1).Columns(i).Formula <> "" Then
        j 
    1
        
        ReDim TNum
    (6)
        
    TNum(j) = Worksheets(1).Columns(i).Formula
        End 
    If
        
    Next i
        
        Term2 
    MsgBox("The values for this item are:" _
        vbCrl 
    _
        TNum
    (1) & ", " TNum(2) & ", " TNum(3) & ", " _
        TNum
    (4) & ", " TNum(5) & ", " TNum(6), _
        vbYesNoCancel 
    vbDefaultButton2"Tariff Numbers"
    Last edited by carg1; 07-01-2005 at 12:21 AM.

  2. #2
    Patrick Molloy
    Guest

    RE: Dynamic arrays and type mismatches

    two things here

    (1) re: If Worksheets(1).Columns(i).Formula <> "" Then
    you're looking at a collection of variants ... so I'm not clear as to ehat
    this is trying to achieve. If you are testing to see if the column has data,
    theen use the COUNTA() function

    (2)

    Dim TNum(), Term2, Iput, Iput2 As String
    Dim j, k As Integer

    is wrong...
    in VBA, if you don't explicitly state the data type, the variant is the
    default.
    You have DIM, by default is this:
    Dim TNum() As Variant, Term2 As Variant, Iput As Variant, Iput2 As String
    Dim j As Variant, k As Integer

    I assume that you want

    Dim TNum() As String, Term2 As String, Iput As String, Iput2 As String
    Dim j As Integer, k As Integer


    "carg1" wrote:

    >
    > Hey folks, hope you're all good tonight. I'm having a problem with a
    > type mismatch first and foremost, and need a little guidance on dynamic
    > arrays. This is a search macro. I want to find an item number, have it
    > display any values for the item (which will always be in columns B:G),
    > and then ask if any values need to be added or changed (haven't gotten
    > to that part yet). I was doing some preliminary testing, and the macro
    > stops in error at
    >
    > Code:
    > --------------------
    > If Worksheets(1).Columns(i).Formula <> "" Then
    > --------------------
    >
    > citing a type mismatch as the problem. I tried a few ways around this
    > using the Rows and Columns properties, trying Range, but I get errors
    > ranging from that to global class failures. I'm somewhat new to this,
    > using arrays and ifs anyway, so this is perplexing me and I'm sure its
    > something simple. Any suggestions?
    >
    >
    > PHP code:
    > --------------------
    > Sub Search()
    >
    > Dim TNum(), Term2, Iput, Iput2 As String
    > Dim j, k As Integer
    > j = 0
    >
    > Iput = InputBox("Please enter item:") 'Input
    > Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _
    > searchorder:=xlByRows).Activate 'Find it
    >
    > For i = 2 To 7
    >
    > If Worksheets(1).Columns(i).Formula <> "" Then
    > j = j + 1
    >
    > ReDim TNum(1 - 6)
    > TNum(j) = Worksheets(1).Columns(i).Formula
    > End If
    > Next i
    >
    > Term2 = MsgBox("The values for this item are:" & _
    > vbCrl & _
    > TNum(1) & ", " & TNum(2) & ", " & TNum(3) & ", " & _
    > TNum(4) & ", " & TNum(5) & ", " & TNum(6), _
    > vbYesNoCancel + vbDefaultButton2, "Tariff Numbers")
    > --------------------
    >
    >
    > --
    > carg1
    > ------------------------------------------------------------------------
    > carg1's Profile: http://www.excelforum.com/member.php...o&userid=15271
    > View this thread: http://www.excelforum.com/showthread...hreadid=383784
    >
    >


  3. #3
    K Dales
    Guest

    RE: Dynamic arrays and type mismatches

    Worksheets(1).Columns(i).Formula: A column does not have a formula. I assume
    you are trying here to look in columns 2-7 for in the row you found in the
    ..Find method earlier in the code; if so I would do it this way (add a Dim
    FoundCell as Range, FoundRow as Integer in the declarations):

    Set FoundCell = Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _
    searchorder:=xlByRows)
    FoundRow = FoundCell.Row
    ....
    Then, on the 'problem' line:
    If Worksheets(1).Cells(FoundRow,i).Formula <>"" Then
    ....
    Also, later in your code,
    TNum(j) = Worksheets(1).Cells(FoundRow,i).Formula

    Now a few things about the ReDim: ReDim will wipe out existing values held
    in the array unless you ReDim Preserve. Also, if you use ReDim (1-6), it
    does not index the array from 1 to 6; instead it does ReDim ("1 minus 6") and
    you will get a 'subscript out of range' error for a subscript of -5)

    I think that (with all taken into account) you will want it to read as
    follows:
    ReDim Preserve TNum(1 to j)
    --
    - K Dales


    "carg1" wrote:

    >
    > Hey folks, hope you're all good tonight. I'm having a problem with a
    > type mismatch first and foremost, and need a little guidance on dynamic
    > arrays. This is a search macro. I want to find an item number, have it
    > display any values for the item (which will always be in columns B:G),
    > and then ask if any values need to be added or changed (haven't gotten
    > to that part yet). I was doing some preliminary testing, and the macro
    > stops in error at
    >
    > Code:
    > --------------------
    > If Worksheets(1).Columns(i).Formula <> "" Then
    > --------------------
    >
    > citing a type mismatch as the problem. I tried a few ways around this
    > using the Rows and Columns properties, trying Range, but I get errors
    > ranging from that to global class failures. I'm somewhat new to this,
    > using arrays and ifs anyway, so this is perplexing me and I'm sure its
    > something simple. Any suggestions?
    >
    >
    > PHP code:
    > --------------------
    > Sub Search()
    >
    > Dim TNum(), Term2, Iput, Iput2 As String
    > Dim j, k As Integer
    > j = 0
    >
    > Iput = InputBox("Please enter item:") 'Input
    > Cells.Find(what:=Iput, LookIn:=xlFormulas, lookat:=xlWhole, _
    > searchorder:=xlByRows).Activate 'Find it
    >
    > For i = 2 To 7
    >
    > If Worksheets(1).Columns(i).Formula <> "" Then
    > j = j + 1
    >
    > ReDim TNum(1 - 6)
    > TNum(j) = Worksheets(1).Columns(i).Formula
    > End If
    > Next i
    >
    > Term2 = MsgBox("The values for this item are:" & _
    > vbCrl & _
    > TNum(1) & ", " & TNum(2) & ", " & TNum(3) & ", " & _
    > TNum(4) & ", " & TNum(5) & ", " & TNum(6), _
    > vbYesNoCancel + vbDefaultButton2, "Tariff Numbers")
    > --------------------
    >
    >
    > --
    > carg1
    > ------------------------------------------------------------------------
    > carg1's Profile: http://www.excelforum.com/member.php...o&userid=15271
    > View this thread: http://www.excelforum.com/showthread...hreadid=383784
    >
    >


  4. #4
    Registered User
    Join Date
    10-13-2004
    Posts
    26
    Okay, you guys helped me immensely, your suggestions work great, but I'm having one more problem (that I sort of created for myself ). The problem is that I need to be able to output each of the values for items that have less than 6 values. I'm only 50% successful. I figured I needed a loop, so I declared an integer "l" and made this loop:

    Please Login or Register  to view this content.
    What it does, however, is display the values one at a time in the message box. Then, for lack of better words, doubles the amount of values shown. As in, if the values in the cells it looks at are 150 and 1783, I'll get 4 message boxes, the first saying "150,", I'll hit a button, the next says "1783,", hit the button, and two more will pop up, both containing "1783,".

    I'm just trying to make the loop display each of the values with commas in between each. I'll have to figure a way to make it omit the comma on the last value.

    PHP Code: 
    Sub Search()
    '
     
        Dim TNum() As String, Term As String, Term2 As String, Iput As String, Iput2 As String
        Dim j As Integer, k As Integer, l As Integer
        Dim FoundCell As Range, FoundRow As Integer
        j = 0
            
        Iput = InputBox("Please enter item:") '
    Input
        Set FoundCell 
    Cells.Find(what:=IputLookIn:=xlFormulaslookat:=xlWhole_
                searchorder
    :=xlByRows)
        
    FoundRow FoundCell.Row

        
    For 2 To 7
        
        
    If Worksheets(1).Cells(FoundRowi).Value <> "" Then
        j 
    1
        
        ReDim Preserve TNum
    (1 To j)
        
    TNum(j) = Worksheets(1).Cells(FoundRowi).Value

        End 
    If
        
    Next i
        
        
    For 0 To j
        Term2 
    MsgBox("The values for this item are:" vbCr vbCr _
        TNum
    (j) & ","vbYesNoCancel vbDefaultButton2"Values")
        
    Next l 

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1