Holding Multiple Strings And Releasing Into Msgbox?

[Lewzerrrr]

Hi all,

So I'm trying to set up a reminder that checks if the week number is the same as current week number, if so then display a msgbox containing the information but unsure of the method to store the information into a variable.

My code that I've started below is just a small scale but is along the lines of what I want however it doesn't contain multiple strings, just the last one in the loop.

Sub Reminder()

Dim wsSheet1 As Worksheet
Dim LR As Long, I As Long, X As Long


' Should I be using Arrays for this?
Dim AName() As Variant
Dim SNo() As Variant
Dim SName() As Variant
Dim MsgBody As String


Set wsSheet1 = Sheets(1)


LR = wsSheet1.Cells(Rows.Count, "A").End(xlUp).Row


    For I = 2 To LR
    
        With wsSheet1
            If .Cells(I, 1) = Evaluate("=weeknum(today()-238)") Then
                AName = .Cells(I, 2)
                SNo = .Cells(I, 3)
                SName = .Cells(I, 4)
            Else
            
            End If
        End With
    
    Next I
    
'How to use Arrays to contain multiple strings?
MsgBody = vbNewLine & vbNewLine & " - " & AName(X) & ": " & SNo(X) & " " & SName(X)


MsgBox "The following visit packs are needed this week:" & MsgBody


End Sub

Excel and what I want to display in the end..


[TABLE="width: 319"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]WeekNo[/TD]
[TD]Name[/TD]
[TD]ID Number[/TD]
[TD]ID Name[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD]Name1[/TD]
[TD]ID0001[/TD]
[TD]ID-Name1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD]Name2[/TD]
[TD]ID0002[/TD]
[TD]ID-Name2[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]13[/TD]
[TD]Name3[/TD]
[TD]ID0003[/TD]
[TD]ID-Name3[/TD]
[/TR]
</tbody>[/TABLE]

Msgbox

Just a gentle reminder, the following are this week:

- Name1: ID0001 ID-Name1
- Name2: ID0002 ID-Name2

 

[Lewzerrrr]

Solved.

Sub Reminder2()

Dim ws As Worksheet
Dim r As Range, Rng As Range
Dim MsgBoxString As String


Set ws = Sheets(1)
Set Rng = ws.Range("A2:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)


    For Each r In Rng
        If r = Evaluate("=weeknum(today()-238)") Then
            MsgBoxString = MsgBoxString & r.Value & ": " & r.Offset(, 1).Value & " " & r.Offset(, 2).Value & " " & r.Offset(, 3) & vbNewLine
        Else
        
        End If
    Next r
    
    MsgBox "The following visit packs are required this week:" & vbNewLine & vbNewLine & MsgBoxString
    
Set ws = Nothing
Set Rng = Nothing


End Sub