VBA & Macro Problems

How to Find Keywords and Copy Related Values with VBA in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to write a VBA macro to search for specific text markers ("Beginning" and "Ending") in column A, extract values relative to those matches, and transfer the data to a worksheet named Reformat.

Product: Excel
Device & OS: not provided
Scenario: Extracting and restructuring specific data points from a raw dataset based on designated keyword markers within the rows.
Observed behavior: The user wants to automate the process of copying the row above the "Beginning" match and transferring the value from column F (beside the "Ending" match) to column C of a target worksheet.

Before you start

Before running any new VBA macro, verify that your target worksheet is accurately named 'Reformat' and ensure your source dates or data are sorted in the intended order.

Solution 1Recommended

Use the VBA Find Method with Offset and Fully Qualified References

The most reliable and efficient method to search for keywords and copy relative values is by utilizing the Range.Find method combined with Offset, while explicitly defining your worksheet variables.

Using fully qualified worksheet and range references (e.g., explicitly declaring which sheet a range belongs to) prevents code execution errors caused by relying on ActiveSheet.

It is highly recommended to avoid using the Select and Activate methods. Directly assigning values between objects allows the script to run faster and prevents screen flickering.

Define Worksheet Variables

In your VBA editor, set explicit references for your source worksheet and the destination worksheet by using 'Set wsSource = ThisWorkbook.Sheets("SourceSheetName")' and 'Set wsDest = ThisWorkbook.Sheets("Reformat")'.

Search for the First Keyword

Use 'wsSource.Range("A:A").Find("Beginning")' to locate the first keyword. Once found, use the '.Offset(-1, 0)' property to access the data in the row immediately above the match.

Copy the First Value to the Destination

Find the next available blank row on the Reformat sheet using 'wsDest.Cells(Rows.Count, 1).End(xlUp).Row + 1', and assign the value from your Offset range directly to this new row.

Search for the Second Keyword and Transfer Column F

Use the Find method again for "Ending" in column A. Use '.Offset(0, 5)' to target the corresponding value in column F (five columns to the right) and assign this value to column C in your Reformat worksheet.

Efficiency Tip: Direct value transfer (e.g., Range1.Value = Range2.Value) is much faster and cleaner than using the Copy and Paste methods in VBA.

Efficient Macro Support in WPS

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides robust, built-in support for VBA and macros, allowing you to automate data extraction, use the Find method, and perform complex formatting exactly as you would in Microsoft Excel.

1. Open Your Workbook: Launch WPS Spreadsheet and open the workbook containing your raw data and Reformat sheet.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon. If it is not visible, enable it in the WPS Options menu.
3. Open the VBA Editor: Click on 'Visual Basic' or 'Macros' to open the VBA editing environment.
4. Paste and Run Your Code: Insert a new Module, paste your Find and Offset VBA script, and click the 'Run' button to instantly execute your data extraction.

Fully compatible with Microsoft Excel (.xlsm) macro-enabled formats.Built-in Visual Basic editor for writing, debugging, and running custom scripts.Lightweight and fast execution for processing heavy datasets with VBA.Free to download with an intuitive, tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA Find method return a runtime error if the keyword isn't there?

If the Find method cannot locate the specified text, it returns an object value of 'Nothing'. If your code attempts to use Offset on 'Nothing', it causes an error. Always wrap your Find result in an 'If Not [Variable] Is Nothing Then' statement.

How do I find the next available empty row using VBA?

You can locate the last used row in a column and add 1 to it. The standard snippet for this is 'NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1', which mimics pressing Ctrl+Up from the very bottom of the worksheet.

What is a fully qualified reference in Excel VBA?

A fully qualified reference explicitly dictates the entire path to a range object, such as 'ThisWorkbook.Worksheets("Reformat").Range("A1")'. This prevents Excel from guessing the context and accidentally modifying the wrong ActiveSheet.

Can I use Offset to extract a value from multiple columns to the right?

Yes, the Offset property accepts row and column parameters: 'Offset(RowOffset, ColumnOffset)'. For example, '.Offset(0, 5)' targets the cell in the same row but 5 columns to the right, which translates to Column F if you start from Column A.

Excel & Data Problems