Have you tried using a lookup function instead of a VBA process? Your
problem seems like it would lend itself nicely to a vlookup formula.
Assuming you have a table called "Region" on the settings worksheet (see
below), this formula would be entered into cell B10 on your worksheets would
be:

=vlookup(c10,Region,2,0)

The table called Region would look like this:

Initials Region
RW North
AJ South
CG North
etc.

The table also needs to be sorted by Initials in ascending order.
If you ever need to add or delete a salesman, then you simply need to edit
the table...the formulas would remain the same.

Hope this helps....


" wrote:

Hello fellow Excel Users,

I am new to VBA in Excel and need some assistance.

Background: I have a workbook "Corporate Sales" which contains (13)
worksheets, one for each month of the year (Corprate Sales Jan 2004 -
Corprate Sales Dec 2004), and one worksheet that I want to use as a
settings worksheet (Settings Worksheet). I have tried to automate each
worksheet to populate the sales territory based on the salesman's
initials using the following formula:

-----------------------------
column "B" = Sales Territory (contains the formula)
column "C" = Salesman's Initial

=IF(OR(C7="AM",C7="BP",C7="DA",C7="JS",C7="SW",C7= "WB"),"No.",IF(OR(C7="JD",C7="KO",C7="PD",C7="SP") ,"So.","
"))
-----------------------------

unfortunately, if a salesman is added or deleted I need to manually
modify each of the (12) worksheets. I want to develope a VBA "IF"
statement which will do the following:

-----------------------------
Compare salesman's inital columns:

"Settings Worksheet - C (salesman's initials)"

to

"Corprate Sales Jan 2004 - C (salesman's initials)"

and if

"Settings Worksheet - D (sales territory)" = North or South

populate

"Corprate Sales Jan 2004 - D (sales territory)" with No. or So.
-----------------------------

I have tried the following, however, being new to VBA I could not get
anything to work:

-----------------------------
Function GetSalesTerritory(strRep As String, varVal As Variant) As
Variant

If strRep = Worksheet("Settings Worksheet").Cell("C2") And
Worksheet("Settings Worksheet").Cell("D2").Value = "North" Then
Worksheet("Corprate Sales Jan 2004").Cell("B2").Value = "No."
End If

End Function
-----------------------------

I put "=GetSalesTerritory()" in "Corprate Sales Jan 2004 - C (salesmans
initals)" to call the function, but nothing seems to work.

Any and all assiatance would be greatly appreciated.

Also if anyone knows of a good VBA book for Beginners / Intermediate
that I could use as a reference would be helpful.