Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed - VBA If statement!
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed - VBA If statement!
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed - VBA If statement!
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed - VBA If statement!
change [=VLOOKUP(C2,'Settings Worksheet'!C3:C10,4,0)]
to: [=VLOOKUP(C2,'Settings Worksheet'!C$3:D$10,2,0)] 1) In the 2nd argument I changed C3:C10 to C$3:D$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:D:$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. "WebWizard97" wrote in message oups.com... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed - VBA If statement!
George,
Thank you so much for your assistance. I modified my formula as per your description and it is working great. Sincerely, WebWizard97 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed - VBA If statement!
George,
Thank you so much for your assistance. I modified my formula as per your description and it is working great. Sincerely, WebWizard97 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement needed | Excel Worksheet Functions | |||
Help needed would this be an IF statement | Excel Worksheet Functions | |||
If statement ... help needed. | Excel Worksheet Functions | |||
Help with IF statement needed | Excel Worksheet Functions | |||
Help Needed - VBA If statement! | Excel Programming |