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 |
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 . |
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 . . |
All times are GMT +1. The time now is 01:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com