Really dumb String Question

G

Guest

Guest
Hi guys,
Err.. This is kinda stupid, but I am stuck nontheless

I want to write a formula using VBA that writes formula "=DCOUNTA(database, "Code", criteria)"

So I wrote something like

Range("A1").FormulaR1C1 = "=DCOUNTA(database," & ""Code"" & ", criteria)" and it wont run.

Any help guys? Am getting dumber by the minute
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
On 2002-03-07 17:56, Anonymous wrote:
Hi guys,
Err.. This is kinda stupid, but I am stuck nontheless

I want to write a formula using VBA that writes formula "=DCOUNTA(database, "Code", criteria)"

So I wrote something like

Range("A1").FormulaR1C1 = "=DCOUNTA(database," & ""Code"" & ", criteria)" and it wont run.

Any help guys? Am getting dumber by the minute

Are "database" and "criteria" variables you are using in your macro?

If not, you can use:

Range("A1").FormulaR1C1 = "=DCOUNTA(database, ""Code"", criteria)"

If they are use:

Range("A1").FormulaR1C1 = "=DCOUNTA(" & database & ",""Code""," & criteria & ")"

Regards,
 
Upvote 0
On 2002-03-07 17:56, Anonymous wrote:
Hi guys,
Err.. This is kinda stupid, but I am stuck nontheless

I want to write a formula using VBA that writes formula "=DCOUNTA(database, "Code", criteria)"

So I wrote something like

Range("A1").FormulaR1C1 = "=DCOUNTA(database," & ""Code"" & ", criteria)" and it wont run.

Any help guys? Am getting dumber by the minute

Try
Range("A1").Formula = "=DCOUNTA(database," & """Code""" & ", criteria)"


Ivan
 
Upvote 0
You're not being stupid.

You do know what your problem is though? You're trying to put " into a string, but that's what is used in VBA to denote a string. So you're going to have to use Chr(34) to put double quotes in your formula.

e.g.

Chr(34) & "Mark" & Chr(34)

would return

"Mark"




_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-03-07 18:08
 
Upvote 0
Thanks for the replies

Tried it, but I got

range = "C5:D6" ' String, and the range is fixed

=DCOUNTA(database,"Code", 'B5':$F:$F)
 
Upvote 0
On 2002-03-07 18:23, Anonymous wrote:
Thanks for the replies

Tried it, but I got

range = "C5:D6" ' String, and the range is fixed

=DCOUNTA(database,"Code", 'B5':$F:$F)

I don't understand what you mean by your statement above. Can you clarify it for me?
 
Upvote 0
Help guys..

My code

Dim db As String, criteria As String, xx As String
db = "DATABASE" & SVCode
criteria = Range("B7:C8").Address

xx = "=DCOUNTA(" & db & ",""Code""," & criteria & ")"

MsgBox xx

Range("D6").FormulaR1C1 = xx

The MsgBox displays the correct formula, range D6 is blank! Anyone??
 
Upvote 0
On 2002-03-07 18:49, Anonymous wrote:
Help guys..

My code

Dim db As String, criteria As String, xx As String
db = "DATABASE" & SVCode
criteria = Range("B7:C8").Address

xx = "=DCOUNTA(" & db & ",""Code""," & criteria & ")"

MsgBox xx

Range("D6").FormulaR1C1 = xx

The MsgBox displays the correct formula, range D6 is blank! Anyone??

The formula I gave is basically the same
ie change Formula.R1c1 to
Range("D6").Formula = xx

Ivan
 
Upvote 0
On 2002-03-07 18:49, Anonymous wrote:
Help guys..

My code

Dim db As String, criteria As String, xx As String
db = "DATABASE" & SVCode
criteria = Range("B7:C8").Address

xx = "=DCOUNTA(" & db & ",""Code""," & criteria & ")"

MsgBox xx

Range("D6").FormulaR1C1 = xx

The MsgBox displays the correct formula, range D6 is blank! Anyone??

Change
criteria = Range("B7:C8").Address

to
criteria = Range("B7:C8").Address(False, False)

Regards,
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top