Solved
VBA problem
Posted on 2003-10-29
Hi,
I have a worksheet as below:
columnA columnB columnC columnD columnE
48.00 44.00 44.00 48.00
in the VBE, i will create a macro that will insert a formula into columnE.
this is my formula:
cells(1,5).formula="=IF(AND(MOD(AVERAGE(A1:D1),1)<0.1,AVERAGE(A1:D1)>1),TRUNC(AVERAGE(A1:D1)),ROUNDUP(AVERAGE(A1:D1),0)))"
(the value in cells(1,5) should be 46.00)
then i write a coding to change the value in cells(1,5) :
if cells(1,5) mod 4 <1 then
cells(1,5).formula="=IF(AND(MOD(AVERAGE(A1:D1),1)<0.1,AVERAGE(A1:D1)>1),TRUNC(AVERAGE
(A1:D1)),ROUNDUP(AVERAGE(A1:D1),0)))"
elseif cells(1,5) mod 4 <2 then
cells(1,5).formula="=IF(AND(MOD(AVERAGE(A1:D1),1)<0.1,AVERAGE(A1:D1)>1),TRUNC(AVERAGE
(A1:D1)),ROUNDUP(AVERAGE(A1:D1),0)))-1"
elseif cells(1,5) mod 4<3 then
cells(1,5).formula="=IF(AND(MOD(AVERAGE(A1:D1),1)<0.1,AVERAGE(A1:D1)>1),TRUNC(AVERAGE
(A1:D1)),ROUNDUP(AVERAGE(A1:D1),0)))+2"
else
cells(1,5).formula="=IF(AND(MOD(AVERAGE(A1:D1),1)<0.1,AVERAGE(A1:D1)>1),TRUNC(AVERAGE
(A1:D1)),ROUNDUP(AVERAGE(A1:D1),0)))+1"
end if
The coding above meaning it will round the number.
For example, if the value is 5, it will be rounded to 4
if the value is 7, it will be rounded to 8
if the value is 6, it will be rounded to 8
if the value is 3, it will be rounded to 4
so, after i run the macro, the final value for cells(1,5) should be 48.00. Agree?
Now the value in cells(1.5) is 48.00, it will add 2 to the 46.00.
The problem is when i change the value in cells(1,1) to 100.00, the value in cells(1,5) is 61.00. By right the value should be 60.00. What can i do to the coding so that i will get the value of 60.00 in cells(1,5) when i change the value in cells(1,1) to 100. (Meaning i don't wan to run the macro again, i just have to run the macro once, and in the future, when i change the value in cells(1,1), it will gives me the correct value for the cells(1,5) ).
Anyone know how to do it?
cheers,
brandon