Really dumb String Question

[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

 

[Barrie Davidson]

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,

 

[Ivan F Moala]

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

 

[Mark O'Brien]

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

 

[Guest]

Thanks for the replies

Tried it, but I got

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

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

 

[Barrie Davidson]

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?

 

[Guest]

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

 

[Ivan F Moala]

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

 

[Barrie Davidson]

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,