Macros/VBA

  • Thread starter Thread starter mika
  • Start date Start date
M

mika

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?
 
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
Click to expand...
 
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.
 
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
Click to expand...



mika. said:
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.
Click to expand...


.
Click to expand...
 
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
 
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.
 
You could put your code inside a worksheet_change event.
Right click on the sheet tab>view code>in the left window select
worksheet>in the right window select worksheet_change
insert your code


mika. said:
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
Click to expand...



mika. said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
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.


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

--------------------------------------------------------
Click to expand...
------------------
-------
Place this formula in Cell D1.

=FillCell(A1,B1,C1)

--------------------------------------------------------
Click to expand...
------------------
-------

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
--------------------------------------------------------
Click to expand...
------------------ ------------------

.
Click to expand...
 
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. said:
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
Click to expand...
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
Click to expand...
never
see it,
You can maintain your options easily without touching
Click to expand...
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.


A User-Defined Function should do what you want.

Function FillCell(rngA As Range, rngB As Range, rngC
Click to expand...
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

-------------------------------------------------------
Click to expand...
-
------------------
-------
Place this formula in Cell D1.

=FillCell(A1,B1,C1)

-------------------------------------------------------
Click to expand...
-
------------------
-------

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
-------------------------------------------------------
Click to expand...
- -
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.



.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top