Worksheet Summation and Column multiplication

[NewToVBA1]

Hi All,
I have two problem and I could not figure it out by my own nor with by searching the net.

Problem1: I need a VBA macro to add respective cells of two identical worksheets, but with different data, and paste it in a third worksheet. The number of rows with available data will change with time, so I need the code to search the available data and do the addition.

Problem2: I need another code to multiply cells of one column from one worksheet to multiply with another column with another worksheet and paste the resulting column in a third worksheet. Here also, the rows with available data will change with time, so I need the code to search the available data and do the addition.


I am under pressure to complete the work but I did not realise that I it will be so complicated. Thanks in advance for the help.


Regards

 

[mirabeau]

If your 3 sheets are named "a", "b" and "c" respectively then you should be able to add values of corresponding cells on "a" and "b" and put the results in corresponding cells in sheet "c" by the following code

Sub addem()
Dim lrw&, lcl&, i&, j&
Dim aa, bb, cc()
With Sheets("a")
    lrw = .Cells.SpecialCells(11).Row
    lcl = .Cells.SpecialCells(11).Column
    aa = .Cells(1).Resize(lrw, lcl)
End With
With Sheets("b")
    bb = .Cells(1).Resize(lrw, lcl)
End With
ReDim cc(1 To lrw, 1 To lcl)
For i = 1 To lrw
    For j = 1 To lcl
        cc(i, j) = aa(i, j) + bb(i, j)
Next j, i
Sheets("c").Cells(1).Resize(lrw, lcl) = cc
End Sub

You can do something on similar lines if multiplying columns.

 

[NewToVBA1]

Thanks a ton Mirabeau...I am trying at this moment. Looks like it is working.

 

[mirabeau]

Hi,

Thanks for your feedback.

Probably somewhat briefer and faster is this non-looping one, using named ranges

Sub addem2()
Dim lrw&, lcl&
With Sheets("a")
    lrw = .Cells.SpecialCells(11).Row
    lcl = .Cells.SpecialCells(11).Column
    .Cells(1).Resize(lrw, lcl).Name = "aa"
End With
With Sheets("b")
    .Cells(1).Resize(lrw, lcl).Name = "bb"
End With
Sheets("c").Cells(1).Resize(lrw, lcl) = Evaluate("=(aa+bb)")
End Sub

 

[NewToVBA1]

Thanks once again