Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Rabges without for each loops
Hi,
I should like to add the cells in two ranges WITHOUT having to loop through each range separately. Suppose the cells A1 to A5 in my worksheet are filled, as well as cells B1 to B5 Now I thought this would do the trick. But apparently not: Sub AddRanges() Dim r1 As Range Dim r2 As Range Dim r3 As Range Set r1 = Range("a1:a5") Set r2 = Range("b1:b5") Set r3 = Range("c1:c5") r3.value = r1.value + r2.value End Sub Any suggestions? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Rabges without for each loops
Instead of adding the values, insert a formula:
ActiveSheet.Range("C1:C5").FormulaR1C1 = "=RC[-2]+RC[-1]" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "mdalamers via OfficeKB.com" <u32130@uwe wrote in message news:6e8b840acd49a@uwe... Hi, I should like to add the cells in two ranges WITHOUT having to loop through each range separately. Suppose the cells A1 to A5 in my worksheet are filled, as well as cells B1 to B5 Now I thought this would do the trick. But apparently not: Sub AddRanges() Dim r1 As Range Dim r2 As Range Dim r3 As Range Set r1 = Range("a1:a5") Set r2 = Range("b1:b5") Set r3 = Range("c1:c5") r3.value = r1.value + r2.value End Sub Any suggestions? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Rabges without for each loops
Try this, however I had to name the ranges on the worksheet to make it work.
Tried setting the range in code and it did not work...??? Sub AddRanges() Dim Cell As Range Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim r1v As Integer Dim r2v As Integer Dim r3v As Integer For Each Cell In Range("rng1") r1v = r1v + Cell.Value Next For Each Cell In Range("rng2") r2v = r2v + Cell.Value Next For Each Cell In Range("rng3") r3v = r3v + Cell.Value Next MsgBox r1v + r2v + r3v End Sub HTH Regards, Howard "mdalamers via OfficeKB.com" <u32130@uwe wrote in message news:6e8b840acd49a@uwe... Hi, I should like to add the cells in two ranges WITHOUT having to loop through each range separately. Suppose the cells A1 to A5 in my worksheet are filled, as well as cells B1 to B5 Now I thought this would do the trick. But apparently not: Sub AddRanges() Dim r1 As Range Dim r2 As Range Dim r3 As Range Set r1 = Range("a1:a5") Set r2 = Range("b1:b5") Set r3 = Range("c1:c5") r3.value = r1.value + r2.value End Sub Any suggestions? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Rabges without for each loops
One mo
Option Explicit Sub AddRanges() Dim r1 As Range Dim r2 As Range Dim r3 As Range Set r1 = Range("a1:a5") Set r2 = Range("b1:b5") Set r3 = Range("c1:c5") r3.Value = r1.Value r2.Copy r3.PasteSpecial Paste:=xlPasteValues, _ operation:=xlPasteSpecialOperationAdd Application.CutCopyMode = False End Sub It's the same as doing select r1 edit|copy select r3 edit|Paste special values (r1 to r3) then select r2 edit|copy select r3 edit|Paste special|paste values|checking add "mdalamers via OfficeKB.com" wrote: Hi, I should like to add the cells in two ranges WITHOUT having to loop through each range separately. Suppose the cells A1 to A5 in my worksheet are filled, as well as cells B1 to B5 Now I thought this would do the trick. But apparently not: Sub AddRanges() Dim r1 As Range Dim r2 As Range Dim r3 As Range Set r1 = Range("a1:a5") Set r2 = Range("b1:b5") Set r3 = Range("c1:c5") r3.value = r1.value + r2.value End Sub Any suggestions? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Rabges without for each loops
I meant to ask, what's wrong with looping?
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Jon Peltier" wrote in message ... Instead of adding the values, insert a formula: ActiveSheet.Range("C1:C5").FormulaR1C1 = "=RC[-2]+RC[-1]" - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "mdalamers via OfficeKB.com" <u32130@uwe wrote in message news:6e8b840acd49a@uwe... Hi, I should like to add the cells in two ranges WITHOUT having to loop through each range separately. Suppose the cells A1 to A5 in my worksheet are filled, as well as cells B1 to B5 Now I thought this would do the trick. But apparently not: Sub AddRanges() Dim r1 As Range Dim r2 As Range Dim r3 As Range Set r1 = Range("a1:a5") Set r2 = Range("b1:b5") Set r3 = Range("c1:c5") r3.value = r1.value + r2.value End Sub Any suggestions? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Rabges without for each loops
Thanks Guys.
This helped. By the way Jon. Nothing is wrong with looping but I prefer using a few lines rather than many lines. Have a great weekend! Michiel -- Message posted via http://www.officekb.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Rabges without for each loops
Well, it should be a time-to-execute thing, not a number-of-lines thing. The
line count is pretty close. Looping through ranges can be very slow, of course, but if you read the ranges into arrays, and write the results as an array, it will avoid the slow cell-by-cell looping. It might even be faster than Dave's interesting technique, because his has at least one extra worksheet operation. Sub AddRanges() Dim v1 As Variant Dim v2 As Variant Dim v3(1 to 5, 1 to 1) As Variant Dim i As Integer ' get worksheet data into VB arrays v1 = Range("a1:a5").Value v2 = Range("b1:b5").Value ' loop VB arrays to do the addition For i = 1 to 5 v3(i, 1) = v1(i, 1) + v2(i, 1) Next ' write VB array back to sheet Range("c1:c5").Value = v3 End Sub Slightly shorter, one less trip to the worksheet, but reading is faster than writing: Sub AddRanges2() Dim vIn As Variant Dim vOut(1 to 5, 1 to 1) As Variant Dim i As Integer ' get worksheet data into VB arrays vIn = Range("a1:b5").Value ' loop VB arrays to do the addition For i = 1 to 5 vOut(i, 1) = vIn(i, 1) + vIn(i, 2) Next ' write VB array back to sheet Range("c1:c5").Value = vOut End Sub - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "mdalamers via OfficeKB.com" <u32130@uwe wrote in message news:6e952ee1953d3@uwe... Thanks Guys. This helped. By the way Jon. Nothing is wrong with looping but I prefer using a few lines rather than many lines. Have a great weekend! Michiel -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loops | Excel Programming | |||
Loops | Excel Programming | |||
Loops etc. | Excel Programming | |||
help with loops | Excel Programming | |||
LOOPS | Excel Programming |