Create Excel VBA to Search for 1.1.2 and 1.1 and Combine Results in WPS Office
Question details
The user needs WPS Office Excel VBA code that searches a range for the text values 1.1.2 and 1.1 and combines all matching results into a single destination range.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is building or updating an Excel macro to locate rows containing 1.1.2 or 1.1 and consolidate them for review or further processing.
- Observed behavior
- The macro should preserve the periods in both search values, collect each matching row, and write the combined list to one output area without manual copying.
Open the target workbook in WPS Office Excel and enable macros or save the file as a macro-enabled workbook (.xlsm). Before running the code, identify the search range, the criteria values, and the starting cell for the combined output.
Use a Text-Safe VBA Loop with InStr to Search for 1.1.2 and 1.1 and Combine Results
Best when you want a transparent macro that treats both search values as text, preserves periods, and writes matching rows to one output area.
The macro loops through the specified range, converts each cell value to text with CStr, and uses InStr with vbTextCompare to test for 1.1.2 first and then 1.1. The search order prevents a cell containing 1.1.2 from being written twice, because the macro exits the inner loop after the first match.
The code copies the entire row by default. If you want only specific columns or exact matches, adjust the output range or replace the InStr line with If cellText = CStr(v) Then.
In WPS Office Excel, press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
Click Insert > Module in the VBA editor menu to create a new standard module.
Paste the following code into the module: Sub SearchCombine112() Dim rng As Range Dim cell As Range Dim outRow As Long Dim searchVals As Variant Dim v As Variant Dim cellText As String ' Change the sheet, range, and output sheet as needed Set rng = Worksheets("Sheet1").Range("A2:A100") outRow = 2 searchVals = Array("1.1.2", "1.1") For Each cell In rng cellText = CStr(cell.Value) For Each v In searchVals If InStr(1, cellText, CStr(v), vbTextCompare) > 0 Then cell.EntireRow.Copy Worksheets("Output").Cells(outRow, 1) outRow = outRow + 1 Exit For End If Next v Next cell MsgBox "Search complete. Matches combined at output area." End Sub
Change Worksheets("Sheet1").Range("A2:A100") to your actual search range, and change Worksheets("Output").Cells(outRow, 1) to your desired output sheet and start cell.
Place the cursor inside the macro and press F5, or click Run > Run Sub/UserForm in the VBA editor. Return to Excel to review the combined matching rows.
Create a VBA Macro in WPS Office Excel to Search 1.1.2 and 1.1 and Combine Results
Use the built-in VBA editor in WPS Office Excel to insert a text-safe macro that searches for both 1.1.2 and 1.1 and writes the combined matching rows to one output range.
- 1. Launch WPS Office Excel: Open WPS Office Excel and load the workbook that contains the data you want to search.
- 2. Open the VBA editor: Press Alt+F11 to open the built-in VBA editor.
- 3. Insert and paste the macro: Click Insert > Module and paste the search-and-combine VBA code into the new module.
- 4. Set your search and output ranges: Edit the worksheet names and range addresses in the macro to match your source data and output location.
- 5. Run the macro: Press F5 in the VBA editor to execute the macro, then switch back to Excel to inspect the combined results.
Frequently Asked Questions
Why does 1.1.2 become a number when I compare it in VBA?
When criteria are not declared as text, Excel may treat version-like values with periods as text but comparisons can still fail if the cell stores a number. Declare the search values in an Array with double quotes and convert cell values with CStr so the periods are preserved during comparison.
How do I search for exact matches instead of cells that contain 1.1.2 or 1.1?
Replace the InStr line with If cellText = CStr(v) Then in the macro. This returns only cells whose entire value equals one of the two search strings.
Can I send the combined results to a different worksheet?
Yes. In the copy line, change Worksheets("Output") to the destination sheet name, such as Worksheets("Combined"), and set the starting row with Cells(outRow, 1).
How do I make the search case-insensitive?
Use InStr(1, cellText, CStr(v), vbTextCompare) in the code. The vbTextCompare argument makes the match case-insensitive, which is also useful for letters mixed into version codes.
Which file format should I save to keep the VBA macro working in WPS Office?
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) or keep the .xls format. Macro-free .xlsx files will not retain the VBA code.
