lastrow = ActiveSheet.UsedRange.Rows.Count
For row = lastrow To 2 Step -1
Criteria1 = Cells(row, 7)
Criteria2 = Cells(row - 1, 7)
If Criteria1 <> Criteria2 Then Cells(row, 1).EntireRow.Insert
Next row
Cells.Select
Selection.Subtotal GroupBy:=6, Function:=xlCount, TotalList:=Array(6), _
Replace:=True, PageBreaks:=True, SummaryBelowData:=True
ActiveSheet.ResetAllPageBreaks
End Sub
Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1
Set ws = Sheets("Sheet1")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).row
title = "A1:C1"
titlerow = ws.Range(title).Cells(1).row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub
Please help me to solve this problem. uipath invokeVBA activity gets any variant we return from a function as a “COM Component/object” . So I am returning a collection from the macro. and invokeVBA gets it as a COM object(lets refer it as OBJECT) . So i need to convert/read it into a collection again. and i am unable to do it. i am trying this for a week now. Please help.
These are the excel i am trying get cell values collection and the uipath module that i have created.
Below please find the macro(vba).
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
For index=1 To Amount
Set rngX = Worksheets("Sheet1").Range("A1:EZ50").Find("Color 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
For Each cellAddress In CellArray
MsgBox "list populated " & cellAddress
Range(cellAddress).Value = "Color Name"
Next
Set findcellFunction= CellArray
End Function
@vvaidya franckly
what we are trying to do is returning a Collection/Array/List from VBA like this.
Function findcellFunction()As Collection
Dim CellArray
Set CellArray = New Collection
CellArray.Add "xyz"
CellArray.Add "abc"
Set findcellFunction = CellArray
End Function
and converting that via uipath into a collection/array/List again like this.
So when VBA is returning the collection/Array/List it retrieves it as a System._COMObject .
So when we try to convert into collection again via CType method…it gives following error. @lasithdilshan20
Anil_G
Yoichi
ashokkarale
mkankatala
sharazkm32
afna
rkelchuri
naveen.s
dutta.marina
Jon_Smith