CODEWITHSUNDEEP
vbaexcel
Sylphie
Sylphie

Reputation: 85

VBA - Why is adding a collection to my loop giving me a type mismatch error?

I had a really really long, but functional, macro to change cell background colors based on variable thresholds. I thought, there's a 0% chance this is the best way to do this, so I tried to shove some of my variables (the ones for column number) into a collection and cycle through them. I won't make you suffer through the hundreds of lines of the original, but a brief section to illustrate:

For i = 14 To 43 

        If Cells(i, rc) = AMER Then

                If Cells(i, NU_C) <= (Cells(AM_R, NU_C) - pct) Then
                    Cells(i, NU_C).Interior.Color = RGB(242, 220, 219)
                Else

                    If Cells(i, NU_C).Interior.Color = RGB(242, 220, 219) Then
                    Cells(i, NU_C).Interior.ColorIndex = 0
                    End If

                End If

                If Cells(i, FW_C) <= (Cells(AM_R, FW_C) - pct) Then
                    Cells(i, FW_C).Interior.Color = RGB(242, 220, 219)
                Else

                    If Cells(i, FW_C).Interior.Color = RGB(242, 220, 219) Then
                    Cells(i, FW_C).Interior.ColorIndex = 0
                    End If

                End If

That worked fine, except the tears I cried whenever I had to change anything across all eighteen individual If sections. So here's the same part when I tried rewriting it with a collection:

For i = 14 To 43

                If Cells(i, rc) = AMER Then

                    For Each v In C

                        If Cells(i, v) <= (Cells(AM_R, v) - pct) Then
                            Cells(i, v).Interior.Color = RGB(242, 220, 219)
                        Else

                            If Cells(i, v).Interior.Color = RGB(242, 220, 219) Then
                            Cells(i, v).Interior.ColorIndex = 0
                            End If

                        End If

                    Next v

I'll put the full new code at the bottom but anyway, when I try to run it I get an error 13 type mismatch, which highlights this line: 

If Cells(i, v) <= (Cells(AM_R, v) - pct) Then

I'm assuming it's just highlighting that one because it's first, and that it's a more fundamental error than just a typo. I tried using this page to find what's wrong, but it doesn't seem to have my error. The method listed, of dropping the two sides into the Watch window, shows both Cells(i, v) and (Cells(AM_R, v) - pct) as "Variant/Integer" types.

The full code, which is still long but nowhere near as long as it once was:

Sub Test()

Dim i As Integer
Dim rc As Integer
Dim pct As Range

rc = 3 'column


Set pct = Range("AA6")

'Columns
Dim NU_C As Integer
Dim FW_C As Integer
Dim BI_C As Integer
Dim B_C As Integer
Dim DB_C As Integer
Dim TL_C As Integer

NU_C = 9
FW_C = 12
BI_C = 15
B_C = 18
DB_C = 21
TL_C = 24

'Rows
Dim AM_R As Integer
Dim EM_R As Integer
Dim AS_R As Integer

AM_R = 6
EM_R = 7
AS_R = 8

'Regions
Dim AMER As Range
Dim EMEA As Range
Dim ASIA As Range
Set AMER = Cells(AM_R, rc)
Set EMEA = Cells(EM_R, rc)
Set ASIA = Cells(AS_R, rc)


Application.ScreenUpdating = False

Dim C As Collection

Set C = New Collection
C.Add "NU_C"
C.Add "FW_C"
C.Add "BI_C"
C.Add "B_C"
C.Add "DB_C"
C.Add "TL_C"

Dim v As Variant

        For i = 14 To 43

                If Cells(i, rc) = AMER Then

                    For Each v In C

                        If Cells(i, v) <= (Cells(AM_R, v) - pct) Then
                            Cells(i, v).Interior.Color = RGB(242, 220, 219)
                        Else

                            If Cells(i, v).Interior.Color = RGB(242, 220, 219) Then
                            Cells(i, v).Interior.ColorIndex = 0
                            End If

                        End If

                    Next v

                ElseIf Cells(i, rc) = EMEA Then

                    For Each v In C

                        If Cells(i, v) <= (Cells(EM_R, v) - pct) Then
                            Cells(i, v).Interior.Color = RGB(242, 220, 219)
                        Else

                            If Cells(i, v).Interior.Color = RGB(242, 220, 219) Then
                            Cells(i, v).Interior.ColorIndex = 0
                            End If

                        End If

                    Next v

            ElseIf Cells(i, rc) = ASIA Then

                    For Each v In C

                        If Cells(i, v) <= (-pct) Then
                            Cells(i, v).Interior.Color = RGB(242, 220, 219)
                        Else

                            If Cells(i, v).Interior.Color = RGB(242, 220, 219) Then
                            Cells(i, v).Interior.ColorIndex = 0
                            End If

                        End If

                    Next v

            End If

        Next i


End Sub

Could someone please explain to me what I'm missing? I'd really appreciate it! Also, as an aside, am I asking too many questions on this site? I don't want to take advantage and it's not like I can give back by answering questions because I'm not a programmer and I don't know what I'm doing :\

Upvotes: 1

Views: 377

Answers (2)

Rory
Rory

Reputation: 34045

You're adding string literals to your collection, rather than the variables you intended. You should use:

Set C = New Collection
C.Add NU_C
C.Add FW_C
C.Add BI_C
C.Add B_C
C.Add DB_C
C.Add TL_C

Although you don't really need the variables at that point! You could also use an array:

Dim C
C = Array(9, 12, 15, 18, 21, 24)
For each v in C

Upvotes: 1

nagarajannd
nagarajannd

Reputation: 715

You're appending strings into the collection and later referring the same in Cells method.

While adding values to the collection

Set C = New Collection
C.Add "NU_C"
C.Add "FW_C"
C.Add "BI_C"
C.Add "B_C"
C.Add "DB_C"
C.Add "TL_C"

add actual values as

    Set C = New Collection
    C.Add 9
    C.Add 12
    C.Add 15
    C.Add 18
    C.Add 21
    C.Add 24

Hopefully this helps - Thanks

Upvotes: 0

Related Questions