I have these entries in my VBA instead of using WorksheetFunction as I couldn't work out how to get it to work...

If possible, I would love to have at least the first two lines replaced with WorksheetFunction. Any help would be very much appreciated. Thanks in advance!

Application.Goto Reference:="'Handling Units'!HUNUM01"

ActiveCell.FormulaR1C1 = "=IFERROR((WHNUMB&REPT(0,14-LEN(STARTNUMB))&STARTNUMB (RC[-1]-1)),"""")"

Selection.AutoFill Destination:=Range("B5:B" & Range("A" & Rows.Count).End(xlUp).Row)

CodePudding user response：

Make sure every object that is located in a worksheet (like Range, Cells, Rows, Columns etc) are referenced to a worksheet!

Therefore using Worksheets("Handling Units").Range("HUNUM01") is more straight forward than using Range("'Handling Units'!HUNUM01"). In your code the ranges in Destination:= Range("B5:B" & Range("A" & Rows.Count).End(xlUp).Row) have no sheet specified so this code might fail if Handling Units is not the active sheet. To avoid this specify a worksheet for all objects located in a worksheet. Do not let Excel guess, it might guess wrong!

With ThisWorkbook.Worksheets("Handling Units")
    .Range("HUNUM01").FormulaR1C1 = "=IFERROR((WHNUMB&REPT(0,14-LEN(STARTNUMB))&STARTNUMB (RC[-1]-1)),"""")"
    .Range("HUNUM01").AutoFill Destination:= .Range("B5:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With

Note that the leading dot in .Range makes this Range using the worksheet of the With statemant. Make sure it is there otherwise it does NOT use the With statement even if it is inside the With block.

CodePudding user response：

I ended up just going with this, seems simple enough and more effective than what I had before.

Range("'Handling Units'!HUNUM01").FormulaR1C1 = "=IFERROR((WHNUMB&REPT(0,14-LEN(STARTNUMB))&STARTNUMB (RC[-1]-1)),"""")"
Range("'Handling Units'!HUNUM01").AutoFill Destination:= Range("B5:B" & Range("A" & Rows.Count).End(xlUp).Row)