+ Reply to Thread
Results 1 to 10 of 10

Select and copy array of cells in VBA

  1. #1
    Registered User
    Join Date
    04-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    73

    Select and copy array of cells in VBA

    Hello,

    i need help with debuging a code that i wrote (dont know a lot about VBA). It stops working in 8 or 9th line (I have put it in italic format). What must i change so that it will work? ist there any better way to write this macro? is the problem in skipBlanks?

    Please Login or Register  to view this content.

    Thank you for your help

  2. #2
    Valued Forum Contributor dotchiejack's Avatar
    Join Date
    05-21-2015
    Location
    Antwerp,Belgium
    MS-Off Ver
    2016
    Posts
    507

    Re: Select and copy array of cells in VBA

    Without a example ?
    I'll guess this is what you are afther.
    Please Login or Register  to view this content.
    Avoid Select and activate, it slows down your code big time an it is rarely needed.

  3. #3
    Registered User
    Join Date
    04-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    73

    Re: Select and copy array of cells in VBA

    It seems to be working, with only two problems for one i cannot find a pattern.
    One is that the range in column W is not the same as range in column E when formulas are copied. (which is important). because with the second macro i want to be able to return the copied formulas to original cells if needed. And if formulas are copied into the column W without the blanks in between ranges this would be harder to do.
    Something like this i suppose but what i wrote dosent work :D
    Please Login or Register  to view this content.
    Second one is when it copies the values back to original range i get a #N/A as a result in some cells. From D29:E29 and also in D67:E70, D80:E84 , D100:D101. The formulas are the same in all cells but only in this cells the values come as N/A. Is it possible that this two problems are corelated?

    If u need workbook let me know.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,442

    Re: Select and copy array of cells in VBA

    You cannot use .value=.Value on non contiguous ranges.
    try
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    73

    Re: Select and copy array of cells in VBA

    This works like a charm

    I will try to do reverse macro my self(to return the forumlas back to normal place).

    Thank you very many :D

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,442

    Re: Select and copy array of cells in VBA

    Glad to help & thanks for the feedback

  7. #7
    Registered User
    Join Date
    04-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    73

    Re: Select and copy array of cells in VBA

    Problems again :D
    Im really bad at this

    i tried to modify the code so that it would return the formulas back in original place. But my attempt was denied

    Please Login or Register  to view this content.
    it says that End With without With

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,442

    Re: Select and copy array of cells in VBA

    You're missing the
    Please Login or Register  to view this content.
    which need to go just before the End With

  9. #9
    Registered User
    Join Date
    04-08-2013
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    73

    Re: Select and copy array of cells in VBA

    Awsome this solved the problem thnx again

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,442

    Re: Select and copy array of cells in VBA

    You're welcome

+ 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: 3
    Last Post: 12-14-2015, 04:09 AM
  2. select and copy cells
    By Richard Brook in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-11-2013, 10:26 AM
  3. Replies: 2
    Last Post: 09-06-2012, 07:55 PM
  4. [SOLVED] can only copy/select visible cells w autofilter on &hidden columns, want to copy all cells
    By JTwrk in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-25-2012, 02:12 AM
  5. Select and copy cells based on values in another cells
    By JoC in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2008, 04:39 AM
  6. Select a range of cells and then copy them, and paste into other cells
    By Dynelor in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-24-2007, 04:03 PM
  7. select variables ranges, copy to array, paste the array in new workbook
    By Mathew in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2005, 05:06 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