Macro syntax problem - Dean
 

I have the following formula in EXCEL (it works)

=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,"=y",C55:C77)

which I want to have visual basic type into the current cell.

Here is my attempt at writing it in VBA but I am getting a compile error
(expected end of statement). Can someone help me, please with my syntax
(perhaps sumif doesn't work in VBA, or perhaps I have left out a comma or
something)?

ActiveCell.FormulaR1C1 =
"=H78*PerfPercent*ScalingFactor*C78/SUMIF(R[-23]C[-3]:R[-1]C[-3]," = Y
",R[-23]C[-6]:R[-1]C[-6])"

Thanks!
Dean

Macro syntax problem - Dean
 

On Fri, 24 Aug 2007 18:16:38 -0700, "Dean" wrote:

I have the following formula in EXCEL (it works)

=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,"=y",C55:C77)

which I want to have visual basic type into the current cell.

Here is my attempt at writing it in VBA but I am getting a compile error
(expected end of statement). Can someone help me, please with my syntax
(perhaps sumif doesn't work in VBA, or perhaps I have left out a comma or
something)?

ActiveCell.FormulaR1C1 =
"=H78*PerfPercent*ScalingFactor*C78/SUMIF(R[-23]C[-3]:R[-1]C[-3]," = Y
",R[-23]C[-6]:R[-1]C[-6])"

Thanks!
Dean


When you have quote marks within your formula, which is a string, you've got to
double them so VBA doesn't think it's the end of the string:

.... C[-3],""=Y"",R[-23] ...


--ron

Macro syntax problem - Dean
 

Thanks, it compiles now but it is producing the wrong equation.
Specifically,

The H78 has apostrophes on either side of it. And the C78 has been replaced
with $bz:$bz

Any idea why?

Thanks again
Dean


"Ron Rosenfeld" wrote in message
...
On Fri, 24 Aug 2007 18:16:38 -0700, "Dean"
wrote:

I have the following formula in EXCEL (it works)

=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,"=y",C55:C77)

which I want to have visual basic type into the current cell.

Here is my attempt at writing it in VBA but I am getting a compile error
(expected end of statement). Can someone help me, please with my syntax
(perhaps sumif doesn't work in VBA, or perhaps I have left out a comma or
something)?

ActiveCell.FormulaR1C1 =
"=H78*PerfPercent*ScalingFactor*C78/SUMIF(R[-23]C[-3]:R[-1]C[-3]," = Y
",R[-23]C[-6]:R[-1]C[-6])"

Thanks!
Dean


When you have quote marks within your formula, which is a string, you've
got to
double them so VBA doesn't think it's the end of the string:

... C[-3],""=Y"",R[-23] ...


--ron

Macro syntax problem - Dean
 

On Fri, 24 Aug 2007 18:55:22 -0700, "Dean" wrote:

Thanks, it compiles now but it is producing the wrong equation.
Specifically,

The H78 has apostrophes on either side of it. And the C78 has been replaced
with $bz:$bz

Any idea why?

Thanks again
Dean

In R1C1 format, H78 is just a string, not a cell address, and C78 is the 78th
column which is column BZ in A1 format. If those values are meant to be cell
addresses, you need to put them in R1C1 format and not in A1 format (like the
rest of your formula).
--ron

Macro syntax problem - Dean
 

try
ActiveCell.Formula =
"=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,""=y"", C55:C77)"
or
range("A1").formula
="=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,""=y"", C55:C77)"
--


Gary

"Dean" wrote in message
...
Thanks, it compiles now but it is producing the wrong equation.
Specifically,

The H78 has apostrophes on either side of it. And the C78 has been
replaced with $bz:$bz

Any idea why?

Thanks again
Dean


"Ron Rosenfeld" wrote in message
...
On Fri, 24 Aug 2007 18:16:38 -0700, "Dean"
wrote:

I have the following formula in EXCEL (it works)

=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,"=y",C55:C77)

which I want to have visual basic type into the current cell.

Here is my attempt at writing it in VBA but I am getting a compile error
(expected end of statement). Can someone help me, please with my syntax
(perhaps sumif doesn't work in VBA, or perhaps I have left out a comma or
something)?

ActiveCell.FormulaR1C1 =
"=H78*PerfPercent*ScalingFactor*C78/SUMIF(R[-23]C[-3]:R[-1]C[-3]," = Y
",R[-23]C[-6]:R[-1]C[-6])"

Thanks!
Dean


When you have quote marks within your formula, which is a string, you've
got to
double them so VBA doesn't think it's the end of the string:

... C[-3],""=Y"",R[-23] ...


--ron

Macro syntax problem - Dean
 

Ahh, I see. Thanks much.

"Ron Rosenfeld" wrote in message
...
On Fri, 24 Aug 2007 18:55:22 -0700, "Dean"
wrote:

Thanks, it compiles now but it is producing the wrong equation.
Specifically,

The H78 has apostrophes on either side of it. And the C78 has been
replaced
with $bz:$bz

Any idea why?

Thanks again
Dean

In R1C1 format, H78 is just a string, not a cell address, and C78 is the
78th
column which is column BZ in A1 format. If those values are meant to be
cell
addresses, you need to put them in R1C1 format and not in A1 format (like
the
rest of your formula).
--ron

Macro syntax problem - Dean
 

Oh, I like this much better than the other format I started with. I guess I
grabbed whatever code that I had and tried to modify it, without thinking to
ask, if I could do it this way. Silly, silly me!

Now I know two ways, with Ron's help, which is good.

Thanks!
Dean

"Gary Keramidas" wrote in message
...
try
ActiveCell.Formula =
"=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,""=y"", C55:C77)"
or
range("A1").formula
="=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,""=y"", C55:C77)"
--


Gary

"Dean" wrote in message
...
Thanks, it compiles now but it is producing the wrong equation.
Specifically,

The H78 has apostrophes on either side of it. And the C78 has been
replaced with $bz:$bz

Any idea why?

Thanks again
Dean


"Ron Rosenfeld" wrote in message
...
On Fri, 24 Aug 2007 18:16:38 -0700, "Dean"
wrote:

I have the following formula in EXCEL (it works)

=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,"=y",C55:C77)

which I want to have visual basic type into the current cell.

Here is my attempt at writing it in VBA but I am getting a compile error
(expected end of statement). Can someone help me, please with my syntax
(perhaps sumif doesn't work in VBA, or perhaps I have left out a comma
or
something)?

ActiveCell.FormulaR1C1 =
"=H78*PerfPercent*ScalingFactor*C78/SUMIF(R[-23]C[-3]:R[-1]C[-3]," = Y
",R[-23]C[-6]:R[-1]C[-6])"

Thanks!
Dean


When you have quote marks within your formula, which is a string, you've
got to
double them so VBA doesn't think it's the end of the string:

... C[-3],""=Y"",R[-23] ...


--ron

Macro syntax problem - Dean
 

Actually, I have one new problem. I put the following line of code into a
macro that had been working perfectly and now it crashes at this line with
an application defined or object defined error message. Am I screwing up
the syntax again? From what I can tell, both cells C55 and H55 have numbers
in them.

Range("C200").Select
ActiveCell.Formula = "=IF(H55="",0,H55-C55)" ' need to do this because
the cells have moved

Thanks!
Dean


"Dean" wrote in message
...
Oh, I like this much better than the other format I started with. I guess
I grabbed whatever code that I had and tried to modify it, without
thinking to ask, if I could do it this way. Silly, silly me!

Now I know two ways, with Ron's help, which is good.

Thanks!
Dean

"Gary Keramidas" wrote in message
...
try
ActiveCell.Formula =
"=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,""=y"", C55:C77)"
or
range("A1").formula
="=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,""=y"", C55:C77)"
--


Gary

"Dean" wrote in message
...
Thanks, it compiles now but it is producing the wrong equation.
Specifically,

The H78 has apostrophes on either side of it. And the C78 has been
replaced with $bz:$bz

Any idea why?

Thanks again
Dean


"Ron Rosenfeld" wrote in message
...
On Fri, 24 Aug 2007 18:16:38 -0700, "Dean"
wrote:

I have the following formula in EXCEL (it works)

=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,"=y",C55:C77)

which I want to have visual basic type into the current cell.

Here is my attempt at writing it in VBA but I am getting a compile
error
(expected end of statement). Can someone help me, please with my
syntax
(perhaps sumif doesn't work in VBA, or perhaps I have left out a comma
or
something)?

ActiveCell.FormulaR1C1 =
"=H78*PerfPercent*ScalingFactor*C78/SUMIF(R[-23]C[-3]:R[-1]C[-3]," = Y
",R[-23]C[-6]:R[-1]C[-6])"

