If I read you right then you are creating for each matching row a long
string of each of the array elements that then populates a cell. Have you
considered using a Collection object (look up in vba help), this will throw
an error if you try to add a duplicate item. So just ignore the error.
Then read out the collection directly into your worksheet. Something
like......

Dim YourData As New Collection, YourString As String

' build your string here

On Error Resume Next
YourData.Add YourString, CStr(YourString)

' read out collection
For Each Item In YourData
'populate sheet with values
Next Item
--
Cheers
Nigel



"KR" <nospam@nospam.com> wrote in message
news:%23Kz%23ybtNFHA.4052@TK2MSFTNGP12.phx.gbl...
> I am cycling through some larger multidimensional arrays to find records
> that match certain criteria. When I find a match, I copy a row of data to
a
> smaller array, which ultimately populates a row in my spreadsheet (after I
> find all the matches, I add them to the target cell with a chr(10) between
> each, to put each match on a new "row" within the cell).
>
> I've run into a problem with duplicate matches, which results in my row
> having duplicate data in it. I think I know how to address this, I'm just
> not sure of the syntax to make it work.
>
> Assuming I will never have more than 10 good (non-duplicate) matches, and
I
> do have a unique field to compare, I was thinking of adding a new
> one-dimensional array and clearing it between excel rows. Each time I find
> an entry that matches my criteria, I would just use the match function to
> see if that value was already in the one dimensional array, and if so,
skip
> to the next match (if it isn't in the 1-D array, add it as normal).
>
> So I've put some code below, but it returns a position or N/A. What is the
> best way to translate that into a boolean so I can use it in my IF
> statement?
>
> '--------------------------------------------------------------
> Dim CompareArray(1 to 10)
> Dim CompareItem as String
> Dim FoundDuplicate as Boolean
>
> 'do stuff until a match is found
>
> FoundMatch = Excel.worksheetfunction.Match(CompareItem, CompareArray,0)
>
> 'how do I translate FoundMatch (integer or N/A) into FoundDuplicate
Boolean?
>
> If FoundDuplicate = False then
> 'add it to my larger data array
> End if
> '---------------------------------------------------------------
>
>
> --
> The enclosed questions or comments are entirely mine and don't represent
the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>