Return specific cell in that row using row number

[SandManTFC]

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

[SamT]

Option Explicit

Private Sub Workbook_Open()
   GetExpires 'Don't overload an Event Sub
End Sub

Private Sub GetExpires()
   'Your code should all be in this Sub
End Sub

I don't know why you are checking very cell in A6 to D313, when all you need is column C

[SandManTFC]

OK thanks Sam, I'll clean up the code and use the range as Column C only, still not sure how access Coloumn A for the name.

[Paul_Hossler]

Some cleanup and variable renaming

I just used plug numbers since I didn't want to type ALL that data in from a picture. You can attach a small sample workbook to make it easier

Option Explicit


Sub TrainingStatus()
    Dim dataRange As Range, dataRow As Range
    Dim sMsg As String, rowName As String
    Dim rowDate As Date
    Dim ws As Worksheet
    Dim bSomeOutOfDate As Boolean

    bSomeOutOfDate = False


    sMsg = "Attention! The following training expires within 7 days, or have already expired: " & Chr(10) & Chr(10)
    
    On Error GoTo exit_sub


    For Each ws In ThisWorkbook.Worksheets
        sMsg = sMsg & ws.Name & ":"
        
        Set dataRange = ws.Range("A6:D313")
    
        
        For Each dataRow In dataRange.Rows
            rowDate = dataRow.Cells(1, 3).Value
            
            If CLng(rowDate) = 0 Then GoTo Next_dataRow
            
            If rowDate < Date + 8 Then
                bSomeOutOfDate = True
                rowName = dataRow.Cells(1, 1).Value
                If Len(rowName) = 0 Then rowName = dataRow.Cells(1, 1).End(xlUp).Value
                
                sMsg = sMsg & Chr(10) & "Row -  " & dataRow.Row & " - " & rowName & " -- " & rowDate
            End If
Next_dataRow:
        Next dataRow
    Next ws


    If Not bSomeOutOfDate Then sMsg = Chr(10) & "All training is up to date"
    
    MsgBox sMsg & Chr(10) & Chr(10), 48, "Expiring Training Dates!"


exit_sub:


End Sub