VBA 运行时错误 1004

VBA runtime error 1004

提问by user2539552

I'm a little new to VBA and am getting a runtime error that I can't solve. I have the following code

我对 VBA 有点陌生，并且遇到了我无法解决的运行时错误。我有以下代码

Dim vRange As Range
Sheets("Vert E").Cells.FormatConditions.Delete
With Sheets("Vert E")
    Set vRange = .Range(.Cells(2, 2))
End With

vRange.Select

The last line, vRange.Select is throwing an error that says Run-time error '1004': Application-defined or object-defined error. Any ideas?

最后一行，vRange.Select 抛出一个错误，指出运行时错误“1004”：应用程序定义或对象定义错误。有任何想法吗？

回答by Cor_Blimey

A couple of things:

几件事：

1) Remove .Range(_) around .Cells(2,2): it is unnecessary

1) 移除 .Range(_) 周围 .Cells(2,2): 没必要

2) Move the With further up

2) 将 With 进一步向上移动

3) Range.Select can only be used if the Range's worksheet is the active sheet. So to confirm it, add a like .Activate above vRange.Select, and move the End With to below vRange.Select.

3) Range.Select 只有在 Range 的工作表是活动表时才能使用。所以要确认它，在 vRange.Select 上方添加一个类似 .Activate，并将 End With 移动到 vRange.Select 下方。

Like:

喜欢：

Dim vRange As Range
With Sheets("Vert E")
    .Cells.FormatConditions.Delete
    Set vRange = .Cells(2, 2)
    .Activate
    vRange.Select
End With

Hope that helps

希望有帮助

回答by Cor_Blimey

Shorter and neater way of doing it

更短更简洁的方法

 Dim vRange As Range
 Dim ws as Worksheet
 Set ws = Sheets("Vert E")
 ws.Cells.FormatConditions.Delete
 Set vRange = ws.Cells(2, 2)
 ws.Activate
 vRange.Select

note: avoid using Select

注意：避免使用Select

Update:

更新：

To apply some formatting to a cell without Selecting it use

要将某些格式应用于单元格而不选择它，请使用

below example changes the color of the cell

下面的示例更改单元格的颜色

vRange.Interior.Color = RGB(200, 100, 50)

In VBE just type vRange.Interior.and make use of the VBE Intellisense which should help you a bit by listing the available properties of the Interior class.

在 VBE 中，只需键入vRange.Interior.并使用 VBE Intellisense，它应该可以通过列出 Interior 类的可用属性来帮助您。

The best way to apply formatting to a cell is to do it while recording a macroand then editing the code in the VBE.

将格式应用于单元格的最佳方法是在录制宏的同时进行，然后在 VBE 中编辑代码。