+ Reply to Thread
Results 1 to 10 of 10

Concatenate macro Type Mismatch error

  1. #1
    Registered User
    Join Date
    10-31-2013
    Location
    Golden, CO
    MS-Off Ver
    Excel 2010
    Posts
    43

    Concatenate macro Type Mismatch error

    Newbie to macros and VBA here. I am attempting to concatenate several columns and I am getting the Type Mismatch error. I understand why I am getting this error, but I do not know any other way to accomplish what I am seeking. Any help would be greatly appreciated.


    Please Login or Register  to view this content.

    Thanks in advance,
    Ray

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Concatenate macro Type Mismatch error

    I think you forgot your double quotes.

    Please Login or Register  to view this content.

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,018

    Re: Concatenate macro Type Mismatch error

    VBA does not have an idea which " is the end of the string defining formula. Try:
    Please Login or Register  to view this content.
    note that I used formula not formulaR1C1 - not tested but could give other than expected result with R1
    Best Regards,

    Kaper

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Concatenate macro Type Mismatch error

    Hi - you are trying to use A1 style references for the R1C1 formula, you either need to change formular1c1 to formula or change the references from A1 to R1C1.

    You also need to double up quote marks. I would suggest:
    Please Login or Register  to view this content.

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,018

    Re: Concatenate macro Type Mismatch error

    Hi John and ragulduy,

    Is there any reason why you both added spaces at both sides of dash:
    Please Login or Register  to view this content.
    while in original there was only:
    Please Login or Register  to view this content.
    of course double quotes are needed (I added them too) but why spaces around?

  6. #6
    Registered User
    Join Date
    10-31-2013
    Location
    Golden, CO
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Concatenate macro Type Mismatch error

    Thank you fellas, they all worked. But it brought up another issue. I would like this macro to:
    1. Insert the concatenate formula only if cell AA is blank. This is a cumlative monthly file and I dont want to redo previous months, only perform on newly added data.
    2. If column R is blank, I would like the macro to skip that row entirely.

    Is this possible?

    Thanks in advance,
    Ray

  7. #7
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Concatenate macro Type Mismatch error

    If you copy the code in post 1 into the vba editor it will add the spaces, I guess it segragates the parts in quotation marks (i.e. "=concatenate(A1," from the dashes and spaces them apart from the "-" which it is reading as subtraction signs.

    You get the same thing if you type:
    a=1+2
    the VBA editor will adjust it to
    a = 1 + 2

    I hadn't noticed the change when copying back to the post.

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Concatenate macro Type Mismatch error

    Quote Originally Posted by Kaper View Post
    Hi John and ragulduy,

    Is there any reason why you both added spaces at both sides of dash:
    Please Login or Register  to view this content.
    while in original there was only:
    Please Login or Register  to view this content.
    of course double quotes are needed (I added them too) but why spaces around?
    No. The extra spaces were added when I pasted the code from the thread.

  9. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,018

    Re: Concatenate macro Type Mismatch error

    OK. I edited it in "Quick Reply" window so nothing happened "behind my back"

    As for Rays question:
    The simple, but a but time consuming (depends on your data size) way would be to insert formula row by row after checking insterion criteria.

    something like (red added/changed, blue - rewritten):

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    10-31-2013
    Location
    Golden, CO
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Concatenate macro Type Mismatch error

    Issue Solved. Even though its way above my level of understanding, it worked great Kaper!

    Thank you very much Kaper, JHD and Ragulduy for your help.

    Thanks,
    Ray

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Runtime Error 13 - Type Mismatch while running Macro
    By rrajnish in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-09-2013, 02:01 PM
  2. [SOLVED] Error 13 - Type Mismatch - Macro to delete lines
    By Marceltcm in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-18-2013, 05:41 PM
  3. Getting Type Mismatch, Runtime Error 13 With This Macro
    By HowdeeDoodee in forum Excel General
    Replies: 16
    Last Post: 11-13-2012, 07:59 AM
  4. Re: Help with a macro- 'Run-Time error '13' Type Mismatch
    By pman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2012, 03:31 PM
  5. [SOLVED] type mismatch error in Benford's law macro
    By Sody in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2006, 06:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1