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

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

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

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?


--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64384

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

On 14 feb, 13:05, mdmackillop
wrote:
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?

--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile:http://www.thecodecage.com/forumz/member.php?userid=113
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=64384

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

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

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


--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64384

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

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



wrote in message
...
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

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

On 14 feb, 14:06, "Peter T" <peter_t@discussions wrote:
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

wrote in message

...



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- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -


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

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

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




wrote in message
...
On 14 feb, 14:06, "Peter T" <peter_t@discussions wrote:
Do all cells in the offset range contain a value. If not sure change the
last bit to

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

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.