Check if a value is an integer using VBA code

I know I should be able ot do this but I'm drawing a blank and nothing seems
to be working. I want to check a series of user entered values in a vertical
range, see if they are integers, and place them in a 1D integer array. If
they are not postive integers, the array values will be set to zero ( the
range values can remain whatever they are).

How can I do this?

Sub MakeArray()
Dim arr() as Long
Dim i as Long
redim arr(1 to selection.count)
i = 0
for each cell in selection
i = i + 1
arr(i) = 0
if isnumeric(cell) then
if int(cell) = cell then
if int(cell) >= 0 then
arr(i) = cell
end if
end if
end if
Next
End sub

--
Regards,
Tom Ogilvy

"Rayo K" wrote:

> I know I should be able ot do this but I'm drawing a blank and nothing seems
> to be working. I want to check a series of user entered values in a vertical
> range, see if they are integers, and place them in a 1D integer array. If
> they are not postive integers, the array values will be set to zero ( the
> range values can remain whatever they are).
>
> How can I do this?

Thanks,

I had come up this workaround since y is initially a variant, but the
isnumeric is what I was really looking for.

If VarType(y) > 1 And VarType(y) < 6 Then
y = CInt(y)
If y > 0 Then
copies(x) = y
Else: copies(x) = 0
End If
Else: copies(x) = 0
End If

"Tom Ogilvy" wrote:

> Sub MakeArray()
> Dim arr() as Long
> Dim i as Long
> redim arr(1 to selection.count)
> i = 0
> for each cell in selection
> i = i + 1
> arr(i) = 0
> if isnumeric(cell) then
> if int(cell) = cell then
> if int(cell) >= 0 then
> arr(i) = cell
> end if
> end if
> end if
> Next
> End sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Rayo K" wrote:
>
> > I know I should be able ot do this but I'm drawing a blank and nothing seems
> > to be working. I want to check a series of user entered values in a vertical
> > range, see if they are integers, and place them in a 1D integer array. If
> > they are not postive integers, the array values will be set to zero ( the
> > range values can remain whatever they are).
> >
> > How can I do this?

Thanks,

I had come up this workaround since y is initially a variant, but the
isnumeric is what I was really looking for.

If VarType(y) > 1 And VarType(y) < 6 Then
y = CInt(y)
If y > 0 Then
copies(x) = y
Else: copies(x) = 0
End If
Else: copies(x) = 0
End If

"Tom Ogilvy" wrote:

> Sub MakeArray()
> Dim arr() as Long
> Dim i as Long
> redim arr(1 to selection.count)
> i = 0
> for each cell in selection
> i = i + 1
> arr(i) = 0
> if isnumeric(cell) then
> if int(cell) = cell then
> if int(cell) >= 0 then
> arr(i) = cell
> end if
> end if
> end if
> Next
> End sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Rayo K" wrote:
>
> > I know I should be able ot do this but I'm drawing a blank and nothing seems
> > to be working. I want to check a series of user entered values in a vertical
> > range, see if they are integers, and place them in a 1D integer array. If
> > they are not postive integers, the array values will be set to zero ( the
> > range values can remain whatever they are).
> >
> > How can I do this?

As long as you recognize that cint rounds the number

? cint(5.6)
6

You said if it is an integer, not make it an integer. But you know what you
want (but perhaps not how to say it) better than I.

--
regards,
Tom Ogilvy


"Rayo K" wrote:

> Thanks,
>
> I had come up this workaround since y is initially a variant, but the
> isnumeric is what I was really looking for.
>
> If VarType(y) > 1 And VarType(y) < 6 Then
> y = CInt(y)
> If y > 0 Then
> copies(x) = y
> Else: copies(x) = 0
> End If
> Else: copies(x) = 0
> End If
>
> "Tom Ogilvy" wrote:
>
> > Sub MakeArray()
> > Dim arr() as Long
> > Dim i as Long
> > redim arr(1 to selection.count)
> > i = 0
> > for each cell in selection
> > i = i + 1
> > arr(i) = 0
> > if isnumeric(cell) then
> > if int(cell) = cell then
> > if int(cell) >= 0 then
> > arr(i) = cell
> > end if
> > end if
> > end if
> > Next
> > End sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Rayo K" wrote:
> >
> > > I know I should be able ot do this but I'm drawing a blank and nothing seems
> > > to be working. I want to check a series of user entered values in a vertical
> > > range, see if they are integers, and place them in a 1D integer array. If
> > > they are not postive integers, the array values will be set to zero ( the
> > > range values can remain whatever they are).
> > >
> > > How can I do this?

