Selected Answer
Hi Maklil,
That's an interesting way of using the MATCH() function. Very efficient.
Here is the code that excludes all data except those of May 5.
Private Sub Workbook_Open()
' 234
Dim MyDate As Date
Dim Msg As String
Dim Stock As Long
Dim Ws As Worksheet
Dim x As Variant ' MATCH row
Dim Rl As Long ' last used row
Dim R As Long ' loop counter: rows
MyDate = Date - 2
Set Ws = Sheets("stock")
' you have to qualify the "Rows.Count": I added "Ws."
Rl = Ws.Range("b" & Ws.Rows.Count).End(xlUp).Row
With Sheets("entry")
' you have to qualify the "Rows.Count": I added "."
For R = 2 To .Range("a" & .Rows.Count).End(xlUp).Row
If .Cells(R, "A").Value = MyDate Then
x = .Evaluate("match(b" & R & "&c" & R & "&d" & R & ",stock!b1:b" & _
Rl & "&stock!c1:c" & Rl & "&stock!d1:d" & Rl & ",0)")
If IsNumeric(x) Then
Stock = Val(Ws.Cells(x, "E").Value)
Msg = Msg & vbLf & Join(.Evaluate("b" & R & ":e" & R & "&"""""), _
vbTab) & vbTab & Stock & vbTab & _
Format(Stock + Val(.Cells(R, 5).Value), "( 0 )")
End If
End If
Next
If Len(Msg) Then
Msg = Join(Array(Join(Array(Join(.[b1:d1&""], vbTab), vbTab & _
"Sold" & vbTab & "Bal" & vbTab & "Prev"), ""), Msg), vbLf)
Else
Msg = "No result to display for today's data."
End If
MsgBox Msg, vbInformation, "Data of" & Format(MyDate, " mmmm d, yyyy")
End With
End Sub
Please look for the line MyDate = Date - 2 at the top of the code. Date specifies today's (the current) date. Since today is May 7, Date -2 specifies the date of May 5. "Date -3" extracts the data from May 4 etc. I used this trick to be able to use the data in your sample worksheet. And I left it there because this modification will be useful for you to know about. When you want the real TODAY()'s date please remove the "- 2".