05-31-2020 01:36 PM
05-31-2020 01:36 PM
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?
05-31-2020 10:11 PM
06-01-2020 08:33 AM
@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.
06-01-2020 08:58 AM
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
06-01-2020 09:22 AM
@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
06-01-2020 09:40 AMSolution