Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    New Coder
    Join Date
    Apr 2009
    Location
    San Diego, CA
    Posts
    64
    Thanks
    2
    Thanked 1 Time in 1 Post

    VBA/Excel Type Mismatch part the way through a For loop with identical data

    I have been racking my brain on this one all morning. I started the day with a version of this code that did what it was supposed to do, but I had a lot of extra code and I decided to trim it down, create some loops, and generally make the code more efficient, but in doing so have created a Type Mismatch error as I output the data. I have even made all the data in the array completely identical and still get the error after some of the values are output. The array is multidimensional, and the first four sub-arrays will always output properly, and then no matter what I do the subsequent 8 sub-arrays, they will NOT output.

    Code:
        Dim year_lookup As Integer
        Dim crx_lookup As Integer
        Dim acct_lookup As Integer
        Dim cellref As Integer
        Dim months(32) As Variant
        Dim mth As Variant
        Dim mth_paid As Variant
        Dim i As Integer
        
        crx_lookup = Range("Main!$A$20").Value + 2
        Set crx = Sheets(crx_lookup)
        
        acct_lookup = Range("Main!$A$4").Value - 1
        If acct_lookup > 0 Then
            year_lookup = Range("Main!$A$26").Value
            If year_lookup = 1 Then
                cellref = 7
                i = 0
                ' I have hard coded in column numbers to group years,
                ' and we started record keeping in May of 2010 which is
                ' there are four Array(0,0) to start here. These are what
                ' always output correctly, and I have even tried setting all
                ' entries this way and yet I get the Type Mismatch error...!
                While cellref < 25
                    If cellref = 7 Then
                        months(i) = Array(0, 0)
                        i = i + 1
                        months(i) = Array(0, 0)
                        i = i + 1
                        months(i) = Array(0, 0)
                        i = i + 1
                        months(i) = Array(0, 0)
                        i = i + 1
                    Else
                        MsgBox i
                        mth = Application.WorksheetFunction.VLookup(acct_lookup, crx.Range("3:1048576"), cellref, False)
                        mth_paid = Application.WorksheetFunction.VLookup(acct_lookup, crx.Range("3:1048576"), (cellref + 1), False)
                        months(i) = Array(mth, mth_paid)
                    End If
                    cellref = cellref + 2
                    i = i + 1
                Wend
            End If
        End If
        
        i = 2
        For Each payment In months
            With ActiveSheet
    ***THIS IS WHERE THE ERROR OCCURS***
                .Cells(29, i).Value = payment(0)
                .Cells(30, i).Value = payment(1)
            End With
            i = i + 1
        Next payment
    Last edited by Gremlyn1; 07-26-2011 at 01:53 AM.


 

Posting Permissions

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