Issue Creating Autofill Macro with a VBA Function

Question

I am having an issue creating a macro that will autofill a VBA function named "FindMyOrderNumber". Every time I run a macro to Autofill "FindMyOrderNumber" only the first cell in the column is populated.

This function will look up an order number in column A (A1) and return the name of the worksheet it can be found B (B1).

Option Explicit
Function FindMyOrderNumber(strOrder As String) As String

    Dim ws As Worksheet
    Dim rng As Range

    For Each ws In Worksheets
        If ws.CodeName <> "Sheet3" Then
            Set rng = Nothing
            On Error Resume Next
                Set rng = ws.Cells.Find(What:=strOrder, LookAt:=xlWhole)
            On Error GoTo 0
            If Not rng Is Nothing Then
                FindMyOrderNumber = ws.Name
                Exit For
            End If
        End If
    Next

    Set rng = Nothing
    Set ws = Nothing

End Function

I created this macro to enter my VBA function "=findmyordernumber(a1)" in cell B1 then to Autofill column B.

Sub AutofillVBAFunction()

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=FindMyOrderNumber(RC[-1])"
    Selection.Autofill Destination:=Range("B1:B68")
    Range("B1:B68").Select
End Sub

After I run this macro only B1 is populated.

Sorry if this has been discussed I am new and I tried How to fill-up cells within a Excel worksheet from a VBA function? and other questions and I could not apply it to my issue.

Please help

@Davesexcel Sounds like an answer to me, if you can also explain why their current code isn't working that would make an excellent one at that. — Aiken

Davesexcel Davesexcel · Accepted Answer · 2015-02-19T13:03:21

Add application.volatile to the function, that way it will calculate as the sheet changes.

Function FindMyOrderNumber(strOrder As String) As String

    Dim ws As Worksheet
    Dim rng As Range
    Application.Volatile
    For Each ws In Worksheets
        If ws.CodeName <> "Sheet3" Then
            Set rng = Nothing
            On Error Resume Next
            Set rng = ws.Cells.Find(What:=strOrder, LookAt:=xlWhole)
            On Error GoTo 0
            If Not rng Is Nothing Then
                FindMyOrderNumber = ws.Name
                Exit For
            End If
        End If
    Next

    Set rng = Nothing
    Set ws = Nothing

End Function

It also wouldn't hurt to calculate the sheet when You add the formula to the range.

Sub Button1_Click()
    Dim Rws As Long, Rng As Range
    Rws = Cells(Rows.Count, "A").End(xlUp).Row
    Set Rng = Range(Cells(1, 2), Cells(Rws, 2))
    Rng = "=FindMyOrderNumber(RC[-1])"
End Sub

Issue Creating Autofill Macro with a VBA Function

1 Answers