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

 

[ACommandLineKindaGuy]

Can you just show the code here? I don't think anyone's willing--at least I'm not--to just download some macro-enabled excel file

 

[Tonyk1051]

Can you just show the code here? I don't think anyone's willing--at least I'm not--to just download some macro-enabled excel file

There is no code, I’m asking for one….

 

[James006]

There is no code, I’m asking for one….

It would seem to me, based on your precise demands , you do need to hire a VBA expert

 

[Tonyk1051]

It would seem to me, based on your precise demands , you do need to hire a VBA expert

thanks for the advice but no. I come here first. If I get nothing then i get nothing.

 

[ACommandLineKindaGuy]

I don't get it. What's the T2 TEST MACRO.xlsm file? And if there's no code, why does it have an .xlsm extension rather than a .xlsx?

 

[Tonyk1051]

I don't get it. What's the T2 TEST MACRO.xlsm file? And if there's no code, why does it have an .xlsm extension rather than a .xlsx?

The file is a test file for when a person who wants to test the code they have written based on the rules

 

[ACommandLineKindaGuy]

I see--so although you haven't actually said it--you don't allow uploads. You provide what you haven't actually said is an empty macro-enabled file. And I, not knowing you from Adam, am to open it and then work on it in the cloud. Well, maybe you'll get someone to help you. I'll give it one last shot: Post an image or better yet, a mini-sheet so we can see the organization of what you want to manipulate

 

[Tonyk1051]

I don't get it. What's the T2 TEST MACRO.xlsm file? And if there's no code, why does it have an .xlsm extension rather than a .xlsx?

I see--so although you haven't actually said it--you don't allow uploads. You provide what you haven't actually said is an empty macro-enabled file. And I, not knowing you from Adam, am to open it and then work on it in the cloud. Well, maybe you'll get someone to help you. I'll give it one last shot: Post an image or better yet, a mini-sheet so we can see the organization of what you want to manipulate

No worries, the admins here usually help me out. But I think I’ll just use chatgpt

 

[HaHoBe]

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