Wauvel

Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All VBA snippets

Find a column by its header (don't hard-code B)

Use Range.Find to locate a value — or a column by name — so your macro survives inserted or reordered columns.

1What it does

Searches the header row for a column by NAME ('Amount') with Range.Find and uses the column it returns instead of a hard-coded letter. The moment someone inserts a column, every macro that assumed 'Amount is column B' silently writes to the wrong place — finding it by header keeps the macro robust. Find also locates any value anywhere, returning the cell (or Nothing when it isn't there).

2The code

Sub FindByHeader()
    Dim ws As Worksheet, found As Range, col As Long
    Set ws = ActiveSheet

    ' Find the "Amount" column by its HEADER instead of hard-coding "column B",
    ' so the macro survives someone inserting or reordering columns.
    Set found = ws.Rows(1).Find(What:="Amount", LookAt:=xlWhole, MatchCase:=False)

    If found Is Nothing Then
        MsgBox "No 'Amount' column found."
        Exit Sub
    End If

    col = found.Column   ' use col wherever you'd have hard-coded 2
    MsgBox "Amount is column " & col
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac:Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Moduleand paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Locate the right column when layouts shift between files or months.
  • Jump to a specific record, total row, or marker in a sheet.
  • Check whether a value exists before acting on it.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Header row

IDcol 1Datecol 2Amountcol 3Regioncol 4

5Pitfalls

Find returns Nothing and the next line errors with 91 (object not set).

Fix: Always test If found Is Nothing Then before using the result.

Find's LookAt / MatchCase are sticky — they persist from the last search (even the Find dialog).

Fix: Pass LookAt and MatchCase explicitly every call so a stray partial match can't sneak in.

6No-code alternatives

  • MATCH — =MATCH("Amount", 1:1, 0) returns a column position with a formula — no macro.
  • Hard-coded column — Fine only when the layout is truly fixed and you control the file.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Get my free report →