如果列和行是变量，则选择范围 [英] Selecting a range if column and rows are variables

问题描述

这是我以前的帖子在插入COUNTIF公式时您有保存该值的变量.

下面是解决方案.

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?

我已经尝试过这些方法

"=COUNTIF( & Columnz $1: & Columnz &2 ,& Columnz &2000)=0"

以及

"=COUNTIF( "& Columnz" $1: " & Columnz"2,& Columnz &2000)=0"

推荐答案

我想在上面的回答中加上一点，即OFFSET非常有用，特别是在循环时.
例如:

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

您还可以使您的VBA更具可读性，并通过使用本机Excel(R1C1)语法消除对变量公式"的需求.像

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.

最后，您可以使用RANGE(cell1，cell2)的双参数"版本:

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