CODEWITHSUNDEEP
arraysexcelvbatype-mismatch
Jeypi
Jeypi

Reputation: 13

Array type mismatch

I have my listening history from Spotify on an Excel sheet. I'd like to extract all unique artists from that list and write them in a different column in the same Excel sheet.

Sub CountUniqueValues()

    Dim wb As Workbook
    Set wb = Workbooks("Spotify_Analysis_XYZ.xlsm")
    
    Dim ws As Worksheet
    Set ws1 = wb.Worksheets("Sheet1")
    
    Dim LastRow As Long
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    Dim UniqueArtists As Variant
    UniqueArtists = Evaluate("Unique(" & "B2:B" & LastRow & ",TRUE,FALSE)")
    ws1.Cells("2:40000", 8) = UniqueArtists
    
End Sub

Where all artists are stored in column B.

I get a type mismatch.

Upvotes: 1

Views: 148

Answers (2)

Gary's Student
Gary's Student

Reputation: 96791

This will work if you make sure the right worksheet of the right workbook is Active:

Sub uniqB2H()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    Range("H2").Formula2 = "=Unique(B2:B" & LastRow & ")"
    Range("H2#").Value = Range("H2#").Value
End Sub

EDIT#1:

  1. this approach avoids having to build a VBA array and transfer it to the worksheet
  2. in this demo, we assume only a single workbook and a worksheet with data that is already Active; thus none of the ranges need to be qualified
  3. in this demo Formula2 is used to deposit a dynamic spill-down formula in a cell
  4. in this demo we use Range("H2#") which specifies the full spill-down range.

Upvotes: 2

Darrell H
Darrell H

Reputation: 1886

Here's another way to make it work. Your last line was not a range.

Sub CountUniqueValues()

Dim t As Long

Dim wb As Workbook
Set wb = Workbooks("Spotify_Analysis_XYZ.xlsm")

Dim ws1 As Worksheet
Set ws1 = wb.Worksheets("Sheet1")

Dim LastRow As Long

LastRow = ws1.Cells(Rows.Count, "B").End(xlUp).Row

Dim UniqueArtists As Variant
UniqueArtists = Evaluate("Unique(Sheet1!B2:B" & LastRow & ",false,FALSE)")
t = UBound(UniqueArtists) - 1
ws1.Range(ws1.Cells(2, 8), ws1.Cells(2 + t, 8)) = UniqueArtists


End Sub

Upvotes: 1

Related Questions