-1

I have a stock management table in excel sheet. Demo here: Demo photo of this table

I want to create a code in VBA that comes In stock column and will test the whole cells. If any cell.Value < 2 and >0 then go to 4 cells backward take the value of this cell and show a message box "backward cell.value and stock not available".

I tired to make this please help me.

Saiful Islam
  • 17
  • 2

1 Answers1

3

There are a few ways to select table cells, here is one'

Option Explicit

Sub CheckStock()
    Dim r As Long, n As Long, i As Integer, p As Integer
    Dim s As String

    With Sheet1.ListObjects("Table1")
        i = .ListColumns("In stock").Index
        p = .ListColumns("Product Name").Index

        For r = 1 To .DataBodyRange.Rows.Count
            n = .DataBodyRange(r, i)
            If n > 0 And n < 2 Then
                s = s & vbCrLf & .DataBodyRange(r, p)
            End If
        Next
    End With
    If Len(s) > 0 then 
        MsgBox "Products not available :" & s, vbExclamation
    End If
End Sub
CDP1802
  • 13,871
  • 2
  • 7
  • 17
  • Thank you very much. But here was a problem Msgbox is outside of the function. So, Msgbox is all time enable to work. But I want to enable Msgbox when stock is under 2. Otherwise, Msgbox is disabled. – Saiful Islam Sep 25 '21 at 14:07
  • @saiful If for example there are 3 items out of stock do you want the msgbox alert 3 times ? – CDP1802 Sep 25 '21 at 14:38