Macro syntax problem - Dean

[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

 

[Ron Rosenfeld]

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

 

[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

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

 

[Ron Rosenfeld]

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

 

[Gary Keramidas]

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

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

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

 

[Dean]

Ahh, I see. Thanks much.

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

 

[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

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

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

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

 

[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

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

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

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


On Fri, 24 Aug 2007 18:16:38 -0700, "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


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

 

[Gary Keramidas]

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

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

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

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

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


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

 

[Ron Rosenfeld]

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

 

[Dean]

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

Dean