Excel VBA – Complete Tutorial

Key Formulas in Your Inventory System:

1. Remaining Stock:

excel

CopyEdit

=C2-D2

(Stock In – Stock Out)

1. Total Value:

excel

CopyEdit

=E2*G2

(Remaining Stock × Selling Price)

1. Profit Calculation:

excel

CopyEdit

=G2-F2

(Selling Price – Purchase Price)

1. Sum, Min, Max, Average:
   • Total Stock In: =SUM(C2:C100)
   • Total Stock Out: =SUM(D2:D100)
   • Minimum Stock Available: =MIN(E2:E100)
   • Maximum Stock Available: =MAX(E2:E100)
   • Average Selling Price: =AVERAGE(G2:G100)

Sub Remainprice()

Sheets(“VBA”).Select

Range(“e2:e11”).Formula = “=c2-d2”

End Sub

Sub totalvalue()

Sheets(“VBA”).Select

Range(“h2:h11”).Formula = “=e2*g2”

End Sub

Sub profitloss()

Sheets(“VBA”).Select

Range(“i2:i11”).Formula = “=g2-f2”

End Sub

Sub AutoSumE()

    Dim lastRow As Long

    ‘ Column E me last filled row find karein

    lastRow = Cells(Rows.Count, 5).End(xlUp).Row

    ‘ Last row ke neeche sum insert karein

    Cells(lastRow + 1, 5).Formula = “=SUM(E1:E” & lastRow & “)”

End Sub

Sub totalvalues()

    Dim lastRows As Long

    ‘ Column E me last filled row find karein

    lastRows = Cells(Rows.Count, 8).End(xlUp).Row

    ‘ Last row ke neeche sum insert karein

    Cells(lastRows + 1, 8).Formula = “=SUM(H1:H” & lastRows & “)”

End Sub

VBA Code for Minimum Stock in Column “E”

Sub MinStockE()

    Dim minrow As Long

    ‘ Column E me last filled row find karein

    minrow = Cells(Rows.Count, 5).End(xlUp).Row

    ‘ Last row ke neeche MIN formula insert karein

    Cells(minrow + 1, 5).Formula = “=MIN(E2:E” & minrow & “)”

End Sub

VBA Code for Maximum Stock in Column “E”

Sub MaxStockE()

    Dim lastRow As Long

    ‘ Column E me last filled row find karein

    lastRow = Cells(Rows.Count, 5).End(xlUp).Row

    ‘ Last row ke neeche MAX formula insert karein

    Cells(lastRow + 1, 5).Formula = “=MAX(E2:E” & lastRow & “)”

End Sub

VBA Code for Average Stock in Column “E”

Sub AvgStockE()

    Dim lastRow As Long

    ‘ Column E me last filled row find karein

    lastRow = Cells(Rows.Count, 5).End(xlUp).Row

    ‘ Last row ke neeche AVERAGE formula insert karein

    Cells(lastRow + 1, 5).Formula = “=AVERAGE(E2:E” & lastRow & “)”

End Sub

Code Explanation:

1. lastRow = Cells(Rows.Count, 5).End(xlUp).Row
   • Column “E” (5th column) ka last filled row number find karega.
2. Cells(lastRow + 1, 5).Formula = “=AVERAGE(E2:E” & lastRow & “)”
   • “E2” se lekar last filled row tak average calculate karega.
   • Result last row ke neeche show karega.

Filter by product id

Sub FilterByProductID()

    Dim ws As Worksheet

    Dim productID As Variant

    ‘ Active Sheet ko set karna

    Set ws = ActiveSheet

    ‘ User se Product ID enter karwane ka prompt

    productID = InputBox(“Enter the Product ID:”, “Filter by Product ID”)

    ‘ Agar user ne value enter nahi ki, toh exit ho jaye

    If productID = “” Then Exit Sub

    ‘ AutoFilter lagana on Column A (Product ID)

    ws.Range(“A1”).AutoFilter Field:=1, Criteria1:=productID

    MsgBox “Filter applied for Product ID: ” & productID, vbInformation, “Filter Applied”

End Sub

COUNTIF (Stock Check) in VBA

Sub Count_Zero_Stock()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“Inventory”) ‘Sheet ka naam change karein

    Dim zeroStockCount As Integer

    zeroStockCount = Application.WorksheetFunction.CountIf(ws.Range(“E2:E100”), 10)

    MsgBox “Total Out of Stock Items: ” & zeroStockCount, vbInformation, “Stock Check”

