Show cell value in a Msgbox based on IF statement on another column - Excel VBA

1
votes

I want to create a Msgbox alert to tell me when employees' contract are coming to an end.

Those contract which are between 4 - 1 month(s) of expiry will have their cells filled in Red:

enter image description here

This is my code thus far:

Private Sub workbook_open()

Dim rngData As Range
Dim rngCell As Range
Dim counter As Long: counter = 0
Set rngData = Range("E4:E" & Cells(Rows.Count, Range("E3").Column).End(xlUp).Row)


For Each rngCell In rngData
    If rngCell.Value = "4" Or rngCell.Value = "3" Or rngCell.Value = "2" Or rngCell.Value = "1" And rngCell.Value <> "" Then

        counter = counter + 1
    End If
Next rngCell
MsgBox counter & " employees are reaching their contract expiration date!"

Range("A4:E13").Sort _
Key1:=Range("E4"), Order1:=xlAscending


End Sub

However, how do I include a line of code to tell me the EE No. of the employees whose contract are expiring, to be shown in the Msgbox as well?

2
excelvbaexcel-2010
Store the EE numbers in an array as you loop. - BigBen
@BigBen how can the user do that? - user11850057
If rngCell.Value = "4" Or rngCell.Value = "3"... Is there a formula in the Months Left column? - Siddharth Rout
If there is a formula then there is a better way to handle it than looping through each record... - Siddharth Rout
ok that's great :) - Siddharth Rout

2 Answers

0
votes

You can also just build a string list to display at the end of the loop.

Private Sub workbook_open()
    Dim rngData As Range
    Dim rngCell As Range
    Dim expired As String
    Dim counter As Long: counter = 0
    Set rngData = Range("E4:E" & Cells(Rows.Count, Range("E3").Column).End(xlUp).Row)
    For Each rngCell In rngData
        If rngCell.Value = "4" Or rngCell.Value = "3" Or _
           rngCell.Value = "2" Or rngCell.Value = "1" And rngCell.Value <> "" Then
            counter = counter + 1
            '--- add to the list of expired EE Numbers
            expired = expired & rngCell.Offset(0, -4).Value & ","
        End If
    Next rngCell
    expired = Left$(expired, Len(expired) - 1)   'delete the trailing comma
    MsgBox counter & " employees are reaching their contract expiration date! (" & _
           expired & ")"
    Range("A4:E13").Sort Key1:=Range("E4"), Order1:=xlAscending
End Sub
0
votes

Build the string you want to display as you find them

Dim str As String
For Each rngCell In rngData
    If rngCell.Value <> "" And rngCell.Value <= 4 Then
       str = str & vbCr & rngCell.Offset(0, -4).Value
       counter = counter + 1
    End If
Next rngCell
MsgBox counter & " employees are reaching their contract expiration date!" & str