CODEWITHSUNDEEP
vbaexcelcountvlookup
Peter Cheverton
Peter Cheverton

Reputation: 1

Counting cells using Excel VBA which are a result of a vlookup statement

I have a spreadsheet on one sheet with the values in column C being generated using the results of a vlookup statement, from a value which I enter in column A.

I need to be able to count the number of cells in column C up to a maximum of 51 rows (from row 1 to row 51) which have a value in them, not including errors, after I have entered all my values in column A.

Oh - by the way, each time I do the count there will be a different number of rows used.

I've tried using:

ccc = Range("C:C").Cells.SpecialCells(xlCellTypeConstants).Count

but this only counts the first line which is my header row.

Sorry if there is already an answer out there, but I've been looking for quite a while and can't find anything.

Thanks.

Upvotes: 0

Views: 1072

Answers (3)

Valli
Valli

Reputation: 1450

You are looking to count cells that have no errors. Replace your vlookup by the below formula. So all errors will be replaced by "NOT FOUND" Text

=IFERROR(VLOOKUP(C1,A1:B3,2,FALSE), "NOT FOUND")

Then add this to find the number of cells that are non blank and non erroneous

=COUNTA(D:D) - COUNTIF(D:D,"NOT FOUND")

Assumptions:-

A:B Source Range

C Lookup Column

D the vlookup function is in this coulmn

For VBA

cnt = Application.WorksheetFunction.CountA(D:D) - Application.WorksheetFunction.Countif(D:D, "NOT FOUND")

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21619

The worksheet function CountA counts only cells with values:

=CountA(C1:C51)

We can call any worksheet function from VBA with the WorksheetFunction function:

dim c as integer
c = WorksheetFunction.CountA([C1:C51])

CountIf can be used to skip errors:

Skip errors with:  `=COUNTIF(D5:D9,">0")`

Upvotes: 1

Fernando J. Rivera
Fernando J. Rivera

Reputation: 749

You can easily do this without VBA, but you could try:

sub testy()
dim myRange as range
dim numRows as long

Set myRange = Range("C:C")
numRows = Application.WorksheetFunction.CountA(myRange) - _
myRange.SpecialCells(xlCellTypeFormulas, xlErrors).Count

end sub

Your code is not working because xlCellTypeConstants is specifically telling it to count only constant values, ignoring formulas calculated values.

Upvotes: 2

Related Questions