VBA & Macro Problems

How to Find the Best Column Combination Below a Target in Excel with VBA

Steve KSteve K Nov 13, 2026 870 views

Question details

The user needs to select one value from each column in a table so the total is as high as possible but does not exceed a target, using Excel-compatible VBA or another optimization method.

How to Find the Best Column Combination Below a Target in Excel with VBA
Product
WPS Spreadsheet
Device & OS
not provided
Scenario
The user has a data range with multiple columns and a target value, and wants to identify the best combination of one value per column.
Observed behavior
A standard worksheet formula cannot directly return the optimal combination; a recursive VBA function or a query/Solver workflow is required.
Before you start

Before you start, save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and make sure the Developer tab or VBA editor is available so you can insert and run the recursive procedure.

Solution 1Recommended

Use a recursive VBA function to find the closest combination under a target

Best for small to medium ranges where you want a formula-driven result and can enable macros.

The recursive function checks one value from each column, totals the selection, and keeps the largest total that does not exceed the target. This avoids manually evaluating every combination.

1
Open the VBA editor

In WPS Spreadsheet or Excel, press Alt+F11, or go to Developer > Visual Basic to open the VBA editor.

2
Insert a standard module

In the Project pane, right-click your workbook name, choose Insert > Module, and open the new module.

3
Add the FindSolution VBA procedure

Paste a recursive FindSolution function that starts at the first column, selects each cell in turn, moves to the next column, and compares totals against the target cell.

4
Call the function from a cell

Enter =FindSolution(A2:D11,G2) in the cell where you want the result, replacing A2:D11 with your data range and G2 with the target cell.

Macros must be enabled: Save the file as an .xlsm workbook and choose Enable Content when the security warning appears, otherwise the custom function will not run.

Find the closest column combination under a target in WPS Spreadsheet

WPS Spreadsheet can handle this optimization with a recursive VBA function in a macro-enabled workbook, or with Power Query for larger datasets, while keeping full compatibility with Excel files.

  1. 1. Save as a macro-enabled workbook: Open the file in WPS Spreadsheet and save it as an Excel Macro-Enabled Workbook (.xlsm).
  2. 2. Open the VBA editor: Press Alt+F11 or click Developer > Visual Basic to open the VBA editor.
  3. 3. Insert the FindSolution module: Right-click the project in the Project Explorer, choose Insert > Module, and paste the recursive VBA function.
  4. 4. Return to the worksheet and run the formula: In a cell, enter =FindSolution(A2:D11,G2), using your actual data range and target cell.
  5. 5. Enable macros if prompted: Click Enable Content when the macro security warning appears so the custom function can calculate.
Formula-based VBA finds the largest valid total without testing combinations manuallyWorks with Excel .xlsx and .xlsm files and preserves macro-enabled workbook compatibilityFree, lightweight WPS Spreadsheet toolbar and VBA editor are familiar to Excel usersPower Query and Solver can be used as alternatives for large or complex datasets
QA img-9

Frequently Asked Questions

Does this method work in WPS Spreadsheet?

Yes. Save the workbook as .xlsm, enable macros, and insert the VBA function in a standard module, then call it from a worksheet cell.

What formula should I use to return the best total?

Use =FindSolution(A2:D11,G2), replacing A2:D11 with the range containing your columns and G2 with the target cell.

Why does my function return an error or zero?

Usually the workbook is not saved as .xlsm, macros are disabled, or the function is placed in a worksheet module instead of a standard module.

What can I do if the dataset is too large for recursion?

Use Power Query to generate and filter combinations, or try Solver or another optimization approach to avoid evaluating every combination.

Can I show which value was selected from each column?

Yes. Modify the VBA procedure to store or output the selected cell addresses and values when it finds a better valid total.