Excel VBA: Type mismatch in incremental variable

Question

I am fairly new to VBA and i have been fighting with this tiny piece of code for over a day now so i turn to you, my knowledge superiors.

I have the following situation:

The end goal is to get 4 variables: ADR1, ADR2, ADR3 and ADR4. These variables should be created by an IF function that compares 4 sets of cells with one target cell (found in the IF and ELSIF parts).

When finding a match between these cells, I want to assign a certain cell value to the variable ADR1. Once the value is added to variable ADR1, the loop will start again to assign a value to ADR2 and so on until ADR4.

In order to create the four variables ADR1-4, i used a 'for I = 1 to 4' statement.

When i execute this code i get a 'type mismatch error' on line 'ADR(I) = Cells(1, "B").Value' so the first ELSIF part.

Could anyone of you explain to me why i am getting this mismatch and how it should be solved?

Sub variable_incrementation()

    'resetting variables
ADR1 = 0
ADR2 = 0
ADR3 = 0
ADR4 = 0
Dim N As Integer
N = 4

Dim ADR(1 To 4) As Long
Dim I As Long

For I = 1 To 4

Do

'N is supposed to increase by steps of four due to the target cells that contain the matching information

N = N + 4
    If Activesheet.Cells.(2, "A") = Activesheet.Cells.(N, "D") Or _
       Activesheet.Cells.(3, "A") = Activesheet.Cells.(N, "D") Then
     ADR(I) = Activesheet.Cells.(1, "A").Value

    ElseIf Activesheet.Cells.(2, "B") = Activesheet.Cells.(N, "D") Or _
           Activesheet.Cells.(3, "B") = Activesheet.Cells.(N, "D") Or _
           Activesheet.Cells.(4, "B") = Activesheet.Cells.(N, "D") Then
         ADR(I) = Activesheet.Cells.(1, "B").Value

    ElseIf Activesheet.Cells.(2, "C") = Activesheet.Cells.(N, "D") Or _
           Activesheet.Cells.(3, "C") = Activesheet.Cells.(N, "D") Or _
           Activesheet.Cells.(4, "C") = Activesheet.Cells.(N, "D") Or _
           Activesheet.Cells.(5, "C") = Activesheet.Cells.(N, "D") Then
         ADR(I) = Activesheet.Cells.(1, "C").Value

    ElseIf Activesheet.Cells.(2, "D") = Activesheet.Cells.(N, "D") Or _
           Activesheet.Cells.(3, "D") = Activesheet.Cells.(N, "D") Or _
           Activesheet.Cells.(4, "D") = Activesheet.Cells.(N, "D") Then
         ADR(I) = Activesheet.Cells.(1, "D").Value
    Else
    End If

    Loop Until (IsEmpty(ADR) = False) Or (N <= 43)
Next I


For J = 1 To 4
MsgBox (ADRJ)
Next

End Sub

Make sure the value on the ActiveSheet, since this is what you are searching without stating the parent of the range object in that cell is a number and not text or an error. — Scott Craner
Cells() method requires two integer values (rowindex and columnindex), why are you providing strings? — avb
@avb the column can be a string, VBA will parse it to the column number. — Scott Craner
@ScottCraner, really works, Excel will never stop suprising me :) — avb

Siddharth Rout Siddharth Rout · Accepted Answer · 2017-06-02T13:53:48

You have declared the array as Long

Dim ADR(1 To 4) As Long

And hence when you try to store a value which is not a number you will get the Type Mismatch error. You may want to declare it as a Variant or have additional checks to see if it is a number or not

Excel VBA: Type mismatch in incremental variable

1 Answers