You are welcome!
On Oct 28, 7:06 pm, OSAVentures Calamba <osav...@gmail.com> wrote:
> @Rolf ... Thank you very much... You were a BIG BROTHER to me, teaching me
> in detailed patience how to make my first steps in walking to macros. I
> tested my codes with ALL your suggestions and it went fine. May you be
> blessed with good health and long life.
>
>
>
> On Wed, Oct 28, 2009 at 11:20 PM, RolfJ <r...@pacificsound.us> wrote:
>
> > 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,
>
> ...
>
> read more »- 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
-~----------~----~----~----~------~----~------~--~---
