Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
backing up formulas in sheet coding? | Excel Discussion (Misc queries) | |||
Need a little excel coding help | Excel Programming | |||
help with coding in excel | Excel Worksheet Functions | |||
Implant macro coding into ASP coding | Excel Programming | |||
C C+ Excel Coding | Excel Programming |