show data ranges dynamically on the msgbox based on condition

[abdelfattah]

hello
i need the code it shows the data from a2:e of course dynamic on msgbox based on condition in column e is date if e=date+10 then show the data relating this date
i try with this code but not dynamic it's specific range and i stuck how i write the condition more over this code shows all of data

Sub mesage()

    Dim xRg As Range

    Dim xTxt As String

    Dim xCell As Range

    Dim xStr As String

    Dim xRow As Long

    Dim xCol As Long

    On Error Resume Next

    If ActiveWindow.RangeSelection.Count > 1 Then

      xTxt = ActiveWindow.RangeSelection.AddressLocal

    Else

      xTxt = ActiveSheet.UsedRange.AddressLocal

    End If

    Set xRg = Range("a2:e10")

    If xRg Is Nothing Then Exit Sub

    On Error Resume Next

    For xRow = 1 To xRg.Rows.Count

        For xCol = 1 To xRg.Columns.Count

            xStr = xStr & xRg.Cells(xRow, xCol).Value & vbTab

        Next

        xStr = xStr & vbCrLf
    Next

    MsgBox xStr, vbInformation, "xrg"

End Sub

 

[DanteAmor]

How about:

Sub TestMsg()
  Dim i As Long, j As Long, xStr As String
  For i = 2 To Range("E" & Rows.Count).End(3).Row
    If Range("E" & i).Value = Date + 10 Then
      For j = 1 To 5
        xStr = xStr & Cells(i, j) & vbTab
      Next
      xStr = xStr & vbCr
    End If
  Next
  MsgBox xStr, vbInformation
End Sub

 

[GWteB]

Sub mesage()

    Dim xRg     As Range
    Dim xCell   As Range
    Dim xStr    As String
    Dim i       As Long

    Set xRg = Range("A2:E10")
        
        For Each xCell In xRg.Offset(0, 4).Resize(xRg.Rows.Count, 1)
        If xCell.Value = Date + 10 Then   '<<< in this line the condition is set related to Column "E:E"
            For i = -4 To 0 Step 1
                xStr = xStr & xCell.Offset(0, i).Value & vbTab
            Next i
        End If
    Next
    MsgBox xStr, vbInformation, "xRg"
    Set xRg = Nothing
End Sub

 

[abdelfattah]

How about:

Sub TestMsg()
  Dim i As Long, j As Long, xStr As String
  For i = 2 To Range("E" & Rows.Count).End(3).Row
    If Range("E" & i).Value = Date + 10 Then
      For j = 1 To 5
        xStr = xStr & Cells(i, j) & vbTab
      Next
      xStr = xStr & vbCr
    End If
  Next
  MsgBox xStr, vbInformation
End Sub

well done i appreciate this help the code is excellent i would this explain this number End(3) what to indicate sometimes i found maybe 4 or 2 ..etc more over in your code i try to show the header topics from a1 : e i do this For i = 1 To Range("E" & Rows.Count).End(3).Row but it doesn't success how i can do it

 

[DanteAmor]

End(3) means End(xlup) . To find the last row with data.
Do you want to show the headers in the msgbox?

 

[abdelfattah]

Sub mesage()

    Dim xRg     As Range
    Dim xCell   As Range
    Dim xStr    As String
    Dim i       As Long

    Set xRg = Range("A2:E10")
       
        For Each xCell In xRg.Offset(0, 4).Resize(xRg.Rows.Count, 1)
        If xCell.Value = Date + 10 Then   '<<< in this line the condition is set related to Column "E:E"
            For i = -4 To 0 Step 1
                xStr = xStr & xCell.Offset(0, i).Value & vbTab
            Next i
        End If
    Next
    MsgBox xStr, vbInformation, "xRg"
    Set xRg = Nothing
End Sub

your code not completely work Row data formats unordered, first row, overlapped the column a in second data row show with the first data row for more to understand the image 1 your result the image 2 this is what i would with simple changing just making code begins from header topics from a1:e

 

[abdelfattah]

End(3) means End(xlup) . To find the last row with data.
Do you want to show the headers in the msgbox?

yes i do , and why specify the last row from 3 the data begins 2?

 

[DanteAmor]

For i = 2 To Range("E" & Rows.Count).End(3).Row
The 2, means that it will start the cycle in row 2.
Range ("E" & Rows.Count), means it will go to the last cell of the column E sheet.
.end(3), means to scroll up to find a cell with data.
.row, means it returns the row number.
The 3 does not mean a row number, it is a key to indicate that it goes up.
So the cycle goes from row 2 to the last row with data.

Perform this exercise, on a new sheet in cell e10 type a letter and press enter.
Now press the End key and then press the down key, the cursor will be in cell E1048576
Now run this macro:

Sub test()
  Range("E" & Rows.Count).End(3).Select
End Sub

The cursor is now in cell A10
And if you run this macro:

Sub test2()
  MsgBox Range("E" & Rows.Count).End(3).Row
End Sub

The result is 10, because the row with data from bottom to up is number 10.
_____________________________________________________________________________________________

You must put the headings with code, for example:

Sub TestMsg()
  Dim i As Long, j As Long, xStr As String
  
  For j = 1 To 5
    xStr = xStr & Cells(1, j) & vbTab
  Next
  xStr = xStr & vbCr & vbCr
  
  For i = 2 To Range("E" & Rows.Count).End(3).Row
    If Range("E" & i).Value = Date + 10 Then
      For j = 1 To 5
        xStr = xStr & Cells(i, j) & vbTab
      Next
      xStr = xStr & vbCr
    End If
  Next
  MsgBox xStr, vbInformation
End Sub

 

[abdelfattah]

thanks so much your code is awesome and i would thank about your explanation

 

[GWteB]

your code not completely work Row data formats unordered

Sorry, an essential line of code was missing.

    Next i
    xStr = xStr & vbCrLf   ' <<< the closing carriage return line feed was missing.
End If

Fortunately, Dante has helped you a long way, so you weren't empty-handed.