#DIV/O! problems 1

[FunkMaster]

Hello Wonder if anyone can help me?
Right as soon in the code below i have got an script that sticks an distinct count in the cell z2 but if there is an problem. if only the title is there and no other rows it trys and divides by 0 which is not good.

Sheets("2.1.1&quot.Select
Value = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
Sheets("2.1.1&quot.Range("Z2&quot.FormulaArray = "=SUM(1/COUNTIF_(A2:A" & Value & ",A2:A" & Value & &quot)"

i have tryed converting it into an value (showen below) then using if statment to convert the value to 0 but of cousre VB changes automatically the #DIV/0! to the error 2007 and thats doing me nut in.

Sheets("2.1.1&quot.Range("Z2&quot.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ValueZ2 = ActiveSheet.Range("Z2&quot
ValueE = Err(2007)
If ValueZ2 = ValueE Then
Sheets("2.1.1&quot.Range("Z2&quot = "0"

any help is much appretioned (did i spell that right?)

cheers

5% is not an rise, its an poke in the eye.

 

[SkipVought]

Hi,
Use the following technique...

=IF(Value=2,"",SUM(...))

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[FunkMaster]

Not to be stupid or anything , what is that technique?

oh just an sec i get it i see you mean the first bit and have
Sheets("2.1.1&quot.Select
Value = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
=IF(Value=2,"",SUM(A2:A" & Value & ",A2:A" & Value & &quot)&quot)else
Sheets("2.1.1&quot.Range("Z2&quot.FormulaArray = "=SUM(1/COUNTIF_(A2:A" & Value & ",A2:A" & Value & &quot)"

and have that going on

okay that makes sense cool i will try that

cheers skip

Wayne
5% is not an rise, its an poke in the eye.