concatenating line decided not to work
 

I have copied some code from one section of code to another. For the
life of me, I can not figure out why it is not working.
I can get as far as the lines that have the rc[-1]/reviewtotalCA
formulas - and it works fine. baselinetotalCA and reviewtotalCA are
names of cells elsewhere. However, I can not use the intermediate
window to obtain their values.
Also, the following formula, jformula, works elsewhere, but here, it
does not. Elsewhere, I use "named" cells as references, and it works
fine. But it is not here. I have tried giving variable names to the
values in the named cells and using the variable names in jformula -
but can't get that to work either.


Please shed some light on why this is not working.


Set causetable = Range("aa2").CurrentRegion
fields = causetable.Rows.Count


Set causetable2 = causetable.Offset(fields, 1).Resize(1, 4)


causetable2.Select
Selection.Formula = "=sum(" & causetable.Columns(2).Address(Â*True,
False) & ")"
ActiveCell.Name = "baselinetotalCA"
causetable2.Range("c1").Name = "reviewtotalCA"
'reviewtotalCA = .Value
Range("ac2:ac" & fields + 1).FormulaR1C1 = "=rc[-1]/baselinetotalCA"
Range("aE2:aE" & fields + 1).FormulaR1C1 = "=rc[-1]/reviewtotalCA"


'the above lines give me expected results


jFormula = "=concatenate(""Baseline: "",TEXT(begbase3,""m/d/yy"")" & _
","" through "",TEXT(endbase3,""m/d/yy"")" & _
", "" (Total "",text(baselinetotalCA,""0"")Â*,"" events; Yearly avg
"","")"")"
Range("ac1").Formula = jFormula

concatenating line decided not to work
 

Your formula worked for me.

Give I had the 3 names defined, it produced:

Baseline: 1/1/04 through 1/15/05 (Total 100 events; Yearly avg )

--
Regards,
Tom Ogilvy

"Papa Jonah" wrote in message
...
I have copied some code from one section of code to another. For the
life of me, I can not figure out why it is not working.
I can get as far as the lines that have the rc[-1]/reviewtotalCA
formulas - and it works fine. baselinetotalCA and reviewtotalCA are
names of cells elsewhere. However, I can not use the intermediate
window to obtain their values.
Also, the following formula, jformula, works elsewhere, but here, it
does not. Elsewhere, I use "named" cells as references, and it works
fine. But it is not here. I have tried giving variable names to the
values in the named cells and using the variable names in jformula -
but can't get that to work either.


Please shed some light on why this is not working.


Set causetable = Range("aa2").CurrentRegion
fields = causetable.Rows.Count


Set causetable2 = causetable.Offset(fields, 1).Resize(1, 4)


causetable2.Select
Selection.Formula = "=sum(" & causetable.Columns(2).Address(*True,
False) & ")"
ActiveCell.Name = "baselinetotalCA"
causetable2.Range("c1").Name = "reviewtotalCA"
'reviewtotalCA = .Value
Range("ac2:ac" & fields + 1).FormulaR1C1 = "=rc[-1]/baselinetotalCA"
Range("aE2:aE" & fields + 1).FormulaR1C1 = "=rc[-1]/reviewtotalCA"


'the above lines give me expected results


jFormula = "=concatenate(""Baseline: "",TEXT(begbase3,""m/d/yy"")" & _
","" through "",TEXT(endbase3,""m/d/yy"")" & _
", "" (Total "",text(baselinetotalCA,""0"")*,"" events; Yearly avg
"","")"")"
Range("ac1").Formula = jFormula

concatenating line decided not to work
 

That's what it is supposed to do, but it won't for me. Did you define
reviewtotalCA with a name or as a variable?

"Tom Ogilvy" wrote:

Your formula worked for me.

Give I had the 3 names defined, it produced:

Baseline: 1/1/04 through 1/15/05 (Total 100 events; Yearly avg )

--
Regards,
Tom Ogilvy

