Fix an Excel VBA Macro That Does Not Fill a Formula Down in Column C
Question details
The user needs to correct a VBA macro so it writes a formula into cell C8 and fills that formula down through the last used row.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro inserts column C, adds an authorization-check formula beginning in C8, and should extend the formula to all populated rows.
- Observed behavior
- The formula is not reliably placed in C8 or is not filled down to the last used row.
Confirm that LoadWS refers to the intended worksheet and that row 8 contains the first row that needs the formula. Verify that the worksheet named ENG Employee List exists before running the macro.
Assign the formula string directly to C8 and fill down only when needed
Use this approach when the formula is stored in an array incorrectly or C8 remains blank after the macro runs.
A VBA array declared as Dim Formulas(1) normally has indexes 0 and 1 unless Option Base 1 is set. Assigning the whole array to the Formula property can use an empty element. Assign the formula string directly, or explicitly use Formulas(1).
Inside the With LoadWS block, insert the column with .Columns("C:C").Insert.
Use .Range("C8").Formula = "=IF(IFERROR(MATCH(A8,'ENG Employee List'!B:B,0)>0,FALSE),""Authorised Resource"",""Unauthorised Resource"")" to assign the formula directly to C8.
Set LastRow = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row so the last-row search is qualified by LoadWS.
Use If LastRow > 8 Then .Range("C8:C" & LastRow).FillDown. This avoids applying FillDown to the one-cell range C8:C8.
Use the intended array element if your macro requires a formula array
Use this option when your procedure stores several formulas in an array before placing them on the worksheet.
Fill an authorization formula down a column with WPS Spreadsheet
WPS Spreadsheet lets you enter the authorization-check formula in the first result cell and fill it through the used rows without manually copying each cell. It provides a familiar spreadsheet workspace for working with Excel-style formulas and worksheets.
- 1. Open the worksheet in WPS Spreadsheet: Open the workbook, then select the worksheet that contains the employee records.
- 2. Insert column C if required: Select the C column header, right-click it, and choose Insert to create the destination column.
- 3. Enter the formula in C8: Click cell C8, enter the authorization formula, and press Enter.
- 4. Select the destination range: Click C8 and drag to the final row that needs a result in column C.
- 5. Fill the formula down: On the Home tab, choose Fill > Down to copy the C8 formula through the selected cells.
Frequently Asked Questions
Why does my VBA formula array leave cell C8 blank?
Without Option Base 1, Dim Formulas(1) creates elements 0 and 1. If the formula is stored in Formulas(1), assign .Range("C8").Formula = Formulas(1) rather than assigning the array itself.
Why should I skip FillDown when LastRow equals 8?
When LastRow is 8, the target range is only C8:C8. Use If LastRow > 8 Then before FillDown so the macro fills only when there are rows below the starting formula cell.
How do I find the last used row on the LoadWS worksheet?
Use LastRow = LoadWS.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row, or use .Cells.Find inside a With LoadWS block.
Should worksheet ranges be qualified in a VBA macro?
Yes. Use references such as .Range("C8"), .Columns("C:C"), and .Cells within With LoadWS. Qualified references prevent the macro from acting on whichever worksheet happens to be active.
