Message Pop Up Box Query

[CarlStephens]

Hello All,

In the below code highlighted bold, there is in column AQ a possibility that there could be a "Yes" in the cell. Is there a code to add to the below, that will say in addition to the " new employee records found" (in red below), that the code will also count the number of "Yes" cells and pop up in the same message box as "new employee records found"? Thank you.

With wsData.Rows(1)
.AutoFilter Field:=42, Criteria1:="Not On Joiners List"
.AutoFilter Field:=43, Criteria1:="<90"
If wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
wsData.Range("AR2:AU" & lr).SpecialCells(xlCellTypeVisible).Copy
wsDest.Range("AC" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
wsDest.Select
MsgBox wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count - 1 & " new employee records were found."
Else
MsgBox "No new employee postings found."
End If

 

[nemmi69]

Does this example help?

 Range("C3").Value = WorksheetFunction.CountIf(Range("AQ:AQ"), "yes")

 

[CarlStephens]

Thank you for your reply. It kind of helps.

The below part of the code count in red counts how many rows were copied and pasted in the wsDest sheet, and after a message box appears (after pasting) saying how many rows were pasted across. What I need in the same message box, is to say how many rows have a "Yes" in them. Does that make sense, as I am not very good at explaining myself? Thank you again for your time.

With wsData.Rows(1)
.AutoFilter Field:=42, Criteria1:="Not On Joiners List"
.AutoFilter Field:=43, Criteria1:="<90"
If wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
wsData.Range("AR2:AU" & lr).SpecialCells(xlCellTypeVisible).Copy
wsDest.Range("AC" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
wsDest.Select
MsgBox wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count - 1 & " new employee records were found."

 

[Joe4]

nemmi69's reply shows you how to to that calculation. So you can just add it to your current MsgBox, i.e.

MsgBox wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count - 1 & " new employee records were found." & vbCrLf & _
    WorksheetFunction.CountIf(wsData.Range("AQ:AQ"), "yes") & " yes items"

 

[CarlStephens]

Thank you so much, that worked. I am a fairly newbie to VBA so still trying to pick it up. Thank you once again.

 

[Joe4]

You are welcome.

Since you are fairly new to VBA, I will let you in on a few key details of my last post.
- vbCrLf is how you do a carriage return/line feed in VBA. We wanted our message box to go to a second line, so we used that.
- Using space and underscore (" _") at the end of a line of VBA code is telling VBA that this particular command wraps and goes to another line. We could not use that, and just keep typing across, but sometimes that can make it had to read. So that is often used for readability sake. It is how you split a single command over multiple lines.

 

[CarlStephens]

That makes total sense now that you have put that, thank you. So, if I was to put a third line (and any subsequent lines), I would need to add & vbCrLf & _ at the end of each line? Something like the below?

MsgBox wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count - 1 & " new employee record found." & vbCrLf & _
WorksheetFunction.CountIf(wsData.Range("AX:AX"), "yes") & " yes items." & vbCrLf & _
MsgBox "XYZ."

 

[Joe4]

That makes total sense now that you have put that, thank you. So, if I was to put a third line (and any subsequent lines), I would need to add & vbCrLf & _ at the end of each line? Something like the below?

MsgBox wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count - 1 & " new employee record found." & vbCrLf & _
WorksheetFunction.CountIf(wsData.Range("AX:AX"), "yes") & " yes items." & vbCrLf & _
MsgBox "XYZ."

Not quite. Remember, the space/underscore means a continuation of the original command. There, you do NOT use the "MsgBox" command again (as it is just a continuation of the original), i.e.

MsgBox wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count - 1 & " new employee record found." & vbCrLf & _
    WorksheetFunction.CountIf(wsData.Range("AX:AX"), "yes") & " yes items." & vbCrLf & _
    "XYZ."

Note that you do NOT need to do the continuation for the third line. Like I said, it is just for code readability. You could also do this and have the exact same results:

MsgBox wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count - 1 & " new employee record found." & vbCrLf & _
    WorksheetFunction.CountIf(wsData.Range("AX:AX"), "yes") & " yes items." & vbCrLf & "XYZ."

 

[CarlStephens]

Of course, and thank you for clarifying that for me. If I wanted a space/row between each of the three lines, what would I need to add to the code?

 

[Joe4]

Of course, and thank you for clarifying that for me. If I wanted a space/row between each of the three lines, what would I need to add to the code?

Simply double up on the vbCrLf command (it makes sense logically, you are doing 2 carriage return line feeds, just like you would on any word processor or type-writer), i.e.

... & vbCrLf & vbCrLf & ...