+ Reply to Thread
Results 1 to 10 of 10

Using "If Target.Value Equals..." to compare resulting cell value instead of formula?

  1. #1
    Registered User
    Join Date
    12-20-2016
    Location
    United States
    MS-Off Ver
    2016
    Posts
    40

    Using "If Target.Value Equals..." to compare resulting cell value instead of formula?

    Hi everyone! So a small VBA snippet that I've used for various things over the years has stumped me today. Instead of looking for a plain cell value like I normally do, today I am looking for the value of a formula. Let's say my current cell (C5) value is:

    Cell: C5
    Please Login or Register  to view this content.

    I'm then using the below VBA to look for the word "Apples" throughout all of column C (not just C5, or a small range).

    Please Login or Register  to view this content.

    The problem I'm finding is, the formula I've filled down a huge range within column C is what is becoming the value of any given cell in that column, not the actual result of the formula ("Apples" or "Oranges"). I've looked up several things online, yet can't seem to find an easy answer to finding the resulting cell value (not the formula) for the above type of script (where the syntax is 'If Target.Value = "Apples" Then....')

    Any ideas on how to cleanly solve this? Thanks so much everyone!

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Using "If Target.Value Equals..." to compare resulting cell value instead of formula?

    Try the Macro Recorder

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    So your Code is

    Please Login or Register  to view this content.

    Debugged and tested Code:

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 09-18-2020 at 06:02 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    12-20-2016
    Location
    United States
    MS-Off Ver
    2016
    Posts
    40

    Re: Using "If Target.Value Equals..." to compare resulting cell value instead of formula?

    Hi mehmetcik, thanks a ton for your time and your help with this! Before I made the post I was actually trying to think how I could achieve using the macro recorder to capture what I was trying to do, but couldn't (and still can't lol) wrap my head around the portion of simply finding the string that the formula gave.

    So it looks like if I simply put the below in column C, it does insert the word "Fruit" into column L....
    Please Login or Register  to view this content.
    However, with the word "Apples" resulting from a VLOOKUP function, the VBA does not seem to pick up on the resulting string, "Apples" (without quotes). Sorry I didn't mention I was using VLOOKUP initially, as I didn't honestly think it would made a difference. Here is what I have in a random row (43) of column C:
    Please Login or Register  to view this content.
    So obviously my formula is in cell C43, and looks at the word in B43. It then looks at the worksheet named "Produce" to find the matching word from B43 on the original sheet, counts 3 columns over, and the resulting string is "Apples" (again, without the quotes of course). So when I go to the original sheet and type in a specific word in B43 for example, and C43 returns the word Apples, nothing is happening with column L unfortunately.

    Would you happen to have any idea why this might be? I studied the VBA a little bit but I cannot seem to understand the little program context well enough to see what I could possibly change to make this work. Thanks again, I super-appreciate being kind of enough to help!!!

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Using "If Target.Value Equals..." to compare resulting cell value instead of formula?

    The Worksheet_Change event is not triggered by a formula calculation (it is triggered by the initial entry of the formula, but not by any subsequent calculations of it). You would need to monitor the input cells to the formula, or use the Worksheet_Calculate event (which does not provide a Target parameter)
    Rory

  5. #5
    Registered User
    Join Date
    12-20-2016
    Location
    United States
    MS-Off Ver
    2016
    Posts
    40

    Re: Using "If Target.Value Equals..." to compare resulting cell value instead of formula?

    Hi Rory! Thanks a bunch for the help! That makes sense, regarding what you explained. And you nailed exactly what I was hoping to do. Would you mind explaining what you mean by "you would need to monitor the input cells to the formula"?

    I was wondering if there was perhaps a way to copy the resulting text of the cell in column C to another column in the same row (using the Worksheet_Change function), and then I could use that plain text value from the "helper" column as my criteria. I'm going to fiddle with that and see if I can get anywhere with it, but absolutely open to any other suggestions as to how I can accomplish this, even if it's a little messier than I previously hoped

    Thanks again!

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Using "If Target.Value Equals..." to compare resulting cell value instead of formula?

    Currently your code is testing to see if column C was changed, but that won't work if column C has a formula in it. If the lookup values in column B are being entered manually, you could monitor column B for changes, and then test Target.Offset(, 1).Value instead.

  7. #7
    Registered User
    Join Date
    12-20-2016
    Location
    United States
    MS-Off Ver
    2016
    Posts
    40

    Re: Using "If Target.Value Equals..." to compare resulting cell value instead of formula?

    Thanks once again Rory, I appreciate you elaborating on that. I follow you know. So I'm not that good with VBA yet and I keep staring at my original code as well as what mehmetcik provided, and I can't seem to understand where or how to use Target.Offset(, 1).Value, nor how to monitor column B for changes. My apologies for the confusion on my end.... so far I only understand simple VBA haha.

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Using "If Target.Value Equals..." to compare resulting cell value instead of formula?

    Try this:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-20-2016
    Location
    United States
    MS-Off Ver
    2016
    Posts
    40

    Re: Using "If Target.Value Equals..." to compare resulting cell value instead of formula?

    Rory thank you sooo much!! That worked like a charm!!!!!!! And sorry to both you and mehmetcik for leaving out that important information about how I was fetching the values for column C. But I REALLY appreciate both of your guys' help so much!

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Using "If Target.Value Equals..." to compare resulting cell value instead of formula?

    Glad we could help.

+ 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: 09-30-2018, 11:26 PM
  2. Replies: 4
    Last Post: 06-11-2017, 02:03 PM
  3. [SOLVED] If cell equals "CR" then make cell to left negative and delete "CR".......
    By pasqualebaldi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-14-2016, 07:51 PM
  4. "Worksheet change" not changing cell on other sheet to match target cell "fill".
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-02-2015, 12:02 PM
  5. IF Statement to compare two dates and insert "Yes" or "No" in a third cell
    By tbrookes3 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-03-2015, 11:44 PM
  6. [SOLVED] problem with IF cell contains "text" from resulting formula
    By fredderf81 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-11-2014, 02:04 PM
  7. [SOLVED] Delete row if cell in Column N equal "Different" or Column P equals "Blank"
    By Cyberpawz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2012, 08:25 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