VBA code Overflow error

[Hiport]

Hi,

Why do i get run time error on this line? i have a variable as long so i should not be getting overflow

Dim ka As Long

With Worksheets("Sheet1")
ka = Intersect(.UsedRange, .Range("A5:P5000"))
End With

 

[Boller]

you cannot assign more than one value to ka, which is what your code is trying to do.

 

[Hiport]

you cannot assign more than one value to ka, which is what your code is trying to do.

So what can i do?

 

[Norie]

What do you want to do?

As Boller says that code is trying to assign multiple values, which are coming from the range that's the result of the Intersect.

If you actually want multiple values then if you remove the type part (As Long) of the declaration the code won't error.

However you'll end up with an array of values.

 

[Hiport]

Even if i remove As long i still get overflow.

I am just doing a test to see if a variable can be assigned to a range

What do you want to do?

As Boller says that code is trying to assign multiple values, which are coming from the range that's the result of the Intersect.

If you actually want multiple values then if you remove the type part (As Long) of the declaration the code won't error.

However you'll end up with an array of values.

 

[Norie]

I can't replicate the error even with pretty large ranges, eg more than 1 million cells.

Even if some of those cells have very large numbers or errors, eg DIV#0!, in them no error.

The only error is 'Out of memory', and that only seems to happen with very large ranges like an entire worksheet.

 

[Hiport]

Ok i no what it is if any of the cells which contain a number but the actual format of the cell is reflected by ######## then you get overflow.

 

[Norie]

If it's formatted as ######### then it's probably an error

I only checked for division by zero because it's the most obvious one, to me anyway, would have something to do with overflow.

Perhaps it's some other error?

What is in the cell(s) with that formatting?

 

[Rick Rothstein]

Why do i get run time error on this line? i have a variable as long so i should not be getting overflow

Dim ka As Long

With Worksheets("Sheet1")
ka = Intersect(.UsedRange, .Range("A5:P5000"))
End With

I know you have other responses and I'll let you follow up with them; however, I wanted to point out two reasons why you could be getting errors with the above quoted code...

1) The Intersect method returns a Range and you are trying to assign that to a variable declared as Long. If you change the variable type to Range, your code would still generate an error because objects must be Set, so you would need to do this...

Dim ka As Range
With Worksheets("Sheet1")
Set ka = Intersect(.UsedRange, .Range("A5:P5000"))
End With

If, on the other hand, you change the declaration of ka to Variant, the assignment of the range returned by the Intersect method will be converted into a 2-dimensional array containing the elements in the intersection, so you would need to address each cell's value with something like Ka(2,1).

2) The other possible reason for an error is if the UsedRange and the range A5:P5000 do not intersect (that is, no data on the worksheet occurs in rows higher than Row 4).

 

[Norie]

Rick

Good point there in 2, but if the 2 ranges don't intersect the error is Object variable not set....

It's the overflow error that's confusing me, it seems strange for this sort of thing.

Perhaps I'm missing something really obvious.