Please find below a revised and annotated version of your Post_to_Dbase
() routine. I corrected what seemed to me a couple of logic errors
(which I explained in my annotations):
Sub Post_to_Dbase()
'Suppressing screen updating and Excel alerts
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
'Declaring variables
'Dim PayPeriod As Integer
'It really doesn't matter whether this is declared as String or
Integer
'(VBA is very forgiving that way, for the most part)
'String typically happens to be safer.
'It does however matter when you later in your code make the
'assumption that PayPeriod is a String variable after all,
'as you do in your statement 'If PayPeriod <> "" Then'.
'Therefore I changed this declaration to:
Dim PayPeriod As String
Dim Rng As Range
'In case of error what ever reason
On Error GoTo Err_Execute
'This is a good idea in general,
'but can be improved on by displaying the error code.
'However I recommend to not use it while you are debugging your
code,
'because you will never find out where your code went wrong
'unless you are stepping through it (using the F8 key) or are
using
'breakpoints.
'Retrieve date value to search for
'PayPeriod = Sheets("Computation").Range("B7").Value
'ARE YOU SURE THIS IS WHAT YOU WANT?
'This statement would assign the IDNo not the Period to PayPeriod.
'In addition your original code points to the cell in the second
row
'of your Compute range not the first.
'I changed it to
'(slightly modified from my original code
' using the column letter instead of the column number):
PayPeriod = Worksheets("Computation").Range("Compute").Cells(1,
PERIOD_COLUMN).Value
'Comment: using the Worksheets collection instead of the Sheets
collection is
' generally better practice (although it requires a bit
more typing).
' The reason: the Worksheets collection really contains
only the
' worksheets in your workbook. The Sheets collection
also includes other
' sheets, e.g. Chart sheets. That can lead to unexpected
results.
' This is not a problem in your particular case. I just
thought you should
' be aware of the distinction.
'Select worksheet to paste append from if the variable is exiting
If PayPeriod <> "" Then
'With Sheets("PayDbase").Range("B:B")
'ARE YOU SURE THIS IS WHAT YOU WANT?
'This statement would look for the period in the IDNo
column
'and of course not find it.
'I changed it to:
With Sheets("PayDbase").Range("E:E")
Set Rng = .Find(What:=PayPeriod, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
'If Not Rng Is Nothing Then
'WATCH OUT: I thought you only wanted to copy the data if
there was no
'data for this period present.
'That is the case when Rng comes up as nothing after you
look for the
'specific period.
'Hence I changed this to:
If Rng Is Nothing Then
'Paste the entire ranged named to dbase worksheet
ActiveSheet.Calculate
'I am not sure why you insist on including a call to
the Calculate
'method. Is your workbook set to manual calculation?
'There is nothing fundamentally wrong with the way you
coded
'the next couple of statements, but using the Select
method
'is generally not a good idea.
'The reasons:
' - the worksheet the cells you want to select needs
to be the ActiveSheet
' - it is slower (probably not a problem for your
case, but still)
' and therefore it is good practice to minimize
the use of it.
'It can just be written more concisely (see below).
' Worksheets("Computation").Range("compute").Copy
' Sheets("PayDbase").Select
' Range("A65536").Select
' Selection.End(xlUp).Select
' 'one empty row down to paste the values
' ActiveCell(1, 0).Select
' Worksheets("PayDbase").Range("a6").PasteSpecial _
' Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, _
' Transpose:=False
' Application.CutCopyMode = False
'Again declaring and setting the sourceRange adn
targetCell this way
'simplifies the code.
'Notice that I am not using the Select method at all.
Dim targetCell As Range
Set targetCell = Worksheets("PayDbase").Range("A" &
Rows.Count).End(xlUp).Offset(1)
Dim sourceRange As Range
Set sourceRange = Worksheets("Computation").Range
("Compute")
sourceRange.Copy
targetCell.PasteSpecial Paste:=xlPasteValues
MsgBox "Payroll closed and posted, you may print
payslips now!"
Else
MsgBox "Payroll Period is already posted!"
End If
End With
End If
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
Err_Execute:
MsgBox "An error occurred." & vbCr & vbCr & "Error: " & Error$
End Sub
Hope this helped,
Rolf
On Oct 27, 8:54 pm, OSAVentures Calamba <osav...@gmail.com> wrote:
> @Rolf Thank you very much for your help. You are sent by angels.
> I read your code and tried to self understand the logic because im just a
> beginner. I made some remarks. I also pasted here my revised code which i
> adopted from many sites to suit my actual situation. I rename
> Post_to_summary TO Post_to_Dbase ( second before your email came in). Is it
> better if you can comment your logic flow for beginners like me.
>
> Sub Post_to_Dbase()
> 'suppressing screen
> With Application
> .ScreenUpdating = False
> .DisplayAlerts = False
> End With
> 'declaring variable
> Dim PayPeriod As Integer
> Dim Rng As Range
> 'in case of error what ever reason
> On Error GoTo Err_Execute
> 'Retrieve date value to search for
> PayPeriod = Sheets("Computation").Range("B7").Value
> 'select worksheet to paste append from if the variable is exiting
> If PayPeriod <> "" Then
> With Sheets("PayDbase").Range("B:B")
> Set Rng = .Find(What:=PayPeriod, _
> After:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not Rng Is Nothing Then
> 'paste the entire ranged named to dbase worksheet
> ActiveSheet.Calculate
> Worksheets("Computation").Range("compute").Copy
> Sheets("PayDbase").Select
> Range("A65536").Select
> Selection.End(xlUp).Select
> 'one empty row down to paste the values
> ActiveCell(1, 0).Select
> Worksheets("PayDbase").Range("a6").PasteSpecial
> Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> MsgBox "Payroll closed and posted, you may print payslips now!"
> Else
> MsgBox "Payroll Period is already posted!"
> End If
> End With
> With Application
> .ScreenUpdating = True
> .DisplayAlerts = True
> End With
> Err_Execute:
> MsgBox "An error occurred."
> End Sub
>
>
>
>
>
> On Wed, Oct 28, 2009 at 4:00 AM, RolfJ <r...@pacificsound.us> wrote:
>
> > I took another look at your workbook and think I have a reasonable
> > solution for your Post_to_Summary routine:
>
> > Sub Post_to_Summary()
>
> > With Application
> > .ScreenUpdating = False
> > .DisplayAlerts = False
> > End With
>
> > Dim nPeriod As String ' my period is an integer coz of some math
> > operations somewhere
> > nPeriod = Range("Compute").Cells(1, 5).Value
>
> > Dim targetColumn As Range
> > 'this code pasting the range "compute" already? ' compute is dynamic
> > named ranged already?"*
> > *
>
> * *
>
>
>
>
>
> > Set targetColumn = Worksheets("DBase").Columns("E")
> > Dim targetCell As Range
>
> > Application.CutCopyMode = False
>
> > Set targetCell = targetColumn.Find(What:=nPeriod,
> > After:=targetColumn.Cells(1, 1), LookIn:=xlFormulas, LookAt _
> > :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
> > MatchCase:= _
> > False, SearchFormat:=False)
>
> > If targetCell Is Nothing Then
> > Set targetCell = Worksheets("DBase").Range("E" &
> > Rows.Count).End(xlUp).Offset(1, -4)
> > Dim sourceRange As Range
> > Set sourceRange = Worksheets("Computation").Range("Compute")
> > sourceRange.Copy
> > targetCell.PasteSpecial Paste:=xlPasteValues
> > End If
>
> > Application.CutCopyMode = False
> > With Application
> > .ScreenUpdating = True
> > .DisplayAlerts = True
> > End With
>
> > End Sub
>
> > I am still a bit fuzzy on what you are trying to accomplish with your
> > Prints_All_slips routine, but maybe this code does what you are
> > looking for (if not it should be reasonably straightforward to make
> > any modifictions (e.g. regarding the formatting which I omitted) you
> > might need):
>
> > " I want to print all the slips for the current period. *My workbook is a
>
> payroll template
> workbook*. The flow goes as follows. The structure of computation and
> dbase worksheet is the same.
>
> start->Compute payment for the current period -> then post to dbase
> worksheet-> print payslip based on posted data on dbase worksheet. This is
> the routine. Thanks for indulgence.
>
> Sub Prints_All_slips()
>
>
>
>
>
> > With Application
> > .ScreenUpdating = False
> > .DisplayAlerts = False
> > End With
>
> > If MsgBox(" Printer Properly Set-up?", vbYesNo + vbQuestion) =
> > vbNo Then
> > Exit Sub
> > Else
> > Dim iRow As Long
> > Dim nSlip As Integer
> > nSlip = 1
> > Dim idNo As String
> > Dim nPeriod As String
> > Do
> > idNo = Worksheets("Computation").Range("B7").Offset
> > (iRow).Value
> > Worksheets("SLIPs").Range("D" & 4 + (nSlip - 1) *
> > SLIP_ROWSPACING).Offset(iRow).Value = idNo
> > nPeriod = Worksheets("Computation").Range("B7").Offset
> > (iRow, 4).Value
> > Worksheets("SLIPs").Range("M" & 6 + (nSlip - 1) *
> > SLIP_ROWSPACING).Offset(iRow).Value = nPeriod
> > If Worksheets("Computation").Range("B7").Offset(iRow +
> > 1).Value = "" Then Exit Do
> > iRow = iRow + 1
> > nSlip = nSlip + 1
> > Loop
> > End If
>
> > With Application
> > .ScreenUpdating = True
> > .DisplayAlerts = True
> > End With
>
> > End Sub
>
> > Hope this helped,
> > Rolf
>
> > On Oct 27, 3:44 am, OSAVentures Calamba <osav...@gmail.com> wrote:
> > > @Rolf Thank you very much. I attached my reduced sheet with explanation.
> > > ...*you say 'it
> > > stops'. [macro stops running or break] Also, what to you mean to indicate
> > > with the -<<<< debugs here flag?* *[when the macro breaks it points to
> > that
> > > line.]*
>
> > > I've 'painfully revised the code. the updated one is below. quite
> > > complicated for a beginner like me.
>
> > > below is my erratic code to paste the entire named ranged to my dbase
> > > worksheet. I want to insert a code where it should stop posting if the
> > > values is found in the dbase already. [ explained in the attached
> > worksheet]
>
> > > this one also overwrites data in the dbase worksheet. which should not
> > be.
> > > it should append only on the next row containing data.
>
> > > Sub Post_to_Summary()
> > > With Application
> > > .ScreenUpdating = False
> > > .DisplayAlerts = False
> > > End With
> > > Worksheets("Computation").Range("compute").Copy
> > > Worksheets("PayDbase").Range("a6").PasteSpecial Paste:=xlPasteValues,
> > > Operation:=xlNone, SkipBlanks _
> > > :=False, Transpose:=False
> > > Application.CutCopyMode = False
> > > 'Range("compute").Select
> > > 'Selection.Copy
> > > 'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > > SkipBlanks _
> > > :=False, Transpose:=False
> > > With Application
> > > .ScreenUpdating = True
> > > .DisplayAlerts = True
> > > End With
> > > End Sub
>
> > > Below is my code ( full of error ) to copy_paste_print as explained in
> > the
> > > [attached worksheet]. I just get some code from macro recording.
>
> > > Sub Prints_All_slips()
> > > With Application
> > > .ScreenUpdating = False
> > > .DisplayAlerts = False
> > > End With
> > > If MsgBox(" Printer Properly Set-up?", vbYesNo + vbQuestion) = vbNo
> > Then
> > > Exit Sub
> > > Else
> > > ' Select cell b7, first line of data computation sheet.
> > > 'Do Until IsEmpty(ActiveCell)
> > > Sheets("Computation").Select
> > > Range("B7").Select
> > > Selection.Copy
> > > 'ActiveCell.Offset(1, 0).Select
> > > Sheets("PAYSLIP").Select
> > > Range("D4").Select
> > > Selection.PasteSpecial Paste:=xlPasteValues
> > > Application.CutCopyMode = False
> > > Do Until IsEmpty(ActiveCell)
> > > Sheets("Computation").Select
> > > ActiveCell.Offset(1, 0).Select
> > > Selection.Copy
> > > Sheets("PAYSLIP").Select
> > > Range("D41").Select
> > > Selection.PasteSpecial Paste:=xlPasteValues
> > > Application.CutCopyMode = False
> > > 'Print_All_Pay
> > > Range("B1:N68").Select
> > > Selection.PrintOut Copies:=1
> > > 'Print_All_Pay
> > > Sheets("Computation").Select
> > > ActiveCell.Offset(1, 0).Select
> > > Selection.Copy
> > > Sheets("PAYSLIP").Select
> > > Range("D4").Select
> > > Selection.PasteSpecial Paste:=xlPasteValues
> > > Application.CutCopyMode = False
> > > With Selection
> > > .HorizontalAlignment = xlGeneral
> > > .VerticalAlignment = xlBottom
> > > End With
> > > Loop
> > > With Application
> > > .ScreenUpdating = True
> > > .DisplayAlerts = True
> > > End With
> > > End If
> > > End Sub
>
> > > On Tue, Oct 27, 2009 at 12:15 PM, RolfJ <r...@pacificsound.us> wrote:
>
> > > > I am having difficulties following what you would like to do. In
> > > > particular it is not clear to me what you mean when you say 'it
> > > > stops'. Also, what to you mean to indicate with the -<<<< debugs here
> > > > flag?
>
> > > > Please post or e-mail example worksheets and I'll take a closer look.
>
> > > > On Oct 26, 6:28 pm, OSAVentures Calamba <osav...@gmail.com> wrote:
> > > > > Dear Macro Experts,
>
> > > > > I'm just starting to write a macro. What I'm trying to do is I want
> > to
> > > > copy
> > > > > a value from 'computation' sheet.column B and posting it as values in
> > > > 'Slip'
> > > > > range "D4" and do that while sheet.columnB is not empty. Below is
> > the
> > > > code
> > > > > i made but stops.
>
> > > > > Sub copyIDtopay()
> > > > > With Application
> > > > > .ScreenUpdating = False
> > > > > .DisplayAlerts = False
> > > > > End With
> > > > > ' Select cell b7, first
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
