using excel formulas in VB coding

[Matthew Dyer]

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, " >= "&L12,E:E )"
Range("J12").Formula = "=COUNTIF(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?

 

[IdiotZ42]

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

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

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

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

 

[Mike H]

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,"">=""&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]

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

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

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

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












- 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?

 

[Dave Peterson]

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

Range("J11").Formula = "=SUMIF(D,"">=""&L12,E:E)"
Range("J12").Formula = "=COUNTIF(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)"