+ Reply to Thread
Results 1 to 8 of 8

I'm not sure where to place my If Then statement

  1. #1
    Registered User
    Join Date
    04-16-2020
    Location
    Grand Rapids, MI
    MS-Off Ver
    15.0.5215.1000
    Posts
    9

    I'm not sure where to place my If Then statement

    Hello,

    I have very little knowledge of VBA and am having a difficult time placing an If Then statement in the correct spot. I need cell I11 to read "1.50" if cell J11 is "General Inspection Level II". I would like it to be within Worksheet_Change. Basically I just need to know where to place

    If Range("J11").Value = "General Inspection Level II" Then
    Range("I11").Value = "1.50"

    If something works better than If Then I am all for it! Thank you in advance!



    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,720

    Re: I'm not sure where to place my If Then statement

    I think this does everything you want:

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    04-16-2020
    Location
    Grand Rapids, MI
    MS-Off Ver
    15.0.5215.1000
    Posts
    9

    Re: I'm not sure where to place my If Then statement

    That works for most cases. For some reason when I select "General Inspection Level II" from the dropdown list it updates cells I11 and I7, but it doesn't update the number of rows. If I make any other change the rows update perfectly. It is only when I switch the General Inspection Level II.

    By the way, thank you for shortening that code! I feel a little silly about how much time I spent on the original.

  4. #4
    Registered User
    Join Date
    04-16-2020
    Location
    Grand Rapids, MI
    MS-Off Ver
    15.0.5215.1000
    Posts
    9

    Re: I'm not sure where to place my If Then statement

    Here is a sample workbook.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,720

    Re: I'm not sure where to place my If Then statement

    Your cell I7 does not 'change' - what is does is recalculate. Change is due to a user entry in that cell - with a formula, you need to use the Calculate event. And I think this is enough, since I7 and C11 are linked, and you have merged cells:

    Please Login or Register  to view this content.
    [/CODE]

    One thing that I am unsure of is why you used the formula

    =C11

    in cell AW2 - it seems to defeat your purpose.

    Also, though this works:

    INDEX(AW2:AW6,MATCH(C11,AV2:AV6,1),1)

    It can also be done with Vlookup, like so:

    VLOOKUP(C11,AV2:AW6,2,True)

  6. #6
    Registered User
    Join Date
    04-16-2020
    Location
    Grand Rapids, MI
    MS-Off Ver
    15.0.5215.1000
    Posts
    9

    Re: I'm not sure where to place my If Then statement

    I7 has the possibility to change. If one of our inspectors is using the report but the number of samples is not based off AQL, then they will just type over the formula.

    =C11 needs to be included in cell AW2 because the sample size for order quantities between 2 and 8 for General Inspection Level II is equal to the order quantity and is subject to change depending on the contents of different cells.

    The purpose of this report is to automatically determine the sample size based on order quantity, AQL level and customer requirement then automatically hide the rows that will not be used.

    As previously stated, everything works perfectly except when changing cell J11 to "General Inspection Level II". It works when changing it to C=0 and QS096, but not the other way around.

  7. #7
    Registered User
    Join Date
    04-16-2020
    Location
    Grand Rapids, MI
    MS-Off Ver
    15.0.5215.1000
    Posts
    9

    Re: I'm not sure where to place my If Then statement

    I forgot to try your code! Everything works except when changing Cell I7. As explained above, there may be circumstances where someone needs to input a value over the formula in I7, so I'll need it to run the code then, too.

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,720

    Re: I'm not sure where to place my If Then statement

    So you allow them to overwrite cell I7? I would not do it that way - just add a cell where they can enter a value - say, I5 - and change the formula in I7 to include a conditional:

    =IF(I5<>"",I5,Old formula without the = sign at the start)

    That way, the formula still works by allows.

    Other wise, change the code to

    Please Login or Register  to view this content.

+ 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. IF Statement to place images/pictures
    By BugmanGB in forum Excel General
    Replies: 2
    Last Post: 05-16-2014, 10:57 AM
  2. if statement place data in according sheet
    By eaztechpro in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-15-2014, 05:23 PM
  3. [SOLVED] IF statement to read month format and place month name in next cell
    By david1987 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2012, 11:41 AM
  4. Replies: 2
    Last Post: 02-04-2011, 09:33 AM
  5. Decimal place if statement
    By jacko311 in forum Excel General
    Replies: 2
    Last Post: 11-09-2009, 10:42 AM
  6. Big sheet..neeed to jump from place to place
    By RHagerty in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2007, 07:48 AM
  7. [SOLVED] Using "double equal signs" in place of an IF statement
    By Scott P in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2006, 11:15 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