I'm trying to display the names of the accounts that need to be billed during the current week. So far the code works except for the output; the Msg baox displays the empty rows as well therefore my message box is extremely big and un-usable. This is what my code looks like:
Can someone help me find a way to display the message box without the empty cells of the final array please?
Much appreciated in advance
Code:
Private Sub CommandButton1_Click()
Dim today As Date
Dim P(156) As Date, Account(156) As String, product(156) As String, opportunity(156) As String, final(156, 3) As Variant
Dim i As Integer, x As Integer, y As Integer, txt As String
'Populate different arrays from Excel Sheet
For i = 0 To 155
Account(i) = Cells(i + 2, 21).Value
product(i) = Cells(i + 2, 29).Value
opportunity(i) = Cells(i + 2, 2).Value
'Populate closed date Array
P(i) = Cells(i + 2, 16).Value
Next
'Puts today's date in cell AR1
Range("AR1").Value = Date
today = Range("AR1").Value
'Compare closed date to today and see if its less than 7 days away
'If so, put in final array
For x = 0 To 155
For y = 0 To 2
If DateDiff("m", today, P(x)) = 0 And DateDiff("d", today, P(x)) <= 7 Then
final(x, 1) = Account(x)
final(x, 2) = product(x)
final(x, 3) = opportunity(x)
End If
'Convert the array to txt
txt = txt & final(x, y) & vbCrLf
Next y
Next
'Message box to display accounts needed to be billed
MsgBox ("Accounts needed to be billed " & txt)
End Sub
Can someone help me find a way to display the message box without the empty cells of the final array please?
Much appreciated in advance