Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

mod code show message box to show details of inventory

0

hi

I  have  this  code    it  works  when   open file  based on  sheets STOCK & ENTRY    

I  would  when  show   the message   show  just   based on  date  today      ,  the code   shows  all  data based on sheet entry and   it  repeat the  values which are  existed  stock , what  I  want  show  based on    date  (today)  in my PC

 it  should only  show  the  data  in 05/05/2021   and  I  want  add  another  column   it  summing  the  values  to show  the  previous stock   

I put some  comments  inside  the  file  may be  help 

Answer
Discuss

Answers

0
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".

Discuss

Discussion

Hi variatus,
that's  great !   thanks  for  provide  me  the  solution 
best regards,
Maklil
MAKLIL (rep: 34) May 7, '21 at 6:29 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login