asked on

Enter values in one range based upon values in another range...

Whenever "(1)" [without " "] shows up in range AQ102:AQ141 I would like VBA code to enter 0 [zero] in the corresponding cell in range AQ59:AQ98.

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Daniel Reynolds

Your specs are a little vague, but if AQ102:AQ141 are text formatted cells, the following formula will provide the zero for the corresponding cell(s).

=IF(AQ102 = "(1)",0,"")
once you have entered the formula in cell AQ59, just select the cell and drag the lower right corner down thru cell AQ98. This will copy the formula to the remaining cells.

ronadair

ASKER

Graham -

I tried the code but it won't execute. The line
If cl.Value = "(1)" Then
is highlighted in yellow.

Division produces the values in AQ102:AQ141 & a few of them come up #DIV/0!. Could this be screwing up the code?

Ron

GrahamSkan

Yes. I wasn't expecting that. Try cl.Text instead of cl.Value.

ronadair

ASKER

Graham -

I tried the change... still no cigar. ;>(

Ron

ronadair

ASKER

Graham -
I got rid of the #DIV/0! error with the formula
=IF(OR(Y59=0,AB8=0),0,Y59/AB8)
Your solution now works.

Thanks.

ronadair

ASKER

Graham -

I fixed the #DIV/0! issue & the code worked with a minor adjustment. Back to cl.Value and "-1" rather than "(1)".

Thanks,

Ron

GrahamSkan

Yes if you simply enter (1) into a cell, it is converted to -1, but if you enter '(1), you will get the text - less the apostrophe.