2

This was my previous post Insert COUNTIF formula when you have variable holding 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 $1: & Columnz &2 ,& Columnz &2000)=0"

and these way 

"=COUNTIF( "& Columnz" $1: " & Columnz"2,& Columnz &2000)=0"
Community
  • 1
  • 1
niko
  • 9,285
  • 27
  • 84
  • 131

4 Answers4

6

To define a range, you can also use Cells, for instance:

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"
JMax
  • 26,109
  • 12
  • 69
  • 88
  • +1 Yeah, I had only recently seen this as well. For some reason i had it in my mind that when you use Cells() that both values had to be numeric. :p – Gaijinhunter Aug 26 '11 at 07:57
2

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
iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • 1
    *my 2 cents*: if you don't need the index inside the `for` loop, it is faster to use this kind of statement `For Each c In Range("A1:A10")` because then, Excel doesn't have to interpret the `Range` on every loop – JMax Aug 25 '11 at 13:16
  • @Jmax: very right. I concentrated on the question an d missed that view. – iDevlop Aug 25 '11 at 13:18
1

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:

"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 & "$1" & ":" & columnz & "1,P2)=0"

Just remove the variable from the string and include the row in the other string literals.

Jacob
  • 41,721
  • 6
  • 79
  • 81
  • @Niko with number, do you mean a string literal (like: "1" or "2") or a numeric variable? – Jacob Aug 25 '11 at 12:14
  • It shows application defined error Dont know why Can you please change The P column also to variable in your Answer thats where Im doing mistake I think – niko Aug 25 '11 at 12:16
0

it can be somthing like that:

startRow = 3
endRow = 17

myRange=("B" & StartRow & ":" & "B" & EndRow)
then your range = ("B3":"B17")

Rtronic
  • 635
  • 5
  • 6