VB Excel Range

N

Nick.Korynski

I need to compare the values in a cell in one workbook to the same cell
in another workbook and store them in the same cell in a new workbook.
I wrote the code already to open each workbook, add a new workbook, and
format my heading information on the sheets. However, I am struggling
with finding an efficient way to make the value of A1 in my new
workbook=A1 in workbook1 - A1 in workbook2. With the Range command, is
there any way to have the cell know its own address? Or do I have to
increment cell by cell, store the address and then insert that
information into the formula to perform the subtraction? Any help
would be appreciated. Also let me know if this makes no sense or
doesn't provide enough info on what I'm trying to do. Thanks.

Nick
 
G

Gary''s Student

Try:

Sub Macro1()
Dim r1, r2, r3 As Range

For i = 1 To 10
For j = 1 To 10
Set r1 = Workbooks("Book1").Worksheets("Sheet1").Cells(i, j)
Set r2 = Workbooks("Book2").Worksheets("Sheet1").Cells(i, j)
Set r3 = Workbooks("Book3").Worksheets("Sheet1").Cells(i, j)
r3.Value = r2.Value - r1.Value
Next
Next

End Sub

There is probably a cleaner way to do this. This assumes that all three
Books are already opened.
 
Top