How to Return the Column Number from Excel VBA Find
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.

- 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).
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.
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.
In your VBA Editor, start by declaring a variable to hold the search result. Type: Dim rngFound As Range
Assign the search result to your variable using the 'Set' keyword. For example: Set rngFound = Cells.Find(What:="YourSearchValue", LookIn:=xlValues, SearchFormat:=False)
Access the numerical column position using the Column property of your Range object by typing: rngFound.Column

Append the Column Property Directly to the Find Expression
If you are absolutely certain the target value exists within your searched range, you can bypass the variable declaration and extract the column number in a single line of code.
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. Open your workbook: Launch WPS Spreadsheets and open your Macro-Enabled Workbook (.xlsm or .xls).
- 2. Access the Developer Tab: Click on the 'Developer' tab located in the top ribbon menu to access your macro tools.
- 3. Open the VBA Editor: Click on 'Visual Basic' or 'WPS Macro' to launch the built-in VBA code editor.
- 4. Run your Find script: Paste your Range.Find code into the module and click 'Run' to seamlessly locate your column numbers.

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'.




