语法在列和行是变量时选择范围 [英] Syntax In selecting a range if Column and rows are variables

问题描述

如果列是一个变量，我该怎么办？

这是我的上一篇文章

当您具有变量值时插入COUNTIF公式

以下是解决方案，但现在我有一个新的问题

  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屋！