Formula building code : what's wrong with this little sub?

H

hermac

Hello,
I have a table that is supposed to show the change in another
(parallel) table after changing inputs in a simulation model.
Can you figure what 's wrong with the code below?

Sub Macro3()
Dim c As Range
For Each c In Range("P44:Z54").Cells
c.Formula = "=" & c.Offset(0, -14).Address(0, 0) & "-" & c.Offset(0,
-14).Value
Next c
End Sub

Thank you very much for your remarks !!
Herman
 
M

mdmackillop

Your formula is working but the logic is "wrong". You are sayin
Subtract the value in a cell from the cell with that reference i
itself, so you end up with 0. What formula do you wish in cell P44
 
H

hermac

Your formula is working but the logic is "wrong".  You are saying
Subtract the value in a cell from the cell with that reference ie
itself, so you end up with 0.  What formula do you wish in cell P44?
Click to expand...

Thanks mdmackillop but no, the formula does not get built. I get an
application- or object defined error 1004.
Yes of course, the immediate outcome of the formula would be 0.
But after changing one or more inputs in the model that feeds the
source table, the outcome in the table should show the result of the
changes in the model.
Like comparing an old picture with a new one.
Thanks anyway
 
M

mdmackillop

I get =B44-0.3499 (I used random number in source area) What type o
data are you using? If you are running the code with no data, it wil
error (as it will on any blank cells
 
P

Peter T

Do all cells in the offset range contain a value. If not sure change the
last bit to

& val(c.Offset(0, -14).Value)

Regards,
Peter T
 
H

hermac

Do all cells in the offset range contain a value. If not sure change the
last bit to

& val(c.Offset(0, -14).Value)

Regards,
Peter T








- Tekst uit oorspronkelijk bericht weergeven -
Click to expand...


The data in the offset table are all numeric results of formulas.
so e.g B44 = sum(....) and currently has the value 873873.
Funny thing is , if I change the code like this (just a hard number 10
right after the last concatenation sign instead of the c.offset
formula), it works fine. I get 873863 in P44.

Sub Macro3()
Dim c As Range
For Each c In Range("P44:Z54").Cells
c.Formula = "=" & c.Offset(0, -14).Address(0, 0) & "-" & 10
Next c
End Sub

Peter, your remark has something to do with the problem but does not
solve it completely.
Yes, indeed , ALL cells in the offset range contain numeric values as
results of formulas.
By adding the val instruction to the c.offset, the sub doesn't crash
anymore but results are not really accurate.
The line c.Formula = "=" & c.Offset(0, -14).Address(0, 0) & "-" & val
(c.Offset(0,-14)) (where the sub crashed before)
should deliver nothing but zero's before changes in the offset range.
It does not. When the offset range gives 181%, the result is0.81 or
exactly 1 less.

Thanks again
Herman
 
P

Peter T

There's nothing wrong with your code, but the resulting formulas will only
work as anticipated if the relevant values make sense for the formula. Only
you can see that.

Change the initial "=" to "#=" and run your code

Look in the cells and see if the relevant formulas make sense.

When done to Edit replace "#=" with "=" on a few cells, should get expected
results. If not why not, the answer will be in front of you but not us.

Regards,
Peter T
 
Top