Vikram Dhemare
New Member
Hello,
I'm working on a excel VBA template which gives users to feed the data in certain dynamic cells. However, what i want is there is to be limitations for users to restrict entry on certain conditions. Somehow i was able to make that by using custom validation forumla. The formula which i'm using is
"=(COUNTIF(C$5:C$14,"Yes")/COUNTA($B$5:$B$14))<= 0.33.
By 33% the figure comes to 3.3 whereas the nearest match figure should be 4, and thus should allow user to enter data in 4 cells but the result are restricting to 3 cells only.
The vba code which i'm using are as below;
With Selection.Validation
.Delete 'delete previous validation
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=(COUNTIF(" & iAdd & ",""Yes"")/COUNTA(" & bAdd & "))<= " & AllQuota & ""
AllQuota is the varibale returns a value (%) from a cell from another sheet. The table range of cells are different for each sheet.
Here is the table;
I tried calling Ceiling.Math worksheet function but it did not work.
Can someone help me with this?
Thank you in advance!
Vikram
I'm working on a excel VBA template which gives users to feed the data in certain dynamic cells. However, what i want is there is to be limitations for users to restrict entry on certain conditions. Somehow i was able to make that by using custom validation forumla. The formula which i'm using is
"=(COUNTIF(C$5:C$14,"Yes")/COUNTA($B$5:$B$14))<= 0.33.
By 33% the figure comes to 3.3 whereas the nearest match figure should be 4, and thus should allow user to enter data in 4 cells but the result are restricting to 3 cells only.
The vba code which i'm using are as below;
With Selection.Validation
.Delete 'delete previous validation
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=(COUNTIF(" & iAdd & ",""Yes"")/COUNTA(" & bAdd & "))<= " & AllQuota & ""
AllQuota is the varibale returns a value (%) from a cell from another sheet. The table range of cells are different for each sheet.
Here is the table;
I tried calling Ceiling.Math worksheet function but it did not work.
Can someone help me with this?
Thank you in advance!
Vikram