DCOUNTA formual in VB Script
 

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

DCOUNTA formual in VB Script
 

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

Jacqui

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

.

DCOUNTA formual in VB Script
 

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

.