Need Excel VBA Help

[elyanivson]

Hi everyone,
I'm trying to get a cell function solution into a variable.I'll explain.
I used the "CountIF" function to get the numbers of cells that include the word "FALSE".the solution will show in cell A1.
Now, I want to create a VBA function that use the solution i got on cell A1 and I'm really desperate of trying get it.

When I use the CountIf function and recording it by Macro this is what I get in VBA screen:

ActiveCell.FormulaR1C1 = "=COUNTIF(C8,FALSE)"

I'll be glad to get any help to solve this issue.
In addition, could someone help me figure what is the C8 that written in the first part of the "countif" function?

Thank you very much in advance!

[Bob Phillips]

ActiveCell.Formula = "=COUNTIF(C8,FALSE)"

[JWest]

The 'C8' you are seeing is your range. So the current formula reads "If Cell C8 is False, then count it". Therefore, if you have a larger range you need to specify it. For instance if my range was all of column C I could use:

= "COUNTIF(C:C, FALSE)"

If I only want certain cells I could be even more specific:

= "COUNTIF(C8:C20, FALSE)"

[AnAnalyst]

Chaps,

The macro recorder has a tendency to use "R1C1" cell references, and that is what is happening here:

ActiveCell.FormulaR1C1 = "=COUNTIF(C8,FALSE)"

So "C8" actually means "Column 8".

I think the o/p's question is about creating a variable in vba that uses the value stored in cell A1, which is the count of "FALSE" values held in column 8 of the spreadsheet.

elyanivson, as part of the vba you can define the variable:

Dim lngMyVar as long

... then assign the value in cell A1 to the variable:

lngMyVar = Sheets("Sheet1").Cells(1,1).Value

(Replace "Sheet1" with your sheet's name)

lngMyVar can then be used in whatever other code you are constructing.

[Bob Phillips]

The macro recorder has a tendency to use "R1C1" cell references, and that is what is happening here:

ActiveCell.FormulaR1C1 = "=COUNTIF(C8,FALSE)"

It does that is true, but C8 is not R1C1 style, the macro recorder would actually create this line

ActiveCell.FormulaR1C1 = "=COUNTIF(R[7]C[-8],FALSE)"


or something similar, depending upon which cell is active.

So that was not recorded.

[AnAnalyst]

It does that is true, but C8 is not R1C1 style, the macro recorder would actually create this line

ActiveCell.FormulaR1C1 = "=COUNTIF(R[7]C[-8],FALSE)"


or something similar, depending upon which cell is active.

So that was not recorded.

Sorry, but I have to disagree - if (in Excel 2010, anyway) you record a macro to count the number of FALSE's in a column and use an absolute reference to the column you get exactly what the o/p stated:

ActiveCell.FormulaR1C1 = "=COUNTIF(C8,FALSE)"

[Aussiebear]

Sorry, but I have to disagree - if (in Excel 2010, anyway) you record a macro to count the number of FALSE's in a column and use an absolute reference to the column you get exactly what the o/p stated:

ActiveCell.FormulaR1C1 = "=COUNTIF(C8,FALSE)"

Why isn't "c8" the cell location? This seems to defy logic if I follow your suggestion. Can you also tell me just what is "False"?

If I wanted a column I'd write C:C.

[AnAnalyst]

Why isn't "c8" the cell location? This seems to defy logic if I follow your suggestion. Can you also tell me just what is "False"?

If I wanted a column I'd write C:C.

Hi Aussiebear,

In the standard formula notation used by most in Excel you would indeed refer to a column (absolutely) as "$H:$H" (in this case the eighth column) - but the macro recorder code generated by the o/p is in R1C1 format. In R1C1 notation you would refer absolutely to a whole column with simply "C8" which means the eighth column.

The o/p has (I assume) formulae in column 8 ($H:$H) that produce "FALSE" as a result in some instances. The "countif" function is being used in cell A1 to count any result that is "FALSE" in column 8 ($H:$H).