range object

[matrixknow]

The COUNTIF works, when I use a fixed range ("A1:A100"). The range should be a variable and I use an range object. However, it types it literaly "(range1, E3)" and not eg ("A1:A156") and the formula gives an error "#Name?".

 Set range1 = Sheet20.Range("E3:E" & LastRow)
   Sheet20.Range("D3:D" & LastRow).Formula = "=IF(COUNTIF(range1,E3)>1,""D"",""F"")"

How do I need to write this ?

 

[combo]

Use range1.Address (with parameter if necessary) to build the string.

combo

 

[xlbo]

As per Combo's reply, the FORMULA property takes a STRING so "range1" is interpreted literally. You must insert the address as part of the string:

"=IF(COUNTIF(" & range1.address & ",E3)>1,""D"",""F"")"


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question