Help Needed - VBA If statement!

[WebWizard97]

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.

 

[Guest]

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

 

[WebWizard97]

Thanks for the replies, however, I am still having some difficulty.

The worksheet which contains the salesmen's initals and sales territory
is a separate worksheet called (Settings Worksheet) it consists of the
following columns:

-----(Settings Worksheet)--------------------------------
Column A = Branch / Division Name (i.e. Branch 01, Branch 02, etc.)

Column B = Salesman's Full Name (i.e. Steve Smith, John Doe, etc.)

Column C = Salesman's Initials (i.e. SS, JD, etc.)

Column D = Sales Territory (i.e. North or South)
--------------------------------------------------------------------

I want to use this formula on my January Sales worksheet (Corprate
Sales Jan 2004), in the following columns:

-----(Corprate Sales Jan 2004)--------------------------------
Column B = The column I want to display the sales territory in (i.e.
No. or So.)

Column C = The column where the salesman's initials are entered (i.e.
SS, JD, etc.)
--------------------------------------------------------------------

I modified the original formula

FROM: [=vlookup(c10,Region,2,0)]

TO: [=VLOOKUP(C2,'Settings Worksheet'!C3:C10,4,0)]

and put it in B2 on the (Corprate Sales Jan 2004) worksheet, but I am
getting a (#REF!) error because the (4) is referring to column D on the
(Corprate Sales Jan 2004) worksheet not column D on the (Settings
Worksheet).

How would you reference column D on (Setting Worksheet) in the formula.
Any and All help is greatly appreciated.

 

[George Nicholson]

change [=VLOOKUP(C2,'Settings Worksheet'!C3:C10,4,0)]

to: [=VLOOKUP(C2,'Settings Worksheet'!C$3$10,2,0)]

1) In the 2nd argument I changed C3:C10 to C$3$10. You want to include
2 columns and make the row#s absolute so you can copy the formula down
without changing the cell references to the table.
2) In the 3rd argument, I changed 4 to 2 because that value is in
reference to the column you are searching for a match (column C). This
value has to fall within the range specified in the 2nd argument. If you
pass vlookup a 1 column range to look in while asking it to return a value
from the 4th column *of that range*, you will get the #REF error you were
getting (just like it says in the Help file).

vlookup construction: look up a value (C2) within the first column of a
range (C$3:$10, just the SalesmanInitials & Region table) and when it
finds a matching value, return the value from the *2nd* column (2) of that
range. The 0, or False, for the last argument tells Vlookup to find an exact
match.

Since you are going for an exact match, Column C in your lookup table does
not have to be in Ascending order. If you were going for an "approximate"
match, it would be.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Thanks for the replies, however, I am still having some difficulty.

The worksheet which contains the salesmen's initals and sales territory
is a separate worksheet called (Settings Worksheet) it consists of the
following columns:

-----(Settings Worksheet)--------------------------------
Column A = Branch / Division Name (i.e. Branch 01, Branch 02, etc.)

Column B = Salesman's Full Name (i.e. Steve Smith, John Doe, etc.)

Column C = Salesman's Initials (i.e. SS, JD, etc.)

Column D = Sales Territory (i.e. North or South)
--------------------------------------------------------------------

I want to use this formula on my January Sales worksheet (Corprate
Sales Jan 2004), in the following columns:

-----(Corprate Sales Jan 2004)--------------------------------
Column B = The column I want to display the sales territory in (i.e.
No. or So.)

Column C = The column where the salesman's initials are entered (i.e.
SS, JD, etc.)
--------------------------------------------------------------------

I modified the original formula

FROM: [=vlookup(c10,Region,2,0)]

TO: [=VLOOKUP(C2,'Settings Worksheet'!C3:C10,4,0)]

and put it in B2 on the (Corprate Sales Jan 2004) worksheet, but I am
getting a (#REF!) error because the (4) is referring to column D on the
(Corprate Sales Jan 2004) worksheet not column D on the (Settings
Worksheet).

How would you reference column D on (Setting Worksheet) in the formula.
Any and All help is greatly appreciated.

 

[WebWizard97]

George,

Thank you so much for your assistance. I modified my formula as per
your description and it is working great.

Sincerely,

WebWizard97

 

[WebWizard97]

George,

Thank you so much for your assistance. I modified my formula as per
your description and it is working great.

Sincerely,

WebWizard97