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.

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.

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.



.