Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
building a formula | Excel Programming | |||
VBA formula building | Excel Programming | |||
Building a zip code database | Excel Programming | |||
building code for an individual report | Excel Programming | |||
When building formula in excel, it would be very useful to have t. | Excel Programming |