Status Bar Count from a Macro

[Bruce1951]

I have created a macro in Excel 2019 that presents the answer (Count) in the status bar at the lower left corner of the spreadsheet, (example 24 of 145 records found). The answer is correct. I would like to take that answer/count and place it (just the number "24") in a spreadsheet cell rather than writing it down on a piece of paper and then typing it into the cell. How do I do this?

Thanks,

Bruce

[#2]

Name a cell as "ResultCell" then lets say the count value created in you macro routine is called FoundCount then add a line of code at the end saying Range("ResultCell").value = FoundCount

[#3]

@Wyn Hopkins 

Hi Wyn,

Thanks for the info. Unfortunately I am not knowledgeable enough to do what you are suggesting.

[#4]

@Bruce1951  I'm a little confused on your question.  Are you saying your macro is creating the text in the status bar or that your macro is doing something like applying a filter which results in Excel presenting that text in the status bar and you would rather have that text in a cell?  I'm thinking you mean the later and it would be easier to just do a count of the number of rows 'found' on the sheet.  Maybe include a little more details and the sheet itself.

[#5]

@mtarler

The latter is correct but I have several "Counts" of specific numbers in a column and just trying to create a quicker/easier method for transferring the Status Line answers of the "Count" by having it show up in cells rather than on the status line one at a time. I have Macros ctrl+(a-j) and want the Count to show up in cells so I can then use a macro to transfer the answers to a MSWord document table.  I have attached the macro below. Thanks

[#6]

@Bruce1951  I think what you are looking for is how to use a function like "countif" in VBA and those are all included in "WorksheetFunction" so WorksheetFunction.Countif(…) and then you can assign that to whatever cell you like.

So your first macro could look like this:

Sub CriteriaScore1()
'
' CriteriaScore1 Macro
'
' Keyboard Shortcut: Ctrl+b
'
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$O$146").AutoFilter Field:=3, Criteria1:="1"
    ActiveSheet.Range("$P$1")=worksheetfunction.countif(ActiveSheet.Range("$C$1:$C$146"), "1")
End Sub

[#7]

@mtarler 

Very cool. It worked. And so easy now that you showed me what to write into the macro text.

Thanks so much,

Bruce

[#8 · Accepted solution]

Your very welcome, glad it helped. Please mark as answered to close the thread.

[#9]

@mtarler 

Answered.