Solved

Concatenate in vba give "type mismatch" error

Posted on 2014-11-26
6
212 Views
Last Modified: 2014-11-26
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.
0
Comment
Question by:MikeOM_DBA
  • 4
  • 2
6 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 40467602
Do any of the cells you want to concatenate contain error values?
0
 
LVL 29

Author Comment

by:MikeOM_DBA
ID: 40467626
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

0
 
LVL 29

Author Comment

by:MikeOM_DBA
ID: 40467638
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.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 29

Author Comment

by:MikeOM_DBA
ID: 40468196
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...
0
 
LVL 33

Expert Comment

by:Norie
ID: 40467666
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

0
 
LVL 29

Author Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question