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


"Monique" wrote in message
...
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


"Bernie Deitrick" wrote:

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


"Monique" wrote in message
...
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" wrote:

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


"Monique" wrote in message
...
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