SandManTFC
05-14-2020, 09:19 AM
Hi all,
I'm struggling to return what i need from VBA, I'm trying to return the name of the person in column A with the expired date being in column C. I have managed to retrun the row number and the expired date, but no other cell values in that row, screenshot attached.
Private Sub Workbook_Open()
Dim cl As Range
Dim rng As Range
Dim str As String
Dim sht_str As String
Dim sht As Worksheet
sht_str = "Attention! The following training expires within 7 days, or have already expired: " & Chr(10) & Chr(10)
sht_strs = "Test"
For Each sht In Me.Worksheets
sht_str = sht_str & sht.Name & ":"
str = ""
Set rng = sht.Range("A6:D313")
On Error GoTo exit_sub
For Each rw In rng
If rw.Value = "" Then GoTo Next_rw
If rw.Value < Date + 8 Then str = str & Chr(10) & "Row - " & rw.Row & " - " & rw.Value
Next_rw:
Next rw
If str = "" Then str = Chr(10) & "All training is up to date"
sht_str = sht_str & str & Chr(10) & Chr(10)
Next sht
MsgBox sht_str, 48, "Expiring Training Dates!"
exit_sub:
End Sub
I'm struggling to return what i need from VBA, I'm trying to return the name of the person in column A with the expired date being in column C. I have managed to retrun the row number and the expired date, but no other cell values in that row, screenshot attached.
Private Sub Workbook_Open()
Dim cl As Range
Dim rng As Range
Dim str As String
Dim sht_str As String
Dim sht As Worksheet
sht_str = "Attention! The following training expires within 7 days, or have already expired: " & Chr(10) & Chr(10)
sht_strs = "Test"
For Each sht In Me.Worksheets
sht_str = sht_str & sht.Name & ":"
str = ""
Set rng = sht.Range("A6:D313")
On Error GoTo exit_sub
For Each rw In rng
If rw.Value = "" Then GoTo Next_rw
If rw.Value < Date + 8 Then str = str & Chr(10) & "Row - " & rw.Row & " - " & rw.Value
Next_rw:
Next rw
If str = "" Then str = Chr(10) & "All training is up to date"
sht_str = sht_str & str & Chr(10) & Chr(10)
Next sht
MsgBox sht_str, 48, "Expiring Training Dates!"
exit_sub:
End Sub