VBA Syntax Problem

[Kaelen]

So I am filling a few static array variables using both randomly generated numbers and values pulled from the spreadsheet. For some reason the values being pulled from the cells are giving me a runtime error 13. I cannot figure out what the problem is. Here is the code.


For i = 0 To 15
For j = 1 To 10

Sheets("Rolls").Cells((10 * i) + j, 13) = TypeR((10 * i) + j)

TypeR((10 * i) + j) = Int((6 * Rnd) + 1) + Int((6 * Rnd) + 1) + Sheets("Animal Tables").Range("B" & (13 + i))

SizeR((10 * i) + j) = Int((6 * Rnd) + 1) + Int((6 * Rnd) + 1) + Sheets("Animal Tables").Range("C" & (13 + i)) + WorksheetFunction.HLookup(MovementR((10 * i) + j), Sheets("Animal Tables").Range("J13:O29"), (1 + i))

Next j
Next i

 

[Rick Rothstein]

Since Runtime Error 13 is a Type Mismatch error, I think you will need to tell us what data type your TypeR, SizeR and MovementR arrays are declared (Dim'med) as. It would probably be helpful to know the Dim'med or ReDim'med bounds for these arrays as well. Also, we would need to know what kind of values are stored in the ranges B13:B28, C13:C28 and J13:O29 on your "Animal Tables" sheet. By the way, is there a reason the B and C ranges have a maximum row of 28 whereas the J:O range has a maximum row of 29?

 

[Kaelen]

Blarg!! That was it. B and C should have been from 14 to 29 as well. row 13 has text in it not numbers. Thanks for catching that. I have been banging my head against this for way too long.

 

[Rick Rothstein]

B and C should have been from 14 to 29 as well.

Not sure if this matters or not, but you hard coded the J:O range to go from 13 to 29, not 14 to 29.