|
Macros/VBA
I have a column D where the values of D's cells are
dependent on cells A, B, and C of that row. Once A, B, or C is changed, I want the calculation for D to be updated. How do I create a macro with VBA that will do this? (I believe I need a macro because the conditional statements are very tedious and complex by using nested ands and ors.) Thanks very much. |
Macros/VBA
My problem is actually that I don't know how to make the
dependent cell update - the conditional statement is ugly so I am not sure whether to do nested ands/ors or do it through VBA, if possible. Any suggestions? -----Original Message----- Try this first -- Look in Tools -- Options -- Calculation tab and make sure "Automatic" is checked. If I'm reading your message right, your dependent cell should update whenever the sources change. -----Original Message----- I have a column D where the values of D's cells are dependent on cells A, B, and C of that row. Once A, B, or C is changed, I want the calculation for D to be updated. How do I create a macro with VBA that will do this? (I believe I need a macro because the conditional statements are very tedious and complex by using nested ands and ors.) Thanks very much. . . |
Macros/VBA
code would look something like
If [a1]="b" and [a2]="M" and [a3]="F" then [a4]=1 If [a1]="b" and [a2]="M" and [a3]="O" then [a4]=2 If [a1]="b" and [a2]="M" and [a3]="T" then [a4]=2 etc these are case sensetive so you may need if ucase([a1])="B" etc or a1=b and a2=nM and a3=F, a4=3 or a1=b and a2=nm and a3=O, a4=4 or a1=b and a2=nm and a3=T, a4=5 or a1=s and a3=F, a4=6 or a1=s and a3=O, a4=7 or a1=s and a3=T, a4=8 or a1=a and a2=M and a3=F, a4=7 or a1=a and a2=m and a3=O, a4=8or a1=a and a2=m and a3=T, a4=8 "mika." wrote in message ... Hello, The if statement (in pseudocode) would be something like this: If a1=b and a2=M and a3=F, a4=1 or a1=b and a2=m and a3=O, a4=2 or a1=b and a2=m and a3=T, a4=2 or a1=b and a2=nM and a3=F, a4=3 or a1=b and a2=nm and a3=O, a4=4 or a1=b and a2=nm and a3=T, a4=5 or a1=s and a3=F, a4=6 or a1=s and a3=O, a4=7 or a1=s and a3=T, a4=8 or a1=a and a2=M and a3=F, a4=7 or a1=a and a2=m and a3=O, a4=8or a1=a and a2=m and a3=T, a4=8 ...etc. There are 14 statements in total; this is why I thought it would be easier to use VBA to write the conditional statement rather than nested =(or (and...))..etc. Any advice? Thanks. -----Original Message----- Why not tell us the detail. Perhaps it needn't be so tedious and complex |
Macros/VBA
Sorry - I thought you had the formula in but it wouldn't
calculate. Barring a more clever solution from someone else, I would do this in VBA something like this: if a1 = B then if a2 = M then fx = a3_subFx + 0 else... Making the test of a3 it's own private function and calling it from within higher-level if statments testing a1 or a2 would save you some coding. You would have to work out the math so that you always got the right answer. -----Original Message----- My problem is actually that I don't know how to make the dependent cell update - the conditional statement is ugly so I am not sure whether to do nested ands/ors or do it through VBA, if possible. Any suggestions? -----Original Message----- Try this first -- Look in Tools -- Options -- Calculation tab and make sure "Automatic" is checked. If I'm reading your message right, your dependent cell should update whenever the sources change. -----Original Message----- I have a column D where the values of D's cells are dependent on cells A, B, and C of that row. Once A, B, or C is changed, I want the calculation for D to be updated. How do I create a macro with VBA that will do this? (I believe I need a macro because the conditional statements are very tedious and complex by using nested ands and ors.) Thanks very much. . . . |
Macros/VBA
Hi Don,
The problem is this -- is there a way, once a1 or a2 or a3 is updated, that I can get a4 to automatically update using VBA -- in a simimlar way to how the dependent cell is automatically updated if it were written as a function like =(OR(AND...)) etc.? Thanks. -----Original Message----- code would look something like If [a1]="b" and [a2]="M" and [a3]="F" then [a4]=1 If [a1]="b" and [a2]="M" and [a3]="O" then [a4]=2 If [a1]="b" and [a2]="M" and [a3]="T" then [a4]=2 etc these are case sensetive so you may need if ucase([a1])="B" etc or a1=b and a2=nM and a3=F, a4=3 or a1=b and a2=nm and a3=O, a4=4 or a1=b and a2=nm and a3=T, a4=5 or a1=s and a3=F, a4=6 or a1=s and a3=O, a4=7 or a1=s and a3=T, a4=8 or a1=a and a2=M and a3=F, a4=7 or a1=a and a2=m and a3=O, a4=8or a1=a and a2=m and a3=T, a4=8 "mika." wrote in message ... Hello, The if statement (in pseudocode) would be something like this: If a1=b and a2=M and a3=F, a4=1 or a1=b and a2=m and a3=O, a4=2 or a1=b and a2=m and a3=T, a4=2 or a1=b and a2=nM and a3=F, a4=3 or a1=b and a2=nm and a3=O, a4=4 or a1=b and a2=nm and a3=T, a4=5 or a1=s and a3=F, a4=6 or a1=s and a3=O, a4=7 or a1=s and a3=T, a4=8 or a1=a and a2=M and a3=F, a4=7 or a1=a and a2=m and a3=O, a4=8or a1=a and a2=m and a3=T, a4=8 ...etc. There are 14 statements in total; this is why I thought it would be easier to use VBA to write the conditional statement rather than nested =(or (and...))..etc. Any advice? Thanks. -----Original Message----- Why not tell us the detail. Perhaps it needn't be so tedious and complex . |
Macros/VBA
A User-Defined Function should do what you want.
Function FillCell(rngA As Range, rngB As Range, rngC As Range) As Variant Application.Volatile If rngA.Value = "o" And rngB.Value = "m" And rngC.Value = "l" Then FillCell = 87 ElseIf rngA.Value = "o" And rngB.Value = 1 And rngC.Value = 6 Then FillCell = 65 ElseIf ' As many combos as you need. Else FillCell = "Combination not valid." End If End Function --------------------------------------------------------------------------------- Place this formula in Cell D1. =FillCell(A1,B1,C1) --------------------------------------------------------------------------------- When you change any of the values in the three cells, the value in D1 should change. You can copy this function down Column D as many times as needed. You'll need to adjust the code to get the combination that you're seeking. HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- On Wed, 3 Sep 2003 12:44:51 -0700, "mika" wrote: I have a column D where the values of D's cells are dependent on cells A, B, and C of that row. Once A, B, or C is changed, I want the calculation for D to be updated. How do I create a macro with VBA that will do this? (I believe I need a macro because the conditional statements are very tedious and complex by using nested ands and ors.) Thanks very much. |
Macros/VBA
Mika,
This may actually work better without a Macro. Try this. In a new worksheet create the following table (It is possible to do this within your existing sheet but I like to do tables outside my input sheet) two columns a b "bmO" 2 "bmT" 2 "bnMF" 3 ........ thousands of combinations are possible. The text string should match the combination you desire to choose and can be any length. This new sheet can be Hidden if you desire so users never see it, You can maintain your options easily without touching any code. Name these columns as a range. Use something convenient. "Options" perhaps. Now on your first sheet the formula in a4 would look something like this. =vlookup(concatenate(a1,a2,a3),options,2,false) What your doing is simply creating a text string from your choices. Then looking that string up in a table to get the result that matches the value from Column b on the new sheet. The vlookup can be used again and again. Hope this helps Gary M. wrote in message ... A User-Defined Function should do what you want. Function FillCell(rngA As Range, rngB As Range, rngC As Range) As Variant Application.Volatile If rngA.Value = "o" And rngB.Value = "m" And rngC.Value = "l" Then FillCell = 87 ElseIf rngA.Value = "o" And rngB.Value = 1 And rngC.Value = 6 Then FillCell = 65 ElseIf ' As many combos as you need. Else FillCell = "Combination not valid." End If End Function -------------------------------------------------------------------------- ------- Place this formula in Cell D1. =FillCell(A1,B1,C1) -------------------------------------------------------------------------- ------- When you change any of the values in the three cells, the value in D1 should change. You can copy this function down Column D as many times as needed. You'll need to adjust the code to get the combination that you're seeking. HTH Paul -------------------------------------------------------------------------- ------------------------------------ Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------- ------------------------------------ On Wed, 3 Sep 2003 12:44:51 -0700, "mika" wrote: I have a column D where the values of D's cells are dependent on cells A, B, and C of that row. Once A, B, or C is changed, I want the calculation for D to be updated. How do I create a macro with VBA that will do this? (I believe I need a macro because the conditional statements are very tedious and complex by using nested ands and ors.) Thanks very much. |
Macros/VBA
You could put your code inside a worksheet_change event.
Right click on the sheet tabview codein the left window select worksheetin the right window select worksheet_change insert your code "mika." wrote in message ... Hi Don, The problem is this -- is there a way, once a1 or a2 or a3 is updated, that I can get a4 to automatically update using VBA -- in a simimlar way to how the dependent cell is automatically updated if it were written as a function like =(OR(AND...)) etc.? Thanks. -----Original Message----- code would look something like If [a1]="b" and [a2]="M" and [a3]="F" then [a4]=1 If [a1]="b" and [a2]="M" and [a3]="O" then [a4]=2 If [a1]="b" and [a2]="M" and [a3]="T" then [a4]=2 etc these are case sensetive so you may need if ucase([a1])="B" etc or a1=b and a2=nM and a3=F, a4=3 or a1=b and a2=nm and a3=O, a4=4 or a1=b and a2=nm and a3=T, a4=5 or a1=s and a3=F, a4=6 or a1=s and a3=O, a4=7 or a1=s and a3=T, a4=8 or a1=a and a2=M and a3=F, a4=7 or a1=a and a2=m and a3=O, a4=8or a1=a and a2=m and a3=T, a4=8 "mika." wrote in message ... Hello, The if statement (in pseudocode) would be something like this: If a1=b and a2=M and a3=F, a4=1 or a1=b and a2=m and a3=O, a4=2 or a1=b and a2=m and a3=T, a4=2 or a1=b and a2=nM and a3=F, a4=3 or a1=b and a2=nm and a3=O, a4=4 or a1=b and a2=nm and a3=T, a4=5 or a1=s and a3=F, a4=6 or a1=s and a3=O, a4=7 or a1=s and a3=T, a4=8 or a1=a and a2=M and a3=F, a4=7 or a1=a and a2=m and a3=O, a4=8or a1=a and a2=m and a3=T, a4=8 ...etc. There are 14 statements in total; this is why I thought it would be easier to use VBA to write the conditional statement rather than nested =(or (and...))..etc. Any advice? Thanks. -----Original Message----- Why not tell us the detail. Perhaps it needn't be so tedious and complex . |
Macros/VBA
Hi Gary,
This is what I put down: =VLOOKUP(CONCATENATE(O3,"#",S3,"#",R3,"#",P3),OSRP ,2,FALSE) OSRP is the name of column A in my other sheet, and the values I want to be inputed in my dependent cell are in column B of the other sheet. I did the # because I am allowed to have blank values for O3, S3, R3 and P3. But for the above statement, I get an error saying "the macros int his project are disabled" and then #NAME? appears. Any suggestsions? Thanks, mika. -----Original Message----- Mika, This may actually work better without a Macro. Try this. In a new worksheet create the following table (It is possible to do this within your existing sheet but I like to do tables outside my input sheet) two columns a b "bmO" 2 "bmT" 2 "bnMF" 3 ........ thousands of combinations are possible. The text string should match the combination you desire to choose and can be any length. This new sheet can be Hidden if you desire so users never see it, You can maintain your options easily without touching any code. Name these columns as a range. Use something convenient. "Options" perhaps. Now on your first sheet the formula in a4 would look something like this. =vlookup(concatenate(a1,a2,a3),options,2,false) What your doing is simply creating a text string from your choices. Then looking that string up in a table to get the result that matches the value from Column b on the new sheet. The vlookup can be used again and again. Hope this helps Gary M. wrote in message .. . A User-Defined Function should do what you want. Function FillCell(rngA As Range, rngB As Range, rngC As Range) As Variant Application.Volatile If rngA.Value = "o" And rngB.Value = "m" And rngC.Value = "l" Then FillCell = 87 ElseIf rngA.Value = "o" And rngB.Value = 1 And rngC.Value = 6 Then FillCell = 65 ElseIf ' As many combos as you need. Else FillCell = "Combination not valid." End If End Function -------------------------------------------------------- ------------------ ------- Place this formula in Cell D1. =FillCell(A1,B1,C1) -------------------------------------------------------- ------------------ ------- When you change any of the values in the three cells, the value in D1 should change. You can copy this function down Column D as many times as needed. You'll need to adjust the code to get the combination that you're seeking. HTH Paul -------------------------------------------------------- ------------------ ------------------------------------ Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------- ------------------ ------------------------------------ On Wed, 3 Sep 2003 12:44:51 -0700, "mika" wrote: I have a column D where the values of D's cells are dependent on cells A, B, and C of that row. Once A, B, or C is changed, I want the calculation for D to be updated. How do I create a macro with VBA that will do this? (I believe I need a macro because the conditional statements are very tedious and complex by using nested ands and ors.) Thanks very much. . |
Macros/VBA
Mika,
The "macros Disabled" warning comes from macro security setting. Close your worksheet , Open a blank worksheet. From the main menu choose Tools/Macro/Security/ choose the macro security level you want. Most IT departments demand at least a medium security setting so you have to choose to run worksheet Macros. Re-open the sheet your working in. you may be prompted to enable macros but the warning should be gone. The Vlookup solution does not use macros at all so perhaps you have some macro modules still in the sheet. If you have no other macros in your program you may need to open the VBA editor and remove all the macro modules for your project. or remove the macros from the worksheet objects if that is where you put them. You have probably figured it out but when you add new items to the table for choices, you need to either insert them in the middle of your named range or edit the name definition to include the new rows. (From the menu, Insert/Name/Define) Good luck. "mika." wrote in message ... Never mind -- I figured out what you meant by "naming" the range now. :) Thank you. -----Original Message----- Hi Gary, This is what I put down: =VLOOKUP(CONCATENATE (O3,"#",S3,"#",R3,"#",P3),OSRP,2,FALSE) OSRP is the name of column A in my other sheet, and the values I want to be inputed in my dependent cell are in column B of the other sheet. I did the # because I am allowed to have blank values for O3, S3, R3 and P3. But for the above statement, I get an error saying "the macros int his project are disabled" and then #NAME? appears. Any suggestsions? Thanks, mika. -----Original Message----- Mika, This may actually work better without a Macro. Try this. In a new worksheet create the following table (It is possible to do this within your existing sheet but I like to do tables outside my input sheet) two columns a b "bmO" 2 "bmT" 2 "bnMF" 3 ........ thousands of combinations are possible. The text string should match the combination you desire to choose and can be any length. This new sheet can be Hidden if you desire so users never see it, You can maintain your options easily without touching any code. Name these columns as a range. Use something convenient. "Options" perhaps. Now on your first sheet the formula in a4 would look something like this. =vlookup(concatenate(a1,a2,a3),options,2,false ) What your doing is simply creating a text string from your choices. Then looking that string up in a table to get the result that matches the value from Column b on the new sheet. The vlookup can be used again and again. Hope this helps Gary M. wrote in message . .. A User-Defined Function should do what you want. Function FillCell(rngA As Range, rngB As Range, rngC As Range) As Variant Application.Volatile If rngA.Value = "o" And rngB.Value = "m" And rngC.Value = "l" Then FillCell = 87 ElseIf rngA.Value = "o" And rngB.Value = 1 And rngC.Value = 6 Then FillCell = 65 ElseIf ' As many combos as you need. Else FillCell = "Combination not valid." End If End Function ------------------------------------------------------- - ------------------ ------- Place this formula in Cell D1. =FillCell(A1,B1,C1) ------------------------------------------------------- - ------------------ ------- When you change any of the values in the three cells, the value in D1 should change. You can copy this function down Column D as many times as needed. You'll need to adjust the code to get the combination that you're seeking. HTH Paul ------------------------------------------------------- - ------------------ ------------------------------------ Be advised to back up your WorkBook before attempting to make changes. ------------------------------------------------------- - ------------------ ------------------------------------ On Wed, 3 Sep 2003 12:44:51 -0700, "mika" wrote: I have a column D where the values of D's cells are dependent on cells A, B, and C of that row. Once A, B, or C is changed, I want the calculation for D to be updated. How do I create a macro with VBA that will do this? (I believe I need a macro because the conditional statements are very tedious and complex by using nested ands and ors.) Thanks very much. . . |
| All times are GMT +1. The time now is 08:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com