How to Fill Blank Cells in TOTAL Rows Using Excel VBA
Question details
The user needs an Excel VBA macro to find rows containing 'TOTAL' in column F and fill corresponding blank cells in column L using the formula from L1, while avoiding run-time error 91.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Automating the process of filling formulas into specific blank cells for summary or total rows without overwriting existing data.
- Observed behavior
- The user wants to safely copy formulas into blank cells and convert them to values, but encountered a 'run-time error 91: Object variable or With block variable not set' when trying to check empty cells using the Find method.
Before running the macro, ensure that you have enabled the Developer tab to access the VBA Editor and verify that cell L1 contains the correct reference formula.
Create a VBA Macro with Find and IsEmpty
Use a VBA loop to search for 'TOTAL' in column F, check for blank cells in column L, apply the formula, and handle potential errors.
To automate filling the blank cells in column L for rows where column F says 'TOTAL', you can use a combination of the Find method and a loop in VBA. It is crucial to validate the search results to prevent run-time errors.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in your spreadsheet software.
Click on 'Insert' in the top menu and select 'Module' to create a new blank script.
Write a Do...Loop using the Find method to search for the word 'TOTAL' in column F. Determine the final row dynamically by finding the last populated row in column B.
For each found cell, use the IsEmpty function on the corresponding cell in column L (using the Offset property) to ensure you do not overwrite existing values.
Copy the formula from cell L1 to the empty cell. Include an 'If Not rng Is Nothing' check to prevent run-time error 91 when the Find method returns no result. Optionally, convert the inserted formulas to values at the end of the script.
Automate Worksheets Easily with WPS Spreadsheet
WPS Office Spreadsheet provides excellent support for VBA and macros, allowing you to seamlessly run your Excel scripts, handle large datasets, and automate repetitive tasks like filling blank cells.
- 1. Download and Install WPS Office: Download WPS Office from the official website and install it on your device.
- 2. Open Your Workbook: Launch WPS Spreadsheet and open your existing Excel macro-enabled workbook (.xlsm).
- 3. Enable Macros: Go to the Developer tab and click 'Macros' or press Alt + F8 to run your custom VBA script to fill the TOTAL rows.
Frequently Asked Questions
Why do I get 'Run-time error 91' when using the Find method in VBA?
This error occurs because the Find method returns 'Nothing' if the search term is not found. If your code tries to access properties (like .Row or .Offset) of this non-existent object without checking first, it triggers Error 91. Always use 'If Not myRange Is Nothing Then' to handle this.
How do I check if a cell is blank before overwriting it in VBA?
You can use the IsEmpty(Cell) function or check if the cell's value is an empty string (Cell.Value = ""). This prevents your macro from accidentally replacing existing data.
How can I convert formulas to values after applying them via a macro?
After applying the formula, you can convert the range to values by setting its value to itself. For example: Range("L2:L100").Value = Range("L2:L100").Value. This replaces the underlying formula with the calculated result.
