Type Mismatch error stepping through an array

Question

I have the following code. I'm passing in a cell's value from column "d" as a string (a list of days of the week, could be one day or as many as all 7 days) into this function. I'm also using the fuction to fill an array of Integers. I'm getting a type mismatch at the indicated line and I can't figure out why. Especially since I Dim the array right before filling it and stepping through it. I'd appreciate any help/explaination. Option Explicit is turned on.

Public i as Integer

Public Function ParseColumnD(cellvalue as String) as String

Dim BREdaysString() as String
Dim daysAsInt() As Integer

BREdaysString() = Split(cellvalue, ", ")

For Each i in BREdaysString()
    If BREdaysString(i) = "Monday" Then           '<-----Type Mismatch error here.
        daysAsInt(i) = 4
    ElseIf BREdaysString(i) = "Tuesday" Then
        daysAsInt(i) = 5
    ElseIf BREdaysString(i) = "Wednesday" Then
        daysAsInt(i) = 6
    ElseIf BREdaysString(i) = "Thursday" Then
        daysAsInt(i) = 7
    ElseIf BREdaysString(i) = "Friday" Then
        daysAsInt(i) = 8
    ElseIf BREdaysString(i) = "Saturday" Then
        daysAsInt(i) = 9
    ElseIf BREdaysString(i) = "Sunday" Then
        daysAsInt(i) = 10
    End If
Next

'to check to make sure the conversion from days of the week to integers is correct 
'I take the int array and put it as the function's string, return it and put in another cell.

For Each i in daysAsInt()
   If i = 1 Then
      ParseColumnD = daysAsInt(i)
   ElseIf i > 1 Then
      ParseColumnD = ParseColumnD & ", " & daysAsInt(i)
   End If
Next
End Funciton

In the regular module I use the function like this...

Sub MySub()

Dim BREitems as Range
Dim lastrow as Long

lastrow = Cells(Rows.count, "a").End(xlUp).Row
Set BREitems = Range("a5:a" & lastrow)

For Each i in BREitems
    Cells(i.Row, "g").value = ParseColumnD(Cells(i.row, "d"))
Next
End Sub

BigBen · Accepted Answer · 2020-01-16T14:22:05.333

1

You want a For...Next loop here to iterate over the array, using LBound and Ubound, not a For Each loop.

For i = LBound(BREdaysString) to Ubound(BREdaysString)

A For Each loop would work if you did the following:

For Each i in BREdaysString()
     If i = "Monday" Then

Obviously using a better variable name then i, and Dimming as a String, as this is pretty confusing. But you can't use i as an index if you're using a For Each loop. That would be like trying to do If BREdaysString("Monday") = "Monday" Then

I don't see the point of i being Public here either. And just use Long instead of Integer .

edited Jan 16 '20 at 14:22

answered Jan 16 '20 at 14:16

BigBen

46,229
7
24
40

changing this loop worked great! thank you. i is a public variable because I use it all in loops all over this project. I don't want to have to declare it in every sub/function. I only included that here because I said option explicit is on and I wanted to make sure the example code declared all variables. – Chris H. Jan 17 '20 at 13:24
Having it `Public` is bad practice, IMO. – BigBen Jan 17 '20 at 13:30

Type Mismatch error stepping through an array

1 Answers1