VBA Code Question

[Excel_Assis]

Could someone please help with the following.


For i = 37 To .Range("B" & .Rows.Count).End(xlUp).Row
If .Range("E" & i).Value <> "" Then
.Range("E" & i).Offset(0, -1).Value = .Range("E" & i).Value
End If



The code works to line starting with For i = 37 To .Range
then skips over the rest. It all worked fine on another spread sheet.

 

[Excel_Assis]

hyat sorry the macro actually has the right page name in it.

 

[Excel_Assis]

Guys if I run this part of the macro on this data it works fine.

Sub Button1_Click()
'Sub Advanced_Filter_Sheet3()
Dim wsSrc As Worksheet: Set wsSrc = Sheets("Sheet1")
Dim wsDest As Worksheet: Set wsDest = Sheets("Sheet2")
Dim wsDestSum As Worksheet: Set wsDestSum = Sheets("Sheet3")
Dim i As Long
With wsDest
.UsedRange.Clear
wsSrc.Range("B8:B22,E8:E22,I8:I22,K8:K22").Copy .Range("B10")


For i = 11 To .Range("B" & .Rows.Count).End(xlUp).Row
If .Range("E" & i).Value <> "" Then
.Range("E" & i).Offset(0, -1).Value = .Range("E" & i).Value
End If
Next i

.Columns("E").Delete
.Range("B10:D40").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("E10"), Unique:=True
.Columns("B:D").Delete
End With

Excel Workbook
	B	C	D	E
10	ITEM	ACC No.	JOB NO. A	JOB NO. B
11	Cat	10005	1234	9876
12	Dog	10003	1234
13	Rat	10006	1234
14	Pig	10008	1234
15	Rat	10006	1234
16	Dog	10003		9876
17	Dog	10003	1234
18	Pig	10008		9876
19	Cat	10005	1234
20	Dog	10003	6789
21	Bird	10110	1111
22	Horse	10118	3232
23	Dog	10003	3245
24	Bull	18888	8787
Sheet2

But if I run the same part of the macro on this data it does not work.

Excel Workbook
	B	C	D	E
10	ITEM	ACC No.	JOB NO. A	JOB NO. B
11	Field Hand Services	9-0120	1046	2021
12	SURVEYS	9-0400	9999
13	Petty Cash	1-1109	1069	2099
14	Training/Conferences/Courses	9-0125	1021
15	Drafting & Computing Services	9-0115	1046
16	Payroll Recoveries	6-1099	1000
17	Staff Paye	2-2101	1021
18	Management Fees	4-3000		2070
19	Management Fees	4-3000		2020
20	Director Fees	6-1091		2047
21	Vehicle Operating Costs	9-0760	1032	2069
22
23
24
Sheet2

I have no idea why. Any suggestions.

 

[Michael M]

Did you try Hyats method of stepping through the code using F8 to see what happens at each line ?
I can't see why sample 1 is working and sample 2 is not!

 

[hyat]

Excel_Assis, I tried pasting the second sample of data you gave in columns B, E, I, and K of sheet 1 respectively, and running the code. It worked ok.

Maybe there's a difference in how/where you're pasting sample 1 and sample 2? Or alternatively maybe you have put sample 1 and sample 2 in different ranges / sheets?

 

[Excel_Assis]

Hi Guys after stepping through the code using f8 at this point in the code last line shown below (.Columns("B:D").Delete) on Sample 1 I am left with the following:

'Sub Advanced_Filter_Sheet3()
Dim wsSrc As Worksheet: Set wsSrc = Sheets("Sheet1")
Dim wsDest As Worksheet: Set wsDest = Sheets("Sheet2")
Dim wsDestSum As Worksheet: Set wsDestSum = Sheets("Sheet3")
Dim i As Long
With wsDest
.UsedRange.Clear
wsSrc.Range("B8:B22,E8:E22,I8:I22,K8:K22").Copy .Range("B10")


For i = 15 To .Range("B" & .Rows.Count).End(xlUp).Row
If .Range("E" & i).Value <> "" Then
.Range("E" & i).Offset(0, -1).Value = .Range("E" & i).Value
End If
Next i

