Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding two ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding two ranges
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding two ranges
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding two ranges
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding two ranges
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding two ranges
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 "Monique" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding two ranges
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? "Roy" wrote: 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 "Monique" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding two ranges
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding two ranges
Thank you so much... that definitely did work. I didn't realize I had a
typing error. I really appreciate your help. "Bernie Deitrick" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding two ranges
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 -- HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding numbers that contain ranges | Excel Worksheet Functions | |||
Adding Non-Contiguous Ranges | Excel Discussion (Misc queries) | |||
adding ranges | Excel Discussion (Misc queries) | |||
adding named ranges | New Users to Excel | |||
Adding ranges | Excel Programming |