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.

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

.

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

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

.

.

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




.

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.

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.

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




.

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.



.

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.



.

.