I m trying to retrieve the cell addresses which contains a particular text. The VBA is returning a collection and the activity gets it as a COM_Object so far. But I do not understand how to convert it into a collection again. I have included an assign activity to convert it as a collection:
CType ( COMObject , collection)
But it gives a error saying it can not convert it into a collection again. So Would you please help me to read this output object?
> Function findcellFunction(ByAmount As Integer)As Collection
> 'On Error Resume Next
> Dim rngX As Range
> Dim WS As Worksheet
> Dim datax As Range
> Dim cellAddress As Variant
> Dim index As Integer
> Dim iTotal As Integer
> Dim CellArray
> iTotal = 0
> Set CellArray = New Collection
> 'Iterate until all cell values are found
> For index=1 To Amount
> Set rngX = Worksheets("rptBOMColorPrint").Range("A1:EZ50").Find("Colour Name", lookat:=xlPart)
> If Not rngX Is Nothing Then
> MsgBox "Found at " & rngX.Address
> CellArray.Add rngX.Address
> End If
> Cells(rngX.Row,rngX.Column).Delete
> iTotal =iTotal + index
> Next index
> 'shows list that has been populated with cell addresses
> For Each cellAddress In CellArray
> MsgBox "list populated " & cellAddress
> Range(cellAddress).Value = "Colour Name"
> Next
> Set findcellFunction= CellArray
> End Function
>
find cell address.xaml (9.7 KB)