vba 如何将范围设置为变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18511810/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set range to variable?
提问by andysando
I'm trying to save a range from another sheet to a variable and then compare it to a cell. I have this code:
我正在尝试将另一个工作表的范围保存到变量，然后将其与单元格进行比较。我有这个代码：
Function collectUtfall(A1 As String, Ax As String)
Dim rng As Variant
Dim sum as Integer
sum = 0
Set rng = Sheets("Utfall").Range("N2").Value <------- This line
If rng = Ax Then
sum = sum + 10
Else: sum = 33
End If
collectUtfall = sum
End Function
The problem is that it's not acting the way I hoped. I get #Value error, and I have narrowed it down to the line marked in the code. If I remove that line I don't get the error but of course the result is only 0.
问题是它没有按照我希望的方式行事。我收到 #Value 错误，我已将其范围缩小到代码中标记的行。如果我删除该行，我不会收到错误，但结果当然只有 0。
I have tried to dim rng As Range also, doesn't work.
我也尝试将 rng As Range 调暗，但不起作用。
What can be the problem?
可能是什么问题？
EDIT: Original problem solved, but encountered another one.If I insted use this code (small changes) I get the same error again. Am I using offset wrong or where is the problem now?
编辑：原来的问题解决了，但遇到了另一个问题。如果我插入使用此代码（小的更改），我会再次收到相同的错误。我使用的偏移量是错误的还是现在问题出在哪里？
Function collectUtfall(A1 As String, Ax As String)
Dim rng As Variant
Dim sum As Integer
sum = 0
rng = Sheets("Utfall").Range("M2:O272").Value
If rng.Offset(0, 1) = Ax Then
sum = sum + 10
Else: sum = 33
End If
collectUtfall = sum
End Function
回答by Joe
"Set" is unneeded in Set rng =; remove it.
中不需要“设置” Set rng =；去掉它。
rng = Sheets("sheet1").Range("N2").Value
rng = Sheets("sheet1").Range("N2").Value
回答by Joe
There are several problems with your code.
您的代码有几个问题。
I recommend you start learning what Setis used for and when.
我建议您开始学习Set用于什么以及何时使用。
Difference between Dimand Set - provided by Doug Glancy
Also, check out what datatypes to chosefor your variables and why. See their limitations and example of usage.
此外，请查看为变量选择哪些数据类型以及原因。查看它们的限制和使用示例。
Find out how to work with Ranges. When to use .Value and when not. When you pointing a variable to a single cell and when to multiple cells.
了解如何使用 Ranges。何时使用 .Value，何时不使用。当您将变量指向单个单元格以及何时指向多个单元格时。
Learn how to loopover a Range collection.
了解如何遍历Range 集合。
Read a bit about Code Indentation.
阅读一些关于代码缩进的内容。
and after that you will be able to write your own:
之后，您将能够编写自己的：
Function collectUtfall(A1 As String, Ax As String)
Dim rng As Range
Dim sum As Long: sum = 0
Set rng = Sheets("Utfall").Range("M2:O272")
Dim cell As Range
For Each cell In rng
If StrComp(cell.Offset(0, 1).Text, Ax, vbTextCompare) = 0 Then
sum = sum + 10
Else
sum = 33
End If
Next
collectUtfall = sum
End Function
回答by Aaron Thomas
You may have conflicting datatypes. If Ax is type string, you should be comparing a string to it. You could define rng as string (dim rng as string), or do cstr:
您可能有冲突的数据类型。如果 Ax 是字符串类型，您应该将字符串与它进行比较。您可以将 rng 定义为字符串（将 rng 定义为字符串），或者执行 cstr：
If CStr(Rng) = Ax Then
