Hello,
I am having some difficulty with this function...can someone assist?
I am trying to count the number of non-empty (text data) cells in a
given array of cells within a worksheet using vba. The problem is that
Worksheetfunction.counta does not return the right number of cells
containing text info when there is data in column 42 or 46 or 50. But
seems to work okay upto column 38. The code I use is provided below.
When I execute the code in stepwise mode, it shows the data for columns
18,22,26,30,34 and 36. But when the cursor is placed over the
cells(Row,38, 42, 46, or 50)...it does not show to contain data even
though data is present. Is there error in the code I use?

With Sheets("Data")
For Row = 4 To lastr
c = WorksheetFunction.CountA(.Cells(Row, 18), .Cells(Row, 22), _
.Cells(Row, 26), .Cells(Row, 30), .Cells(Row, 34), .Cells(Row, 38) _
.Cells(Row, 42), .Cells(Row, 46), .Cells(Row, 50)) ' # items.

etc
c is not correct for values greater than 5
Thanks

Ally

Ally,

Try

c = WorksheetFunction.CountA(Range("R" & Row & ",V" & Row & ",Z" & Row &
_
",AD" & Row & ",AH" & Row & ",AJ" &
Row & _
",AL" & Row & ",AN" & Row & ",AT" &
Row & _
",AX" & Row)) ' # items.


--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
oups.com...
Hello,
I am having some difficulty with this function...can someone assist?
I am trying to count the number of non-empty (text data) cells in a
given array of cells within a worksheet using vba. The problem is that
Worksheetfunction.counta does not return the right number of cells
containing text info when there is data in column 42 or 46 or 50. But
seems to work okay upto column 38. The code I use is provided below.
When I execute the code in stepwise mode, it shows the data for columns
18,22,26,30,34 and 36. But when the cursor is placed over the
cells(Row,38, 42, 46, or 50)...it does not show to contain data even
though data is present. Is there error in the code I use?

With Sheets("Data")
For Row = 4 To lastr
c = WorksheetFunction.CountA(.Cells(Row, 18), .Cells(Row, 22), _
.Cells(Row, 26), .Cells(Row, 30), .Cells(Row, 34), .Cells(Row, 38) _
.Cells(Row, 42), .Cells(Row, 46), .Cells(Row, 50)) ' # items.

etc
c is not correct for values greater than 5
Thanks

Ally