Case macro help

[Tonyk1051]

Hi,

Can some one help create a Macro for the rules below for the worksheet in the link? would be much appreciated, thanks (i tried doing one myself using a "case" macro it was an utter failure...

if column G is denied by manufacture, denied by defect, denied by product type or sku or denied dis. time frame has open box in column W (Defect Cat)
then put in column H REEVALUATE - TO STOCK OR USED

if column G is denied by manufacture, denied by defect, denied by product type or sku or denied dis. time frame has Defective / unspecified in column W (Defect Cat)
then put in column H, MFR

if column W has keyword damaged and column O is less than $300 then put in column H, TL UNLESS if column J is tested confirmed defective or tested confirmed damaged put in column H, liq.com
if column T is 3years old from todays date then put in column H, TL
if column F is Viewsonic, LG or NEC and column W is Defective/ unspecified then in column H put pic of defect required
if column N has keyword television or monitors and column W is damaged then put in column H TL
if column N has keyword unlocked cell phones and column X is not all upper case text then put in column H, TT

test.xlsm heres the link to the file

 

[My Aswer Is This]

Hi,

Can some one help create a Macro for the rules below for the worksheet in the link? would be much appreciated, thanks (i tried doing one myself using a "case" macro it was an utter failure...

if column G is denied by manufacture, denied by defect, denied by product type or sku or denied dis. time frame has open box in column W (Defect Cat)
then put in column H REEVALUATE - TO STOCK OR USED

if column G is denied by manufacture, denied by defect, denied by product type or sku or denied dis. time frame has Defective / unspecified in column W (Defect Cat)
then put in column H, MFR

if column W has keyword damaged and column O is less than $300 then put in column H, TL UNLESS if column J is tested confirmed defective or tested confirmed damaged put in column H, liq.com
if column T is 3years old from todays date then put in column H, TL
if column F is Viewsonic, LG or NEC and column W is Defective/ unspecified then in column H put pic of defect required
if column N has keyword television or monitors and column W is damaged then put in column H TL
if column N has keyword unlocked cell phones and column X is not all upper case text then put in column H, TT

test.xlsm heres the link to the file

I never click on links.

How do you plan to run this script? By pressing a button or what?

 

[johnnyL]

Don't have dropbox, can you upload somewhere else for example here <--- and then provide the link?

 

[My Aswer Is This]

If your trying to learn case statements:

Here is a sheet change event script using Case:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/24/2021  8:59:04 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Column = 1 Then
        
        Select Case Target.Value
            Case "Alpha", "Bravo", "Charlie"
                Target.Offset(, 1).Value = "Yes"
    
            Case "Delta", "Echo", "Foxtrot"
                Target.Offset(, 1).Value = "No"
            
        End Select

End If
End Sub

 

[Tonyk1051]

Don't have dropbox, can you upload somewhere else for example here <--- and then provide the link?

Don't have dropbox, can you upload somewhere else for example here <--- and then provide the link?

Gofile - Free file sharing and storage platform here you go

 

[Marc L]

Don't have dropbox

As anyone can download from Dropbox …​

 

[johnnyL]

Ok here is a quick summary of the file for those that don't like to download from links:

test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	Item ID	Kit Flag	Source	Vendor	Vendor Liason	Manufacturer	RTV Category			Date RTV Category	User RTV Category	Shelf	Prior Category	Product group	Booknet	Warehouse	Has Lithium	Open Box	DFI	Date Rcvd	Customer Purchase Date	RMA Date		Defect Desc	Serial No	Item Code	Cust Flag	Catalog No	Buyer	Brand	Description	Longn Catalog No	Po	Is used	Multi Vendor Flag	Last Vendor	Return Reason 1
2				INGRAM MICRO		ViewSonic	DENIED DIST. TIMEFRAME							MULTIMEDIA-See All Projectors	864.31					10/5/21	Oct 1, 2021	10/3/21	Open Box	OPEN BOX
3				LG ELECTRONICS USA, INC	narics	LG	PICTURE OF DEFECT DONE						PICTURE OF DEFECT REQUIRED	COMPUTERS-Computer Monitors	945.99			NO	SEE NOTES	6/15/21	Feb 2, 2021	2/14/21	Damaged	box suffered damage/holes....unit will not turn on
4				TECH DATA CORP.		NEC	DENIED DIST. TIMEFRAME							COMPUTERS-Computer Monitors	389.58					10/1/21	Aug 11, 2021	8/16/21	Defective / unspecified	doa
5				SAMSUNG ELECTRONICS AMERICA		Samsung	DENIED BY MANUFACTURE							COMPUTERS-Computer Monitors	237.59					9/3/21			Defective / unspecified	per case # 59136369 screen flickers and does not show the display
6				TECH DATA/SONY PRODUCTS		Sony	DENIED DEFECT							HEADPHONES-Headphones	12					10/15/21			Open Box	OPEN BOX
7				EPSON AMERICA, INC	narics	Epson	DENIED PRODUCT TYPE OR SKU							PRINTERS-Inkjet Printers Accessories	8.49			SEE NOTE	NO	10/7/21			Open Box	BOX IS BROKEN
8				TECH DATA CORP.		NEC	DENIED DIST. TIMEFRAME							COMPUTERS-Computer Monitors	389.58					10/1/21	Aug 11, 2021	8/16/21	Defective / unspecified	doa
9				JEG & SONS INC.		Samsung	DENIED DIST. TIMEFRAME							MOBILE-Unlocked Cell Phones	345					1/25/21	Nov 20, 2019	1/7/21	Defective / unspecified	PHONE SCREEN FAILED
10				BBQ TRADING LLC	tonyk	OnePlus	TESTED CONFIRMED DEFECTIVE						TESTING TEAM	MOBILE-Unlocked Cell Phones	325			NO	NO	2/17/21	Nov 27, 2020	1/24/21	Defective / unspecified	The screen has stopped responding
11				DIGITAL DATA DISTRIBUTORS	llavner	LG	MANUFACTURE							TELEVISIONS-Televisions	1,896.25			NO		10/4/21	Sep 9, 2021	9/14/21	Damaged	CRACKED SCREEN
12				TECH DATA CORP.		Apple	DENIED DIST. TIMEFRAME							COMPUTER-Computer Monitor Adapters	59.39					10/11/21	Aug 24, 2021	10/6/21	Open Box	OPEN BOX
13				TECH DATA CORP.		NEC	DENIED DIST. TIMEFRAME							MULTIMEDIA-Commercial Monitors	1,000					10/7/21	Aug 10, 2021		Defective / unspecified	DOESNT WORK ,
14				D & H DISTRIBUTING CO.	tonyk	Datacolor	MANUFACTURE						DENIED DIST. TIMEFRAME	COMPUTER MONITORS-Calibration	249.33			SEE NOTE	NO	1/19/21	Oct 12, 2020	11/1/20	Defective / unspecified	THE SOFTWARE NEEDED TO OPERATE THIS DEVICE DOESNT WO HIS BE EXCHANGED WITH?
15				D & H DISTRIBUTING CO.	tonyk	MSI	MANUFACTURE						DENIED DIST. TIMEFRAME	COMPUTERS-Portable Monitors	191			SEE NOTE	NO	9/10/21	Aug 2, 2021	9/1/21	Defective / unspecified	This monitor will not recognize the HDMI input.
16				ROLAND CORPORATION U.S.		Boss	DENIED DEFECT							GUITARS-Effects Pedals	73.23					10/13/18			Open Box	OPEN BOX
17				TECH DATA CORP.	tonyk	NEC	PICTURE OF DEFECT DONE						PICTURE OF DEFECT REQUIRED	MULTIMEDIA-Commercial Monitors	895			YES	NO	3/21/18	Mar 6, 2018	3/9/18	Damaged	white line in middle of screen, no box damage
18				LINECO INC.		Lineco	DENIED BY MANUFACTURE							ARCHIVAL STORAGE-Museum & Preservation Boxes	14.4					9/19/21			Damaged	The box both outer and inner came damaged.
19				EPSON AMERICA, INC		Epson	DENIED DEFECT							PRINTERS-Large Format Printers	1,756					7/12/21			Damaged	Box is damaged.
20				TECH DATA CORP.		HP	DENIED DIST. TIMEFRAME							COMPUTER-Notebooks	2,022.82					10/5/21	Mar 22, 2021	10/5/21	Damaged	DAMAGED IN TRANSIT
21
22
23
24
25
On a Category

And here is a quick summary of the OP intentions:

if column F is 'ViewSonic', 'LG' or 'NEC' and column W is 'Defective / unspecified' then put in column H 'PICTURE OF DEFECT REQUIRED'

if column G is 'DENIED BY MANUFACTURE', 'DENIED DEFECT', 'DENIED PRODUCT TYPE OR SKU' or 'DENIED DIST. TIMEFRAME' has 'Open Box' in column W
then put in column H 'REEVALUATE - TO STOCK OR USED'

if column G is 'DENIED BY MANUFACTURE', 'DENIED DEFECT', 'DENIED PRODUCT TYPE OR SKU' or 'DENIED DIST. TIMEFRAME' has 'Defective / unspecified' in column W
then put in column H 'MFR'

if column N is 'MOBILE-Unlocked Cell Phones' and column X is not all upper case text then put in column H 'TT'
if column N is ;TELEVISIONS-Televisions' or 'COMPUTERS-Computer Monitors' or 'COMPUTER-Computer Monitor Adapters' or 'MULTIMEDIA-Commercial Monitors' or 'COMPUTER MONITORS-Calibration' Or
'COMPUTERS-Portable Monitors' and column W is damaged then put in column H 'TL'

if column T is 3years old from todays date then put in column H 'TL'

if column W is 'Damaged' and column O is less than $300 then put in column H, TL, UNLESS ...
' ---> This hasn't been seen ---> if column J is tested confirmed defective or tested confirmed damaged then put in column H, liq.com

@Tonyk1051 please correct/add to anything that I may have incorrect. I have posted this in hopes of getting you more responses to your OP.

 

[Tonyk1051]

Ok here is a quick summary of the file for those that don't like to download from links:

test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	Item ID	Kit Flag	Source	Vendor	Vendor Liason	Manufacturer	RTV Category			Date RTV Category	User RTV Category	Shelf	Prior Category	Product group	Booknet	Warehouse	Has Lithium	Open Box	DFI	Date Rcvd	Customer Purchase Date	RMA Date		Defect Desc	Serial No	Item Code	Cust Flag	Catalog No	Buyer	Brand	Description	Longn Catalog No	Po	Is used	Multi Vendor Flag	Last Vendor	Return Reason 1
2				INGRAM MICRO		ViewSonic	DENIED DIST. TIMEFRAME							MULTIMEDIA-See All Projectors	864.31					10/5/21	Oct 1, 2021	10/3/21	Open Box	OPEN BOX
3				LG ELECTRONICS USA, INC	narics	LG	PICTURE OF DEFECT DONE						PICTURE OF DEFECT REQUIRED	COMPUTERS-Computer Monitors	945.99			NO	SEE NOTES	6/15/21	Feb 2, 2021	2/14/21	Damaged	box suffered damage/holes....unit will not turn on
4				TECH DATA CORP.		NEC	DENIED DIST. TIMEFRAME							COMPUTERS-Computer Monitors	389.58					10/1/21	Aug 11, 2021	8/16/21	Defective / unspecified	doa
5				SAMSUNG ELECTRONICS AMERICA		Samsung	DENIED BY MANUFACTURE							COMPUTERS-Computer Monitors	237.59					9/3/21			Defective / unspecified	per case # 59136369 screen flickers and does not show the display
6				TECH DATA/SONY PRODUCTS		Sony	DENIED DEFECT							HEADPHONES-Headphones	12					10/15/21			Open Box	OPEN BOX
7				EPSON AMERICA, INC	narics	Epson	DENIED PRODUCT TYPE OR SKU							PRINTERS-Inkjet Printers Accessories	8.49			SEE NOTE	NO	10/7/21			Open Box	BOX IS BROKEN
8				TECH DATA CORP.		NEC	DENIED DIST. TIMEFRAME							COMPUTERS-Computer Monitors	389.58					10/1/21	Aug 11, 2021	8/16/21	Defective / unspecified	doa
9				JEG & SONS INC.		Samsung	DENIED DIST. TIMEFRAME							MOBILE-Unlocked Cell Phones	345					1/25/21	Nov 20, 2019	1/7/21	Defective / unspecified	PHONE SCREEN FAILED
10				BBQ TRADING LLC	tonyk	OnePlus	TESTED CONFIRMED DEFECTIVE						TESTING TEAM	MOBILE-Unlocked Cell Phones	325			NO	NO	2/17/21	Nov 27, 2020	1/24/21	Defective / unspecified	The screen has stopped responding
11				DIGITAL DATA DISTRIBUTORS	llavner	LG	MANUFACTURE							TELEVISIONS-Televisions	1,896.25			NO		10/4/21	Sep 9, 2021	9/14/21	Damaged	CRACKED SCREEN
12				TECH DATA CORP.		Apple	DENIED DIST. TIMEFRAME							COMPUTER-Computer Monitor Adapters	59.39					10/11/21	Aug 24, 2021	10/6/21	Open Box	OPEN BOX
13				TECH DATA CORP.		NEC	DENIED DIST. TIMEFRAME							MULTIMEDIA-Commercial Monitors	1,000					10/7/21	Aug 10, 2021		Defective / unspecified	DOESNT WORK ,
14				D & H DISTRIBUTING CO.	tonyk	Datacolor	MANUFACTURE						DENIED DIST. TIMEFRAME	COMPUTER MONITORS-Calibration	249.33			SEE NOTE	NO	1/19/21	Oct 12, 2020	11/1/20	Defective / unspecified	THE SOFTWARE NEEDED TO OPERATE THIS DEVICE DOESNT WO HIS BE EXCHANGED WITH?
15				D & H DISTRIBUTING CO.	tonyk	MSI	MANUFACTURE						DENIED DIST. TIMEFRAME	COMPUTERS-Portable Monitors	191			SEE NOTE	NO	9/10/21	Aug 2, 2021	9/1/21	Defective / unspecified	This monitor will not recognize the HDMI input.
16				ROLAND CORPORATION U.S.		Boss	DENIED DEFECT							GUITARS-Effects Pedals	73.23					10/13/18			Open Box	OPEN BOX
17				TECH DATA CORP.	tonyk	NEC	PICTURE OF DEFECT DONE						PICTURE OF DEFECT REQUIRED	MULTIMEDIA-Commercial Monitors	895			YES	NO	3/21/18	Mar 6, 2018	3/9/18	Damaged	white line in middle of screen, no box damage
18				LINECO INC.		Lineco	DENIED BY MANUFACTURE							ARCHIVAL STORAGE-Museum & Preservation Boxes	14.4					9/19/21			Damaged	The box both outer and inner came damaged.
19				EPSON AMERICA, INC		Epson	DENIED DEFECT							PRINTERS-Large Format Printers	1,756					7/12/21			Damaged	Box is damaged.
20				TECH DATA CORP.		HP	DENIED DIST. TIMEFRAME							COMPUTER-Notebooks	2,022.82					10/5/21	Mar 22, 2021	10/5/21	Damaged	DAMAGED IN TRANSIT
21
22
23
24
25
On a Category

And here is a quick summary of the OP intentions:

if column F is 'ViewSonic', 'LG' or 'NEC' and column W is 'Defective / unspecified' then put in column H 'PICTURE OF DEFECT REQUIRED'

if column G is 'DENIED BY MANUFACTURE', 'DENIED DEFECT', 'DENIED PRODUCT TYPE OR SKU' or 'DENIED DIST. TIMEFRAME' has 'Open Box' in column W
then put in column H 'REEVALUATE - TO STOCK OR USED'

if column G is 'DENIED BY MANUFACTURE', 'DENIED DEFECT', 'DENIED PRODUCT TYPE OR SKU' or 'DENIED DIST. TIMEFRAME' has 'Defective / unspecified' in column W
then put in column H 'MFR'

if column N is 'MOBILE-Unlocked Cell Phones' and column X is not all upper case text then put in column H 'TT'
if column N is ;TELEVISIONS-Televisions' or 'COMPUTERS-Computer Monitors' or 'COMPUTER-Computer Monitor Adapters' or 'MULTIMEDIA-Commercial Monitors' or 'COMPUTER MONITORS-Calibration' Or
'COMPUTERS-Portable Monitors' and column W is damaged then put in column H 'TL'

if column T is 3years old from todays date then put in column H 'TL'

if column W is 'Damaged' and column O is less than $300 then put in column H, TL, UNLESS ...
' ---> This hasn't been seen ---> if column J is tested confirmed defective or tested confirmed damaged then put in column H, liq.com

@Tonyk1051 please correct/add to anything that I may have incorrect. I have posted this in hopes of getting you more responses to your OP.

sorry for the delay, all the rules you have are correct

 

[Tonyk1051]

If your trying to learn case statements:

Here is a sheet change event script using Case:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/24/2021  8:59:04 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Column = 1 Then
       
        Select Case Target.Value
            Case "Alpha", "Bravo", "Charlie"
                Target.Offset(, 1).Value = "Yes"
   
            Case "Delta", "Echo", "Foxtrot"
                Target.Offset(, 1).Value = "No"
           
        End Select

End If
End Sub

this actually helped me out alot i managed to create a macro for the denied rules and it works perfect but im stuck for the rule
if column W is 'Damaged' and column O is less than $300 then put in column H, TL, UNLESS if column J is tested confirmed damaged then put in column H, liq.com below is the code i made and it kind of works, it will put anything that is damaged under 300 to TL but its supposed to change to liq.com if it also has "tested confirmed damaged" please see below and let me know what i need to change or if theres a better way to go about this

Sub ljbnj()

Dim cel As Range, Rng As Range, Defect As String

Worksheets("On a Category").Activate

Range("W2").Select

Set Rng = Range(Selection, Selection.End(xlDown))

For Each cel In Rng

Defect = cel.Text

Select Case Defect

Case "Damaged"

If cel.Offset(, 0) = "Damaged" And cel.Offset(, -8) <= 300 Then

cel.Offset(, -15) = "TL"

ElseIf cel.Offset(, -16) = "TESTED CONFIRMED DAMAGED" Then

cel.Offset(, -15) = "liq.com"

End If

'etcetera

End Select

cel.Offset(, -15).Interior.Color = vbGreen 'So you can see what already is done

Next cel



End Sub

 

[johnnyL]

Try the following:

Sub newTest()
'
'   Turn Settings off
      Application.ScreenUpdating = False                                    ' Turn Screen Updating off
         Application.Calculation = xlCalculationManual                      ' Turn AutoCalculation off
        Application.EnableEvents = False                                    ' Turn EnableEvents off
'
    Dim FirstRowOfData  As Long
    Dim LastRowInSheet  As Long
    Dim cel             As Range, Rng As Range
    Dim ThisCell        As String
    Dim wsDestination   As Worksheet
'
    FirstRowOfData = 2                                                                                                  ' <--- Set this value
    Set wsDestination = Sheets("Sheet17")                                                                                   ' <--- Set this to correct sheet
'
    LastRowInSheet = wsDestination.Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row    ' Find last used Row in sheet
'
'----------------------------------------------------------------------------------------------------------------------
'
    Set Rng = Range("F" & FirstRowOfData & ":F" & LastRowInSheet)                                                       ' F column
'
    For Each cel In Rng
        ThisCell = cel.Text
'
        Select Case ThisCell
            Case "ViewSonic", "LG", "NEC"
                If cel.Offset(0, 17) = "Defective / unspecified" Then cel.Offset(0, 2) = "PICTURE OF DEFECT REQUIRED"
        End Select
    Next
'
'----------------------------------------------------------------------------------------------------------------------
'
    Set Rng = Range("G" & FirstRowOfData & ":G" & LastRowInSheet)                                                       ' G column
'
    For Each cel In Rng
        ThisCell = cel.Text
'
        Select Case ThisCell
            Case "DENIED BY MANUFACTURE", "DENIED DEFECT", "DENIED PRODUCT TYPE OR SKU", "DENIED DIST. TIMEFRAME"
                If cel.Offset(0, 16) = "Open Box" Then cel.Offset(0, 1) = "REEVALUATE - TO STOCK OR USED"
                If cel.Offset(0, 16) = "Defective / unspecified" Then cel.Offset(0, 1) = "MFR"
        End Select
    Next
'
'----------------------------------------------------------------------------------------------------------------------
'
    Set Rng = Range("N" & FirstRowOfData & ":N" & LastRowInSheet)                                                       ' N column
'
    For Each cel In Rng
        ThisCell = cel.Text
'
        Select Case ThisCell
            Case "MOBILE-Unlocked Cell Phones"
                If cel.Offset(0, 10) <> UCase(cel.Offset(0, 10)) Then cel.Offset(0, -6) = "TT"                          ' If column X not all caps then ...
            Case "TELEVISIONS-Televisions", "COMPUTERS-Computer Monitors", "COMPUTER-Computer Monitor Adapters", "MULTIMEDIA-Commercial Monitors", "COMPUTER MONITORS-Calibration", "COMPUTERS-Portable Monitors"
                If cel.Offset(0, 9) = "Damaged" Then cel.Offset(0, -6) = "TL"
        End Select
    Next
'
'----------------------------------------------------------------------------------------------------------------------
'
    Set Rng = Range("T" & FirstRowOfData & ":T" & LastRowInSheet)                                                       ' T column
'
    For Each cel In Rng
        If DateAdd("yyyy", 3, cel) < Date Then cel.Offset(0, -12) = "TL"
    Next
'
'----------------------------------------------------------------------------------------------------------------------
'
    Set Rng = Range("W" & FirstRowOfData & ":W" & LastRowInSheet)                                                       ' W column
'
    For Each cel In Rng
        ThisCell = cel.Text
'
        Select Case ThisCell
            Case "Damaged"
                If cel.Offset(0, -8) < 300 Then
                    If cel.Offset(0, -13) = "tested confirmed defective" Or cel.Offset(0, -13) = "tested confirmed damaged" Then
                        cel.Offset(0, -15) = "liq.com"
                    Else
                        cel.Offset(0, -15) = "TL"
                    End If
                End If
        End Select
    Next
'
'----------------------------------------------------------------------------------------------------------------------
'
'   Turn Settings back on
    Application.EnableEvents = True                                                             ' Turn EnableEvents back on
    Application.Calculation = xlCalculationAutomatic                                            ' Turn AutoCalculation back on
    Application.ScreenUpdating = True                                                           ' Turn Screen Updating back on
End Sub