adding two ranges

[Guest]

Below is a function that I am creating that will add the cells in each range
with each other. It does not work. Can anyone tell me what it is I'm doing
wrong?

Thanks in advance

Public Function AddCells(RngOne As Range, RngTwo As Range)

Dim fCell As Range
Dim sCell As Range

For Each fCell In RngOne

For Each sCell In RngTwo
sCell.Value = sCell
Next sCell

fCell.Value = fCell + sCell

Next fCell

End Function

 

[Bernie Deitrick]

Monique,

What you are doing wrong:
#1 It's a function, which can't change the value of cells - a function can only return a value to
the cell from which it is called.
#2 sCell is always the last cell in RngTwo, because of your for each statement prior to the adding

Perhaps something like this:

Sub AddCells(RngOne As Range, RngTwo As Range)
Dim i As Integer

If RngOne.Cells.Count <> RngTwo.Cells.Count Then
MsgBox "The ranges are not similarly sized"
Exit Sub
End If

For i = 1 To RngOne.Cells.Count
RngOne.Cells(i).Value = RngOne.Cells(i).Value + _
RngTwo.Cells(i).Value
Next i

End Sub

'Called like

Sub test()
AddCells Range("A1:A10"), Range("B1:B10")
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

I am not trying to change the values in the cells. I am trying to create a
function that will add two separate ranges together when it is called in a
cell.

Picture this:
A1 = 6
A2 = 8
A3 = A1 + A2 (this is where the function will do the math for me)

Now picture this occuring with a group of numbers instead of one.

A1 6 B1 7 C1 10
A2 8 B2 8 C2 89
A3 4 B3 10 C3 14
function would add the values from A1:A3 and put that value in A4, etc.

I hope this helps better understand what I want to do.
Thanks again for you quick response

 

[Bernie Deitrick]

Monique,

You could use Excel's built-in function:

=SUM(A1:A3)

If you wanted to do your own function, it would simply be

Public Function AddCells(RngOne As Range, RngTwo As Range)
AddCells = Application.Sum(RngOne, RngTwo)
End Function

Used like

=AddCells(A1,A2)

But SUM is _much_ preferred.

HTH,
Bernie
MS Excel MVP

 

[Guest]

When trying out this method, I get a type mismatch. I cannot use Excel
Built-in application because the client wants all formulas hidden from view
with only values seen on the user side.


Function AddRanges(RngOne As Range, RngTwo As Range)

AddCells = Application.Sum(RngOne, RngTwo)

End Function

Sub test()

Range("C70:V71") = AddRanges("C68:V68", "C69:V69")

End Sub

 

[Guest]

Monique,

If you check the locked and hidden properties of the cell(s) containing the
formula and then put a password on the sheet, they could see the results, not
the formulas, and couldn't edit the calculated (protected) cells. It takes 2
different menu choices to make it happen.

Format/Cells/Protection - check the locked and hidden boxes
Tools/Protection/Protect Sheet - Enter a password twice

Would that do the trick?

Roy

 

[Guest]

yes that would work. however, the client wants a sort of calculation engine
where all calculations involved in this project take place in one area.

so do you think it is possible to perform calculations on two or more
separate ranges using a function or do i have to go cell by cell and code the
calculation?

 

[Bernie Deitrick]

Monique,

Ah, I think I'm beginning to understand. Note that you had C70:V71 as the target address, and I
changed that to be a one-row range.

Sub test()
Range("C70:V70").Value = AddRanges("C68:V68", "C69:V69")
End Sub

Function AddRanges(RngOne As String, RngTwo As String) As Variant
Dim ReturnVals() As Variant
Dim i As Integer

ReDim ReturnVals(1 To Range(RngOne).Cells.Count)
For i = 1 To Range(RngOne).Cells.Count
ReturnVals(i) = Range(RngOne)(i).Value + Range(RngTwo)(i).Value
Next i

AddRanges = ReturnVals

End Function


HTH,
Bernie
MS Excel MVP

 

[Guest]

Thank you so much... that definitely did work. I didn't realize I had a
typing error.

I really appreciate your help.

 

[Bernie Deitrick]

This changed code will allow you to add vertically oriented ranges:

Function AddRanges(RngOne As String, RngTwo As String) As Variant
Dim ReturnVals() As Variant
Dim i As Integer

ReDim ReturnVals(1 To Range(RngOne).Cells.Count)
For i = 1 To Range(RngOne).Cells.Count
ReturnVals(i) = Range(RngOne)(i).Value + Range(RngTwo)(i).Value
Next i

If Range(RngOne).Rows.Count = 1 Then
AddRanges = ReturnVals
Else
AddRanges = Application.Transpose(ReturnVals)
End If

End Function