+ Reply to Thread
Results 1 to 10 of 10

VBA to get figures from another sheet in same workbook

  1. #1
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Angry VBA to get figures from another sheet in same workbook

    Hi Guys,

    I am running a macro that needs sheet1 to be active. During execution the macro populates a variable called "NAME", name=Joe Bloggs

    What I would like is to look in Sheet2 across row 2 until it finds "NAME" (Joe Bloggs) when it finds it, populate a variable called "YearEndFigures" with the value of the last cell in that column.

    The wall I have been banging my head against now needs repairs

    TIA
    Keith

  2. #2
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: VBA to get figures from another sheet in same workbook

    Hi,

    See if you can use this code...
    Please Login or Register  to view this content.
    Regards,
    Rudi

  3. #3
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: VBA to get figures from another sheet in same workbook

    Hi Rudi,

    Thanks for a quick reply.

    I copied and pasted the code into a new sub. When I ran it by pressing the F8 key it ran through and sNAME was "" and iCol = 79 (79 is the last cell in column A of sheet2) so I went to sheet1 and populated cell A1, but then I got a runtime error '91':

    "Object variable or With block variable not set"
    on line>>

    Please Login or Register  to view this content.


    Regards,
    Keith

  4. #4
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: VBA to get figures from another sheet in same workbook

    Hi,

    The code I posted was sample code and you would need to change the referencing in it to accomplish what you need.
    You go the error since the Find command was not able to locate a match in Sheet 2 and Row 2 for the value in Sheet 1 and cell A1.

    The code below is a bit more "stable" as it hase some logical error handling in it. If it finds no matching value in row 2 based on the value in A1, it does nothing instead of debugging. Also, I don't know what value you will be picking up in the YearEndFigures, so I made the variable variant, to store anything...

    Try it out now and make reference adjustments as needed...

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: VBA to get figures from another sheet in same workbook

    Good Morning Rudi,
    Thanks again for your help.

    I now have sName populating along with iCol ( this was my fault as the name on one sheet had a "." after the name, sorry) but YearEndFigures = EMPTY.

    YearEndFigures is at the moment> =SUBTOTAL(109,G$3:G$81) but as rows get added the G$81 goes to G$82, etc, and its the resulting value that I would like.

    Keith

  6. #6
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: VBA to get figures from another sheet in same workbook

    Morning Keith...
    It's a pleasure to help.

    I have attached a sample workbook to illustrate the macro in the context of my tests, however, try it now with the changes I made based on info from your last post...

    Please Login or Register  to view this content.
    Workbook attached too...
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: VBA to get figures from another sheet in same workbook

    Good Afternoon Rudi (its still morning here, just)


    My column A, say A10=D5&E5, so my NAME (A10) = Firstname&Lastname.

    On sheet 2 the cell that contains the name that is in sheet1(A5) is =Sheet1'!$A10.

    So, sNAME gets populated correctly, but, iCol does not find the Name in sNAME it would see =Sheet1'!$A10

    I don't know IF it will complicate things further, but, both NAMES in column A sheet1 and =Sheet1'!$A10 are hyperlinks too each other.

    Kind regards,
    Keith

  8. #8
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: VBA to get figures from another sheet in same workbook

    That helps since the default search that a FIND command is to search by Formula, but we want the value returned by the formula, so I have to explicitly add the argument: LookIn:=xlValues.

    See if this works now:

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    11-30-2005
    Location
    Penzance Cornwall, UK
    MS-Off Ver
    Office 365
    Posts
    198

    Re: VBA to get figures from another sheet in same workbook

    Rudi I'd like to shake your hand.

    I use the F8 key to run through the code and get good results, I change the ref from A1 to other cells where I have names and still get the correct results.

    I will mark this post as solved as the original question has now been solved.

    Now I will try and figure out where to insert and modify "your" code into my existing macro/module and then more rigorous testing.



    Watch this space

    Once again, thank you for ALL your help.

    Enjoy the rest of the weekend.

    Keith

  10. #10
    Valued Forum Contributor
    Join Date
    07-04-2012
    Location
    Cape Town, RSA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1,050

    Re: VBA to get figures from another sheet in same workbook

    Glad to help...
    Cheers

    Have a great day too.

+ 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. Conversion of an array of figures inputted into cells as word figures to Excel figures
    By Allan Simpson730 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-08-2013, 07:25 AM
  2. VBA. If criteria is met, sum up figures that are over two sheets (same workbook)
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-01-2011, 11:46 PM
  3. Transfer figures from workbook to master
    By neodjandre in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-15-2007, 08:04 AM
  4. Replies: 5
    Last Post: 04-28-2006, 09:00 AM
  5. I need a daily sales sheet to pull from monthly figures sheet
    By Draegen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-24-2005, 05:26 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1