+ Reply to Thread
Results 1 to 23 of 23

offset not working

  1. #1
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    offset not working

    my code is not offsetting when its pasting the code to the destination it just overwrites what was there before this is my whole code for copying and pasting

    Please Login or Register  to view this content.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: offset not working

    I can't see where you use "nextrow" to set the DstRng.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    Quote Originally Posted by TMS View Post
    I can't see where you use "nextrow" to set the DstRng.


    Regards, TMS
    Please Login or Register  to view this content.
    thats the whole code

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: offset not working

    I'm confused by your code. At the beginning, you set DstRng based on column 1

    Please Login or Register  to view this content.
    Then, later, you determine nextrow based on column 18

    Please Login or Register  to view this content.

    and then you copy and paste the data (to the original DstRng)

    Please Login or Register  to view this content.

    I'm probably missing something, but I still don't see how your're using nextrow.

  5. #5
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    Quote Originally Posted by TMS View Post
    I'm confused by your code. At the beginning, you set DstRng based on column 1

    Please Login or Register  to view this content.
    Then, later, you determine nextrow based on column 18

    Please Login or Register  to view this content.

    and then you copy and paste the data (to the original DstRng)

    Please Login or Register  to view this content.

    I'm probably missing something, but I still don't see how your're using nextrow.
    here is my file for review
    https://www.dropbox.com/s/z9qkgxe2x7...orum.xlsm?dl=0

  6. #6
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    Please Login or Register  to view this content.
    this is the original code that works on another workbook but just the sheet numbers have changed but i dont know why i can get this to work on my book

  7. #7
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    Quote Originally Posted by TMS View Post
    I'm confused by your code. At the beginning, you set DstRng based on column 1

    Please Login or Register  to view this content.
    Then, later, you determine nextrow based on column 18

    Please Login or Register  to view this content.

    and then you copy and paste the data (to the original DstRng)

    Please Login or Register  to view this content.

    I'm probably missing something, but I still don't see how your're using nextrow.
    Please Login or Register  to view this content.
    here it is

  8. #8
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    Could someone help please

  9. #9
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    im still at a lose, its just over writing and not filling down when new invoice is added to the database
    Last edited by Learning ExL; 04-19-2015 at 12:29 PM.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: offset not working

    OK

    This line:
    Please Login or Register  to view this content.
    does this ...

    With Sheet16 (which is Invoice Database) go up from cell A1048576 (the last row) and then offset from there 18 rows and 1 column. So, A1048576 up gives you cell A1. Cell A1, offset 18 rows and 1 column, gives you cell B19 ... every time. I still do not understand what you are doing with nextrow; it seems to have no purpose. If you want the next cell in column B as your destination range, you should be using
    Please Login or Register  to view this content.

    Regards, TMS

  11. #11
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    Quote Originally Posted by TMS View Post
    OK

    This line:
    Please Login or Register  to view this content.
    does this ...

    With Sheet16 (which is Invoice Database) go up from cell A1048576 (the last row) and then offset from there 18 rows and 1 column. So, A1048576 up gives you cell A1. Cell A1, offset 18 rows and 1 column, gives you cell B19 ... every time. I still do not understand what you are doing with nextrow; it seems to have no purpose. If you want the next cell in column B as your destination range, you should be using
    Please Login or Register  to view this content.

    Regards, TMS
    my database starts in B18 and when i changed to your code it put the invoice into A2

  12. #12
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    and it still overrights that last entries and not continue down creating a list

  13. #13
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    and it still overrights that last entries and not continue down creating a list

    Please Login or Register  to view this content.
    above equals col B

    Please Login or Register  to view this content.
    above equal B19

    but why does it not find the last row and enter the detail in the next empty row

  14. #14
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    ok i used this and it entered the datails in row B19 then entered an new invoice and it over rote it

    Please Login or Register  to view this content.

  15. #15
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: offset not working

    I really do not know if this helps you but I use it for pasting data after a specific row.
    'change the rango for yr needs
    Please Login or Register  to view this content.
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: offset not working

    Last attempt ...

    This:

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

    Please Login or Register  to view this content.

    This:

    Please Login or Register  to view this content.

    is the last row in column 1 ... column A.


    I cannot say this a different way.

  17. #17
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    This part of the code is supposed to do the work
    Please Login or Register  to view this content.
    Well it does on another work book but on this book it does not 😠

  18. #18
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: offset not working

    Different sheet, different column. Otherwise, perfect.

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: offset not working

    I cannot upload the workbook, it is too big. But here is the amended code:

    Please Login or Register  to view this content.

    And here is the output:


    B
    C
    D
    E
    F
    10
    Totals OutStandings
    £ 1,748.40
    Statement
    11
    Customer
    Payee
    12
    Nampak10
    Removed for security
    13
    Removed for security
    14
    Jenna Way
    Removed for security
    15
    NEWPORT PAGENLL
    Removed for security
    16
    MK16 9QJ
    Removed for security
    17
    Removed for security
    18
    Date
    Agency
    Agency ID
    InvNo
    Total
    19
    19/04/15
    xxx
    9
    £ 126.90
    20
    19/04/15
    xxx
    #N/A
    9
    £ 149.46
    21
    19/04/15
    xxx
    #N/A
    9
    £ 149.46
    22
    19/04/15
    xxx
    #N/A
    9
    £ 149.46
    23
    19/04/15
    xxx
    #N/A
    9
    £ 149.46
    24
    19/04/15
    xxx
    #N/A
    9
    £ 149.46
    25
    19/04/15
    yyyy
    10
    £ 126.90
    26
    19/04/15
    yyyy
    #N/A
    10
    £ 149.46
    27
    19/04/15
    yyyy
    #N/A
    10
    £ 149.46
    28
    19/04/15
    yyyy
    #N/A
    10
    £ 149.46
    29
    19/04/15
    yyyy
    #N/A
    10
    £ 149.46
    30
    19/04/15
    yyyy
    #N/A
    10
    £ 149.46
    31



    Regards, TMS
    Last edited by TMS; 04-19-2015 at 04:49 PM.

  20. #20
    Forum Contributor
    Join Date
    03-30-2015
    Location
    Northampton
    MS-Off Ver
    2010
    Posts
    275

    Re: offset not working

    Thank you sir did you by any chance have a play with the add credit as well I know it was not part of this post

  21. #21
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: offset not working

    Thanks for the rep.

    Unless you want to test column 5 (column E), which would be the Invoice Number column, then I would guess this:

    Please Login or Register  to view this content.

    should, again, be this:

    Please Login or Register  to view this content.

    Regards, TMS

  22. #22
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: offset not working

    Mmm, if you change the code as above (at least, for consistency), you also need:

    Please Login or Register  to view this content.

    Regards, TMS

  23. #23
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: offset not working

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. [SOLVED] Can anyone tell me why this isn't working? .offset
    By Taemex in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-12-2014, 07:11 PM
  2. Offset not working
    By sput_sput in forum Excel General
    Replies: 12
    Last Post: 08-25-2010, 02:51 PM
  3. offset not working
    By BorisS in forum Excel General
    Replies: 2
    Last Post: 11-30-2005, 04:20 AM
  4. Offset now working?
    By sleah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-08-2005, 01:57 AM
  5. Offset Not Working
    By sleah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-07-2005, 05:56 PM

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