+ Reply to Thread
Results 1 to 7 of 7

Code not recognising last used column in row range

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Code not recognising last used column in row range

    Should be straightforward, but can't see why it's going wrong!

    As shown in sample attached.
    User receives new version of drawing (e.g. Version AS).
    Enters date received into next free column in Date Headers in row 6 (Cols D - S in the sample), finds the row for the Drawing Number (Row 7 in sample), and adds Version number in same Column as date.
    To add the new record to Database, enters Y in Col AD of row.
    With AD7 as the "active cell", Macro needs to search the "Date" range of the record (D7 - S7 in sample), "find" the last column in it with data (Col H in sample) and copy the date in row 6 to next free row in Database Col A.

    Problems:
    (a) Macro is not recognising Col H as the last "used" column in the "Date" range
    (b) Code copying date to Database generates a RunTime Error 1004

    Please Login or Register  to view this content.
    All solutions, suggestions and alternatives welcome as ever

    Ochimus

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Code not recognising last used column in row range

    This seems a non standard and unduly complicated way of capturing data in a database.

    Whenever I create this sort of application for a client I always make sure the data entry row has exactly the same column labels as the database and in the same order.
    This just makes things so much simpler and more obvious.

    If you do that all you need then is a single line of code to copy the data entry row to the next available database row, and then another line of code to clear the data entry row.

    Other things I tend to build in are a check function cell in Excel that identifies if all relevant cells are completed so that the macro can read the check cell and only copy and paste the new record if all is OK.
    I also generally insert a row at the top of the database to which I copy the new record. This puts the latest entries first which is often more useful.

    Your current macro is failing since you're not identifying the last drawing revision ref. Personally, if you stick to your current system I'd do an {end}{left} from column T to identify the last revision ref and.
    Even then your macro would need to copy A:C, then copy the revision ref and then copy T:AC.
    Last edited by Richard Buttrey; 09-27-2019 at 08:29 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Code not recognising last used column in row range

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Code not recognising last used column in row range

    Hi there,

    Take a look at the attached version of your workbook and see if it does what you need. It uses the following code:

    Please Login or Register  to view this content.
    The highlighted values may be changed to suit your own requirements.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Code not recognising last used column in row range

    Here's the approach I was suggesting. It uses the following macro

    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Code not recognising last used column in row range

    Many thanks to each for the prompt responses.

    I've gone with Richard's solution in finding the relevant Column because additional Date columns will be added as a Project continues, which will change the "range" of Date columns from D to whatever. So the Code can't "hard code" the final Column of potential dates.

    Revised Code now selects whichever cell in the row is in the column headed "Revision reviewed", then "Selection.End(xlToLeft).Select" finds the "new" Variation cell, and copies the data to the next free row on the Database.

    The original Code couldn't take the "standard" approach to copying data, because the Register records every Variation for a drawing in the same row, "overwriting" any comments and implications from the previous version.
    I created the Code I did because the User wanted an "audit trail" where he could select any Variation and see in the Register the comments and consequences of that Variation copied from the Database
    Hence the Database structure has to be different.

    Can now mark this as "solved", and thanks again.

    Ochimus

  7. #7
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Code not recognising last used column in row range

    Ok, thanks for your feedback.

    Greg M

+ 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. Replies: 1
    Last Post: 12-28-2016, 07:06 PM
  2. [SOLVED] Code not recognising "myfilepath" workbook as Source
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-20-2016, 07:31 PM
  3. [SOLVED] VBA code not recognising selected option button
    By Harribone in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-22-2015, 10:07 AM
  4. [SOLVED] Use the column Header to column letter code to set range in Macro
    By capson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2013, 11:06 AM
  5. Replies: 5
    Last Post: 02-18-2012, 04:40 AM
  6. IF statement recognising multipe entries in one column and applying result to group??
    By pedro1982 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-07-2012, 05:15 PM
  7. Excel 2007 not recognising code built in Excel 2003
    By GNelson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-23-2011, 09:50 AM

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