Excel VBA - run-time error '1004: Select method of Range class failed

I have the following Excel VBA code,

我有以下Excel VBA代码,

Private Sub Worksheet_Calculate()
    myMacro
End Sub





Public Sub myMacro()


    If Range("C4").Value = "Sales" Then

            Range("E4:AB5").Select
Selection.NumberFormat = "$#,##0.0"


    Else

             Range("E4:AB5").Select
Selection.NumberFormat = "#,##0"

    End If

End Sub

My problem is the cell "C4" references from a drop down menu in a different sheet via a vlookup. Each time I get the following error,

我的问题是通过vlookup从不同工作表中的下拉菜单中引用单元格“C4”。每次我收到以下错误,

'run-time error '1004: Select method of Range class failed' and it does not work.

If the cell "C4" references from the same sheet it works, however I need it to reference from a different sheet.

如果单元格“C4”从相同的工作表引用它,但我需要它从不同的工作表引用。

1 个解决方案

#1

Your question isn't completely clear but if you want to look up C4 in a different sheet (I have used Sheet2 as an example), and then format the activesheet (no need to Select) you could try:

您的问题并不完全清楚,但如果您想在不同的工作表中查找C4(我使用Sheet2作为示例),然后格式化活动表(无需选择),您可以尝试:

Public Sub myMacro()
Dim ws As Worksheet
Set ws = Sheets("Sheet2")

If ws.Range("C4").Value = "Sales" Then
    Range("E4:AB5").NumberFormat = "$#,##0.0"
Else
    Range("E4:AB5").NumberFormat = "#,##0"
End If

End Sub