Controlling actions based on data in external excel file

[AGlazer]

Hi, everyone.

I'm in the process of writing a macro that basically handles a decisions tree. I've got data like this:

In excel file "Test1.xls"
Name B Sig
Rent .25 .01
Own 1.2 .15
Parents .22 .78

In excel file "Test2.xls"
Name B Sig
Rent - D .15 .20
Own - D .22 .05
Parents -D .19 .22

What I want to be able to do is go into file Test2.xls and run a macro that does the following

If column &quot;sig&quot;, row A in test1.xls < .105 and column &quot;sig, row A in test2.xls < .105, then add both B's.

If column &quot;sig&quot;, row A in test1.xls > .105 and column &quot;sig&quot;, row A in test 2.xls <.105, then use the B from test 2.xls.

If column &quot;sig&quot;, row A in test1.xls > .105 and column &quot;sig, row A in test2.xls > .105, then put a 0.

If column &quot;sig&quot;, row A in test1.xls > .105 and column &quot;sig&quot;, row A in test 2.xls <.105, then use the B from test 1.xls.

I'm first not sure how to access the external file and then how to perform the complicated If statements. I considered doing something like this (this is pseudocode and doesn't work):


Sub SelectionTool()
Dim nItems As Integer
Dim i As Integer
Dim j As Integer
Dim sig As Integer
Dim benchFile As String

' set universal constants
sig = 0.105
nItems = 12
benchFile = &quot;bench_test.xls&quot;

With Worksheet(&quot;orgbrand-of&quot

For i = 1 To nItems Step 1
j = i + 4
oldValue = Application.WorksheetFunction.VLookup(.Range(&quot;A&quot; & j & &quot;&quot, &quot;[&quot; & benchFile & &quot;]'worksheet'!R2C1:R10:C4&quot;, 2, False)
newValue = .Range(&quot;G&quot; & j & &quot;&quot.Value
If newValue < sig Then
If oldValue < sig Then
Range(&quot;X&quot; & j & &quot;&quot.Select
ActiveCell.FormulaR1C1 = newValue + oldValue
Else
Range(&quot;X&quot; & j & &quot;&quot.Select
ActiveCell.FormulaR1C1 = newValue
End If
Else
If oldValue < sig Then
Range(&quot;X&quot; & j & &quot;&quot.Select
ActiveCell.FormulaR1C1 = oldValue
Else
Range(&quot;X&quot; & j & &quot;&quot.Select
ActiveCell.FormulaR1C1 = &quot;0&quot;
End If
End If
Next i

Any ideas?