Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 178
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 178
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
backing up formulas in sheet coding? Derrick Excel Discussion (Misc queries) 2 June 24th 09 07:52 PM
Need a little excel coding help hshayh0rn Excel Programming 12 May 5th 09 04:53 PM
help with coding in excel associates Excel Worksheet Functions 3 June 7th 06 10:41 AM
Implant macro coding into ASP coding Sam yong Excel Programming 5 September 15th 05 10:37 AM
C C+ Excel Coding David Copp[_2_] Excel Programming 3 January 27th 04 06:11 AM


All times are GMT +1. The time now is 10:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"