Newbie question ...

silmara

New Member
Joined
Jan 27, 2005
Messages
9
I don't really know how I could put this into words, so here it is ...

A1
B1 B2

X1 Y1 Z1
X2 Y2 Z2
Xn Yn Zn

Where A1 is set equal to B1 and B2 (via a formula in A1 cell).

How would it be possible to do the followings in an automated way:

. Copy or feed X1 value to B1 and Y1 value to B2
. A1's result value then copied or fed to Z1
. So on for X2,Y2,Z2 up to Xn,Yn,Zn

Your help is appreciated ! Note that it is important for the above structure to remain intact.

Silmara
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Why not put the formula next to X1 and Y1 and copy down.

Lets say your formula is A1 is:
=sum(B1:B2)
and that X1 is in cell A3 and Y1 is in B3
In Z1, try:
=sum(A3:B3)
and copy down
 

silmara

New Member
Joined
Jan 27, 2005
Messages
9
vconfused

Actually, A1 is a top event of an enormous fault tree (see explanation below). B1 and B2 are just there to demonstrate my question. The arborescence below A1 is much more complicated than just B1 and B2.

tactps

That is why (see above) I can't simply put down in Z1 = sum(a3:b3). I can't do the work of representing the entire fault tree by an equation.

Silmara

So in other words, I have a large fault tree, i.e. an arborescence with a single value on top (call a top event probability) and various basic events at the bottom. In between there are various AND and OR gates. I want to feed values (called failure rates) to the basic events at the bottom and collect the results at the top to put this into a table.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460

ADVERTISEMENT

How about a VBA solution:
Highlight the first column of cells that you want the calc done on.

Sub doit()
Dim cell As Range
For Each cell In Selection
[a2] = cell.Value
[b2] = cell.Offset(0, 1).Value
cell.Offset(0, 2) = [a1].Value
Next cell
End Sub
 

silmara

New Member
Joined
Jan 27, 2005
Messages
9
tactps

It resolved my problem ! Almost ... Would it be possible to call that doit procedure directly from a cell ? Or does it absolutly need to be call via a key stroke (like any other macros)?

Thanks for your help !

Silmara
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
The one I've written works on a range, so you can't then click on another cell.

How about adding a macro button that you can place over a cell?:
1. From the "View" menu choose "Toolbar" then "Forms".
2. Click on the "Button" icon.
3. Drag over the area you want the button to cover.
4. Assign the "doit" macro.

Is this ok?
 

silmara

New Member
Joined
Jan 27, 2005
Messages
9
That could be an idea for the button. But the perfect scenario would be that it updates alone.

I made my sub public and calling it from a cell. However, when it tries to update the content of another cell, the process steps out of the macro and the rest of the code is not executed (see below, it stops at [ae26] line and exits the macro - therefore the only thing this macro is doing is updating cell ae26).

Public Sub Probability()
'
' Probability Macro
' Keyboard Shortcut: Ctrl+p
'
Dim ProbabilityCell As Range
Dim DeltaColumn As Integer
Dim ExposureTime As Integer

Const FailureRateColumn = 7
Const ExposureTimeRow = 3

'Feed Fault Tree with Exposure Time
[ae26] = Cells(ExposureTimeRow, (Selection.Column + 1)).Value
[ai26] = Cells(ExposureTimeRow, (Selection.Column + 1)).Value

DeltaColumn = Selection.Column - FailureRateColumn

'Feed Fault Tree with Failure Rate and collect Top Event Probability
For Each ProbabilityCell In Selection
[aa25] = ProbabilityCell.Offset(0, -DeltaColumn).Value
[ai25] = ProbabilityCell.Offset(0, -DeltaColumn).Value
ProbabilityCell.Value = [ak5].Value
Next ProbabilityCell

End Sub

Again, thank you for your help !

Silmara
 

Forum statistics

Threads
1,148,274
Messages
5,745,796
Members
423,973
Latest member
man_this_is_hard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top