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.

 

[Michael M]

Don't know what precedes the For loop but try removing the periods.

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

 

[Excel_Assis]

Hi Michael thanks for the suggestion I take it you meant remove the "" however this did not work.

The preceding code and following code is this.


Dim wsSrc As Worksheet: Set wsSrc = Sheets("Claim Form")
Dim wsDest As Worksheet: Set wsDest = Sheets("Sort Sum")
Dim wsDestSum As Worksheet: Set wsDestSum = Sheets("Cash Payment Voucher")
Dim i As Long
With wsDest
.UsedRange.Clear
wsSrc.Range("B9:B47,E9:E47,I9:I47,K9:K47").Copy .Range("B10")

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
Next i

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

 

[Michael M]

Not certain, but shouldn't this line refer to a sheet as well

For i = 37 To .Range("B" & .Rows.Count).End(xlUp).Row

wsDest.activate
For i = 37 To .Range("B" & .Rows.Count).End(xlUp).Row

 

[Excel_Assis]

Hi Michael after looking deeper working on the problem I believe the problem exists with the .Value part of the For Loop.

I think the values in the cells are creating the problem. Could you advise if I am correct, the sample shown below is the data the For Loop is running on and how this could be fixed.

Excel Workbook
	B	C	D	E
10	ITEM	ACC No.	JOB NO. A	JOB NO. B
11	Field Hand Services	9-0120	CASN46	CAPG21
12	SURVEYS	9-0400	CA9999
13	Petty Cash	1-1109	CAPK69	CA9999
14	Training/Conferences/Courses	9-0125	CAPG21
15	Drafting & Computing Services	9-0115	CASN46
16	Payroll Recoveries	6-1099	CA0000
17	Staff Paye	2-2101	CAPG21
18	Management Fees	4-3000		CAPL70
19	Management Fees	4-3000		CAPG20
20	Director Fees	6-1091		CASR47
21	Vehicle Operating Costs	9-0760	CASO32	CAPK69
22
23
24
Sheet2

 

[hyat]

Any particular reason why you're starting the loop at i = 37?

 

[hyat]

Your starting row in the data you posted is 11, so it seems to me the loop should start at 11 too, not 37.

 

[Excel_Assis]

Hi hyat I am of the understanding i = 37 represents the number on lines the loop is looking at.

 

[hyat]

Also, it may be that the loop is looking at data in the "Sort Sum" sheet, whereas your sheet is called "Sheet2"?

 

[hyat]

A useful method of finding out how the code is working, i.e. whether the loop is being entered into, what values the variables are taking, whether the problem is with the .Value part or not, would be to step through the code line by line by using the F8 key.