Possible Data Type Issue

[shelfish]

Hi.

I'm having some difficulty with a for..next that is exiting early.
There is no error thrown. See code below...

ReDim b(1 To lastRow) 'VERIFIED TO BE OF VALUE 3123
For i = 1 To lastRow
With Cells(1, 1).Offset(i, 0)
b(i) = Trim(unit(.Offset(i, 1), .Offset(i, 2)))
End With
Next

The 'unit' function just concatenates the values...
Function unit(model As String, serial As String) As String

Nowhere in the function is the value of i changed. It is only used
once in a debug.print statement....
Debug.Print i & " = " & unit

This all works perfectly right up until i = 1561, which is half of the
lastRow. Why it would fail there I don't know. The value in the cells
to be concatenated for i = 1561 is identical to the values for 1562,
but 1562 fails inside the function....

Function unit(model As String, serial As String) As String

'VALIDATE ARGS
If Not (Len(model)) > 0 Or Not (Len(serial)) > 0 Then
unit = "x"
Exit Function
End If

So again, everything i = 1 to 1561 passes just fine and everything
after, i = 1562 to 3123 fails

Thanks for any assistance you can offer.

Shelton

 

[shelfish]

more...


When i = 1562

debug.Print cells(1,1).Offset(i, 1) gets the model just fine.
debug.Print cells(1,1).Offset(i, 2) gets the serial just fine.

Call the function(model, serial)

from within the function...

debug.Print model = ""
debug.Print serial = ""

Any ideas?

 

[Dave Peterson]

When i is the lastrow (3123), then this portion:

With Cells(1, 1).Offset(i, 0)
b(i) = Trim(unit(.Offset(i, 1), .Offset(i, 2)))
End With

is the same as:

With Cells(1, 1).Offset(3123, 0)
b(i) = Trim(unit(.Offset(3123, 1), .Offset(3123, 2)))
End With


Did you really want to offset 3123 rows twice????

trim(unit(cells(1,1).offset(3123,0).offset(3123,1), _
cells(1,1).offset(3123,0).offset(3123,2)))

 

[shelfish]

You got me. I was afraid it would be something little. Those are the
most annoying.

Thanks,
S.