在Excel中引用单元格和工作表-ReferencestocellsandworksheetsinExcel

6  

Bottomline:

• When handling objects, use Set.

处理对象时,请使用Set。

• When handling primitive data (integers, longs, strings*, variants*) you do not use it.

处理原始数据(整数,长整数,字符串*,变体*)时,不要使用它。

Sheet, Workbook, Range, are objects. Therefore, you need to use Set when assigning them to variables.

工作表,工作簿,范围,是对象。因此,在将它们分配给变量时需要使用Set。

A Range.Value returns a Variant (that can be a long, a string, etc.) So, you cannot use Set.

Range.Value返回Variant(可以是long,字符串等)。因此,您不能使用Set。

==========================

Now, about your error message... I'd say then that maybe before in your code, Cell is being declared as object. Try use another variable name, or check the Cell variable type.

现在,关于你的错误信息...我当时可能会说,在你的代码之前,Cell被声明为对象。尝试使用另一个变量名,或检查Cell变量类型。

To check this, right click on it and then click in 'Definition'. Have it declared as Variant might fix the problem (be aware of side effects it might cause, though).

要检查这一点,请右键单击它,然后单击“定义”。将它声明为Variant可能会解决问题(但要注意它可能导致的副作用)。

==========================

*I know these types aren't 'primitive'; I used as an example here for the sake of explanation's cleanliness.

*我知道这些类型不是“原始的”;为了解释清洁,我在这里举了一个例子。

2  

As your assigning an object the line

作为您分配对象的行

Sheet = ActiveWorkbook.Worksheets("foobar") 

should read

Set Sheet = ActiveWorkbook.Worksheets("foobar")

1  

In your line

在你的行

Cell = ActiveWorkbook.Worksheets("foobar").Cells(i, 3).Value

you are trying to assign the value of cell (probably a string) to a cell. Take the .Value off and the assignment should work better. The Excel message you encountered is not the best: sometimes you get it when you assign variables of the wrong type.

您正在尝试将单元格的值(可能是字符串)分配给单元格。取下.Value,分配应该更好。您遇到的Excel消息不是最好的:有时您在分配错误类型的变量时会得到它。

However, the If Cell Like might not work. (Hint: the .Value has to get moved, not deleted.)

但是,If Cell Like可能不起作用。 (提示:.Value必须移动,而不是删除。)

0  

I just tried the following code and it works probably in your code make sure the ActiveWorkBook is actually the Active Work book you want(if you are working with multiple work books)

我刚刚尝试了以下代码,它可能在您的代码中工作,确保ActiveWorkBook实际上是您想要的Active Work书(如果您正在使用多个工作簿)

Sub test()
i = 3
If ActiveWorkbook.Worksheets("Sheet1").Cells(i, 2).Value Like ":" Then
    MsgBox "I found a colon!"
Else
    MsgBox "didn't find a colon!"
End If

Cell = ActiveWorkbook.Worksheets("Sheet1").Cells(i, 2).Value
'fails right here
If Cell Like ":" Then
    MsgBox "I found a colon!"
End If
End Sub

0  

Use Dim MyCell as Variant (or as string) since Cell is an existing object

使用Dim MyCell作为Variant(或作为字符串),因为Cell是现有对象