End Sub

VBA Code for IF Condition (Low Stock Alert)

➡️ Ye VBA code Remaining Stock (Column E) ko check karega.
➡️ Agar stock 10 se kam hoga, to “Low Stock” likh dega.
➡️ Agar stock 10 ya zyada hoga, to “Available” likh dega.

Sub LowStockAlert()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“vba”) ‘ Apni sheet ka naam change karein

    Dim i As Integer

    Dim lastRow As Integer

    lastRow = ws.Cells(ws.Rows.Count, 5).End(xlUp).Row ‘ Column E (Remaining Stock) ka last row find karega

    For i = 2 To lastRow

        If ws.Cells(i, 5).Value < 10 Then

            ws.Cells(i, 9).Value = “Low Stock”  ‘ Column I me result show karega

        Else

            ws.Cells(i, 9).Value = “Available”

        End If

    Next i

End Sub

LEFT Function (Extract First 5 Characters)

Sub LeftProductName()

    Dim ws As Worksheet

    Set ws = Worksheets(“vba”) ‘ Sheet name “vba” set karna

    Dim lastRow As Integer

    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

    Dim i As Integer

    For i = 2 To lastRow

        ws.Cells(i, 12).Value = Left(ws.Cells(i, 2).Value, 3)

    Next i

End Sub

RIGHT Function (Extract Last 4 Characters)

Sub RightProductName()

    Dim ws As Worksheet

    Set ws = Worksheets(“vba”) 

    Dim lastRow As Integer

    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row 

    Dim i As Integer

    For i = 2 To lastRow

        ws.Cells(i, 10).Value = Right(ws.Cells(i, 2).Value, 4) 

    Next i

End Sub

MID Function (Extract Middle Characters)

Sub MidProductName()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“VBA”)

    Dim lastRow As Integer

    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

    Dim i As Integer

    For i = 2 To lastRow

        ws.Cells(i, 10).Value = Mid(ws.Cells(i, 2).Value, 3, 5) ‘ 3rd character se 5 characters extract karega

    Next i

End Sub

LEN Function (Length of Product Name)

Sub LengthProductName()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“VBA”)

    Dim lastRow As Integer

    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

    Dim i As Integer

    For i = 2 To lastRow

        ws.Cells(i, 10).Value = Len(ws.Cells(i, 2).Value) ‘ Column B ke text ki total length dega

    Next i

End Sub

TRIM Function (Remove Extra Spaces)

Sub TrimProductName()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“VBA”)

    Dim lastRow As Integer

    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

    Dim i As Integer

    For i = 2 To lastRow

        ws.Cells(i, 10).Value = Trim(ws.Cells(i, 2).Value) ‘ Extra spaces hata kar Column J me likhega

    Next i

End Sub

Join First name and last name = Full name

Sub JoinFullName()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“sheet1”) ‘ Apni sheet ka naam yahan dalain

    Dim lastRow As Integer

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ‘ Last row find karega (Column A)

    Dim i As Integer

    For i = 2 To lastRow ‘ Row 2 se last row tak loop chalega

        ws.Cells(i, 4).Value = ws.Cells(i, 1).Value & ” ” & ws.Cells(i, 2).Value ‘ A + ” ” + B ko D me likhega

    Next i

    MsgBox “Full Names updated successfully!”, vbInformation, “Done!”

End Sub

Upper case /Lower case

Sub ConvertToUpperCase()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“sheet1”) ‘ Apni sheet ka naam yahan dalain

    Dim lastRow As Integer

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ‘ Column A ka last row find karega

    Dim i As Integer

    For i = 2 To lastRow ‘ Row 2 se last row tak loop chalega

        ws.Cells(i, 1).Value = UCase(ws.Cells(i, 1).Value) ‘ A1 column ko uppercase karega

    Next i

    MsgBox “Column A converted to UPPERCASE successfully!”, vbInformation, “Done!”

End Sub

Column ko upper case me karna ho tu

Sub ConvertColumnBToUpperCase()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“VBA”) ‘ Apni sheet ka naam yahan dalain

    Dim lastRow As Integer

    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row ‘ Column B ka last row find karega

    Dim i As Integer

    For i = 2 To lastRow ‘ Row 2 se last row tak loop chalega

        ws.Cells(i, 2).Value = UCase(ws.Cells(i, 2).Value) ‘ B column ko uppercase karega

    Next i

    MsgBox “Column B converted to UPPERCASE successfully!”, vbInformation, “Done!”

