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

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.

Code:

---

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

---

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

Quote:

---

im going assume this code does whats instructed so ill give a try

---

Why do you assume that? I said

Quote:

---

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.

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?

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)

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?).

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.

This is work in progress and needs checking thoroughly.
A 5 line macro:

Code:

---

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

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

I'll await your comments on my current offering's correctness before trying to shorten the code/formula.

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

Quote:

---

Originally Posted by tonyk1051

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?

if its over $50 it goes to tradeport

OK, another table to complete, anything missing add to the bottom of the table.

Here you go

try:

Code:

---

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

---

works perfect now, thanks p45 for the help