VBA & Macro Problems

How to Return the Column Number from Excel VBA Find

John WilsonJohn Wilson Oct 29, 2026 868 views

Question details

The user needs to retrieve the exact numerical column index of a searched value using the Excel VBA Find method instead of the value itself.

How to Return the Column Number from Excel VBA Find
Product
Excel
Device & OS
not provided
Scenario
Writing a VBA macro to locate a specific data value in a spreadsheet and dynamically determine its column position to use in further data manipulation.
Observed behavior
The standard Find method returns the matched Range object or its text value, preventing the user from directly seeing the integer column position (e.g., returning '225-01-1022' instead of '5' for column E).
Before you start

Ensure that the Developer tab is enabled on your spreadsheet ribbon and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) before testing and running VBA code.

Solution 1Recommended

Store the Find Result in a Range Variable (Recommended)

The safest and most reliable way to retrieve a column number is to store the Find method's result in a Range object. This allows you to extract the column number while preventing errors if the value is missing.

Directly calling properties on the Find method can cause your macro to crash if the search term does not exist in the spreadsheet. By saving the result into a dedicated variable, you can add conditional checks before requesting the column position.

1
Declare a Range variable

In your VBA Editor, start by declaring a variable to hold the search result. Type: Dim rngFound As Range

2
Execute the Find method

Assign the search result to your variable using the 'Set' keyword. For example: Set rngFound = Cells.Find(What:="YourSearchValue", LookIn:=xlValues, SearchFormat:=False)

3
Extract the column number

Access the numerical column position using the Column property of your Range object by typing: rngFound.Column

Store the Find Result in a Range Variable (Recommended)
Prevent Runtime Errors: Wrap your column extraction in an 'If Not rngFound Is Nothing Then' statement. This ensures the macro only attempts to extract the column if the searched value actually exists.
Advanced Macro Support in WPS Spreadsheets

Easily Execute VBA Macros in WPS Spreadsheets

WPS Spreadsheets provides comprehensive support for VBA macros. You can execute the exact same Range.Find and .Column scripts to locate data dynamically without needing to modify your existing Excel code.

  1. 1. Open your workbook: Launch WPS Spreadsheets and open your Macro-Enabled Workbook (.xlsm or .xls).
  2. 2. Access the Developer Tab: Click on the 'Developer' tab located in the top ribbon menu to access your macro tools.
  3. 3. Open the VBA Editor: Click on 'Visual Basic' or 'WPS Macro' to launch the built-in VBA code editor.
  4. 4. Run your Find script: Paste your Range.Find code into the module and click 'Run' to seamlessly locate your column numbers.
Fully compatible with Microsoft Excel VBA syntax and the Find method.Seamlessly opens, edits, and runs Microsoft Office .xlsm and .xlsb formats.Built-in Developer tools available for free without complex configuration.Lightweight architecture ensures complex macros run smoothly and efficiently.
microsoft office alternative - wps office

Frequently Asked Questions

How do I prevent runtime errors if the VBA Find method finds nothing?

You should assign the search result to a Range variable instead of appending .Column directly to the Find method. Once assigned, use an 'If Not rngFound Is Nothing Then' statement to verify that a match exists before attempting to extract the column number.

Does the VBA .Column property return a letter or a number?

The .Column property always returns a numerical integer representing the column's position from the left side of the worksheet. For example, Column A returns 1, and Column E returns 5.

Can I convert the returned column number back to a column letter in VBA?

Yes, you can convert the column integer back to a letter using the Address property. For instance, the script 'Split(Cells(1, colNum).Address(True, False), "$")(0)' will convert the number 5 back into the letter 'E'.