=ISODD Behaving Oddly

[Craig]

I use the following macro in Sheet 3:-

Sub counter()
Dim C As Integer
Do Until Range("c8").Value = 7
Calculate
C = C + 1
Cells(8, 4) = C
Loop
End Sub

In Sheet 2 I have a set of =If(Isodd(*data*),1,0) and the result of these
*If* statements changes the *value =7* parameter in the above loop

The *data* are randomly generated variables held in Sheet 1 that need to be
re-calculated by the macro until Value=7( note: I only need to re-calc this
sheet's data - all other sheets' calcs are based on Sheet 1 values)

My problem is that when I run the macro all the =If(isodd(.....)s results in
Sheet 2 change to a VALUE! error - even though the data are and remain
numerical [ according to Excel Help this can only happen if the *data* are
non-numeric]

What am I doing wrong ? Should the re-calc be only applied to Sheet1 and how
do I do that?
Also how do I ensure that only Cells(8,4) of Sheet 3 is written to i.e. if I
launch the macro from another sheet its Cells (8,4) is written to.

Many TIA

Craig

 

[yogendra joshi]

This is because....

You have made a "small" mistake...

Cells(8,4) refers to "D8" and not C8....

are you making any further calculations based
on D8 to change C8??? (in that case i am really sorry,
but we will need a bit more detailed query as to how
C8 is affected by D8)

HTH...

Yogendra

 

[Craig]

No, that's not the error.
C8 contains the value to be checked whilst D8 is just an indicator to show
that the macro is looping and and how many times it has looped at any given
time.
But thanks for your interest
Regards
Craig

 

[Tom Ogilvy]

Next to the isodd formula put in a formula

=isnumber(*data*)

Sub counter()
Dim C As Integer
With Worksheets("Sheet3")
Do Until .Range("c8").Value = 7
Calculate
C = C + 1
.Cells(8, 4) = C
Loop
End With
End Sub

Everytime you enter a number in C8, the workbook should calculate if
calculation is set to automatic.

 

[Barry]

Tom,
Many thanks for the reply.
Not quite sure what you mean by * next to the isodd formula put in formula
_=isnumber(data)_*

I need to check how many numbers in SHEET1 J3:Q3 are ODD and the formula
I'm using is *=IF(ISODD(SHEET1!J3),1,0)* and so on for K-Q
So where does =isnumber fit in?[ BTW J3:Q3 are definitely numbers]

I've also modified the macro as you suggested but the =IF(ISODD....)s still
revert to a VALUE! error.

There's also another problem that's developed in that whilst running the
macro if I press ESC and choose END the macro restarts itself.
The only way I can stop it is to choose DEBUG and then quit the debug
process.

Regards
Craig

Next to the isodd formula put in a formula

=isnumber(*data*)

Sub counter()
Dim C As Integer
With Worksheets("Sheet3")
Do Until .Range("c8").Value = 7
Calculate
C = C + 1
.Cells(8, 4) = C
Loop
End With
End Sub

Everytime you enter a number in C8, the workbook should calculate if
calculation is set to automatic.


--
Regards,
Tom Ogilvy

I use the following macro in Sheet 3:-

Sub counter()
Dim C As Integer
Do Until Range("c8").Value = 7
Calculate
C = C + 1
Cells(8, 4) = C
Loop
End Sub

In Sheet 2 I have a set of =If(Isodd(*data*),1,0) and the result of these
*If* statements changes the *value =7* parameter in the above loop

The *data* are randomly generated variables held in Sheet 1 that need to be
re-calculated by the macro until Value=7( note: I only need to re-calc this
sheet's data - all other sheets' calcs are based on Sheet 1 values)

My problem is that when I run the macro all the =If(isodd(.....)s

results

in
Sheet 2 change to a VALUE! error - even though the data are and remain
numerical [ according to Excel Help this can only happen if the *data* are
non-numeric]

What am I doing wrong ? Should the re-calc be only applied to Sheet1 and how
do I do that?
Also how do I ensure that only Cells(8,4) of Sheet 3 is written to i.e.

if

I
launch the macro from another sheet its Cells (8,4) is written to.

Many TIA

Craig

 

[Tom Ogilvy]

Next to the cell containing
=IF(ISODD(SHEET1!J3),1,0)

put in
=ISNUMBER(SHEET1!J3)

It will tell you whether the cell is definitely a number. There is no
reason for isodd to return #value unless it isn't, or perhaps your code
causes some error in the calculation process and calculations do not
complete.

the code I provided was in answer to your second question. I has no bearing
on the #Value problem.

--
Regards,
Tom Ogilvy

Tom,
Many thanks for the reply.
Not quite sure what you mean by * next to the isodd formula put in formula
_=isnumber(data)_*

I need to check how many numbers in SHEET1 J3:Q3 are ODD and the formula
I'm using is *=IF(ISODD(SHEET1!J3),1,0)* and so on for K-Q
So where does =isnumber fit in?[ BTW J3:Q3 are definitely numbers]

I've also modified the macro as you suggested but the =IF(ISODD....)s still
revert to a VALUE! error.

There's also another problem that's developed in that whilst running the
macro if I press ESC and choose END the macro restarts itself.
The only way I can stop it is to choose DEBUG and then quit the debug
process.

Regards
Craig

Next to the isodd formula put in a formula

=isnumber(*data*)

Sub counter()
Dim C As Integer
With Worksheets("Sheet3")
Do Until .Range("c8").Value = 7
Calculate
C = C + 1
.Cells(8, 4) = C
Loop
End With
End Sub

Everytime you enter a number in C8, the workbook should calculate if
calculation is set to automatic.


--
Regards,
Tom Ogilvy

I use the following macro in Sheet 3:-

Sub counter()
Dim C As Integer
Do Until Range("c8").Value = 7
Calculate
C = C + 1
Cells(8, 4) = C
Loop
End Sub

In Sheet 2 I have a set of =If(Isodd(*data*),1,0) and the result of these
*If* statements changes the *value =7* parameter in the above loop

The *data* are randomly generated variables held in Sheet 1 that need

to
be

re-calculated by the macro until Value=7( note: I only need to re-calc this
sheet's data - all other sheets' calcs are based on Sheet 1 values)

My problem is that when I run the macro all the =If(isodd(.....)s

results

in
Sheet 2 change to a VALUE! error - even though the data are and remain
numerical [ according to Excel Help this can only happen if the *data* are
non-numeric]

What am I doing wrong ? Should the re-calc be only applied to Sheet1

and
how

do I do that?
Also how do I ensure that only Cells(8,4) of Sheet 3 is written to

i.e.

if