.Columns("E").Delete
.Range("B10:D40").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("E10"), Unique:=True
.Columns("B:D").Delete

Excel Workbook
	B	C	D	E	F	G
10	ITEM	ACC No.	JOB NO. A	ITEM	ACC No.	JOB NO. A
11	Cat	10005	1234	Cat	10005	1234
12	Dog	10003	1234	Dog	10003	1234
13	Rat	10006	1234	Rat	10006	1234
14	Pig	10008	1234	Pig	10008	1234
15	Rat	10006	1234	Dog	10003	9876
16	Dog	10003	9876	Pig	10008	9876
17	Dog	10003	1234	Dog	10003	6789
18	Pig	10008	9876	Bird	10110	1111
19	Cat	10005	1234	Horse	10118	3232
20	Dog	10003	6789	Dog	10003	3245
21	Bird	10110	1111	Bull	18888	8787
22	Horse	10118	3232
23	Dog	10003	3245
24	Bull	18888	8787
25
Sheet2

At the same point in the code of Sample2 I am left with this:

As you can see both columns are the same here not like in Samlpe 1 where it shows the differences.

Excel Workbook
	B	C	D	E	F	G
10	ITEM	ACC No.	JOB NO. A	ITEM	ACC No.	JOB NO. A
11	Field Hand Services	9-0120	1046	Field Hand Services	9-0120	1046
12	SURVEYS	9-0400	9999	SURVEYS	9-0400	9999
13	Petty Cash	1-1109	1069	Petty Cash	1-1109	1069
14	Training/Conferences/Courses	9-0125	1021	Training/Conferences/Courses	9-0125	1021
15	Drafting & Computing Services	9-0115	1046	Drafting & Computing Services	9-0115	1046
16	Payroll Recoveries	6-1099	1000	Payroll Recoveries	6-1099	1000
17	Staff Paye	2-2101	1021	Staff Paye	2-2101	1021
18	Management Fees	4-3000	2070	Management Fees	4-3000	2070
19	Management Fees	4-3000	2020	Management Fees	4-3000	2020
20	Director Fees	6-1091	2047	Director Fees	6-1091	2047
21	Vehicle Operating Costs	9-0760	2069	Vehicle Operating Costs	9-0760	2069
22
23
24
25
Sheet2

 

[hyat]

That may be because in Sample 1, there were 3 non-unique rows that AdvancedFilter got rid of whereas in Sample 2, each row is unique...

 

[Excel_Assis]

Here is the data from sheet1 as you can see on the first row of data the is for example two Field Hand Services with unique Jobs No. Job No. A and Job No. B it should create a record for this on sheet2 like it does in sample 1 for the dog.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
8	DATE	ITEM			ACC No.	US$		RECEIPT	JOB NO. A	JOB A US$	JOB NO. B	JOB B US$
9		Field Hand Services			9-0120	R5000	$1.22		1046	$0.61	2021	$0.61
10		SURVEYS			9-0400	R70	$0.02		9999	$0.02
11		Petty Cash			1-1109	$2.00	$2.00		1069	$1.00	2099	$1.00
12		Training/Conferences/Courses			9-0125		$0.00		1021	$0.00
13		Drafting & Computing Services			9-0115	R20000	$4.88		1046	$4.88
14		Payroll Recoveries			6-1099	R500000	$121.95		1000	$121.95
15		Staff Paye			2-2101	R470	$0.11		1021	$0.11
16		Management Fees			4-3000	$10,000.00	$10,000.00				2070	$10,000.00
17		Management Fees			4-3000	$9,000.00	$9,000.00				2020	$9,000.00
18		Director Fees			6-1091	$25,000.00	$25,000.00				2047	$25,000.00
19		Vehicle Operating Costs			9-0760	$550.00	$550.00		1032	$275.00	2069	$275.00
20							$0.00
21							$0.00
22							$0.00
23							0
24							0
25
Sheet1

 

[hyat]