End Sub

Column  ko Lower case me karna ho tu

Sub ConvertColumnBToUpperCase()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“sheet1”) ‘ Apni sheet ka naam yahan dalain

    Dim lastRow As Integer

    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row ‘ Column B ka last row find karega

    Dim i As Integer

    For i = 2 To lastRow ‘ Row 2 se last row tak loop chalega

        ws.Cells(i, 2).Value = LCase(ws.Cells(i, 2).Value) ‘ B column ko lower karega

    Next i

    MsgBox “Column B converted to Lower successfully!”, vbInformation, “Done!”

End Sub

 Proper case code

Sub ConvertColumnBToProperCase()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets(“sheet1”) ‘ Apni sheet ka naam yahan dalain

    Dim lastRow As Integer

    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row ‘ Column B ka last row find karega

    Dim i As Integer

    For i = 2 To lastRow ‘ Row 2 se last row tak loop chalega

        ws.Cells(i, 2).Value = Application.WorksheetFunction.Proper(ws.Cells(i, 2).Value) ‘ Proper Case karega

    Next i

    MsgBox “Column B converted to PROPER CASE successfully!”, vbInformation, “Done!”

End Sub

Vlookup ( fine product name by id )

 Sub FindProductName()

    Dim ws As Worksheet

    Dim searchID As Variant

    Dim productName As Variant

    ‘ Set worksheet

    Set ws = ThisWorkbook.Sheets(“VBA”)

    ‘ Get search ID from user

    searchID = InputBox(“Enter Product ID to search:”)

    ‘ Convert searchID to number if possible

    If IsNumeric(searchID) Then

        searchID = CLng(searchID) ‘ Convert to Long (Integer)

    End If

    ‘ Apply VLOOKUP for Product Name (Column 2)

    On Error Resume Next

    productName = Application.WorksheetFunction.VLookup(searchID, ws.Range(“A2:G100”), 2, False)

    On Error GoTo 0

    ‘ Show Result

    If IsError(productName) Or IsEmpty(productName) Then

        MsgBox “ID not found!”, vbExclamation

    Else

        MsgBox “Product Name for ID ” & searchID & ” is: ” & productName

    End If

End Sub

Multiple vlookup find product by id

Sub FindProductDetails()

    Dim ws As Worksheet

    Dim searchID As Variant

    Dim productName As Variant

    Dim stockIn As Variant

    ‘ Set worksheet

    Set ws = ThisWorkbook.Sheets(“VBA”)

    ‘ Get search ID from user

    searchID = InputBox(“Enter Product ID to search:”)

    ‘ Validate input (Empty Check)

    If searchID = “” Then

        MsgBox “No ID entered!”, vbExclamation

        Exit Sub

    End If

    ‘ Convert searchID to number if possible

    If IsNumeric(searchID) Then

        searchID = CLng(searchID) ‘ Convert to Long (Integer)

    Else

        MsgBox “Invalid ID! Please enter a number.”, vbCritical

        Exit Sub

    End If

    ‘ Apply VLOOKUP for Product Name (Column 2) and Stock In (Column 3)

    On Error Resume Next

    productName = Application.WorksheetFunction.VLookup(searchID, ws.Range(“A2:G100”), 2, False)

    stockIn = Application.WorksheetFunction.VLookup(searchID, ws.Range(“A2:G100”), 3, False)

    On Error GoTo 0

    ‘ Show Result

    If IsError(productName) Or IsError(stockIn) Or IsEmpty(productName) Or IsEmpty(stockIn) Then

        MsgBox “ID not found!”, vbExclamation

    Else

        MsgBox “Product Details for ID ” & searchID & “:” & vbNewLine & _

               “Product Name: ” & productName & vbNewLine & _

               “Stock In: ” & stockIn, vbInformation

    End If

End Sub

…………………………………………………………………………………………………..

Hlookup ( Find product name and remaining by ids)

