View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
[email protected] hermac@telenet.be is offline
external usenet poster
 
Posts: 5
Default 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