My Macro Isn't Working

[shayalsamawi]

Hi,

Thanks so so much for helping me. I honestly love this site so much - it gives me wikipedia vibes (big fan).

My macro isn't working! Not sure what to do - super frustrating!

This is my sheet:

Book123.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
4		Project Description	Sub Ledger	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Finished Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours	Uncompleted Hours
5				Person 1	Person 2	Person 3	Person 4	Person 5	Person 6	Person 7	Person 8	Person 9	Person 10	Person 11	Person 12	Person 13	Person 14	Person 15	Person 16
6		Project Description	Subledger	106324	6101274	106321	106553	171134	252888	6100650	119526	141816	6101053	273969	163932	6101224	6100537	6101203	106313
7	Indirect Time	Admin - Sales + Marketing	SM
8		Admin - Product Support	PS
9		Admin - Applications Support	AS
10		Admin - Customer Support	CS
11		Customer Support (Help Desk)	HD
12		Admin - General	A						2	3
13		Leave	L
14		Bank Holiday	B
15		Training	T
16		Sick	S					1
17		Time Off in Lieu	TOIL
18		Other	O
19	Development Programme	PRODUCT SUPPORT - Pulsar/Firebird	052L2, 10
20		PRODUCT SUPPORT - Rockcore Product Support	052L3, 10		3	4	37
21		PRODUCT SUPPORT - Overburden	052L3, 20
22		PRODUCT SUPPORT - MQC	052L1, 10
23		PRODUCT SUPPORT - CEM	052L1, 20
24		PRODUCT SUPPORT - LEXMAR	052L1, 30
25		PRODUCT SUPPORT - MQR	052L4, 10	2
26		Hypersense Product Support	052L21, 10							2
27		Productionisation	052L12, 130
28		90 80MHz Halbach	052L13,10
29		Overburden Mk2	052L20, 10
30		MQC-R	052L26, 10
31		Q-Sense MQC	052L27, 10
32		Automation for X-Pulse	052L28, 10
33		AZUL	052L14, 10
34		BLOC - Work Package 1	052L24,10
35		BLOC - Work Package 2	052L24,20
36		BLOC - Work Package 3	052L24,30
37		BLOC - Work Package 4	052L24,40
38		BLOC - Work Package 5	052L24,50
39		BLOC - Work Package 6	052L24,60
40		Spinflow V3.1	052L29, 10
41		External lock for X-Pulse	052L30, 10
42		Switched Probe X-Pulse	052L38, 10
43		X-Pulse 80/Cost Down	052L31, 10
44		Halbach 20	052L32, 10
45		MQC HT	052L33, 10
46		Spin Studio Application Releases - Toothpaste	052L34, 10
47		Spin Echo (Grant)	052L35, 10
48		X-Pulse Probe	052L38, 10
49		Nottingham Trent University	052L37, 10
50	Project Build Time	VDvG	052J926
51		Copenhagen	052J777
52		Dupont Autosampler	052J2750
53	Installations	MQR20-IFPEN, France	052X002
54		Geospec 12-50 and 2-100 - SONATRACH	052X013
55		Geospec 12-50 and 2-75 - KAUST	052X218
56		Munich PM visit	052X902
57		ESSO MQC to MQC+ upgrade	052X898
58		X-Pulse installation at Heriot-Watt University - 052S573 - 4 days on site	052X945
59		DuPont MQC+ MQ-Auto Installation (preparation)	052X784
60		University of Oxford glove box move (initial survey)	052X981
61		Installation and training at CBRN Romania 052S650	052X973
62		Lulea University of Technology Sweden (052S789)	052X1018
63	Service	FOC Customer Support
64		Support Contracts	SUPP
65		Munster University Pulsar Repair and Upgrade to Win 10
66		Repair to PUL1026 - Germany (Rototec-Spintec)	052X890
67		Holme House Repair Visit	052X1022
68		ONGC CEWELL remote PM	052X984
69		Minakem Pulsar Assessment + Repair	052X1021
70		Toulouse Upgrade and Repair	052L36, 10
71		United Utilities MQC+ Autosampler PM	052X1033
72		Koura Global (Mexichem) PM	052X1039
Sheet1

Cell Formulas
Range	Formula
D4:S4	D4	=IF(SUM(D7:D94)>36.99,"Finished Hours","Uncompleted Hours")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:S4	Cell Value	contains "Finished Hours"	text	NO
D4:S4	Cell Value	contains "Uncompleted Hours"	text	NO
D6:S6	Cell Value	contains "Finished Hours"	text	NO
I12	Cell Value	contains "Finished"	text	NO
D6:S6	Cell Value	contains "Uncompleted Hours"	text	NO
D7	Cell Value	contains ""Uncompleted Hours""	text	NO

This is the VBA code I'm using:

Sub shayalsamawi()
Dim Ary As Variant, Nary As Variant
Dim r As Long, c As Long, nr As Long

Ary = Sheets("Sheet1").Range("D6").CurrentRegion.Value2
ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 4)
For c = 3 To UBound(Ary, 2)
For r = 2 To UBound(Ary)
If Ary(r, c) <> "" Then
nr = nr + 1
Nary(nr, 1) = Ary(1, c)
Nary(nr, 2) = Ary(r, 1)
Nary(nr, 3) = Ary(r, 2)
Nary(nr, 4) = Ary(r, c)
End If
Next r
Next c
Sheets("Sheet2").Range("B2").Resize(nr, 4).Value = Nary
End Sub

This is what it's giving me:

Book123.xlsm
	B	C	D	E
2	Sub Ledger		Project Description	Subledger
3	Sub Ledger	Indirect Time	Admin - Sales + Marketing	SM
4	Sub Ledger		Admin - Product Support	PS
5	Sub Ledger		Admin - Applications Support	AS
6	Sub Ledger		Admin - Customer Support	CS
7	Sub Ledger		Customer Support (Help Desk)	HD
8	Sub Ledger		Admin - General	A
9	Sub Ledger		Leave	L
10	Sub Ledger		Bank Holiday	B
11	Sub Ledger		Training	T
12	Sub Ledger		Sick	S
13	Sub Ledger		Time Off in Lieu	TOIL
14	Sub Ledger		Other	O
15	Sub Ledger	Development Programme	PRODUCT SUPPORT - Pulsar/Firebird	052L2, 10
16	Sub Ledger		PRODUCT SUPPORT - Rockcore Product Support	052L3, 10
17	Sub Ledger		PRODUCT SUPPORT - Overburden	052L3, 20
18	Sub Ledger		PRODUCT SUPPORT - MQC	052L1, 10
19	Sub Ledger		PRODUCT SUPPORT - CEM	052L1, 20
20	Sub Ledger		PRODUCT SUPPORT - LEXMAR	052L1, 30
21	Sub Ledger		PRODUCT SUPPORT - MQR	052L4, 10
22	Sub Ledger		Hypersense Product Support	052L21, 10
23	Sub Ledger		Productionisation	052L12, 130
24	Sub Ledger		90 80MHz Halbach	052L13,10
Sheet2

This is what I want:

Book123.xlsm
	M	N	O
22	106324	052L2, 10	6
23	106324	052L3, 10	5
24	6101274	052L1, 20	8
Sheet2

So like, the person ID, the project they worked on and how many hours they did (avoiding any cells where there aren't hours in the timesheet). Could you please help me? Would massively appreciate it!!

 

[Fluff]

Duplicate to: Macro help?

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.