IF Then ElseIf Else Statement macro

[tonyk1051]

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

[SamT]

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

[tonyk1051]

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

[SamT]

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.

[p45cal]

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?

[tonyk1051]

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)

[p45cal]

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

[tonyk1051]

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.

[p45cal]

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

[tonyk1051]

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

[p45cal]

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

[tonyk1051]

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

[p45cal]

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 LRand what if it's over $50?

[tonyk1051]

if its over $50 it goes to tradeport

[p45cal]

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

[tonyk1051]

Here you go

[p45cal]

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

[tonyk1051]

works perfect now, thanks p45 for the help