? 2 mod 1
0
? 2.5 mod 1
0

--
Regards.
Tom Ogilvy

Alan Beban wrote in message
...
Keith R wrote:
Oops on the size of the array!
again, snippets are just for display, I've been trying all kinds of
syntax variations with no luck :(

Code snippet 1: throws a compile error, can't assign range to array.

Sub PullArray
Dim TArray(1 To 37) As Variant
TArray = ActiveSheet.Range("C5:AM5").Value '<--
End Sub

Sub PullArray()
Dim TArray() As Variant
ReDim TArray(1 To 1, 1 To 37)
TArray = ActiveSheet.Range("C5:AM5").Value '<--
End Sub


Code snippet 2: If I dim TArray as variant it runs, but then
I can't access the result as an array, e.g. the following
throws a runtime error 9: subscript out of range

Sub PullArray
Dim TArray As Variant '(no longer an array)
TArray = ActiveSheet.Range("C5:AM5").Value
Msgbox TArray(1).value '<--
End Sub

You need TArray(1,1). All arrays loaded directly from worksheet ranges
are 2-dimensional, even "one-row" arrays


Code Snippet 3: Assuming I get the range captured into
my array, I want to validate each bucket, and only allow
integers or blanks, everything else should throw a
messagebox to me to find the problem cell. I was
looking to loop through the collection with a "for/each"
instead of a "for i to x":

Dim r as "element of array" '(not sure what syntax to use here)

Dim r as Variant. You need to accommodate whatever types of values might
have been in the range.

For each r in TArray
If isnumeric(r.value) AND (mod(r.value,1)=0) then
'perform my calculations
Else
Msgbox ("show cell address of bad data so I can fix it")
End if
Next

' versus my current alternative, which is

For i = 1 to 37
If isnumeric(TArray(i).value) AND (mod(TArray(i),1)=0) then
'perform my calculations
Else
Msgbox ("show cell address of bad data so I can fix it")
End if
Next

I would use the current alternative so that I could use the index
numbers. But I would precede the loop with

Set rng = ActiveSheet.Range("C5"); then
For i = 1 to 37
If isnumeric(TArray(1,i)) AND (TArray(1,i) Mod 1) = 0) Then
'perform my calculations
Else
Msgbox rng(1,i).Address
End if
Next

I don't really understand the logic of the

If isnumeric(TArray(1,i)) AND (TArray(1,i) Mod 1)=0) Then

What do you think it's doing?

Alan Beban