CODEWITHSUNDEEP
excelvbaexcel-formula
mrk777
mrk777

Reputation: 167

Array is not considering from the string created in VBA

For some reason, dynamic string created from the specific range is not considering in the array for loops. Below is my code:

Option Explicit
Dim AccrualFile As Workbook, AccrualSht As Worksheet
Dim AccrualFilePath As String
Dim UniqueNames As String, UniqueAccNames As String
Dim Lrows As Long, Lcols As Long


Sub Segregation()

AccrualFilePath = Application.GetOpenFilename(Title:="Please select Accrual Statement")

Set AccrualFile = Workbooks.Open(AccrualFilePath)
Set AccrualSht = AccrualFile.Sheets(1)

Lrows = AccrualSht.Range("B" & Rows.Count).End(xlUp).Row
UniqueNames = Application.WorksheetFunction.TextJoin(""""", """"", True, Application.WorksheetFunction.Unique(AccrualSht.Range("B2:B" & Lrows)))
UniqueAccNames = """""" & UniqueNames & """"""

Dim i As Long
Dim myarr() As Variant

myarr = Array(UniqueAccNames)



For i = LBound(myarr) To UBound(myarr)
    Debug.Print myarr(i)
Next

End Sub

Appreciate your help!!

Thank you.

Upvotes: 1

Views: 81

Answers (1)

BigBen
BigBen

Reputation: 50008

WorksheetFunction.Unique returns a (2D)1 array when passed a Range.

myarr = WorksheetFunction.Unique(AccrualSht.Range("B2:B" & Lrows))

Dim i As Long, j As Long
For i = LBound(myarr, 1) to Ubound(myarr, 1)
   For j = Lbound(myarr, 2) to Ubound(myarr, 2)
       Debug.Print myarr(i, j)
   Next
Next

1 With some exceptions, including:

  • When the output is a single element, it returns a 1D array.

Upvotes: 3

Related Questions