+ Reply to Thread
Results 1 to 5 of 5

Range Concating

  1. 02-01-2016, 06:04 PM #1
    rbirch
    rbirch is offline
    Registered User
    Join Date
    01-17-2013
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    84

    Smile Range Concating

    I'm trying to create a macro where the program will look for "Mail" in an Excel's file column, create and save a document and loop through the process. This is my code so far. It errors out on this line...
    If wk1.Range("P" & "i") = "Mail" Then

    The problems seems to be the ("P" & "i")...The error is a method range failure...



    Please Login or Register  to view this content.
    Thanks for your help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-01-2016, 06:12 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Range Concating

    Your i variable does not need quotes around it. Just i inserts that value.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 02-01-2016, 06:19 PM #3
    Trebor76
    Trebor76 is offline
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,525

    Re: Range Concating

    Hi rbirch,

    Row variables have to be numeric where your using yours as a string. Though just changing this line of code...

    Please Login or Register  to view this content.
    ...to this will do the job:

    Please Login or Register  to view this content.
    I'd also change the i variable to long as variants are the most expensive type of variables in regards of memory.

    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
    Reply With QuoteRegister To Reply
  4. 02-01-2016, 07:24 PM #4
    rbirch
    rbirch is offline
    Registered User
    Join Date
    01-17-2013
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    84

    Re: Range Concating

    For those that stumble upon this thread. Here is my final solution.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 02-01-2016, 07:47 PM #5
    Trebor76
    Trebor76 is offline
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,525

    Re: Range Concating

    I would change this line of code...

    Please Login or Register  to view this content.
    ...to this:

    Please Login or Register  to view this content.
    As an integer variable can only have a positive value up to 32,767 so for large datasets you will get caught out. A long variable can have a positive value up to 2,147,483,647 - far more then the current maximum row number of 1,048,576.

    Regards,

    Robert
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repeat
    By cjtimmer in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-30-2015, 06:27 PM
  2. [SOLVED] Count number of occurances below a range, within a range, and below a range
    By maacmaac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2013, 11:36 PM
  3. Macro stores Active Sheet range but I need it to find a new range each time it runs
    By jaroesner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-13-2013, 08:00 AM
  4. [SOLVED] defining a range subset based on a primary range for use in Median and Mode functions
    By Araise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-06-2013, 06:39 PM
  5. send to range, popup box to input what the range should be each time/select range
    By lax2734 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2012, 01:37 AM
  6. [SOLVED] Copy range from multiple sheets, into a master sheet and moving over by variable col range
    By g1eagle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-23-2012, 01:36 PM
  7. Code to select range and open a user form is opening the wrong range
    By rrbest in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-05-2010, 12:34 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1