But I don't see any records being created for dog. I do however see that three records (highlighted in yellow) appearing in columns A:C are not appearing in columns D:F. AdvancedFilter has filtered these out because they were not unique. If you are looking to create new records, then perhaps you shouldn't be using AdvancedFilter.

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 32px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="BACKGROUND-COLOR: #33cccc; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt; FONT-WEIGHT: bold">ITEM</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt; FONT-WEIGHT: bold">ACC No.</TD><TD style="BACKGROUND-COLOR: #33cccc; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt; FONT-WEIGHT: bold">JOB NO. A</TD><TD style="BACKGROUND-COLOR: #33cccc; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt; FONT-WEIGHT: bold">ITEM</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #33cccc; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt; FONT-WEIGHT: bold">ACC No.</TD><TD style="BACKGROUND-COLOR: #33cccc; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt; FONT-WEIGHT: bold">JOB NO. A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Cat</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10005</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Cat</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10005</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Dog</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10003</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Dog</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10003</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Rat</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10006</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Rat</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10006</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Pig</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10008</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Pig</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10008</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Rat</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10006</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Dog</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10003</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">9876</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Dog</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10003</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">9876</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Pig</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10008</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">9876</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Dog</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10003</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Dog</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10003</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">6789</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Pig</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10008</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">9876</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Bird</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10110</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1111</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Cat</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10005</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1234</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Horse</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10118</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3232</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Dog</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10003</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">6789</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Dog</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10003</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3245</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Bird</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10110</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1111</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Bull</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">18888</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">8787</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Horse</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10118</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3232</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Dog</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10003</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3245</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Bull</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">18888</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">8787</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[CharlesChuckieCharles]

Try sticking step -1 on the end of the for

For i = 37 To .Range("B" & .Rows.Count).End(xlUp).Row step -1
so it counts DOWM from 37 to the first in the range

 

[Excel_Assis]

Hi Charles thanks for the suggestion however that did not work. Hyat in sample1 at the end of the macro Post 15 here are the dog that are present on sheet2.

Excel Workbook
	B	C	D	E	F	G
10	ITEM	ACC No.	JOB NO. A	ITEM	ACC No.	JOB NO. A
11	Cat	10005	9876	Cat	10005	9876
12	Dog	10003	1234	Dog	10003	1234
13	Rat	10006	1234	Rat	10006	1234
14	Pig	10008	1234	Pig	10008	1234
15	Rat	10006	1234	Dog	10003	9876
16	Dog	10003	9876	Pig	10008	9876
17	Dog	10003	1234	Cat	10005	1234
18	Pig	10008	9876	Dog	10003	6789
19	Cat	10005	1234	Bird	10110	1111
20	Dog	10003	6789	Horse	10118	3232
21	Bird	10110	1111	Dog	10003	3245
22	Horse	10118	3232	Bull	18888	8787
23	Dog	10003	3245
24	Bull	18888	8787
25
Sheet2

In the next part on the code a sort is done and only unique records of Job Nos are combined into one Job No column.

However in sample2 as shown previously I do not get the records all the records with the unique Job No. As you can see in Post 17 there is Field Hand Services with a Job No A and a Job No B so that should be shown on sheet2 but it is not.

As you can see no Field Hand Services with Job No 1046 be for the sort takes place.

I do not understand this as it is the same macro used on both work books both have the same sheet names and cell locations the only difference is the data.

Excel Workbook
	B	C	D	E	F	G
10	ITEM	ACC No.	JOB NO. A	ITEM	ACC No.	JOB NO. A
11	Field Hand Services	9-0120	2021	Field Hand Services	9-0120	2021
12	SURVEYS	9-0400	9999	SURVEYS	9-0400	9999
13	Petty Cash	1-1109	2099	Petty Cash	1-1109	2099
14	Training/Conferences/Courses	9-0125	1021	Training/Conferences/Courses	9-0125	1021
15	Drafting & Computing Services	9-0115	1046	Drafting & Computing Services	9-0115	1046
16	Payroll Recoveries	6-1099	1000	Payroll Recoveries	6-1099	1000
17	Staff Paye	2-2101	1021	Staff Paye	2-2101	1021
18	Management Fees	4-3000	2070	Management Fees	4-3000	2070
19	Management Fees	4-3000	2020	Management Fees	4-3000	2020
20	Director Fees	6-1091	2047	Director Fees	6-1091	2047
21	Vehicle Operating Costs	9-0760	2069	Vehicle Operating Costs	9-0760	2069
22
23
Sheet2