Newbie question ...

[silmara]

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

 

[vconfused]

Would B3 = X2 and B4 = Y2?

 

[tactps]

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]

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]

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]

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]

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]

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