I did want to pass the number on only if it was an integer. The line that
forced it to be an integer was a compromise because VarType was returning
actual integers as longs, so the code was assuming there were no integers at
all. But using IsNumeric solved that problem.

"Tom Ogilvy" wrote:

> As long as you recognize that cint rounds the number
>
> ? cint(5.6)
> 6
>
> You said if it is an integer, not make it an integer. But you know what you
> want (but perhaps not how to say it) better than I.
>
> --
> regards,
> Tom Ogilvy
>
>
> "Rayo K" wrote:
>
> > Thanks,
> >
> > I had come up this workaround since y is initially a variant, but the
> > isnumeric is what I was really looking for.
> >
> > If VarType(y) > 1 And VarType(y) < 6 Then
> > y = CInt(y)
> > If y > 0 Then
> > copies(x) = y
> > Else: copies(x) = 0
> > End If
> > Else: copies(x) = 0
> > End If
> >
> > "Tom Ogilvy" wrote:
> >
> > > Sub MakeArray()
> > > Dim arr() as Long
> > > Dim i as Long
> > > redim arr(1 to selection.count)
> > > i = 0
> > > for each cell in selection
> > > i = i + 1
> > > arr(i) = 0
> > > if isnumeric(cell) then
> > > if int(cell) = cell then
> > > if int(cell) >= 0 then
> > > arr(i) = cell
> > > end if
> > > end if
> > > end if
> > > Next
> > > End sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Rayo K" wrote:
> > >
> > > > I know I should be able ot do this but I'm drawing a blank and nothing seems
> > > > to be working. I want to check a series of user entered values in a vertical
> > > > range, see if they are integers, and place them in a 1D integer array. If
> > > > they are not postive integers, the array values will be set to zero ( the
> > > > range values can remain whatever they are).
> > > >
> > > > How can I do this?

You said your users placed the values in cells as I recall. All values in
cells are stored as double - whether whole numbers or not.

Perhaps it doesn't make any difference, but using vartype isn't going to
work for values extracted from cells.

from the immediate window printing out the cell value and the results of
vartype on a variant variable that was assigned that value
Value Vartype result
1 5
2 5
3 5
5.5 5
4.5 5



--
Regards,
Tom Ogilvy


"Rayo K" wrote:

> I did want to pass the number on only if it was an integer. The line that
> forced it to be an integer was a compromise because VarType was returning
> actual integers as longs, so the code was assuming there were no integers at
> all. But using IsNumeric solved that problem.
>
> "Tom Ogilvy" wrote:
>
> > As long as you recognize that cint rounds the number
> >
> > ? cint(5.6)
> > 6
> >
> > You said if it is an integer, not make it an integer. But you know what you
> > want (but perhaps not how to say it) better than I.
> >
> > --
> > regards,
> > Tom Ogilvy
> >
> >
> > "Rayo K" wrote:
> >
> > > Thanks,
> > >
> > > I had come up this workaround since y is initially a variant, but the
> > > isnumeric is what I was really looking for.
> > >
> > > If VarType(y) > 1 And VarType(y) < 6 Then
> > > y = CInt(y)
> > > If y > 0 Then
> > > copies(x) = y
> > > Else: copies(x) = 0
> > > End If
> > > Else: copies(x) = 0
> > > End If
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Sub MakeArray()
> > > > Dim arr() as Long
> > > > Dim i as Long
> > > > redim arr(1 to selection.count)
> > > > i = 0
> > > > for each cell in selection
> > > > i = i + 1
> > > > arr(i) = 0
> > > > if isnumeric(cell) then
> > > > if int(cell) = cell then
> > > > if int(cell) >= 0 then
> > > > arr(i) = cell
> > > > end if
> > > > end if
> > > > end if
> > > > Next
> > > > End sub
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Rayo K" wrote:
> > > >
> > > > > I know I should be able ot do this but I'm drawing a blank and nothing seems
> > > > > to be working. I want to check a series of user entered values in a vertical
> > > > > range, see if they are integers, and place them in a 1D integer array. If
> > > > > they are not postive integers, the array values will be set to zero ( the
> > > > > range values can remain whatever they are).
> > > > >
> > > > > How can I do this?