Sub FindProductDetails_HLookup()

    Dim ws As Worksheet

    Dim searchID As Variant

    Dim productName As Variant

    Dim remainingStock As Variant

    ‘ Set worksheet

    Set ws = ThisWorkbook.Sheets(“hlookup”)

    ‘ Get search ID from user

    searchID = InputBox(“Enter Product ID to search:”)

    ‘ Validate input (Empty Check)

    If searchID = “” Then

        MsgBox “No ID entered!”, vbExclamation

        Exit Sub

    End If

    ‘ Convert searchID to number if possible

    If IsNumeric(searchID) Then

        searchID = CLng(searchID) ‘ Convert to Long (Integer)

    Else

        MsgBox “Invalid ID! Please enter a number.”, vbCritical

        Exit Sub

    End If

    ‘ Apply HLOOKUP for Product Name (Row 2) and Remaining Stock (Row 5)

    On Error Resume Next

    productName = Application.WorksheetFunction.HLookup(searchID, ws.Range(“B1:G5”), 2, False)

    remainingStock = Application.WorksheetFunction.HLookup(searchID, ws.Range(“B1:G5”), 5, False)

    On Error GoTo 0

    ‘ Show Result

    If IsError(productName) Or IsError(remainingStock) Or IsEmpty(productName) Or IsEmpty(remainingStock) Then

        MsgBox “ID not found!”, vbExclamation

    Else

        MsgBox “Product Details for ID ” & searchID & “:” & vbNewLine & _

               “Product Name: ” & productName & vbNewLine & _

               “Remaining Stock: ” & remainingStock, vbInformation

    End If

End Sub

INDEX AND MATCH  (PRODUCT ID KI BaSE PAY PRODUCT NAME)

Sub FindProductByID()

    Dim ws As Worksheet

    Dim productID As Variant

    Dim result As Variant

    Dim searchRow As Variant

    ‘ Set the worksheet

    Set ws = ThisWorkbook.Sheets(“Sheet1”) ‘ Apni sheet ka naam yahan likhein

    ‘ Get Product ID from user

    productID = InputBox(“Enter Product ID to search:”)

    ‘ Convert input to number if possible

    If IsNumeric(productID) Then

        productID = CLng(productID)

    Else

        MsgBox “Please enter a valid numeric Product ID.”, vbExclamation

        Exit Sub

    End If

    ‘ Use MATCH to find row number of the product ID

    On Error Resume Next

    searchRow = Application.WorksheetFunction.Match(productID, ws.Range(“A2:A100”), 0)

    On Error GoTo 0

    ‘ Check if product ID was found

    If IsError(searchRow) Or searchRow = 0 Then

        MsgBox “Product ID not found!”, vbExclamation

    Else

        ‘ Use INDEX to get the Product Name from Column B

        result = Application.WorksheetFunction.Index(ws.Range(“B2:B100”), searchRow)

        ‘ Show the result

        MsgBox “Product Name for ID ” & productID & ” is: ” & result, vbInformation, “Search Result”

    End If

End Sub

How This Works

1. User se Product ID input liya jata hai.
2. MATCH function ka use karke row number find kiya jata hai.
3. INDEX function ka use karke us row ka Product Name return hota hai.
4. Agar Product ID exist nahi karti, toh error message show hota hai.

📌 Example

Agar aap Product ID = 108 enter karein, toh output hoga:
✅ “Product Name for ID 108 is: Product 8”

Agar koi invalid ID enter karein, toh output hoga:
⚠ “Product ID not found!”

Agar aapko Profit ya Selling Price dhoondhna ho, toh Column B ko Column H ya koi aur column se replace kar sakte hain.

Koi aur modification chahiye toh batao! 🚀

Sub CalculateProfit()

    Dim ws As Worksheet

    Dim lastRow As Long

    Dim i As Integer

    Set ws = ThisWorkbook.Sheets(“VBA”) ‘ Apni sheet ka naam daalain

    ‘ Last row ka pata lagana

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ‘ Loop chalayen taake har row ka profit calculate ho

    For i = 2 To lastRow ‘ Assuming first row has headers

        ‘ Profit calculation formula

        ws.Cells(i, 9).Value = (ws.Cells(i, 7).Value – ws.Cells(i, 6).Value) * ws.Cells(i, 5).Value

        ‘ Agar profit negative hai to usay zero bana dein

        If ws.Cells(i, 9).Value < 0 Then

            ws.Cells(i, 9).Value = 0

        End If

    Next i

    MsgBox “Profit Calculation Complete!”, vbInformation, “Done”

End Sub

Is VBA Code Ka Kaam

