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)