首页 文章

问题使用VBA功能创建自动填充宏

提问于
浏览 次
0

我在创建一个自动填充名为“FindMyOrderNumber”的VBA函数的宏时遇到问题 . 每次运行宏来自动填充“FindMyOrderNumber”时，只会填充列中的第一个单元格 .

此功能将在A列（A1）中查找订单号，并返回可找到的工作表名称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

我创建了这个宏，在单元格B1中输入我的VBA函数“= findmyordernumber（a1）”，然后在自动填充列B中输入 .

Sub AutofillVBAFunction()

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

运行此宏后，仅填充B1 .

对不起，如果这已经讨论过，我是新人，我尝试了How to fill-up cells within a Excel worksheet from a VBA function?和其他问题，我无法将其应用于我的问题 .

请帮忙

excel vba excel-vba

1 回答

  • 0

    将application.volatile添加到函数中，这将随着工作表的更改而计算 .

    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

    将公式添加到范围时计算工作表也不会有什么坏处 .

    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
    回复于

相关问题