DCOUNTA formual in VB Script

[Jacqui Hurst]

I have recorded/modified a script which does sets up some
formulas for me which define criteria for looking up in a
range of cells named Database.

When I wrote this script I had to amend (by educated
guess) the relative entries for the dcounta formulas. I
did this for 50 of them! It all worked fine on it's own.


SITE1 = "=DCOUNTA(Database,5,MBXTypeCrit!R[-4]C[-1]:R[-3]C
[0]"
SITE2 = "=DCOUNTA(Database,5,MBXTypeCrit!R[-3]C[-1]:R[-2]C
[0]"
SITE3 = "=DCOUNTA(Database,5,MBXTypeCrit!R[-2]C[-1]:R[-1]C
[0]"

If Site = 1 Then ActiveCell.FormulaR1C1 = SITE1
If Site = 2 Then ActiveCell.FormulaR1C1 = SITE2
If Site = 3 Then ActiveCell.FormulaR1C1 = SITE3

When I ran it as part of the whole script (importing data
etc) it error with a Runtime error "1004" Application-
define or object-defined error.

I though oh well never mind I'll just run it on its own as
its not essential it runs at the same time.

Now I don't now what I've done but it errors all the time!

I errors when trying to enter the formula in the
ActiveCell. I guess it's something to do with the
relative formula but not sure where to go from here.

Can anyone help?

Regards

Jacqui

 

[Jacqui Hurst]

Doh! Please ignore. I'd lost my end brackets somewhere!

Jacqui

 

[Patrick Molloy]

since you're using references, step through the code and
check that R[-4] or whatever results in a valid cell. So
for example if you're tring to save =R[-4]C in a cell
in row 3, your reference tries to point to a row (-1)
that can't exist.

By the way if you changed
SITE1 = "=DCOUNTA(Data...
SITE2 = "=DCOUNTA(Data...

to
SITEs(1) = "=DCOUNTA(Data...
SITEs(2) = "=DCOUNTA(Data...
etc

then instead of all those IF's like>If Site = 1 Then
ActiveCell.FormulaR1C1 = SITE1

If Site = 2 Then ActiveCell.FormulaR1C1 = SITE2
If Site = 3 Then ActiveCell.FormulaR1C1 = SITE3

just one line takes care of it...
ActiveCell.FormulaR1C1 = SITEs(Site)

HTH
Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I have recorded/modified a script which does sets up some
formulas for me which define criteria for looking up in a
range of cells named Database.

When I wrote this script I had to amend (by educated
guess) the relative entries for the dcounta formulas. I
did this for 50 of them! It all worked fine on it's own.


SITE1 = "=DCOUNTA(Database,5,MBXTypeCrit!R[-4]C[-1]:R[-3] C
[0]"
SITE2 = "=DCOUNTA(Database,5,MBXTypeCrit!R[-3]C[-1]:R[-2] C
[0]"
SITE3 = "=DCOUNTA(Database,5,MBXTypeCrit!R[-2]C[-1]:R[-1] C
[0]"

If Site = 1 Then ActiveCell.FormulaR1C1 = SITE1
If Site = 2 Then ActiveCell.FormulaR1C1 = SITE2
If Site = 3 Then ActiveCell.FormulaR1C1 = SITE3

When I ran it as part of the whole script (importing data
etc) it error with a Runtime error "1004" Application-
define or object-defined error.

I though oh well never mind I'll just run it on its own as
its not essential it runs at the same time.

Now I don't now what I've done but it errors all the time!

I errors when trying to enter the formula in the
ActiveCell. I guess it's something to do with the
relative formula but not sure where to go from here.

Can anyone help?

Regards

Jacqui

.