[Solved] Auto-Calculate Gross Profit

[Tsd5562]

I want to use VBA to automatically add a new column "Gross Profit" (Revenue – COGS) and populate it for all rows. How do I insert the column and calculate?

Quarter	Product Line	Revenue (USD)	Cost of Goods Sold (COGS)	Operating Expenses	Net Income
Q1-2023	Alpha	120,000	70,000	20,000	30,000
Q2-2023	Alpha	135,000	75,000	22,000	38,000
Q3-2023	Alpha	150,000	78,000	24,000	48,000
Q4-2023	Alpha	160,000	80,000	25,000	55,000
Q1-2024	Beta	90,000	45,000	18,000	27,000
Q2-2024	Beta	100,000	50,000	19,000	31,000
Q3-2024	Beta	110,000	52,000	20,000	38,000
Q4-2024	Beta	120,000	55,000	21,000	44,000

 

[shamimarita]

Hello Tsd5562,

You can use the following VBA code to automatically insert a new column labeled "Gross Profit" to the right of "Revenue (USD)" and calculate the values as Revenue – COGS for each row:

Sub AddGrossProfitColumn()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ActiveSheet ' Or specify your sheet: Worksheets("Sheet1")

    ' Find the column index for "Revenue (USD)" and "Cost of Goods Sold (COGS)"
    Dim revenueCol As Long, cogsCol As Long, insertCol As Long
    revenueCol = ws.Rows(1).Find("Revenue (USD)").Column
    cogsCol = ws.Rows(1).Find("Cost of Goods Sold (COGS)").Column

    ' Insert new column to the right of Revenue
    insertCol = revenueCol + 1
    ws.Columns(insertCol).Insert Shift:=xlToRight
    ws.Cells(1, insertCol).Value = "Gross Profit"

    ' Get last row with data
    lastRow = ws.Cells(ws.Rows.Count, revenueCol).End(xlUp).Row

    ' Loop through each row to calculate Gross Profit
    For i = 2 To lastRow
        ws.Cells(i, insertCol).Formula = "=" & ws.Cells(i, revenueCol).Address & "-" & ws.Cells(i, cogsCol).Address
    Next i

    MsgBox "Gross Profit column added successfully!"
End Sub

This code will:

• Insert a "Gross Profit" column next to Revenue.
• Calculate each row using =Revenue - COGS.
• Work for dynamic ranges.