Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DCounta doesn't work | Excel Worksheet Functions | |||
Dcounta | Excel Discussion (Misc queries) | |||
please help with dcounta | Excel Discussion (Misc queries) | |||
DCOUNTA & wildcards | Excel Discussion (Misc queries) | |||
Dcounta & dates | Excel Discussion (Misc queries) |