How to Write an Excel VBA Formula Using a Variable Row Number in WPS Office
Question details
The user needs to insert a user-defined function into a worksheet cell from VBA and build the formula string with a row number that can change at runtime.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is automating a worksheet and wants to place a formula in column D that references the current row number stored in a variable.
- Observed behavior
- The macro should write the completed formula, such as =GetSomething(C10), to the intended cell with the correct dynamic row number.
Open the VBA editor in WPS Office and confirm that the target worksheet, column, and row number variable are available in your procedure before writing the formula.
Build and Insert the Formula by Concatenating the Dynamic Row Number
Use this when you need to write a worksheet formula from VBA and the referenced row number is stored in a variable.
VBA treats formulas as strings. Keep the literal formula text inside quotation marks and join the row number with the & operator.
In the VBA editor, declare a worksheet object and set it to the active sheet, then assign the target row to a variable: Dim objInitialSheet As Worksheet: Set objInitialSheet = ActiveSheet: lastrow = 10.
Enter objInitialSheet.Cells(lastrow, "D").Formula = "=GetSomething(C" & lastrow & ")" so the row number is concatenated outside the quoted formula text.
Press F5 in the VBA editor or go to Developer > Macros, select the macro, and click Run to insert the formula into the worksheet.
Escape Quotation Marks When the Formula Contains Text Criteria
Use this when the formula includes comparison operators or text criteria that require quotes inside the VBA string.
Create Dynamic Row VBA Formulas in WPS Office
WPS Office Spreadsheets supports Excel-compatible VBA macros, so you can build and run the same formula-construction code directly in WPS Office without changing your workflow.
- 1. Open the workbook and VBA editor: Open your macro-enabled workbook in WPS Office and press Alt+F11 to launch the VBA editor.
- 2. Define the worksheet and row variable: In the editor, define a worksheet object and set the target row, for example Set objInitialSheet = ActiveSheet and lastrow = 10.
- 3. Build the dynamic formula: Build the formula with concatenation: objInitialSheet.Cells(lastrow, "D").Formula = "=GetSomething(C" & lastrow & ")".
- 4. Run the macro: Press F5 or run the macro from Developer > Macros to insert the formula into the cell.
- 5. Save as a macro-enabled workbook: Save the workbook as an Excel macro-enabled file so the VBA code remains available.
Frequently Asked Questions
How do I write a VBA formula when the row number changes each time?
Store the row number in a variable, then concatenate it into the formula string: ActiveSheet.Cells(lastrow, "D").Formula = "=GetSomething(C" & lastrow & ")".
Why is my VBA formula missing quotation marks or causing a syntax error?
If the formula includes quotes, double them in VBA. For example, use "=SUMIF(B2:B" & lastrow & ","">5"")" so the cell receives the formula with the correct quoted criteria.
Why should I use a worksheet variable instead of ActiveSheet in VBA?
The active sheet can change if a user clicks another tab while the macro runs. Capture the worksheet in a variable at the start and use that variable for all cell assignments.
Can WPS Office run Excel VBA macros that write formulas?
Yes. WPS Office Spreadsheets supports Excel-compatible VBA, so you can open the VBA editor with Alt+F11 and run the same formula-writing code.