1. Selling Price – Purchase Price ko Remaining Stock se multiply karega.
2. Agar profit negative ho to usay 0 bana dega.
3. I column mein profit ki value dal dega.

VBA (IF/OR CONDITION )

Sub CalculateProfitWithOR()

    Dim ws As Worksheet

    Dim lastRow As Long

    Dim i As Integer

    Set ws = ThisWorkbook.Sheets(“VBA”) ‘ Apni sheet ka naam daalain

    ‘ Last row ka pata lagana

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ‘ Loop chalakar har row ka profit calculate karna

    For i = 2 To lastRow ‘ Assuming first row has headers

        ‘ Profit calculation formula

        ws.Cells(i, 9).Value = (ws.Cells(i, 7).Value – ws.Cells(i, 6).Value) * ws.Cells(i, 5).Value

        ‘ IF condition with OR (Agar profit negative hai YA stock 0 hai to profit bhi 0)

        If ws.Cells(i, 9).Value < 0 Or ws.Cells(i, 5).Value = 0 Then

            ws.Cells(i, 9).Value = 0

        End If

    Next i

    MsgBox “Profit Calculation Complete!”, vbInformation, “Done”

End Sub

VBA ( IF / AND CONDITION)

Sub CalculateProfitWithAND()

    Dim ws As Worksheet

    Dim lastRow As Long

    Dim i As Integer

    Set ws = ThisWorkbook.Sheets(“Sheet1”) ‘ Apni sheet ka naam daalain

    ‘ Last row ka pata lagana

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ‘ Loop chalakar har row ka profit calculate karna

    For i = 2 To lastRow ‘ Assuming first row has headers

        ‘ Profit calculation formula

        ws.Cells(i, 9).Value = (ws.Cells(i, 7).Value – ws.Cells(i, 6).Value) * ws.Cells(i, 5).Value

        ‘ IF condition with AND (Agar profit negative hai AUR stock 0 hai to profit bhi 0)

        If ws.Cells(i, 9).Value < 0 And ws.Cells(i, 5).Value = 0 Then

            ws.Cells(i, 9).Value = 0

        End If

    Next i

    MsgBox “Profit Calculation Complete!”, vbInformation, “Done”

End Sub

Columns & Formulas

Sub CalculateInventory()

    Dim ws As Worksheet

    Dim lastRow As Long

    ‘ Active Sheet ko Set karna

    Set ws = ThisWorkbook.Sheets(“VBA”)

    ‘ Last row find karna

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ‘ Formula apply karna

    ws.Range(“E2:E” & lastRow).Formula = “=C2-D2”  ‘ Remaining Stock

    ws.Range(“H2:H” & lastRow).Formula = “=E2*G2”  ‘ Total Value

    ws.Range(“I2:I” & lastRow).Formula = “=(G2-F2)*D2”  ‘ Profit

    ‘ Message show karna

    MsgBox “Inventory Calculations Updated!”, vbInformation, “Success”

End Sub

Additional Useful Formulas

Sub cal()

    Dim ws As Worksheet

    Dim lastRow As Long

    ‘ Active Sheet ko Set karna

    Set ws = ThisWorkbook.Sheets(“VBA”)

    ‘ Last row find karna

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ‘ Formula apply karna

    ws.Range(“E2:E” & lastRow).Formula = “=C2-D2”  ‘ Remaining Stock

    ws.Range(“H2:H” & lastRow).Formula = “=E2*G2”  ‘ Total Value

    ws.Range(“I2:I” & lastRow).Formula = “=(G2-F2)*D2”  ‘ Profit

    ws.Range(“J2:J” & lastRow).Formula = “=D2/C2”  ‘ Stock Turnover Rate

    ws.Range(“K2:K” & lastRow).Formula = “=D2*G2”  ‘ Gross Revenue

    ws.Range(“L2:L” & lastRow).Formula = “=I2/(D2*F2)”  ‘ Profit Margin %

    ws.Range(“M2:M” & lastRow).Formula = “=IF(E2<10, “”Reorder Required””, “”Sufficient””)”  ‘ Reorder Alert

    ‘ Message show karna

    MsgBox “Inventory Calculations Updated Successfully!”, vbInformation, “Success”

End Sub

Yeh formulas inventory management ko aur accurate aur efficient banayenge.

