strange error

A

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))
 
G

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))
 
A

AD108

THanks, I'll try that.
Max said:
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))
 
A

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.
 
G

Guest

AD108 said:
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

---
 
A

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
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top