Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code Help
I have this set of code in a macro that I am using to gather our
salespeople's customer information. This works great until I get to some of our newer salespeople that don't have any customers yet. It changes every month how many and if a salesperson has any customers. What I need for it to do is to run the application and if their name isn't found go on to the next line of code. Is there a way that I can do this? So far it just brings me an error message and then I'm stuck and have to run all the rest manually. Can I put in some extra code or something to suppress the error message and keep on running? The code that I am currently using is below. Keep in mind we have over 100 employess so this is cumbersome doing it manually. Sheets("W Zone").Select Range("I228").Select ActiveCell.FormulaR1C1 = "aaaa" Range("I228").Select Application.Run "'Branch Productivity Report.xls'! GetCustomerRecord" Sheets("NE Zone").Select Range("I81").Select ActiveCell.FormulaR1C1 = "bbbb" Range("I81").Select Application.Run "'Branch Productivity Report.xls'! GetCustomerRecord" Range("I130").Select ActiveCell.FormulaR1C1 = "cccc" Application.Run "'Branch Productivity Report.xls'! GetCustomerRecord" ActiveWorkbook.Save Thank you in advance for any ideas that you have |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code Help
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code Help
On Jul 25, 8:25 am, "Don Guillett" wrote:
It sounds like your whole project needs to be re-worked. Much more info? -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ups.com... I have this set of code in a macro that I am using to gather our salespeople's customer information. This works great until I get to some of our newer salespeople that don't have any customers yet. It changes every month how many and if a salesperson has any customers. What I need for it to do is to run the application and if their name isn't found go on to the next line of code. Is there a way that I can do this? So far it just brings me an error message and then I'm stuck and have to run all the rest manually. Can I put in some extra code or something to suppress the error message and keep on running? The code that I am currently using is below. Keep in mind we have over 100 employess so this is cumbersome doing it manually. Sheets("W Zone").Select Range("I228").Select ActiveCell.FormulaR1C1 = "aaaa" Range("I228").Select Application.Run "'Branch Productivity Report.xls'! GetCustomerRecord" Sheets("NE Zone").Select Range("I81").Select ActiveCell.FormulaR1C1 = "bbbb" Range("I81").Select Application.Run "'Branch Productivity Report.xls'! GetCustomerRecord" Range("I130").Select ActiveCell.FormulaR1C1 = "cccc" Application.Run "'Branch Productivity Report.xls'! GetCustomerRecord" ActiveWorkbook.Save Thank you in advance for any ideas that you have- Hide quoted text - - Show quoted text Like I said before everything works fine and does exactly what I want it to do. It's just the new people that are throwing a kink into things. At most there are probably about 15 people as of now that are constantly changing, but they are mixed in with the rest of the salespeople. Here is what the Get Customer Record Code looks like: Public Sub GetCustomerRecord() Dim salesRange As Range ' the sales data range, w/out the header Dim salespersonRange As Range ' the sales data for one salesperson Dim companyRange As Range ' the company column ' create a reference to the entire sales data in report file Set salesRange = Workbooks("top customers.xls").Names("SalesData").RefersToRange ' find cell containing salesperson in question. Salesperson searched must be included in a cell in the report. Set salespersonRange = salesRange.Find(what:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole) ' grab the nx7 range of data for this salesperson. Make sure there's a blank line after each set of sales data Set salespersonRange = salespersonRange.CurrentRegion ' delete the salesperson's name from the returned data Set salespersonRange = salespersonRange.Offset(columnoffset:=1) 'extract the first column Set companyRange = salespersonRange Set companyRange = companyRange.Resize(columnsize:=1) ' copy company column to active cell in the project companyRange.Copy Destination:=ActiveCell ' now copy the rest of the data, but first skip the company column Set salespersonRange = salespersonRange.Offset(columnoffset:=1) salespersonRange.Copy Destination:=ActiveCell.Offset(columnoffset:=2) End Sub Does that help any? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow code when used as VBA code instead of macro (copying visible columns) | Excel Programming | |||
Can I use code/macro to change code/macro in an existing file? | Excel Programming | |||
Macro code to remove a macro after it executes | Excel Programming | |||
read macro code by vb code | Excel Programming | |||
do anybody have a sample code for executing excel macro from vb code?<eom | Excel Programming |