+ Reply to Thread
Results 1 to 10 of 10

Type Mismatch when switching from single cell Range value to multiple cell range value

  1. #1
    Registered User
    Join Date
    12-05-2013
    Location
    Where Am I
    MS-Off Ver
    Excel 2010
    Posts
    23

    Type Mismatch when switching from single cell Range value to multiple cell range value

    Went from a single cell --> Range("B" & r) which was working fine, to multiple cell --> Range("B" & r & ":D" & r) and now getting a type mismatch error. Not sure why... Your help is appreciated!

    Please Login or Register  to view this content.
    Last edited by Mr_Ekid; 07-17-2015 at 03:43 PM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676

    Re: Type Mismatch when switching from single cell Range value to multiple cell range value

    Hi, Mr E,

    Please take a few minutes to re-read the forum rules, and then edit your post to add CODE tags.

    Thanks.
    Last edited by shg; 07-09-2015 at 01:50 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-05-2013
    Location
    Where Am I
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Type Mismatch when switching from single cell Range value to multiple cell range value

    Fixed, apologies.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676

    Re: Type Mismatch when switching from single cell Range value to multiple cell range value

    Thank you.

    Maybe (untested):
    Please Login or Register  to view this content.
    Last edited by shg; 07-09-2015 at 02:04 PM.

  5. #5
    Registered User
    Join Date
    12-05-2013
    Location
    Where Am I
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Type Mismatch when switching from single cell Range value to multiple cell range value

    This copies the first row in all rows down to iRow. Upon looping to a row with data entered, it should not replace existing data. Thoughts?

    I created a work around by adding three separate loops for each column, but I figured there should be a way to execute this with less code. See below:

    Please Login or Register  to view this content.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676

    Re: Type Mismatch when switching from single cell Range value to multiple cell range value

    Thoughts?
    Yes -- explain clearly in words and sentences what you have and what you're trying to do.

    A workbook with before and after examples would help.
    Last edited by shg; 07-16-2015 at 12:14 PM.

  7. #7
    Registered User
    Join Date
    12-05-2013
    Location
    Where Am I
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Type Mismatch when switching from single cell Range value to multiple cell range value

    Given: B:D column of first row in loop (row 6 in last code snippet) will always have contain data (Xb,Xc,Xd in example file).

    Run Loop, Repeat to last row: If current cell range ("B"Row:"D"row) has no content, then copy contents of row-1 (the above row) and paste into current range ("B"Row:"D"row). If cell range contains data, do nothing. Loop to next row.

    Per attached file: code will identify that row 6 is populated, do nothing, and move to row 7. Row 7 being empty, will have row 6 copy/pasted into row 7. Row 8 being empty will have row 7 copy/pasted into row 8, etc., until row 10 where the code will again, do nothing. Row 11 being empty,will have row 10 copy/pasted into row 11, etc.

    My original code accomplished only one column: (Col B)
    Please Login or Register  to view this content.
    I tried to add Col B:D by rewriting the following:
    Please Login or Register  to view this content.
    This gives me a type mismatch error. I wanted to understand why & to clean up code.
    Example file attached, without macros.

    BeforeAfter.xlsx

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676

    Re: Type Mismatch when switching from single cell Range value to multiple cell range value

    The power of an example:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-05-2013
    Location
    Where Am I
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Type Mismatch when switching from single cell Range value to multiple cell range value

    Thanks, thats works wonderfully and I will marked [Solved], but can you elaborate on why I got the type mismatch error. Just trying to improve my understanding and not just ask for a solution. Thanks again!!

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,676

    Re: Type Mismatch when switching from single cell Range value to multiple cell range value

    Because you can't compare an array to a single value:

    Please Login or Register  to view this content.

+ 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. Cell = Range("A1") Type mismatch
    By NicholasL in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-24-2014, 01:58 AM
  2. Type Mismatch error when calculating a set of functions on a multiple range
    By AnnaDana in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-20-2013, 02:27 PM
  3. Replies: 5
    Last Post: 07-27-2010, 10:58 AM
  4. Type mismatch using rnge as Range with Type 8 Input Box
    By STEVE BELL in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-03-2005, 01:10 AM
  5. [SOLVED] Type mismatch on range name
    By davegb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2005, 02:05 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