asked on

Concatenate in vba give "type mismatch" error

HI Guys,
Adding some VBA code to my spreadsheet, but cannot seem to make it work:

I'm trying to concatenate two cells like this:

    Dim eSpk(20), eAss(20), eLsn(20), eEvl(20) As String
    . . . 
                eSpk(i) = e0.Offset(0, 1).Value
                eAss(i) = e0.Offset(0, 2).Value
                eLsn(i) = "#" & e0.Offset(0, 3).Value & " " & e0.Offset(0, 4).Value
                eEvl(i) = e0.Offset(0, 5).Value
   . . .

Open in new window

And gives "Type" mismatch.
I'm sure you experts have the solution to this somewhat basic question.
Thanks.

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeOM_DBA

ASKER

No.
If I put the "sub" in it's own module, it works when executed by itself
But if I call from another module it breaks.
here is some more code:

. . .
    Sheets("EmailMM").Activate
    For Each e0 In ActiveSheet.Range("MMemail").Cells
        k = e0.Row
        If k > 1 And e0.Value <> "" Then
            eTo = eTo & e0.Value & ";"
            If e0.Offset(0, -5).Value <> "E" Then
                i = i + 1
                eSpk(i) = e0.Offset(0, 1).Value
                eAss(i) = e0.Offset(0, 2).Value
                eLsn(i) = "#" & e0.Offset(0, 3).Value & " " & e0.Offset(0, 4).Value
                eEvl(i) = e0.Offset(0, 5).Value
            End If
        End If
    Next e0

Open in new window

MikeOM_DBA

ASKER

Found it...

Put the procedure in separate module and call it from main module worked!

Spent almost 2 days debugging.

Thanks anyway for looking into it.

MikeOM_DBA

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for MikeOM_DBA's comment #a40467638
Assisted answer: 100 points for imnorie's comment #a40467602

for the following reason:

Did not find the cause, but now it works...

Norie

Glad you got it working.:)

What happens if you change the code to use an explicit sheet reference like this.

For Each e0 In Sheets("EmailMM").Range("MMemail").Cells

Open in new window

MikeOM_DBA

ASKER

PS: The cause was the relocation of a named range which had absolute ($xx) addressing which then invalidated dependent locations.
Thanks for the tip.

Concatenate in vba give &quot;type mismatch&quot; error

Concatenate in vba give "type mismatch" error