array output to cell range

[miek]

I have three arrays
one array is a range array used as in input to my function
the second array has elements that i want to sent each element to a range
array (the third array)

workseet cell formula: =Count_duplicates(A2:A10, C2:C10)

VBA function:

Function Count_duplicates(ArrayIn, PutDupWhereArray) As Variant
Dim Num_Dups As Long
Dim Num_Blanks As Long
Dim ArrayItems_Duplicates() As Variant ' an array to put duplicates found in
'
Num_Dups = 0
ReDim ArrayItems_Duplicates(0)
'
For Each Element In ArrayIn
If Element > 1 Then
ReDim Preserve ArrayItems_Duplicates(Num_Dups)
ArrayItems_Duplicates(Num_Dups) = Element
Num_Dups = Num_Dups + 1
End If
Next Element
Num_Dups = Num_Dups / 2 ' div by to to get actual duplicate pairs
For Each Element In ArrayItems_Duplicates

'Code that takes each element in array and puts it in
' PutDupWhereArray range array i.e. C2:C10

Next Element
Count_duplicates = Num_Dups
End Function

Thanks for any help

 

[Mike H]

Miek,

A function cannot change any cell except the one it is called from so you
can't write the output from your function to a cell range.

Mike

 

[Joel]

VBA functions will work like a standard array formula functionfunction using
Shft-Cntl-Enter. There are two things you must do.

1) The array returned must start with index = 0
2) You must copy the formula into all the cells where the data is going.
then while all the cells are selected press Enter. Usually I put the formula
in the fist cell. then copy the formula to all the destination cells and
then press enter. The worksheet won't automatically determine the array size
that gets returned.


This code works on my pc using 2003.

Function Get_duplicates(ArrayIn As Range, _
PutDupWhereArray As Range) As Variant
Dim Num_Dups As Long
Dim Num_Blanks As Long
Dim ArrayItems_Duplicates() As Variant ' an array to put duplicates found in
'
Num_Dups = 0
'

For Each Element In ArrayIn
Set c = PutDupWhereArray.Find(what:=Element, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
ReDim Preserve ArrayItems_Duplicates(0 To Num_Dups)
ArrayItems_Duplicates(Num_Dups) = Element
Num_Dups = Num_Dups + 1
End If
Next Element
Get_duplicates = ArrayItems_Duplicates
End Function

 

[Eddy Stan]

Hi

I am happy someone asked this question...

I have a range(b5:c400) and i want an out range(f5:h400)
to show range b, c with count of duplicates.
Ofcourse the output range will be less than 401 items

thanks

 

[Joel]

I made the reults larger than the actual returned data and the function
returned zeros in the locations. I can modify the function to return blank
cells instead of the zeroes by filling the unsued cells with blanks. Don't
blame me for inconsistancy with excel. Th eUDF should behave exactly like
the standard aray formulas on the worksheet, but it doesn't. Let microsft
know of these problems. But I don't think microsoft is interested in fixing
problems.

 

[Eddy Stan]

hi thank youuuuuuuuuuuuuuu.
good suggestion. Microsoft must fix problem.
I suggest MS must think of updating excel 2003 prof version with extra
ordinary vb help file, which should prompt atleast 3 codes, which are error
sensitve code suggestions. Just like function handtips, i wish macro code
handtips.