stop looping and comments on VBA

[shaharul]

I would apprecite if anybody could give any comment on the VBA code
wrote below. It works, but I fell there is a lot need to be improved
Especially the looping for x where i have to stop it at x + 100 becaus
I wouldn't know the value of x. Does anybody have any idea how to sto
x before it reached x + 100.

Thank you very much.

Sub UpdateCheque()

Dim lLastRow As Long
Dim msg1, msgCls
Dim title1, titleCls
Dim default1, defaultCls
Dim strLast, strCls
Dim x As Long
Dim y, z As Long
Dim i As Long
Dim NewRow As Long
Dim ClsChq As Long

With Workbooks("SCSB_Accounts_1Jan04 to 31Dec04"). _
Worksheets("MBB2_Pymt")

lLastRow = .Range("E65536").End(xlUp).Offset(1, 0).Row
y = lLastRow
Do

lLastRow = lLastRow - 1

Loop Until Left(.Cells(lLastRow, "e").value, 2) = "MB"

.Range("f2").value = .Cells(lLastRow, "e").value

msg1 = "What Is The Last Cheque Number, Please Key In The Number I
F2"
title1 = "Last Cheque Number"
default1 = "MBB"

strLast = InputBox(msg1, title1, default1)

msgCls = "What Is The Month You Want To Update"
titleCls = "Closing Month"
defaultCls = "January"

strCls = InputBox(msgCls, titleCls, defaultCls)

x = CLng(Right(strLast, 6))

For x = x To x + 100

For i = Workbooks("PV fo
2004").Worksheets("Data").Range("c65536").End(xlUp). _
Row To 1 Step -1

If "MBB0" & x = Workbooks("PV for 2004"). _
Worksheets("Data").Cells(i, "c").value Then

Select Case strCls

Case "January"
ClsChq = 1
Case "February"
ClsChq = 2
Case "March"
ClsChq = 3
Case "April"
ClsChq = 4
Case "May"
ClsChq = 5
Case "June"
ClsChq = 6
Case "July"
ClsChq = 7
Case "August"
ClsChq = 8
Case "September"
ClsChq = 9
Case "October"
ClsChq = 10
Case "November"
ClsChq = 11
Case "December"
ClsChq = 12

End Select

If Month(Workbooks("PV fo
2004").Worksheets("Data").Cells(i, "b").value) _
<> ClsChq Then Exit For

z = Right(.Cells(lLastRow, "e").value, 6)

NewRow = y + x - z

.Cells(NewRow, "b").value = _
Workbooks("PV for 2004").Worksheets("Data").Cells(i
"b").value
.Cells(NewRow, "b").NumberFormat = "mmm dd"

.Cells(NewRow, "c").value = Workbooks("PV for 2004"). _
Worksheets("Data").Cells(i, "d").value

If Len(Workbooks("PV for 2004"). _
Worksheets("Data").Cells(i, "a").value) = 1 Then
.Cells(NewRow, "d").value = "000" & Workbooks("P
for 2004"). _
Worksheets("Data").Cells(i, "a").value & "/2004"

ElseIf Len(Workbooks("PV for 2004"). _
Worksheets("Data").Cells(i, "a").value) = 2 Then
.Cells(NewRow, "d").value = "00" & Workbooks("P
for 2004"). _
Worksheets("Data").Cells(i, "a").value & "/2004"

ElseIf Len(Workbooks("PV for 2004"). _
Worksheets("Data").Cells(i, "a").value) = 3 Then
.Cells(NewRow, "d").value = "0" & Workbooks("PV fo
2004"). _
Worksheets("Data").Cells(i, "a").value & "/2004"

Else: .Cells(NewRow, "d").value = Workbooks("PV for 2004"
_
.Worksheets("Data").Cells(i, "a").value & "/2004"

End If

.Cells(NewRow, "e").value = Workbooks("PV for 2004"). _
Worksheets("Data").Cells(i, "c").value

.Cells(NewRow, "g").value = Workbooks("PV for 2004"). _
Worksheets("Data").Cells(i, "j").value
.Cells(NewRow, "g").NumberFormat
"#,##0.00_);[Red](#,##0.00)"

.Cells(NewRow, "j").Formula = "=sum(r[0]c[-3]:r[0]c[-1])"
.Cells(NewRow, "j").NumberFormat
"#,##0.00_);[Red](#,##0.00)"

.Cells(NewRow, "k").Formula = "=sum(r[0]c[-5])-sum(r[0]c[-1])"
.Cells(NewRow, "k").NumberFormat
"#,##0.00_);[Red](#,##0.00)"

End If

Next i

Next x

.Cells(y, "a").EntireRow.Delete

End With

End Su

 

[Tushar Mehta]

Use a 'Exit For' statement? Or, better yet, use a Do...Until (or Do
While...) loop

If you do share code, please format it for readability.

Also, in general, it is not the done thing to use the same variable as
loop index variable and the loop control variable. It might work with
VBA because it establishes controls only once. But, in languages that
reevaluate the controls each iteration, 'for x=x to x+100' would create
an infinite loop.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I would apprecite if anybody could give any comment on the VBA code I
wrote below. It works, but I fell there is a lot need to be improved.
Especially the looping for x where i have to stop it at x + 100 because
I wouldn't know the value of x. Does anybody have any idea how to stop
x before it reached x + 100.

Thank you very much.

Sub UpdateCheque()

{snip of unformatted code}

 

[shaharul]

Mr Tusha Mehtar
Thank you for your reply. Just wonder what is loop index variable an
loop control variable. Also how to code using 'Exit for' statement.
have tried do until but it didn't work

 

[Tushar Mehta]

For i=x to y step z
if {some condition} then
exit for
end if
next i

i is the loop index variable,
x, y, and z are loop control variables,

The embedded If statement illustrates how to use Exit For.

If you type in 'exit' (w/o the quotes) in XL VBA help, one of the
suggested topics is 'Exit Statement' (w/o the quotes). It explains the
various Exit statements in detail.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions