+ Reply to Thread
Results 1 to 14 of 14

Add Range to Array find Array value and Paste back to Range

  1. #1
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007; Excel 2010
    Posts
    178

    Add Range to Array find Array value and Paste back to Range

    Hi,

    I am trying to work out (still learning Arrays) how to add a range to an Array then with each value in the Array find that value in
    another sheet and add that value to the array and finally paste results back to worksheet.

    What I have so far is adding range to Array and finding each array value..
    Please Login or Register  to view this content.
    I am still unsure how to add found value to the array and paste back onto sheet at a offset of 7 cells

    Thanks
    Last edited by Foreverlearning; 04-28-2012 at 05:45 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Add Range to Array find Array value and Paste back to Range

    If the first value from C7 is found on the LAB83 sheet in cell B25, what exactly is supposed to happen? LAB83-C25 is pasted to activesheet-J7?
    _________________
    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!)

  3. #3
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007; Excel 2010
    Posts
    178

    Re: Add Range to Array find Array value and Paste back to Range

    What I was trying to do is add the found value into array relating to value and then when all values are searched paste the results back onto sheet on offset of 7
    The values are pasted back into Activesheet at a offset of 7

    Thanks

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Add Range to Array find Array value and Paste back to Range

    Ok, can't visualize that. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to automate.

  5. #5
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007; Excel 2010
    Posts
    178

    Re: Add Range to Array find Array value and Paste back to Range

    Hi Jerry,

    Thanks for helping out.

    I have attached a sample workbook inserting random data, but with same framework.

    I have added a note within the workbook.

    Thanks

    Lionel
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,050

    Re: Add Range to Array find Array value and Paste back to Range

    3 examples
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007; Excel 2010
    Posts
    178

    Re: Add Range to Array find Array value and Paste back to Range

    Hi Jindon,

    Thank you for your input.

    I will try the code out.

    Lionel

  8. #8
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007; Excel 2010
    Posts
    178

    Re: Add Range to Array find Array value and Paste back to Range

    Hi jindon,

    Code did not return expected value
    After dissecting I added some small changes and got result I needed

    Please Login or Register  to view this content.
    Is it now possible after examining my goal to place desired result in same cell as being divided by
    and place in brackets.

    Example 1st number to search is 102079 and value to divide is 7000 (column F)
    result is 93.2

    Objective = 7000(93.2) in same cell as 7000 (From included workbook earlier post)

    Thanks
    Lionel
    Last edited by Foreverlearning; 04-27-2012 at 05:22 PM. Reason: Highlight changes

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Add Range to Array find Array value and Paste back to Range

    I understand the fun with VBA, but doesn't this basic formula do what you described in your sample workbook instructions?

    N8: =IF(C8="", "", F8 / VLOOKUP(C8, LAB!$B:$H, 7, 0))
    ...copied down.

  10. #10
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007; Excel 2010
    Posts
    178

    Re: Add Range to Array find Array value and Paste back to Range

    Hi,

    The worksheet is imported from another source and you basically start with a blank sheet.

    Also I am trying to do all by VBA and hopefully arrays

  11. #11
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Add Range to Array find Array value and Paste back to Range

    Ok, good luck, then. If I were faced with this problem and "had to do it with VBA", I would use VBA to insert that formula and copy it down.

  12. #12
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007; Excel 2010
    Posts
    178

    Re: Add Range to Array find Array value and Paste back to Range

    Ok,

    This will not give me a solution as per my 2nd last post

    Thanks anyway Jerry

    I will slog it out until I find a solution.

    Lionel

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,050

    Re: Add Range to Array find Array value and Paste back to Range

    You mean like this?

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007; Excel 2010
    Posts
    178

    Re: Add Range to Array find Array value and Paste back to Range

    Thanks jindon.

    Everything now works as intended..

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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