语法在列和行是变量时选择范围 [英] Syntax In selecting a range if Column and rows are variables
问题描述
这是我的上一篇文章
以下是解决方案,但现在我有一个新的问题
Range(Q& minRow + 1).Formula == COUNTIF(P $& minRow&:P& minRow&,P& minRow + 1&)= 0
/ pre>
如果列是变量怎么办?
如果两个都是变量,那么语法是什么(I意思是当列和行是未知的,并且它们的值存储在变量中),如果列是可变的并且行是一个数字,语法是什么?
我已经尝试过这些方式
= COUNTIF(& Columnz $ 1:& Columnz& 2,& Columnz& 2000)= 0
这些方式
= COUNTIF(& Columnz $ 1:& Columnz2,&Columns& 2000)= 0
我无法使它出来
解决方案我想添加到上面的回应,OFFSET非常有用,特别是循环。 >
例如:带范围(B3)
对于i = 1到10
.offset(0,i)=something
Next i
结束
您还可以使您的VBA更易于阅读,并通过使用本机Excel(R1C1)语法来消除对变量公式的需求。像
myRange.offset(0,i).FormulaR1C1 == SUM(R1C [-1]:RC [-1 ])
这意味着从前一列的第1列到上一列的同一行的总和。最后,您可以使用RANGE(cell1,cell2)的双参数版本:
对于i = 1到10
.offset(0,i).formula == SUM(& Range(cells(10, 1),cells(10,i))。address&)
Next i
End with
What I have to do if column is a variable ?
This was my Previous Post
Insert COUNTIF formula when you have variable holding the value
Below was the Solution But Now I got a new Question
Range("Q" & minRow + 1).Formula = "=COUNTIF(P$" & minRow & ":P" & minRow & ",P" & minRow + 1 & ")=0"
What if the column is a variable?
What is the Syntax if both are Variables ( I mean when column and row are unknown and thier 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 $1: & Columnz &2 ,& Columnz &2000)=0"
and these way
"=COUNTIF( "& Columnz" $1: " & Columnz"2,& Columnz &2000)=0"
I am unable to make it out.
解决方案I'd like to add to the nice above responses, that OFFSET is very usefull, specially while looping.
e.g.: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
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.
Finally, you can use the "dual arguments" version of 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
这篇关于语法在列和行是变量时选择范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!