0

I have an excel file with an unknown end range (which changes every time) and I want to know the amount of rows that the specific word comes back in that data table.

It is just one column, and I have tried so many different methods but I still do not know the solution to it.

This is the VBA code:

Sub count_cookies()

lastrow = Worksheets("table").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("table").Cells(6, 2) = WorksheetFunction.CountIf(Range("data!A2:A" & lastrow), "* cookies *")
End Sub

So I want the amount counted from sheet "data" to sheet "table", but what am I doing wrong?

EDIT: I got a question, I have filtered data, but countif still counts all of the data, how can I count only the filtered data? IS there a different formula for this?

Gores Hamad
  • 3
  • 1
  • 3
  • You are basing your `lastrow` variable on the "table" worksheet, column A, but then using it as the last row for your "data" sheet? I think you need to base your `lastrow` variable on the "data" worksheet, rest of the code looks good. – tigeravatar Jan 11 '17 at 16:23

2 Answers2

1

It looks like you should be getting the last row from the data sheet since that's the sheet you want the count from. Also, you should specify the sheets when defining a Range. I prefer to use the "Cells" syntax.

Sub count_cookies()

  lastRow = Worksheets("data").Cells(Rows.Count, 1).End(xlUp).Row
  Worksheets("table").Cells(6, 2) = WorksheetFunction.CountIf(Range(Sheets("data").Cells(2, "a"), Sheets("data").Cells(lastRow, "a")), "* cookies *")

End Sub
Nathan Dudley
  • 510
  • 7
  • 17
  • I got a question, I have filtered data, but countif still counts all of the data, how can I count only the filtered data? – Gores Hamad Jan 12 '17 at 08:57
  • You would need to write your own function in VBA to handle this. It would essentially be a loop through the data with a counter. You could use the built-in InStr function to see if the the cell contains " cookies " and then check to see if the entire row is hidden or not. – Nathan Dudley Jan 12 '17 at 19:11
0

you could try this:

With Worksheets("table")
    With .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
        .Cells(6, 2).Value = WorksheetFunction.CountIf(Worksheets("data").Range(.Address), "* cookies *")
    End With
End With
user3598756
  • 28,893
  • 4
  • 18
  • 28
  • I got a question, I have filtered data, but countif still counts all of the data, how can I count only the filtered data? – Gores Hamad Jan 12 '17 at 08:56