Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use VBA variable in COUNTIF function?
I sincerely appreciate whoever's help on this question!
I need to use Excel formula functions of COUNTIF and SUM in VBA. The range for calculation need to refer to a variable set in the VBA which is changing from one worksheet to another. However, the variable can't be recognized by the COUNTIF function. The actual statement is: Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" or Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" This formula is to count the number of "Abs" within the left (C-1) columns of the activecell. "C" is the variable set in VBA that has to be used for iteration steps in diferrent worksheet. Activecell is changing within 1 column of individual worksheet. Must be writen into VBA because number of activecells is uncertain when design. How to make Excel function work by using the variable "C"? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use VBA variable in COUNTIF function?
are you using all of the cells with values?
then you can use the range "activecell.usedrange", it returns an object of the type "Range" containing all cell with values in the current sheet, to wich you can apply any formula or any method that applies to a normal range. you can also avoid usin activecell.formular1c1 and use the value of the formula itself activecell.value=application.worksheetfunction.her e_goes_the_function then you formula would be activecell.value=application.worksheetfunction.cou ntif(activesheet.usedrange,"abs") -- hope it works, regards! --- zz [MX] cuasi-musico,semi-poeta y loco "Terry" wrote in message ... I sincerely appreciate whoever's help on this question! I need to use Excel formula functions of COUNTIF and SUM in VBA. The range for calculation need to refer to a variable set in the VBA which is changing from one worksheet to another. However, the variable can't be recognized by the COUNTIF function. The actual statement is: Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" or Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" This formula is to count the number of "Abs" within the left (C-1) columns of the activecell. "C" is the variable set in VBA that has to be used for iteration steps in diferrent worksheet. Activecell is changing within 1 column of individual worksheet. Must be writen into VBA because number of activecells is uncertain when design. How to make Excel function work by using the variable "C"? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use VBA variable in COUNTIF function?
Thanks Don!
still not working. messagebox shows "0". Really frustrated for this calculation. Spent 10 hours already. any other ways to work it out? need to refer to columns left to activecell and only at the same row because lower rows got repeating data. mind to tell me your email? you may understand better if I pass you the whole project. if you have time on it... "Don Guillett" wrote: try Sub sumifvarcol() mc = ActiveCell.Column - 1 MsgBox Application.CountIf(columns(mc), "abs") End Sub -- Don Guillett SalesAid Software "Terry" wrote in message ... I sincerely appreciate whoever's help on this question! I need to use Excel formula functions of COUNTIF and SUM in VBA. The range for calculation need to refer to a variable set in the VBA which is changing from one worksheet to another. However, the variable can't be recognized by the COUNTIF function. The actual statement is: Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" or Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" This formula is to count the number of "Abs" within the left (C-1) columns of the activecell. "C" is the variable set in VBA that has to be used for iteration steps in diferrent worksheet. Activecell is changing within 1 column of individual worksheet. Must be writen into VBA because number of activecells is uncertain when design. How to make Excel function work by using the variable "C"? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use VBA variable in COUNTIF function?
Send it. My email is here
-- Don Guillett SalesAid Software "Terry" wrote in message ... Thanks Don! still not working. messagebox shows "0". Really frustrated for this calculation. Spent 10 hours already. any other ways to work it out? need to refer to columns left to activecell and only at the same row because lower rows got repeating data. mind to tell me your email? you may understand better if I pass you the whole project. if you have time on it... "Don Guillett" wrote: try Sub sumifvarcol() mc = ActiveCell.Column - 1 MsgBox Application.CountIf(columns(mc), "abs") End Sub -- Don Guillett SalesAid Software "Terry" wrote in message ... I sincerely appreciate whoever's help on this question! I need to use Excel formula functions of COUNTIF and SUM in VBA. The range for calculation need to refer to a variable set in the VBA which is changing from one worksheet to another. However, the variable can't be recognized by the COUNTIF function. The actual statement is: Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" or Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" This formula is to count the number of "Abs" within the left (C-1) columns of the activecell. "C" is the variable set in VBA that has to be used for iteration steps in diferrent worksheet. Activecell is changing within 1 column of individual worksheet. Must be writen into VBA because number of activecells is uncertain when design. How to make Excel function work by using the variable "C"? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use VBA variable in COUNTIF function?
or modify this to suit
Sub sumifvarcol() mc = ActiveCell.Column - 1 'MsgBox Application.CountIf(Columns(mc), "abs") MsgBox Application.CountIf(Range(Cells(7, mc), Cells(20, mc)), "abs") End Sub -- Don Guillett SalesAid Software "Terry" wrote in message ... Thanks Don! still not working. messagebox shows "0". Really frustrated for this calculation. Spent 10 hours already. any other ways to work it out? need to refer to columns left to activecell and only at the same row because lower rows got repeating data. mind to tell me your email? you may understand better if I pass you the whole project. if you have time on it... "Don Guillett" wrote: try Sub sumifvarcol() mc = ActiveCell.Column - 1 MsgBox Application.CountIf(columns(mc), "abs") End Sub -- Don Guillett SalesAid Software "Terry" wrote in message ... I sincerely appreciate whoever's help on this question! I need to use Excel formula functions of COUNTIF and SUM in VBA. The range for calculation need to refer to a variable set in the VBA which is changing from one worksheet to another. However, the variable can't be recognized by the COUNTIF function. The actual statement is: Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" or Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" This formula is to count the number of "Abs" within the left (C-1) columns of the activecell. "C" is the variable set in VBA that has to be used for iteration steps in diferrent worksheet. Activecell is changing within 1 column of individual worksheet. Must be writen into VBA because number of activecells is uncertain when design. How to make Excel function work by using the variable "C"? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use VBA variable in COUNTIF function?
this time it works:
ActiveCell.Offset(0, 1 - C).Resize(1, C - 1).Select Set Ran = Selection ActiveCell.Offset(0, C -1).Select ActiveCell.Value = Application.WorksheetFunction.CountIf(Ran, "Abs") The problem is: this macro is desifned to run at first time after people key in the namelist. the whole project will start to format every worksheet including charts later. It is better to keep formular stay in the cell once all the worksheet established since the macro runs really long time to comlete all formating. And, I designed as erase all exsiting data and reinput when start up. Any way to use the CUUNTIF formula when using VBA variable? if need to get result through execution of macro, nobody will use this form already. "zz" wrote: are you using all of the cells with values? then you can use the range "activecell.usedrange", it returns an object of the type "Range" containing all cell with values in the current sheet, to wich you can apply any formula or any method that applies to a normal range. you can also avoid usin activecell.formular1c1 and use the value of the formula itself activecell.value=application.worksheetfunction.her e_goes_the_function then you formula would be activecell.value=application.worksheetfunction.cou ntif(activesheet.usedrange,"abs") -- hope it works, regards! --- zz [MX] cuasi-musico,semi-poeta y loco "Terry" wrote in message ... I sincerely appreciate whoever's help on this question! I need to use Excel formula functions of COUNTIF and SUM in VBA. The range for calculation need to refer to a variable set in the VBA which is changing from one worksheet to another. However, the variable can't be recognized by the COUNTIF function. The actual statement is: Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" or Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" This formula is to count the number of "Abs" within the left (C-1) columns of the activecell. "C" is the variable set in VBA that has to be used for iteration steps in diferrent worksheet. Activecell is changing within 1 column of individual worksheet. Must be writen into VBA because number of activecells is uncertain when design. How to make Excel function work by using the variable "C"? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use VBA variable in COUNTIF function?
Still not sure of what you are trying to do without seeing it but
Get RID of the selections. ActiveCell.Offset(0, 1 - C).Resize(1, C - 1).Select Set Ran = Selection ActiveCell.Offset(0, C -1).Select ActiveCell.Value = Application.WorksheetFunction.CountIf(Ran, "Abs") try ran=activecell.Offset(0, 1 - C).Resize(1, C - 1) ActiveCell.Offset(0, C -1)= Application.CountIf(Ran, "Abs") -- Don Guillett SalesAid Software "Terry" wrote in message ... this time it works: ActiveCell.Offset(0, 1 - C).Resize(1, C - 1).Select Set Ran = Selection ActiveCell.Offset(0, C -1).Select ActiveCell.Value = Application.WorksheetFunction.CountIf(Ran, "Abs") The problem is: this macro is desifned to run at first time after people key in the namelist. the whole project will start to format every worksheet including charts later. It is better to keep formular stay in the cell once all the worksheet established since the macro runs really long time to comlete all formating. And, I designed as erase all exsiting data and reinput when start up. Any way to use the CUUNTIF formula when using VBA variable? if need to get result through execution of macro, nobody will use this form already. "zz" wrote: are you using all of the cells with values? then you can use the range "activecell.usedrange", it returns an object of the type "Range" containing all cell with values in the current sheet, to wich you can apply any formula or any method that applies to a normal range. you can also avoid usin activecell.formular1c1 and use the value of the formula itself activecell.value=application.worksheetfunction.her e_goes_the_function then you formula would be activecell.value=application.worksheetfunction.cou ntif(activesheet.usedrange,"abs") -- hope it works, regards! --- zz [MX] cuasi-musico,semi-poeta y loco "Terry" wrote in message ... I sincerely appreciate whoever's help on this question! I need to use Excel formula functions of COUNTIF and SUM in VBA. The range for calculation need to refer to a variable set in the VBA which is changing from one worksheet to another. However, the variable can't be recognized by the COUNTIF function. The actual statement is: Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" or Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" This formula is to count the number of "Abs" within the left (C-1) columns of the activecell. "C" is the variable set in VBA that has to be used for iteration steps in diferrent worksheet. Activecell is changing within 1 column of individual worksheet. Must be writen into VBA because number of activecells is uncertain when design. How to make Excel function work by using the variable "C"? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use VBA variable in COUNTIF function?
Untested suggestions:
Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" Selection.Value = Evaluate("COUNTIF(RC["& (1-C) & "]:RC[-1], ""Abs"")" Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" Activecell.FormulaR1C1 = "=COUNTIF(RC[" & (1-C) & "]:RC[-1], ""Abs"")" -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I sincerely appreciate whoever's help on this question! I need to use Excel formula functions of COUNTIF and SUM in VBA. The range for calculation need to refer to a variable set in the VBA which is changing from one worksheet to another. However, the variable can't be recognized by the COUNTIF function. The actual statement is: Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" or Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" This formula is to count the number of "Abs" within the left (C-1) columns of the activecell. "C" is the variable set in VBA that has to be used for iteration steps in diferrent worksheet. Activecell is changing within 1 column of individual worksheet. Must be writen into VBA because number of activecells is uncertain when design. How to make Excel function work by using the variable "C"? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use VBA variable in COUNTIF function?
Thank you so much! This second one works great!
..Value or .FormulaR1C1 is not important. It's "&( )&" makes it work. Thousands of thanks again! "Tushar Mehta" wrote: Untested suggestions: Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" Selection.Value = Evaluate("COUNTIF(RC["& (1-C) & "]:RC[-1], ""Abs"")" Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" Activecell.FormulaR1C1 = "=COUNTIF(RC[" & (1-C) & "]:RC[-1], ""Abs"")" -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I sincerely appreciate whoever's help on this question! I need to use Excel formula functions of COUNTIF and SUM in VBA. The range for calculation need to refer to a variable set in the VBA which is changing from one worksheet to another. However, the variable can't be recognized by the COUNTIF function. The actual statement is: Selection.Value = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" or Activecell.FormulaR1C1 = "=COUNTIF(RC[1 - C]:RC[- 1], ""Abs"")" This formula is to count the number of "Abs" within the left (C-1) columns of the activecell. "C" is the variable set in VBA that has to be used for iteration steps in diferrent worksheet. Activecell is changing within 1 column of individual worksheet. Must be writen into VBA because number of activecells is uncertain when design. How to make Excel function work by using the variable "C"? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use VBA variable in COUNTIF function?
You are welcome. Glad to be of help.
-- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Thank you so much! This second one works great! .Value or .FormulaR1C1 is not important. It's "&( )&" makes it work. Thousands of thanks again! {snip} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIf Function Question | Excel Discussion (Misc queries) | |||
CountIF Function On Linked Spreadsheet | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
Embed a countif function in subtotal function? | Excel Worksheet Functions | |||
change function variable prompts?? | Excel Worksheet Functions |