Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 29th, 2011, 01:22 AM
Registered User
 
Join Date: May 2010
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help in VBA coding (dowhile / LOOP UNITL) function

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
 
Old October 31st, 2011, 11:04 AM
JP JP is offline
Authorized User
 
Join Date: Apr 2008
Posts: 57
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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.
__________________
Regards,
JP
JP SoftTech
 
Old October 31st, 2011, 07:54 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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

Last edited by mtranchi; October 31st, 2011 at 08:01 PM..





Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Coding Technique Question gcoggin Access VBA 0 July 29th, 2009 12:51 PM
Help! Coding queries in vba for subforms froy001 Access VBA 3 May 1st, 2009 08:04 AM
Organization chart in Excel through VBA coding mangesh.gohad Excel VBA 0 July 26th, 2008 12:23 PM
What does (Me!) and (Dim)stand for in VBA Coding? srswedeen Access VBA 1 October 14th, 2005 06:41 AM
event coding - Access VBA bph Access VBA 5 January 6th, 2004 12:17 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.