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

--
Message posted from http://www.ExcelForum.com