Macro Code Help

[tennisash]

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

 

[Don Guillett]

It sounds like your whole project needs to be re-worked. Much more info?

 

[tennisash]

It sounds like your whole project needs to be re-worked. Much more info?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







- 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?