Consulting

Results 1 to 18 of 18

Thread: IF Then ElseIf Else Statement macro

  1. #1

    IF Then ElseIf Else Statement macro

    Hi,

    Im having a tough time trying to set up a code for the intructions below, excel file is in attachment


    need a code to do this
    If coulmn S is yes
    and column W says open box put in column H used, if coulmn W says
    defective /unspecified or damaged then put in coulmn H
    LR if value in column O is $20 or less, put Tradeport if value is between $20-$50
    or put TL if value is over $50 BUT if column N has key word "cell phone" or "notebook" put in TT

    thanks in advance
    Attached Files Attached Files

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,474
    Location
    First I created the Enum (Copied + PasteSpecial (Values,Transpose)) the headers, then Concatenated "nc" and Values
    Then I substituted .Cells(Enums) for Cel.Offsets
    Finally, I merged the two subs into one.

    Looking at the "Self Documenting" code below, I think you can see where the issues are.
    Enum NotACategoryColumns
    'nc Prefix for "Not a Category"
    'Enums Auto increment from last specified value
    'Comments to show offsets
        ncItem_ID = 1
        ncKit_Flag
        ncSource
        ncVendor
        ncVendor_Liason
        ncManufacturer
        ncRTV_Category
        ncCol_H                      'R - 10... 'Note Col H had no Header
        ncBlankColumn_1
        ncDate_RTV_Category
        ncUser_RTV_Category
        ncShelf
        ncPrior_Category
        ncProduct_Group
        ncBooknet
        ncwarehouse
        nchas_lithium
        ncopenbox                   'R
        ncdfi                       'S
        ncDate_Received
        ncCustomer_Purchase_Date    'S + 2
        ncRMA_Date
        ncDefect_Cat
        ncDefect_Desc
        ncBlankColumn_2
        ncSerial_No
        ncItem_Code                 's + 8
        ncCust_Flag                 'S + 9
        nccatlgno
        ncbuyer
        ncbrand
        ncDescription
        ncLong_Catalog_No
        ncPO
        ncIs_Used
        ncMulti_Vendor_Flag         'S + 17
        ncLast_Vendor
        ncReturn_Reason_1
    End Enum
    
    
    Sub VBAX_SamT()
    Dim Rw As Row, varCustomer_Purchase_Date As String
    With Worksheets("Not on a Category")
        For Each Rw In .UsedRange.Offset(1).Rows
    'Begin First Sub
        With Rw.EntireRow
             .Cells(ncCol_H).Interior.Color = vbGreen 'So you can see what already is done
        
            If .Cells(ncopenbox) = "NO" And .Cells(ncDefect_Cat) = "Open Box" Then .Cells(ncCol_H) = "used"
    'End First sub
    
        Select Case .Cells(ncdfi)
            Case "YES"
                If CBool(InStr(.Cells(ncItem_Code), "printer")) Then
                    If .Cells(ncMulti_Vendor_Flag) = "Defective / unspecified" Or .Cells(ncMulti_Vendor_Flag) = "Damaged" Then
                        Customer_Purchase_Date = "TT"
                    ElseIf .Cells(ncMulti_Vendor_Flag) = "Open Box" Then
                        Customer_Purchase_Date = "used"
                    End If
                End If
                If CBool(InStr(.Cells(ncItem_Code), "Film-")) Or CBool(InStr(.Cells(ncItem_Code), "paper")) Then
                    If .Cells(ncCust_Flag) < 20 Then
                        Customer_Purchase_Date = "LR"
                    Else
                        Customer_Purchase_Date = "tradeport"
                    End If
                End If
       
            Case "Epson"
                If CBool(InStr(.Cells(ncItem_Code), "paper")) Or CBool(InStr(.Cells(ncItem_Code), "toner")) Then
                    If cel.Offset(, 9) <= 20# Then
                        Customer_Purchase_Date = "LR"
                    Else
                        Customer_Purchase_Date = "tradeport"
                    End If
                End If
                'etcetera
        End Select
        If Customer_Purchase_Date <> "" Then .Cells(ncCustomer_Purchase_Date) = varCustomer_Purchase_Date
        varCustomer_Purchase_Date = ""
    
        .Cells(ncCustomer_Purchase_Date).Interior.Color = vbGreen 'So you can see what already is done
        Next Rw
    End With
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    woah ive never seen any kind of macro like this before.... column H doesnt need a header so you can put anything its just a coulmn that i use to put the results in for later use, that being said im going assume this code does whats instructed so ill give a try

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,474
    Location
    im going assume this code does whats instructed so ill give a try
    Why do you assume that? I said
    Looking at the "Self Documenting" code below, I think you can see where the issues are.
    the only thing you saw was that I noticed Column H didn't have a header.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,294
    It needs to be crystal clear what you want to see in column H. In the attached, on Sheet1, is a table where I think I've put all the possible combinations of what might appear in the relevant columns. The rightmost column is for you to fill in.
    It's a bit daunting at first glance because ther are 72 combinations, but you'll be able to fill it in wholesale because, for example, the last 48 rows all deal with column S not containing 'Yes'.
    Probably only the first 16 rows need careful attention.
    Currently there's a bunch of question marks where I want to see your answers. If the you want the cell in column H to remain blank, just delete the question mark on that row.
    For column W I've put 'defect' which means your defective /unspecified or damaged
    Likewise for column N I've put 'notebook etc' which means your has key word "cell phone" or "notebook"
    For column O I've put 'low', 'medium', 'high' corresponding to your $20 or less, between $20-$50 and is over $50

    Fill it in and attach the file to your next message. I'm expecting to be able to write a short macro (10 lines or less) to populate column H.

    ps. what version of Excel are you using?
    Attached Files Attached Files
    Last edited by p45cal; 02-27-2021 at 07:56 AM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    Hi, thanks
    Im using Excel 2019, i put what you asked and you're right results will only be if column S is yes (if column W is open box column H will always be used no matter what and column O is never blank because its where the value of each product will be)
    Attached Files Attached Files

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,294
    I cell I20 you put 'depends on what's in column O'. Column O is blank in that case, so what belongs in H?
    Oh hang on…
    you've changed many cells which contained 'something else' and cells which contained 'defect' to separate values for 'damaged' and 'Defective / unspecified', yet both those values return the same in H ('defect', I said, was to cover both those) so now I don't know what goes in H if there's something else in there (ie not 'Damaged', and not 'Defective / unspecified').

    And you've changed some values in the middle column!
    Upshot - it's not crystal clear! Could you try again, with the original file I sent, this time only changing the rightmost column. You only need to do the top 24 rows - I get the rest.
    If there are conditions I haven't catered for add them to the bottom of the table (I used a macro to produce the combinations so I thought I'd produced all combinations?).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    Hi,

    I filled out 22 of them, the 2 blanks, it can be any of the 4 results, remember if its a notebook, cell phone or tablet, value wont matter, column H is always TT. If there was a value in them in which case the real spreadsheet will always have a value since these are real products then it would be LR, tradeport or TL. (FYI defect column will always be filled in with either open box, defective /unspecified or damaged, defective and damaged are treated the same)
    I know its confusing but thanks for helping me p45cal, really apprecaite it.
    Attached Files Attached Files

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,294
    This is work in progress and needs checking thoroughly.
    A 5 line macro:
    Sub blah()
    Set myRng = Sheets("Not on a Category").Cells(1).CurrentRegion.Columns(1).Offset(, 7)
    With Intersect(myRng, myRng.Offset(1))
      .FormulaR1C1 = "=IF(RC19=""YES"",IF(RC23=""Open Box"",""Used"",IF(OR(RC23=""Defective / unspecified"",RC23=""damaged""),IF(OR(ISNUMBER(SEARCH(""notebook"",RC14)), ISNUMBER(SEARCH(""Cell Phones"",RC14))),""TT"",IF(ISBLANK(RC15),"""",VLOOKUP(RC15-0.000000001,{-1,""LR"";20,""Tradeport"";50,""TL""},2))),"""")),"""")"
      .Value = .Value    'if you delete or temporarily comment-out this line you'll be left with a formula in column H to examine and check.
    End With
    End Sub
    Also see comment in the code.

    With regard to column W, can it only be treated in one of two ways: Open Box or not Open Box (in which case it's treated as damaged etc.)?
    What about if column W is blank?, should column H be blank too (as before)?
    Note also in the formula I've used Cell Phones rather than Cell Phone to differentiate between the likes of MOBILE-Unlocked Cell Phones and MOBILE-Cell Phone Cases
    Last edited by p45cal; 02-27-2021 at 05:06 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    technically speaking yes, column W can be treated in one of two ways. Just from experience, column W, N and O will always have something in, its never blank

  11. #11
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,294
    I'll await your comments on my current offering's correctness before trying to shorten the code/formula.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    code works perfect but it needs an adjustment, the key word notebook i need it changed to "COMPUTER-Notebooks" and one extra rule if coulmn N contains keywords "paper", "film" or "toner" then put in coulmn H tradeport if the value is over $20, if value is below $20 put LR

  13. #13
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,294
    Quote Originally Posted by tonyk1051 View Post
    if coulmn N contains keywords "paper", "film" or "toner" then put in coulmn H tradeport if the value is over $20, if value is below $20 put LR
    and what if it's over $50?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  14. #14
    if its over $50 it goes to tradeport

  15. #15
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,294
    OK, another table to complete, anything missing add to the bottom of the table.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  16. #16
    Here you go
    Attached Files Attached Files

  17. #17
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,294
    try:
    Sub blah()
    Set myRng = Sheets("Not on a Category").Cells(1).CurrentRegion.Columns(1).Offset(, 7)
    With Intersect(myRng, myRng.Offset(1))
      .FormulaR1C1 = "=IF(RC19=""Yes"",IF(RC23=""Open Box"",""Used"",IF(OR(RC23=""Defective / unspecified"",RC23=""damaged""),IF(OR(ISNUMBER(SEARCH({""COMPUTER-Notebooks"",""Cell Phones""},RC14))),""TT"",IF(ISBLANK(RC15),"""",IF(OR(ISNUMBER(SEARCH({""paper"",""toner"",""film""},RC14))),IFERROR(VLOOKUP(RC15-0.000000001,{-1,""LR"";20,""Tradeport""},2),""""),IFERROR(VLOOKUP(RC15-0.000000001,{-1,""LR"";20,""Tradeport"";50,""TL""},2),"""")))),"""")),"""")"
      .Value = .Value    'if you delete or comment-out this line you'll be left with a formula in column H to examine and check.
    End With
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  18. #18
    works perfect now, thanks p45 for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •