Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering a formula via VBscript
I have a VBscript which has a formula to do a DCOUNTA on a
database range that match certain criteria. My question is how (with VBscript) do I amend the value of the criteria, currently say SERVER, to be ="=SERVER" The reason being is I'm getting the wrong matches and counts. Sometimes I have the following SERVER,SERVER1,SERVER2 . The server criteria counts all three which puts my totals out. To make it an exact match with DCOUNTA you have to enter the ="=criteria" in the criteria cell. As my data is imported I need to amend the current cell to surround it with the exact bits. My attempts so far have not worked as they come back with a FALSE value rather than the exact text. I also got a VBscript error at one point. Any help or suggestions? Cheers Jacqui |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering a formula via VBscript
Jacqui,
Try something like this: ActiveCell.Formula = _ "=DCOUNTA(A9:A25,""FieldName"",""=Criteria"")" Cheers, Dave. -----Original Message----- I have a VBscript which has a formula to do a DCOUNTA on a database range that match certain criteria. My question is how (with VBscript) do I amend the value of the criteria, currently say SERVER, to be ="=SERVER" The reason being is I'm getting the wrong matches and counts. Sometimes I have the following SERVER,SERVER1,SERVER2 . The server criteria counts all three which puts my totals out. To make it an exact match with DCOUNTA you have to enter the ="=criteria" in the criteria cell. As my data is imported I need to amend the current cell to surround it with the exact bits. My attempts so far have not worked as they come back with a FALSE value rather than the exact text. I also got a VBscript error at one point. Any help or suggestions? Cheers Jacqui . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering a formula via VBscript
Tried various attempts of this but failed.
The criteria must be a range not a value. When trying to enter ""=*Criteria*"" using VB it errors with a compile error saying an expression is required. I need the wild cards in as the search/count I will to do is all in one cell eg server1 server2 server 3 all in cell d5. I need to count how many times server1 appears and also add a value up in a different field. Does this all make sense? Jacqui -----Original Message----- Jacqui, Try something like this: ActiveCell.Formula = _ "=DCOUNTA(A9:A25,""FieldName"",""=Criteria"")" Cheers, Dave. -----Original Message----- I have a VBscript which has a formula to do a DCOUNTA on a database range that match certain criteria. My question is how (with VBscript) do I amend the value of the criteria, currently say SERVER, to be ="=SERVER" The reason being is I'm getting the wrong matches and counts. Sometimes I have the following SERVER,SERVER1,SERVER2 . The server criteria counts all three which puts my totals out. To make it an exact match with DCOUNTA you have to enter the ="=criteria" in the criteria cell. As my data is imported I need to amend the current cell to surround it with the exact bits. My attempts so far have not worked as they come back with a FALSE value rather than the exact text. I also got a VBscript error at one point. Any help or suggestions? Cheers Jacqui . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with parameter substitution in formula (&value), vbScript | Excel Worksheet Functions | |||
Run VBScript from Excel | Excel Discussion (Misc queries) | |||
Anoth vbscript question ... sorry ! | Setting up and Configuration of Excel | |||
conditional formatting + VBscript | Excel Discussion (Misc queries) | |||
how to kill excel using a vbscript | Excel Programming |