Help in VBA coding (dowhile / LOOP UNITL) function

gvreddyhr · October 29th, 2011, 01:22 AM

Hi All,

Hope everyone are doing good, Am in need of your help.

I have created a payroll application using some VBA and worksheet functions as well, everything is working fine, but I am getting struck in pay slip processing, for Payslip creation I created one sheet and used vlookup function, if I key in the employee number, all other particulars will update, and I have created a mail icon which converts that particular range to pdf and generates email, now its becoming very difficult for me to do the same action for all employees.

So am looking where macro automatically runs and generate email for all at a time, below is the more details

Ex:-
Sheet name âpayslipâ in cell âA5â I need to update the employee number, I have the list of applicable employee numbers in sheet âMaillistâ from âa2 to X (xldown / end)â, now I want to write a code where it works as
1. Copy from sheet âMaillistâ cell âa2â and paste in sheet âpayslipâ cell âA5â and then run the mailer code(converts in to pdf and generates email)
2. Copy from sheet âMaillistâ cell âa3â and paste in sheet âpayslipâ cell âA5â and then run the mailer code(converts in to pdf and generates email)
3. Copy from sheet âMaillistâ cell âa4â and paste in sheet âpayslipâ cell âA5â and then run the mailer code(converts in to pdf and generates email).
.
.
.
Copy from sheet âMaillistâ cell âa100â(xldown) and paste in sheet âpayslipâ cell âA5â and then run the mailer code((converts in to pdf and generates email).

I have the code which automatically convert particular range to pdf and generates email, can anyone help me to get the above code..

Thanks in advance

Regards,
GV Reddy

JP · October 31st, 2011, 11:04 AM

Do you have any code written so far?

What I would do is read the range of employee numbers into a VBA array, then loop through it and paste each one into A5 and run the mailer code.

mtranchi · October 31st, 2011, 07:54 PM

JP is right, putting the employees into an array is the fastest way to do it, but judging by the title of your post, you need help with looping. As the old saw goes, "get it working, get it working right, get it working fast." Let's focus on step one for now.

So here's a spreadsheet I called "Employees":

Employee Num Last name First name

1 Smith John
2 Jablomi Heywood
3 Meoff Jack

And since it sounds like you've gotten it working where you manually enter an employee number into a cell, we'll work with that model for now as well. So what we'll do is loop through all the employees, entering each employee number into cell A5 of your "payslip" sheet, then call whatever sub you have that does the email and pdf.

My suggestion is to create a new workbook, name one sheet "Employees" and add the previous data list to it, another sheet "payslip", go into the VBA editor and add a new module, then paste this code:

Code:

Option Explicit

Public TotalEmployees As Integer

Public Sub LoopThruEmployess()
    'this is the variable for looping
    Dim i As Integer
    
    'this is where you're now manually entering the data (setting a variable for the range will speed up the program)
    Dim Target As Range
    Set Target = Sheets("payslip").Range("a5")
    
    'This is the Source Row from which the "Offset" function will work
    Dim SourceRow As Range
    Set SourceRow = Sheets("Employees").Range("A1")
    
    'Note that this assumes two things:
    'One, that you have no text in any cells in the "A" column besides the header column,
    'and two, that your header column is in row 1
    
    'The " -1 " accounts for the header row
    TotalEmployees = WorksheetFunction.CountA(Sheets("Employees").Range("A:A")) - 1
    
    'I use a For loop because it's faster than testing each row as you go down the column, though as JP said, it's faster to copy the range to an array (almost 50 times faster)
    
    'The " i = 1 " accounts for the list of employees starting in row 2
    For i = 1 To TotalEmployees
        Target = SourceRow.Offset(i, 0)
        MsgBox "Call your Subroutine that emails, pdf's your payroll slip for employee number " & Target & " here"
    Next i

End Sub

I just tested it to make sure there's no typos, so it should work fine. Once you're comfortable with how it works, incorporate it into your existing solution.

Hope that helps,

Mike