multi column based macro

[Tonyk1051]

heres the link to the sample file to use macro
T2 TEST MACRO.xlsm

below are all the rules i follow manually and putting them all in one macro would just cause the data to be incorrect so i need it to be 3 separate macros. ive worded it best as can. thanks

Macro 1
If column S has keyword “YES” then put in column H in house repair UNLESS column N has the word Telescope, Lenses, Camera, projector then leave alone ALSO if value is 500 or greater leave alone but highlight the line in blue

If column T has a date that is older than 3 years from todays date put in column H in house repair

IF column O is under $20 then put in column H LR



Macro 2
If column F has keyword Manfrotto, Godox, Yashica or Tiffen and column G is TESTED CONFIRMED DEFECTIVE or TESTED CONFIRMED DAMAGED then put in column H “in house repair”

If column F has keyword Viewsonic, LG or NEC , column N has keyword “monitor” and column W is Defective / unspecified then put in column H pic needed

If column F has keyword SANUS or Middle Atlantic then put in column H Levi stuff

If column F has keyword Dell and column G has TESTED CONFIRMED DAMAGED and column N has the word monitor then put in column H LSI IF it’s not then put in house repair

If column F has keyword HP or Apple and column G is blank or MANUFACTURE and column N has keyword “Printers, Workstations, Monitors, Desktop, Macbook, Tablet, Smartwatches or Notebook” then put in column H TT

If column F is Sony, column N has keyword “headphones” and column G is TESTED CONFIRMED DEFECTIVE or TESTED CONFIRMED DAMAGED then put in column H in house repair, if column G is blank put in column H TT

If column F is B+W and column G is either TESTED CONFIRMED DEFECTIVE OR TESTED CONFIRMED DAMAGED then put in column H in house repair

If column F is Google and column G is MANUFACTURE put in column H TT




Macro 3
If column G has keyword TESTED CONFIRMED DAMAGED and column O is less than 300 then put in column H in house repair

If column G has keyword Liquidate then put in column H in house repair UNLESS column N has the word “Monitors” or “Printers” put in column H LSI

If column W has key word “Open Box” highlight the line in yellow

If column N has key word PRINTERS-Inkjet Printers, PRINTERS-Laser Printers, PRINTERS-Large Format Printers, PRINTERS-3D Printers, PRINTERS-Shredders AND column G has keyword “tested confirmed defective or tested confirmed damaged put in column H in house repair

If column N has keyword Mirrorless Cameras, DSLR Cameras or Point & Shoot Cameras and column P has ZNFI then put in column H TT

If column N has keyword “SCANNERS-Flatbed & Document Scanners” and column W is Defective /unspecified or Damaged put in column H TT

If column N has key word Commercial Monitors, Computer Monitors, Camera Monitors or Recording Monitors and column G is TESTED CONFIRMED DAMAGED and column O is below 800 put in column H in house repair

If column N has keyword Motherboards, column G is TESTED CONFIRMED DAMAGED and column O is less then 300 then put in house repair in column H

 

[Tonyk1051]

Hi Tony,

