strange error

[AD108]

I have the following formula in column "I". I have code that puts a range
of values in to column "G". The values must be pasted as "Values" as the
source range contains formulas. When the values are pasted, all the
formulas in "I" return an error. The weird thing is, if I double click any
of the cells in "G" and press enter, the error goes away. Any suggestions
on how to fix this?

=IF(ISBLANK(G3),"",ROUND(IF(OR(H3="cs",H3=""),G3,G3/$E$3),2))

 

[Guest]

One way ..
Try adding a zero to coerce the text numbers in col G to real numbers:
=IF(ISBLANK(G3),"",ROUND(IF(OR(H3="cs",H3=""),G3+0,(G3+0)/$E$3),2))

 

[AD108]

THanks, I'll try that.

One way ..
Try adding a zero to coerce the text numbers in col G to real numbers:
=IF(ISBLANK(G3),"",ROUND(IF(OR(H3="cs",H3=""),G3+0,(G3+0)/$E$3),2))

 

[Guest]

You're welcome !

 

[AD108]

I tried that, but it's still doing the same thing. If I select the cell,
press F2 and then enter, the error goes away. I tried reformatting them
also. Weird.

Maybe I'll just use some code to convert the cells to numbers.

 

[Guest]

I tried that, but it's still doing the same thing. If I select the cell,
press F2 and then enter, the error goes away. I tried reformatting them
also. Weird.

Wondering whether calc mode has anything to do with it? Could it be
inadvertently set to Manual? Press F9, does it now compute? Check and ensure
calc mode's on Automatic via: Tools > Options > Calculation tab

---

 

[AD108]

Hi Max,

Thanks again. I did try that too.
I solved my problem by looping through each cell in the ranges and
re-setting the the values to the existing values. This took away the bug I
was experiencing.

For each cell in Range(A3:A398)
Cell.Value = Cell.Value
Next

 

[Guest]

Thanks for response. Glad to hear you resolved it.
Sorry that I wasn't of much help here. Cheers.