+ Reply to Thread
Results 1 to 14 of 14

Look through range and with value find on another sheet and do formula

  1. #1
    Registered User
    Join Date
    09-25-2016
    Location
    Florida
    MS-Off Ver
    365
    Posts
    51

    Look through range and with value find on another sheet and do formula

    I know images aren't preferred but there isn't anyway I know of to condense my database. It's size is over 101mb and the sheets of the images I've attached pull data from other sheets such as the names and payouts. I hope these images are acceptable.

    GOAL: On the "Payouts" sheet I have a button to start with the first player in Column AI, take his "gross" in Column AM and find that player on the "Roster" sheet (other image) and add his "gross" winnings to Column F "IP Payout". I need to do this for each player on the "Payouts" sheet down to row 44.

    I have other coding with "Find" Strings and such but it's only placing values in specific cells on other sheets which is easy enough. However, this is above my head!

    payout.png




    roster.png

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Look through range and with value find on another sheet and do formula

    Tricky without the actual workbook but I think you're looking for something like this:

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Registered User
    Join Date
    09-25-2016
    Location
    Florida
    MS-Off Ver
    365
    Posts
    51

    Re: Look through range and with value find on another sheet and do formula

    WBD, that worked exactly! To go further with this project there is another set of payouts (CTP) on the same sheet that wasn't shown in the images provided. So, I took your code and tried to make it work under the same button by changing the variables Long and Variant and the cell references. One item that could be causing the issue is the CTP payouts are merged cells??? Your code works great but I need to add the additional payouts from the same sheet. The players are in column L and payouts in Column N (N is a merged cell with O) Code attached as well as a wider view of the payout sheet.

    Please Login or Register  to view this content.
    Attachment 748970

  4. #4
    Registered User
    Join Date
    09-25-2016
    Location
    Florida
    MS-Off Ver
    365
    Posts
    51

    Re: Look through range and with value find on another sheet and do formula

    I finally understood your comment on the rows and adjusted accordingly. It's working perfectly!!!! Thank you so much, WBD!

  5. #5
    Registered User
    Join Date
    09-25-2016
    Location
    Florida
    MS-Off Ver
    365
    Posts
    51

    Re: Look through range and with value find on another sheet and do formula

    Now I'm getting a "Runtime Error 13, Type Mismatch on this line. Figure this out then we should be golden! I tried posting a new Thread with the error but for some reason I don't see it under "My Posts"..

    Please Login or Register  to view this content.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Look through range and with value find on another sheet and do formula

    The new thread is still there, but has been closed. Continue here, please.
    Last edited by AliGW; 09-26-2021 at 05:15 PM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    Re: Look through range and with value find on another sheet and do formula

    https://software-solutions-online.co...2C%20or%20Long.
    1st question : what is the value of southNETfound_Row and southNET_Row ??
    2nd question : what is the value of rosterSheet.Cells(southNETfound_Row, "D").Value and SoSkinsSheet.Cells(southNET_Row, "AB").Value
    are all 4 numbers, no strings, no errors ?
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  8. #8
    Registered User
    Join Date
    09-25-2016
    Location
    Florida
    MS-Off Ver
    365
    Posts
    51

    Re: Look through range and with value find on another sheet and do formula

    Thank you for replying. I clicked on the link you provided but the right side of each paragraph was cut off due to ads.

    The routine is finding the players name on the SoSkinsSheet and then adding that players winnings, in dollars, to the rosterSheet. Both sheets are already populated with the players name.

    I'm editing this post to include that the Net table of this coding and column B is auto-filled with a "=B12" and etc. In other words for the names in the Gross section they are filled automatically into the Net section with the formula. I saw that any cell in column B in the Net section that didn't auto-fill with a name had a "0" so figured that was causing my error after doing some reading online. So, I did a custom formatting for each of these cells to eliminate the zero. It still didn't solve the problem. I very weak on dimensions and understanding them so that's why I'm struggling with this.

    Please Login or Register  to view this content.
    Last edited by Andy C.; 09-27-2021 at 01:58 PM.

  9. #9
    Registered User
    Join Date
    09-25-2016
    Location
    Florida
    MS-Off Ver
    365
    Posts
    51

    Re: Look through range and with value find on another sheet and do formula

    I created a sample workbook by copying the sheets over to a new workbook. See the comments and code above.

    Thank you in advance for the help!

    Andy
    Attached Files Attached Files

  10. #10
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Look through range and with value find on another sheet and do formula

    Please Login or Register  to view this content.
    WBD

  11. #11
    Registered User
    Join Date
    09-25-2016
    Location
    Florida
    MS-Off Ver
    365
    Posts
    51

    Re: Look through range and with value find on another sheet and do formula

    Hi WBD,

    Your code worked great BUT I took it a step further and added the Net section of the sheet which is just below the Gross section. Look at this code and you'll see I copied what you did and altered it to "add" the Net section payout also to the Roster using the same command button. I did just discover that when I remove the "=cell#" formula in the Net section players rows and then copied and pasted the names from the Gross section that the error did NOT occur. The error shouldn't occur because of the formula since we're only selecting the Value, correct?

    Maybe the code needs redone to add the Gross and Net first before copying over to the Roster sheet??? I'm fine with the way it is because it's quick enough. I just can't fix the Runtime Error 13 without deleting the formula that copies the name from the Gross to the Net.

    Please Login or Register  to view this content.
    Much appreciate your help on this!

    Andy

  12. #12
    Registered User
    Join Date
    09-25-2016
    Location
    Florida
    MS-Off Ver
    365
    Posts
    51

    Re: Look through range and with value find on another sheet and do formula

    geez, I'm slow. Just relooked at the new code you posted and noticed you made changes. Bear with me and I will test it out!

  13. #13
    Registered User
    Join Date
    09-25-2016
    Location
    Florida
    MS-Off Ver
    365
    Posts
    51

    Re: Look through range and with value find on another sheet and do formula

    WBD,

    I think it's work perfectly now. Thank you so much. We'll live test it tomorrow and if all goes well I'll close this post.

    Thanks again and I did the "Add Reputation" for you.

    Andy

  14. #14
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Look through range and with value find on another sheet and do formula

    Cool. Thanks. Yeah - the code I wrote processes both tables.

    WBD

+ 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] loop range in sheet 3 to find value and copy offset cell to sheet 2
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-28-2017, 09:58 AM
  2. VBA Code to find range from Input sheet and hide respective columns in sheet 2.
    By skonduru in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-26-2016, 11:56 AM
  3. Replies: 1
    Last Post: 03-02-2015, 11:18 AM
  4. Copy Range from Sheet A then find the next empty row in sheet B
    By clowesr in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-02-2014, 05:42 PM
  5. Macro stores Active Sheet range but I need it to find a new range each time it runs
    By jaroesner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-13-2013, 08:00 AM
  6. Find value in a certain range and then copy to an other sheet
    By Rysioslaw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2012, 10:23 AM
  7. can't find named range on different sheet
    By urungus in forum Excel General
    Replies: 3
    Last Post: 12-04-2007, 02:07 AM

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