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