vba 如果列和行是变量，则选择范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7189978/
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
Selecting a range if column and rows are variables
提问by niko
This was my previous post Insert COUNTIF formula when you have variable holding the value.
这是我之前的帖子Insert COUNTIF formula when you have variable holder the value。
Below was the Solution.
下面是解决方案。
Range("Q" & minRow + 1).Formula = "=COUNTIF(P$" & minRow & ":P" & minRow & ",P" & minRow + 1 & ")=0"
I have a new Question. What if the column is a variable?
我有一个新问题。如果列是变量怎么办？
What is the syntax if both are Variables (column and row are unknown and their values stored in a variable) and what is the syntax if column is variable and row is a number?
如果两者都是变量（列和行未知并且它们的值存储在变量中），则语法是什么？如果列是变量而行是数字，则语法是什么？
I have tried these ways
我试过这些方法
"=COUNTIF( & Columnz : & Columnz &2 ,& Columnz &2000)=0"
and these way
而这些方式
"=COUNTIF( "& Columnz" : " & Columnz"2,& Columnz &2000)=0"
回答by JMax
To define a range, you can also use Cells, for instance:
要定义范围，您还可以使用Cells，例如：
ActiveSheet.Cells(1,1) '=Range ("A1")
ActiveSheet.Cells(1,"A") '=Range ("A1")
If you want to define a range, you can do:
如果你想定义一个范围，你可以这样做：
Range(Cells(1,1), Cells(10,5)) '=Range("A1:E10")
Thus, you can do:
因此，您可以这样做：
'where Columnz is a Long or an Integer
"=COUNTIF(" & Range(Cells(1, Columnz), Cells(2, Columnz)).Address & "," & Cells(2000,Columnz).Address & ")=0"
回答by Patrick Honorez
I'd like to add to the nice above responses, that OFFSET is very usefull, specially while looping.
e.g.:
我想添加到上面很好的响应中，OFFSET 非常有用，特别是在循环时。
例如：
With Range("B3")
For i = 1 to 10
.offset(0, i) = "something"
Next i
End With
You can also make your VBA much more readable, and eliminate the need for "variable formulae" by using the native Excel (R1C1) syntax. Like
您还可以使您的 VBA 更具可读性，并通过使用本机 Excel (R1C1) 语法消除对“变量公式”的需要。喜欢
myRange.offset(0,i).FormulaR1C1 = "=SUM(R1C[-1]:RC[-1])"
which means sum from row 1 of previous column till same row of previous column.
这意味着从前一列的第 1 行到前一列的同一行的总和。
Finally, you can use the "dual arguments" version of RANGE(cell1, cell2):
最后，您可以使用 RANGE(cell1, cell2) 的“双参数”版本：
With Range("B3")
For i = 1 to 10
.offset(0, i).formula = "=SUM(" & Range(cells(10, 1),cells(10, i)).address & ")"
Next i
End With
回答by Jacob
The row number is already a variable in your example: minRow. String concatenation is done with an ampersand (&) in VB/A. You are halfway right but missing the second ampersand. You can think about it like this:
在您的示例中，行号已经是一个变量：minRow。&在 VB/A 中，字符串连接是用与号 ( )完成的。你说对了一半，但错过了第二个＆符号。你可以这样想：
"first string" & variable1
This is a concat between 2 strings, if you want to add a third string, you have to use another ampersand:
这是两个字符串之间的连接，如果要添加第三个字符串，则必须使用另一个＆符号：
"first string " & variable1 & "second string"
Your code:
您的代码：
"=COUNTIF(" & columnz & "$" & minRow & ":" & columnz & minRow & ",P" & (minRow + 1) & ")=0"
In response to your comments:
回应您的评论：
"=COUNTIF(" & columnz & "" & ":" & columnz & "1,P2)=0"
Just remove the variable from the string and include the row in the other string literals.
只需从字符串中删除变量并将该行包含在其他字符串文字中。
回答by Rtronic
it can be somthing like that:
startRow = 3
endRow = 17
myRange=("B" & StartRow & ":" & "B" & EndRow)
then your range = ("B3":"B17")
它可以是这样的：
startRow = 3
endRow = 17
myRange=("B" & StartRow & ":" & "B" & EndRow)
然后你的范围 = ("B3":"B17")
