Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a value is an integer using VBA code
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a value is an integer using VBA code
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a value is an integer using VBA code
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a value is an integer using VBA code
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if a value is an integer using VBA code
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
check if a number is integer or fraction | New Users to Excel | |||
Check if content in cell is an integer | New Users to Excel | |||
Check if content in cell is an integer | New Users to Excel | |||
Check if result is an integer? | Excel Worksheet Functions | |||
How can i write a vba code to get string or integer "C15" from $c$15? | Excel Programming |