How to Use an IF Statement Inside a VBA Select Case Block
Question details
The user needs to calculate the current cell value by comparing values from the two preceding rows using an IF statement nested safely within a Select Case block.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Running a VBA loop to evaluate cell values based on specific conditions, comparing the two rows directly above the current row index.
- Observed behavior
- When the loop starts at a low index (e.g., i = 2), subtracting from the index results in an invalid row reference (row 0), triggering an application-defined error.
Verify your starting loop index and ensure you have assigned the correct worksheet object to a variable (such as ws2) to prevent modifying the wrong sheet.
Add a Condition to Prevent Invalid Row References
Wrap your row calculation logic in a validation IF statement to ensure the index is large enough, avoiding row 0 or negative row errors.
In VBA, referencing a row number less than 1 will throw a runtime error. When using dynamic indices like `i - 2`, you must ensure `i` is strictly greater than the subtracted amount before executing the row comparison.
Open your VBA editor, find the module containing your loop, and locate the specific `Case` where you want to perform the row comparison.
Add `If i > 2 Then` immediately after your Case statement to verify that checking two rows above is mathematically valid in the worksheet.
Inside the index check, add your logic: `If ws2.Range("C" & i - 2).Value = ws2.Range("C" & i - 1).Value Then`. Ensure you explicitly use the worksheet object (like `ws2`).
Add the desired calculation if true: `ws2.Range("C" & i).Value = ws2.Range("C" & i - 1).Value + 15`. Then use an `Else` block for the false outcome: `ws2.Range("C" & i).Value = ws2.Range("C" & i - 1).Value`.
Conclude the logic by adding two consecutive `End If` statements to properly close both the comparison IF and the index validation IF.
Write and Execute VBA Code Seamlessly in WPS Spreadsheet
WPS Office provides a comprehensive Developer environment that fully supports VBA, allowing you to quickly write, test, and debug complex nested IF statements and Select Case blocks.
- 1. Enable the Developer Tab: Launch WPS Spreadsheet, navigate to the top ribbon, and access the Developer tab to find your macro tools.
- 2. Open the VBA Editor: Click on the 'Visual Basic' or 'Macro' icon to launch the built-in code editor.
- 3. Insert Your Code: Create a new Module or select an existing one, then paste your looping logic along with the nested IF statements and Select Case block.
- 4. Run the Macro: Click the Run button or press F5 to execute your macro. The code will safely skip invalid rows and update your target cells dynamically.
Frequently Asked Questions
Why do I get a runtime error 1004 when looping through rows in VBA?
This commonly happens when your code tries to reference a row that doesn't exist. If you subtract from your loop index (e.g., `i - 2`) and the result is 0 or negative, Excel/WPS Spreadsheet cannot find the cell. Wrapping the logic in an `If i > 2 Then` check prevents this error.
Can I use an IF statement inside a Select Case block?
Yes, VBA allows you to nest IF statements inside any Case block. Simply write your IF...Then...Else logic underneath the specific Case condition, and remember to properly terminate it with an End If before the next Case or End Select.
What happens if I don't use a worksheet variable like ws2 before Range?
If you just write `Range("C" & i)`, VBA defaults to the currently ActiveSheet. This can cause the macro to pull data from or overwrite the wrong tab if you switch sheets while the macro runs. Explicitly stating `ws2.Range` ensures data integrity.