"Papa Jonah" wrote in message
...
I have copied some code from one section of code to another. For the
life of me, I can not figure out why it is not working.
I can get as far as the lines that have the rc[-1]/reviewtotalCA
formulas - and it works fine. baselinetotalCA and reviewtotalCA are
names of cells elsewhere. However, I can not use the intermediate
window to obtain their values.
Also, the following formula, jformula, works elsewhere, but here, it
does not. Elsewhere, I use "named" cells as references, and it works
fine. But it is not here. I have tried giving variable names to the
values in the named cells and using the variable names in jformula -
but can't get that to work either.


Please shed some light on why this is not working.


Set causetable = Range("aa2").CurrentRegion
fields = causetable.Rows.Count


Set causetable2 = causetable.Offset(fields, 1).Resize(1, 4)


causetable2.Select
Selection.Formula = "=sum(" & causetable.Columns(2).Address(Â*True,
False) & ")"
ActiveCell.Name = "baselinetotalCA"
causetable2.Range("c1").Name = "reviewtotalCA"
'reviewtotalCA = .Value
Range("ac2:ac" & fields + 1).FormulaR1C1 = "=rc[-1]/baselinetotalCA"
Range("aE2:aE" & fields + 1).FormulaR1C1 = "=rc[-1]/reviewtotalCA"


'the above lines give me expected results


jFormula = "=concatenate(""Baseline: "",TEXT(begbase3,""m/d/yy"")" & _
","" through "",TEXT(endbase3,""m/d/yy"")" & _
", "" (Total "",text(baselinetotalCA,""0"")Â*,"" events; Yearly avg
"","")"")"
Range("ac1").Formula = jFormula

concatenating line decided not to work
 

I didn't run the code. I used the immediate window to generate your formula
and pasted it into a cell in the worksheet to make sure it produced an
acceptable formula - I assumed that was the source of your problem.
However, it worked fine.

--
Regards,
Tom Ogilvy



"Papa Jonah" wrote in message
...
That's what it is supposed to do, but it won't for me. Did you define
reviewtotalCA with a name or as a variable?

"Tom Ogilvy" wrote:

Your formula worked for me.

Give I had the 3 names defined, it produced:

Baseline: 1/1/04 through 1/15/05 (Total 100 events; Yearly avg )

--
Regards,
Tom Ogilvy

"Papa Jonah" wrote in message
...
I have copied some code from one section of code to another. For the
life of me, I can not figure out why it is not working.
I can get as far as the lines that have the rc[-1]/reviewtotalCA
formulas - and it works fine. baselinetotalCA and reviewtotalCA are
names of cells elsewhere. However, I can not use the intermediate
window to obtain their values.
Also, the following formula, jformula, works elsewhere, but here, it
does not. Elsewhere, I use "named" cells as references, and it works
fine. But it is not here. I have tried giving variable names to the
values in the named cells and using the variable names in jformula -
but can't get that to work either.


Please shed some light on why this is not working.


Set causetable = Range("aa2").CurrentRegion
fields = causetable.Rows.Count


Set causetable2 = causetable.Offset(fields, 1).Resize(1, 4)


causetable2.Select
Selection.Formula = "=sum(" & causetable.Columns(2).Address(*True,
False) & ")"
ActiveCell.Name = "baselinetotalCA"
causetable2.Range("c1").Name = "reviewtotalCA"
'reviewtotalCA = .Value
Range("ac2:ac" & fields + 1).FormulaR1C1 = "=rc[-1]/baselinetotalCA"
Range("aE2:aE" & fields + 1).FormulaR1C1 = "=rc[-1]/reviewtotalCA"


'the above lines give me expected results


jFormula = "=concatenate(""Baseline: "",TEXT(begbase3,""m/d/yy"")" & _
","" through "",TEXT(endbase3,""m/d/yy"")" & _
", "" (Total "",text(baselinetotalCA,""0"")*,"" events; Yearly avg
"","")"")"
Range("ac1").Formula = jFormula