Type Mismatch, dynamic string array.

[jamesblack90]

Hey all, I'm sure this is something really simple so apologies - first time using anything other than recorded macros to change which worksheet you're on >.<

I am getting a type mismatch error on the

errmessage(errcount) = xxxx

lines

I have tried declaring errmessage() as variant and as string and neither seem to work. Any thoughts?

Private Sub check_errors()


Dim errFlag As Boolean
Dim errCount As Integer
Dim errMessage() As Variant
Dim i As Integer, j As Integer


errCount = 0


    If Not Range("name_c1").Value = "" And (Range("status_c1").Value = "" Or Range("deps_c1").Value = "") Then
        errFlag = True
        errMessage(errCount) = "Please fill out all fields for " + Split(Range("name_c1").Value) + "."
        errCount = errCount + 1
    End If
    
    If Not Range("name_c2").Value = "" And (Range("status_c2").Value = "" Or Range("deps_c2").Value = "") Then
        errFlag = True
        errMessage(errCount) = "Please fill out all fields for " + Split(Range("name_c2").Value) + "."
        errCount = errCount + 1
    End If
    
    If Not Range("name_c3").Value = "" And (Range("status_c3").Value = "" Or Range("deps_c3").Value = "") Then
        errFlag = True
        errMessage(errCount) = "Please fill out all fields for " + Split(Range("name_c3").Value) + "."
        errCount = errCount + 1
    End If
    
    If Not Range("name_c4").Value = "" And (Range("status_c4").Value = "" Or Range("deps_c4").Value = "") Then
        errFlag = True
        errMessage(errCount) = "Please fill out all fields for " + Split(Range("name_c4").Value) + "."
        errCount = errCount + 1
    End If


    If errFlag = True Then 
        Range("C29").Value = "Errors:"
    
        For i = LBound(errMessage) To UBound(errMessage)
            j = i + 29
            Cells(j, 3).Value = "'- " + errMessage(i)
        Next i
   End If
 
End Sub

Thanks in advance

 

[JoeMo]

That array has to be dimensioned before you use it. So, prior to use you need something like this:

Redim errMessage(1 to errcount+1)

 

[jamesblack90]

Oh ofcourse it does! Thanks Joe! I tried adding
ReDim Preserve errMessage(0 To errCount + 1)
Above the affected lines but I'm still getting the type mismatch error.

 

[JoeMo]

Oh ofcourse it does! Thanks Joe! I tried adding
ReDim Preserve errMessage(0 To errCount + 1)
Above the affected lines but I'm still getting the type mismatch error.

In the assignment of errmessage elements change the + signs to & (see below in red font)

errMessage(errCount) = "Please fill out all fields for " + Split(Range("name_c1").Value) + "."

 

[jamesblack90]

If only

 

[jamesblack90]

I still haven't figured this out :'(

Any ideas anyone?