Thanks!
Dean


When you have quote marks within your formula, which is a string,
you've got to
double them so VBA doesn't think it's the end of the string:

... C[-3],""=Y"",R[-23] ...


--ron

Macro syntax problem - Dean
 

2 things:

you don't need to select the cell, and you need to double up the quotes,

Range("C200").Formula = "=IF(H55="""",0,H55-C55)"

--


Gary


"Dean" wrote in message
...
Actually, I have one new problem. I put the following line of code into a
macro that had been working perfectly and now it crashes at this line with an
application defined or object defined error message. Am I screwing up the
syntax again? From what I can tell, both cells C55 and H55 have numbers in
them.

Range("C200").Select
ActiveCell.Formula = "=IF(H55="",0,H55-C55)" ' need to do this because the
cells have moved

Thanks!
Dean


"Dean" wrote in message
...
Oh, I like this much better than the other format I started with. I guess I
grabbed whatever code that I had and tried to modify it, without thinking to
ask, if I could do it this way. Silly, silly me!

Now I know two ways, with Ron's help, which is good.

Thanks!
Dean

"Gary Keramidas" wrote in message
...
try
ActiveCell.Formula =
"=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,""=y"", C55:C77)"
or
range("A1").formula
="=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,""=y"", C55:C77)"
--


Gary

"Dean" wrote in message
...
Thanks, it compiles now but it is producing the wrong equation.
Specifically,

The H78 has apostrophes on either side of it. And the C78 has been
replaced with $bz:$bz

Any idea why?

Thanks again
Dean


"Ron Rosenfeld" wrote in message
...
On Fri, 24 Aug 2007 18:16:38 -0700, "Dean"
wrote:

I have the following formula in EXCEL (it works)

=H78*PerfPercent*ScalingFactor*C78/SUMIF(F55:F77,"=y",C55:C77)

which I want to have visual basic type into the current cell.

Here is my attempt at writing it in VBA but I am getting a compile error
(expected end of statement). Can someone help me, please with my syntax
(perhaps sumif doesn't work in VBA, or perhaps I have left out a comma or
something)?

ActiveCell.FormulaR1C1 =
"=H78*PerfPercent*ScalingFactor*C78/SUMIF(R[-23]C[-3]:R[-1]C[-3]," = Y
",R[-23]C[-6]:R[-1]C[-6])"

Thanks!
Dean


When you have quote marks within your formula, which is a string, you've
got to
double them so VBA doesn't think it's the end of the string:

... C[-3],""=Y"",R[-23] ...


--ron

Macro syntax problem - Dean
 

On Fri, 31 Aug 2007 11:34:48 -0700, "Dean" wrote:

Actually, I have one new problem. I put the following line of code into a
macro that had been working perfectly and now it crashes at this line with
an application defined or object defined error message. Am I screwing up
the syntax again? From what I can tell, both cells C55 and H55 have numbers
in them.

Range("C200").Select
ActiveCell.Formula = "=IF(H55="",0,H55-C55)" ' need to do this because
the cells have moved

Thanks!
Dean

Yes, you are screwing up the syntax again. Your problem is with the included
quote marks, as I wrote previously:


....=IF(H55="""",0,...

When you have quote marks within your formula, which is a string,
you've got to
double them so VBA doesn't think it's the end of the string:

--ron

Macro syntax problem - Dean
 

Ah, yes, that short term memory thing! I'm sorry. Thanks to you both for
responding.

Dean

"Ron Rosenfeld" wrote in message
...
On Fri, 31 Aug 2007 11:34:48 -0700, "Dean"
wrote:

Actually, I have one new problem. I put the following line of code into a
macro that had been working perfectly and now it crashes at this line with
an application defined or object defined error message. Am I screwing up
the syntax again? From what I can tell, both cells C55 and H55 have
numbers
in them.

Range("C200").Select
ActiveCell.Formula = "=IF(H55="",0,H55-C55)" ' need to do this because
the cells have moved

Thanks!
Dean

Yes, you are screwing up the syntax again. Your problem is with the
included
quote marks, as I wrote previously:


...=IF(H55="""",0,...

When you have quote marks within your formula, which is a string,
you've got to
double them so VBA doesn't think it's the end of the string:

--ron