using excel formulas in VB coding
 

I'm having some issues when trying to use a macro to generate formulas
in a worksheet.

Range("J10").Formula = "=count(e:e)"

This line works perfectly. Cell J10 will have the formula =count(E:E)
placed in it when the macro is ran. I tried to just follow the basic
setup using other formulas and I'm getting very frustrated...

Range("J11").Formula = "=SUMIF(D:D, " = "&L12,E:E )"
Range("J12").Formula = "=COUNTIF(D:D, " = "&L12)"

Both of the above lines return a boolean value instead of the actual
results of the sumif/countif formulas


Range("J14").Formula = "=SUMIF(E:E, "39999",E:E )"

This above line gives me compie error - expected: end of statement???

Is there a simple way to have a macro place a formula into a specific
cell in a sheet?

using excel formulas in VB coding
 

your problem resides in the number of " that you are using.

try replacing " with " & Chr(34) & "

for example:
Range("J12").Formula = "=COUNTIF(D:D, " = "&L12)"

should read:
Range("J12").Formula = "=COUNTIF(D:D, " & Chr(34) & " = " & Chr(34) & "&L12)"



"Matthew Dyer" wrote:

I'm having some issues when trying to use a macro to generate formulas
in a worksheet.

Range("J10").Formula = "=count(e:e)"

This line works perfectly. Cell J10 will have the formula =count(E:E)
placed in it when the macro is ran. I tried to just follow the basic
setup using other formulas and I'm getting very frustrated...

Range("J11").Formula = "=SUMIF(D:D, " = "&L12,E:E )"
Range("J12").Formula = "=COUNTIF(D:D, " = "&L12)"

Both of the above lines return a boolean value instead of the actual
results of the sumif/countif formulas


Range("J14").Formula = "=SUMIF(E:E, "39999",E:E )"

This above line gives me compie error - expected: end of statement???

Is there a simple way to have a macro place a formula into a specific
cell in a sheet?
.

using excel formulas in VB coding
 

Matthew,

You have two problems. Internal quotes must be doubled up and you have axtra
internal spaces which will mess up the formula. try

Range("J11").Formula = "=SUMIF(D:D,""=""&L12,E:E )"
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Matthew Dyer" wrote:

I'm having some issues when trying to use a macro to generate formulas
in a worksheet.

Range("J10").Formula = "=count(e:e)"

This line works perfectly. Cell J10 will have the formula =count(E:E)
placed in it when the macro is ran. I tried to just follow the basic
setup using other formulas and I'm getting very frustrated...

Range("J11").Formula = "=SUMIF(D:D, " = "&L12,E:E )"
Range("J12").Formula = "=COUNTIF(D:D, " = "&L12)"

Both of the above lines return a boolean value instead of the actual
results of the sumif/countif formulas


Range("J14").Formula = "=SUMIF(E:E, "39999",E:E )"

This above line gives me compie error - expected: end of statement???

Is there a simple way to have a macro place a formula into a specific
cell in a sheet?
.

using excel formulas in VB coding
 

On Mar 16, 5:17*pm, IdiotZ42
wrote:
your problem resides in the number of " that you are using.

try replacing " with " & Chr(34) & "

for example:
Range("J12").Formula = "=COUNTIF(D:D, " = "&L12)"

should read:
Range("J12").Formula = "=COUNTIF(D:D, " & Chr(34) & " = " & Chr(34) & "&L12)"



"Matthew Dyer" wrote:
I'm having some issues when trying to use a macro to generate formulas
in a worksheet.

Range("J10").Formula = "=count(e:e)"

This line works perfectly. Cell J10 will have the formula =count(E:E)
placed in it when the macro is ran. I tried to just follow the basic
setup using other formulas and I'm getting very frustrated...

Range("J11").Formula = "=SUMIF(D:D, " = "&L12,E:E )"
Range("J12").Formula = "=COUNTIF(D:D, " = "&L12)"

Both of the above lines return a boolean value instead of the actual
results of the sumif/countif formulas

Range("J14").Formula = "=SUMIF(E:E, "39999",E:E )"

This above line gives me *compie error - expected: end of statement???

Is there a simple way to have a macro place a formula into a specific
cell in a sheet?
.- Hide quoted text -

- Show quoted text -

I was still getting some funky results so I just went back into my VBA
editor and deleted every space from the code you provided. Then the
editor automatically filled in the spaces where they needed to be and
it worked perfectly. Thanks for your help! Just curious, what is the
Chr(34) actually doing?

using excel formulas in VB coding
 

You have to double up those quote marks inside your string:

Range("J11").Formula = "=SUMIF(D:D,""=""&L12,E:E)"
Range("J12").Formula = "=COUNTIF(D:D,""=""&L12)"
Range("J14").Formula = "=SUMIF(E:E,""39999"",E:E)"

or for the last one:
Range("J14").Formula = "=SUMIF(E:E,""""&39999,E:E)"




Matthew Dyer wrote:

I'm having some issues when trying to use a macro to generate formulas
in a worksheet.

Range("J10").Formula = "=count(e:e)"

This line works perfectly. Cell J10 will have the formula =count(E:E)
placed in it when the macro is ran. I tried to just follow the basic
setup using other formulas and I'm getting very frustrated...

Range("J11").Formula = "=SUMIF(D:D, " = "&L12,E:E )"
Range("J12").Formula = "=COUNTIF(D:D, " = "&L12)"

Both of the above lines return a boolean value instead of the actual
results of the sumif/countif formulas

Range("J14").Formula = "=SUMIF(E:E, "39999",E:E )"

This above line gives me compie error - expected: end of statement???

Is there a simple way to have a macro place a formula into a specific
cell in a sheet?

--

Dave Peterson