How to Copy Values Above and Below Matching IDs using Excel VBA
Question details
The user needs an Excel VBA macro to find specific IDs in column A, copy the value immediately below the ID to column B, and copy the value two rows above it to column C.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting contextually relevant data surrounding specific ID markers using a VBA macro.
- Observed behavior
- The macro successfully locates the IDs in column A and populates columns B and C with the targeted adjacent cell values starting from row 2.
Ensure you have the Developer tab enabled in your spreadsheet and save your workbook as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code.
Use a VBA Loop with the Offset Property
Use a For Each loop combined with the Offset property to navigate relative to the matched ID and extract surrounding values.
The Offset property in VBA is highly efficient for referencing cells based on a known location. By specifying row and column offsets, you can easily grab data above (using a negative row index) or below (using a positive row index) the target cell.
Press ALT + F11 on your keyboard to open the Visual Basic Editor (VBE) in Excel.
Click on Insert in the top menu, then select Module to create a blank script window.
Declare your variables and write a loop to iterate through the cells in column A. For example, use 'For Each cell In ws.Range("A1:A100")'.
Add an If statement to check if the cell value matches your target ID. If matched, use 'cell.Offset(1, 0).Value' to capture the value one row below and output it to column B.
Use 'cell.Offset(-2, 0).Value' to capture the value two rows above the matched ID and output it to column C. Be sure to increment your output row counter (e.g., OutputRow = OutputRow + 1) so results are listed clearly starting from row 2.
Create and Run Macros Easily with WPS Office
WPS Spreadsheets provides a built-in macro editor that fully supports standard VBA syntax, allowing you to automate tasks like cell offsetting and data extraction seamlessly.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open Your Workbook: Launch WPS Spreadsheets and open the workbook where you need to run the data extraction macro.
- 3. Access the VB Editor: Navigate to the Developer tab on the ribbon and click on the 'VB Editor' or 'Macros' button.
- 4. Paste and Run: Insert a module, paste your Offset macro code, and click 'Run' to instantly extract the values above and below your target IDs.
Frequently Asked Questions
What happens if the matched ID is in row 1 or row 2?
If your ID is located in row 1 or 2, attempting to use 'Offset(-2, 0)' will cause a runtime error because it tries to reference a row that does not exist. You should add an IF condition (e.g., 'If cell.Row > 2 Then') to verify the row number before applying a negative offset.
How do I prevent the macro from overwriting existing data in columns B and C?
Before writing the extracted values, you can use a VBA script to find the last used row in columns B and C. Assign this dynamic row number to your output row variable to ensure new data starts at the next available blank row.
Can I use this VBA logic to copy formatting along with the values?
Yes. Instead of extracting just the '.Value', you can use the '.Copy' method on the offset cell and the '.PasteSpecial' method on the destination cell to retain background colors, fonts, and borders.
Why am I getting a 'Subscript out of range' error?
This error usually occurs if you haven't explicitly qualified your ranges with the correct worksheet, or if your workbook does not contain the exact sheet name specified in your code. Ensure you define your worksheet properly, such as 'Set ws = ThisWorkbook.Sheets("Data")'.
