Excel VBA Count different Values from multiple Ranges

Question

I want to find out different values ( text ) in 3 different ranges. I think I found a way online, but it gives me an error.

Here are the codes;

Dim MyDict As Object, MyCols As Variant, OutCol As String, LastRowXY_2 As Long
Dim XY_A As Variant, I_XY As Long, MyData_XY As Variant

Set MyDict = CreateObject("Scripting.Dictionary")
MyCols = Array("P", "S", "V")
OutCol = "AN"

For Each XY_A In MyCols
    LastRowXY_2 = Worksheets(Ders_Sheet_Adi).Cells(Rows.Count, XY_A).End(xlUp).Row
    MyData_XY = Worksheets(Ders_Sheet_Adi).Range(XY_A & "22:" & XY_A & LastRowXY_2).Value
    For I_XY = 1 To UBound(MyData_XY)
        If MyData_XY(I_XY, 1) <> "" Then MyDict(MyData_XY(I_XY, 1)) = 1
    Next I_XY
Next XY_A

Worksheets(Ders_Sheet_Adi).Range(OutCol & "1").Resize(MyDict.Count, 1).Value = WorksheetFunction.Transpose(MyDict.keys)

At the second run of "XY_A", MyData_XY gives a "Type-Mismatch" error.

I can use any kind of idea.

check in debug mode in the second loop what is the value of LastRowXY_2 — Shai Rado
– Shai Rado, Commented Sep 25, 2017 at 12:51
so now debug and see what are you getting (in debug mode) for MyData_XY (Empty) — Shai Rado
– Shai Rado, Commented Sep 25, 2017 at 12:55
At the debug mode, "For I_XY = 1 To UBound(MyData_XY)" gives an error. MyData_XY gives a value, but this row gives an error. ( again type-mismatch ) // "UBound" gives the type-mismatch error. — Bildircin13
– Bildircin13, Commented Sep 25, 2017 at 14:29

Vityata · Accepted Answer · 2017-09-25 12:53:08Z

1

Option 1:

Change:

Worksheets(Ders_Sheet_Adi).Range(OutCol & "1").Resize(MyDict.Count, 1).Value = WorksheetFunction.Transpose(MyDict.keys)

to

Range(OutCol & "1").Resize(1, 1).Value = WorksheetFunction.Transpose(2)

and check whether it works now. If it does, you don't have any values in MyDict, thus, it throws an error.

Option 2: What do you think MyData_XY is? A range, an array? Try to call it like this -> MyData_XY(1)(1,1) instead of this ->MyData_XY(1,1)

answered Sep 25, 2017 at 12:53

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bildircin13 Over a year ago

Thx for answer. Unfortunately, first option didn't have any effect because I get the error before that line. And I couldn't understand the second option I'm afraid.

Vityata Over a year ago

@Bildircin13 - concerning option 2 - your array has arrays within it. You are referring it like this -> MyData_XY(1,1). You should refer it like this MyData(1)(1,1)

Collectives™ on Stack Overflow

Excel VBA Count different Values from multiple Ranges

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related