Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
07-25-2011, 11:40 PM #1
- Join Date
- Apr 2009
- San Diego, CA
- 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.
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 12:53 AM.