Sub AdvancedInventoryCalculations()

    Dim ws As Worksheet

    Dim lastRow As Long

    Dim emailMessage As String

    ‘ Active Sheet ko Set karna

    Set ws = ThisWorkbook.Sheets(“Sheet1”)

    ‘ Last row find karna

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ‘ Formula apply karna

    ws.Range(“E2:E” & lastRow).Formula = “=C2-D2”  ‘ Remaining Stock

    ws.Range(“H2:H” & lastRow).Formula = “=E2*G2”  ‘ Total Value

    ws.Range(“I2:I” & lastRow).Formula = “=(G2-F2)*D2”  ‘ Profit

    ws.Range(“J2:J” & lastRow).Formula = “=D2/C2”  ‘ Stock Turnover Rate

    ws.Range(“K2:K” & lastRow).Formula = “=D2*G2”  ‘ Gross Revenue

    ws.Range(“L2:L” & lastRow).Formula = “=I2/(D2*F2)”  ‘ Profit Margin %

    ws.Range(“M2:M” & lastRow).Formula = “=IF(E2<10, “”Reorder Required””, “”Sufficient””)”  ‘ Reorder Alert

    ws.Range(“N2:N” & lastRow).Formula = “=TODAY()-B2”  ‘ Stock Ageing

    ws.Range(“O2:O” & lastRow).Formula = “=D2/(TODAY()-B2)”  ‘ Daily Sales Rate

    ws.Range(“P2:P” & lastRow).Formula = “=TODAY()+(E2/O2)”  ‘ Projected Stock Out Date

    ws.Range(“Q2:Q” & lastRow).Formula = “=IF(E2<10, 50, 0)”  ‘ Reorder Quantity

    ‘ Low Stock Email Alert System

    emailMessage = “Following products are running low on stock:” & vbCrLf

    For i = 2 To lastRow

        If ws.Cells(i, 5).Value < 10 Then

            emailMessage = emailMessage & ws.Cells(i, 2).Value & ” (Stock Left: ” & ws.Cells(i, 5).Value & “)” & vbCrLf

        End If

    Next i

    ‘ Agar koi low stock hai to message show kare

    If emailMessage <> “Following products are running low on stock:” & vbCrLf Then

        MsgBox emailMessage, vbCritical, “Low Stock Alert!”

    End If

    ‘ Success message

    MsgBox “Advanced Inventory Calculations Updated Successfully!”, vbInformation, “Success”

End Sub

Class 2: Conditional Logic in Excel

• IF Condition: =IF(A2>50, “High Stock”, “Low Stock”)
• IF with AND/OR: =IF(AND(B2>50, C2<20), “Reorder”, “In Stock”)
• Conditional Formatting: Color-code low stock items automatically

Sub ApplyConditionalLogic()

    Dim ws As Worksheet

    Dim lastRow As Long

    ‘ Select Active Sheet

    Set ws = ThisWorkbook.Sheets(“VBA”)

    ‘ Find Last Row

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ‘ Apply IF Condition Formula (High Stock / Low Stock)

    ws.Range(“J2:J” & lastRow).Formula = “=IF(E2>50, “”High Stock””, “”Low Stock””)”

    ‘ Apply IF with AND/OR Formula (Reorder Alert)

    ws.Range(“K2:K” & lastRow).Formula = “=IF(AND(E2<20, C2>50), “”Reorder””, “”In Stock””)”

    ‘ Apply Conditional Formatting for Low Stock

    Dim rng As Range

    Set rng = ws.Range(“E2:E” & lastRow)

    With rng.FormatConditions.Add(Type:=xlExpression, Formula1:=”=E2<10″)

        .Interior.Color = RGB(255, 0, 0) ‘ Red Color

    End With

    ‘ Success Message

    MsgBox “Conditional Logic Applied Successfully!”, vbInformation, “Success”

End Sub

Summary

✔ IF Formula → Stock Level Alert
✔ IF + AND/OR → Reorder Notification
✔ Conditional Formatting → Low Stock Color Alert
✔ VBA Automation → Ek Click par sab kuch apply ho jayega!

 Class 3: Advanced Lookup & Search Functions

• VLOOKUP: =VLOOKUP(101, A:I, 9, FALSE)
• HLOOKUP: =HLOOKUP(“STEEL BEM”, B1:I3, 2, FALSE)
• XLOOKUP: (For latest Excel users)
• INDEX-MATCH: =INDEX(I:I, MATCH(101, A:A, 0))