maybe this will give you some hints on where to start as most of the stuff is repetetive and a macro recording may be used as a guide to have some code to work on (no, macro code is mostly hard-coded and needs a rework but if you supply the basic code I'm sure you get a response with a reworked code).

MrE_1229714_1702B13_multi column based_230212.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Macro	Column	Condition	Action	Col	Contents	Other Action	More Info	what to use
2	1a	S	YES	combined	H	in house repair			Advanced Filter
3	1a	N	neither Telescope, Lenses, Camera, projector	combined	none	none			Advanced Filter
4	1a	O	>500	action	none	none	colour row	blue	Advanced Filter
5
6	1b	T	<=today - 3 years	action	H	in house repair	none	none	AutoFilter
7
8	1c	O	<20	action	H	LR	none	none	AutoFilter
9
10	2a	F	either Manfrotto, Godox, Yashica or Tiffen	combined					AutoFilter
11	2a	G	either TESTED CONFIRMED DEFECTIVE or TESTED CONFIRMED DAMAGED	action	H	in house repair	none	none	AutoFilter
12
13	2b	F	either Viewsonic, LG or NEC	combined				none	AutoFilter
14	2b	N	monitor	combined				none	AutoFilter
15	2b	W	Defective / unspecified	combined	H	pic needed	none	none	AutoFilter
16
17	2c	F	SANUS or Middle Atlantic	action	H	Levi stuff	none	none	AutoFilter
18
19	2d	F	Dell	combined					AutoFilter
20	2d	G	TESTED CONFIRMED DAMAGED	combined					AutoFilter
21	2d	N	contains monitor	action	H	see far right, unclear to me	none	none	AutoFilter		specify: LSI IF it’s not then put in house repair
22
23	2e	F	either HP or Apple	combined					Advanced Filter
24	2e	G	blank or MANUFACTURE	combined					Advanced Filter
25	2e	N	Printers, Workstations, Monitors, Desktop, Macbook, Tablet, Smartwatches or Notebook	action	H	TT	none	none	Advanced Filter
26
27	2f	F	Sony	combined					AutoFilter
28	2f	N	headphones	combined					AutoFilter
29	2f	G	either TESTED CONFIRMED DEFECTIVE or TESTED CONFIRMED DAMAGED	action	H	in house repair	none	none	AutoFilter
30
31	2g	F	Sony	combined					AutoFilter
32	2g	N	headphones	combined					AutoFilter
33	2g	G	blank	action	H	TT	none	none	AutoFilter
34
35	2h	F	B+W	combined					AutoFilter
36	2h	G	either TESTED CONFIRMED DEFECTIVE or TESTED CONFIRMED DAMAGED	action	H	in house repair	none	none	AutoFilter
37
38	2i	F	Google	combined					AutoFilter
39	2i	G	MANUFACTURE	action	H	TT	none	none	AutoFilter
40
41	3a	G	TESTED CONFIRMED DAMAGED	combined					AutoFilter
42	3a	O	less than 300	action	H	in house repair	none	none	AutoFilter
43
44	3b	G	Liquidate	combined					AutoFilter
45	3b	N	either Monitors or Printers	action	H	LSI	none	none	AutoFilter
46
47	3c	G	Liquidate	combined					AutoFilter
48	3c	N	exclude Monitors or Printers	action	H	in house repair	none	none	AutoFilter
49
50	3d	W	Open Box	action			colour row	yellow	AutoFilter
51
52	3e	N	either PRINTERS-Inkjet Printers, PRINTERS-Laser Printers, PRINTERS-Large Format Printers, PRINTERS-3D Printers, PRINTERS-Shredders	combined					Advanced Filter
53	3e	G	either tested confirmed defective or tested confirmed damaged	action	H	in house repair	none	none	Advanced Filter
54
55	3f	N	either Mirrorless Cameras, DSLR Cameras or Point & Shoot Cameras	combined					Advanced Filter
56	3f	P	ZNFI	action	H	TT	none	none	Advanced Filter
57
58	3g	N	SCANNERS-Flatbed & Document Scanners	combined					AutoFilter
59	3g	W	either Defective /unspecified or Damaged	action	H	TT	none	none	AutoFilter
60
61	3h	N	either Commercial Monitors, Computer Monitors, Camera Monitors or Recording Monitors	combined					Advanced Filter
62	3h	G	TESTED CONFIRMED DAMAGED	combined					Advanced Filter
63	3h	O	below 800	action	H	in house repair	none	none	Advanced Filter
64
65	3i	N	Motherboards	combined					AutoFilter
66	3i	G	TESTED CONFIRMED DAMAGED	combined					AutoFilter
67	3i	O	less then 300	action	H	in house repair	none	none	AutoFilter
MrE1702B13

Before applying the Advanced Filter I added a worksheet and set it up like this:

T2 TEST MACRO.xlsm
	A	B	C	D	E	F
1	DFI	Product group	Product group	Product group	Product group	Booknet
2	YES	<>Telescope	<>Lenses	<>Camera	<>projector	<500
Criteria AdvFilt

Cell Formulas
Range	Formula
B2	B2	="<>Telescope"
C2	C2	="<>Lenses"
D2	D2	="<>Camera"
E2	E2	="<>projector"
F2	F2	="<500"

I named Range("A1:F2") as myCrit which I can use as Criteria range when starting the Advanced Filter on the sheet in question.

HTH
Holger

As always haho be, your help is appreciated.