WPS Spreadsheets VBA Macro to Match Two Conditions and Concatenate Values
Question details
The user needs a VBA macro that scans worksheet rows, finds rows where two columns match specified conditions, and concatenates two other columns into a result column.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has worksheets with ID and date columns and wants to match values in those columns before combining the related value cells into a destination column.
- Observed behavior
- The proposed VBA loop is not producing the expected concatenated result.
Open the macro-enabled workbook in WPS Spreadsheets and note the exact sheet names, header row, and column letters for the two match columns and the two value columns before editing the VBA code.
Use a VBA Loop in WPS Spreadsheets to Match Two Columns and Concatenate Values
Use this when you need the concatenation to run inside VBA without the TEXTJOIN worksheet function.
This macro loops through each row from the first data row to the last used row, checks the first and third columns, and writes the fifth and sixth columns to the result column with a space and a comma. Adjust the worksheet names and column letters to match the workbook structure.
In WPS Spreadsheets, open the macro-enabled workbook and press Alt+F11 to open the VBA editor.
In the VBA editor, click Insert > Module to create a new module where you can place the macro.
Paste a loop that uses the last-used row, compares the first match column and second match column, and writes the two value columns to the result column with Chr(32) and Chr(44). Example: For i = 2 To LastRow: If Cells(i, "A").Value = 2 And Cells(i, "C").Value = 3 Then Cells(i, "G").Value = Cells(i, "E").Value & Chr(32) & Cells(i, "F").Value & Chr(44).
Change the worksheet names and column references in the code to your source and destination sheets, such as Sheets("Latest") and Sheets("DRP"), and set the destination result column as needed.
Click Run > Run Sub/UserForm or press F5, then check that the result column contains the concatenated value for each matching row.
Create a Matching and Concatenation Macro in WPS Spreadsheets
Open your macro-enabled workbook in WPS Spreadsheets, open the VBA editor, and run a loop that matches the ID and date columns, then writes the two value columns into the result column with a space and comma.
- 1. Install WPS Office: Download WPS Office Free from the official WPS website and install WPS Spreadsheets.
- 2. Open the macro-enabled workbook: Click File > Open and select the .xlsm workbook containing the source and destination worksheets.
- 3. Open the VBA editor and insert a module: Press Alt+F11 to open the VBA editor, then click Insert > Module.
- 4. Run the condition-matching macro: Paste the VBA loop that compares the two match columns and concatenates the two value columns, then click Run > Run Sub/UserForm.
- 5. Save as a macro-enabled file: Click File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and save the updated file.
Frequently Asked Questions
Can I use VBA instead of TEXTJOIN to concatenate values in Excel?
Yes. In WPS Spreadsheets, open the VBA editor with Alt+F11, insert a module, and use a loop with the & operator, Chr(32) for a space, and Chr(44) for a comma.
Why is my VBA loop not writing anything to the result column?
Check that the worksheet names, column references, and last-row calculation point to the correct source and destination sheets. Also confirm the match values are written exactly as they appear in the cells.
How do I match two conditions in a VBA macro?
Use an If statement inside a For loop, such as If Cells(i, 1).Value = Match1 And Cells(i, 3).Value = Match2 Then, and adapt the column indexes and values to your workbook.
Does WPS Spreadsheets support Excel VBA macros?
Yes. WPS Spreadsheets can open .xlsm workbooks and run VBA macros through the VBA editor, although you should test the specific macro to confirm compatibility.
How do I concatenate two columns with a space and a comma in VBA?
Assign a result cell with a statement like Cells(i, 7).Value = Cells(i, 5).Value & Chr(32) & Cells(i, 6).Value & Chr(44), changing the column numbers as needed.
