问如何在VBA中设置公式来赋值单元格值？
EN

Stack Overflow用户

提问于 2019-06-11 16:41:06

回答 4查看 217关注 0票数 0

我正在使用一个公式在Sheet1中设置一个单元格的值，该公式涉及来自Sheet1和Sheet2的单元格。有没有办法从VBA中设置这样的公式？

这是我所拥有的：

我将一些信息从B11填充到M11，一直填到第29行，比如每行填写一个表单。有时，它只有一行包含信息，也可能是所有包含数据的表。

在G列中，我有一个下拉列表，根据该下拉列表中的值，将填充相应的H单元格。

我设法为它设置了公式，但出于安全考虑，我想从VBA中设置它。

=IFERROR(VLOOKUP(G11;Data!B2:D13;3;FALSE);0)

excel

vba

腾讯云OCR文字识别特惠

文字识别限时抢购，热门产品低至14.9元

回答 4

Stack Overflow用户

回答已采纳

发布于 2019-06-11 17:02:02

您可以将公式保留在G列中，并保护该范围，这样用户就不能对其进行编辑。

或者，使用Worksheet_Change事件处理程序：

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim val as Variant
' In case the change is NOT in column G, then Exit.
If Intersect(Target, Columns("G")) Is Nothing Then Exit Sub
For Each cl in Intersect(Target, Columns("G"))
    val = Application.Vlookup(cl.Value, ThisWorkbook.Worksheets("Data").Range("B2:D13"), 3, False)
    If IsError(val) Then
        cl.Offset(0, 1).Value = 0
    Else
        cl.offset(0, 1).Value = val
    End If
Next
End Sub

如果需要对多个列执行相同的操作，则会稍微复杂一些，因为在给定表中只能有一个Worksheet_Change处理程序。所以你需要像下面这样修改它。这可以用来简化一些，以减少一些冗余，但这是另一个问题的练习:)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim val as Variant
' Handles column G
If Not Intersect(Target, Columns("G")) Is Nothing Then 

    For Each cl in Intersect(Target, Columns("G"))
        val = Application.Vlookup(cl.Value, ThisWorkbook.Worksheets("Data").Range("B2:D13"), 3, False)
        If IsError(val) Then
            cl.Offset(0, 1).Value = 0
        Else
            cl.offset(0, 1).Value = val
        End If
    Next
End If
If Not Intersect(Target, Columns("J") Is Nothing Then
    For Each cl in Intersect(Target, Columns("J"))
        val = { your formula used for populating column J }  '## UPDATE THIS WITH YOUR PROPER FORMULA/FUNCTION
        If IsError(val) Then
            cl.Offset(0, 3).Value = 0
        Else
            cl.offset(0, 3).Value = val
        End If
    Next
End Sub

票数 2

Stack Overflow用户

发布于 2019-06-11 16:59:19

如果您正确使用Ranges和参数，WorksheetFunction.Vlookup()会工作得很好：

Public Function SomeFormula() As Variant

    On Error GoTo SomeFormula_Error

    Dim result As Variant
    result = WorksheetFunction.VLookup(Range("G11"), Worksheets("Data").Range("B2:D13"), 3, False)
    SomeFormula = result

    On Error GoTo 0
    Exit Function

SomeFormula_Error:

    SomeFormula = 0

End Function

票数 2

Stack Overflow用户

发布于 2019-06-11 16:52:28

您可以使用宏记录器:转到功能区中的开发人员选项卡，然后记录宏，然后单击带有公式的单元格，然后单击功能区中的停止记录，然后打开VBA，excel将已经为您生成宏。

当我这样做时，我会得到以下结果：

    Sub Macro1()
        Range("A1").Select
    'I have entered my formula in cell A1
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(VLOOKUP(R[10]C[6],Data!R[1]C[1]:R[12]C[3],3,FALSE),0)"
    End Sub