Can anyone help me with my problem?

I want to to calculate with entire ranges (to which I assigned a name beforehand) just like I would with individual cells. An example:

Result-range = range1 - range2

the ranges have the same dimension and the upperleft cell from range2 should be subtracted from the upperleft cell from range1 and its resulting value written into the upperleft cell in the result-range and so on.

Not a big thing I reckon but still couldn't find a solution

Any kind of hint is much appreciated,
Cheers

Fabalicious

On the basis that range1 & range2 are the same shape and size and subtracting coresponding cells then this should do it.

Sub AddRanges()
Dim i As Integer
Dim j As Integer
Dim R1 As Range
Dim R2 As Range
Dim RR As Range

Set R1 = Range("Range1")
Set R2 = Range("Range2")
Set RR = Range("Result")

For i = 0 To RR.Rows.Count - 1

For j = 0 To RR.Columns.Count - 1
Cells(RR.Row, RR.Column).Offset(i, j).Value = Cells(R1.Row, R1.Column).Offset(i, j) + Cells(R2.Row, R2.Column).Offset(i, j)
Next j

Next i

End Sub

MTB

wicked, thank you very much.

i was quite sure there must be some pre-defined function to do this, that's why i couldn't be bothered to start writing a loop...

can i make a function out of it and call this function in a sub? that obviously must be possible but i couldn't make it run

do you see an easy way to solve this?

Cheers again

F

Hi fabalicious

I am not totally sure there is not a built in function to do this either! (somebody will know) but that takes the fun out of it!

Normally you would just enter the formulas in the cells to do the calculations, but I assume there is more being done to the sheet than this.

Of course it can be written as a function, but I am not sure why you would want to, unless you make it a Boolean function with error handling in case it falls over, ie incapable ranges/no range defined etc. Otherwise you could you can call it as a sub anyway (and pass the ranges or range names as augments in required).

MTB

Range("A1:E5").Value = AddRanges(Range1, Range2)

does not work though...

at least if the parameters are passed to the sub as follows:

Sub AddRanges(Range1 As Range, Range2 As Range)

is there something like a return-function i have to use at the end of the sub in order to pass the result back to the main-script?

Fabalicious

fabalicious

To use it as a Called sub it show be defines as below

Sub AddRanges(byval Range1 as String, byval Range2 as String, byval Result as string)

Dim i As Integer
Dim j As Integer
Dim R1 As Range
Dim R2 As Range
Dim RR As Range

Set R1 = Range(Range1)
Set R2 = Range(Range2)
Set RR = Range(Result)

For i = 0 To RR.Rows.Count - 1

For j = 0 To RR.Columns.Count - 1
Cells(RR.Row, RR.Column).Offset(i, j).Value = Cells(R1.Row, R1.Column).Offset(i, j) + Cells(R2.Row, R2.Column).Offset(i, j)
Next j

Next i

End Sub



Then to run it use

AddRange "Range1 Name", "Range 2 Name", "Result Range Name"

Or you could pass Ranges as you suggest and remove the Dim as Set range statments.

I am no sure what you want to pass back !?

MTB

well i guess i was more thinking loudly... in PERL e.g., you can use the the return function to specifiy the variable to be passed back by the subroutine, so in that case the result range (or rather its values).

i couldn't get your sub call run. i removed one space (range_2) and the "range" in "result range name" and it still didn't work...

as the the result-range is defined in the main script (and passed to the sub as a parameter), i guess i don't have to call the sub anything like that:

Range("A1:E5").Value = AddRanges "Range1 Name", "Range2 Name", "Result Name"

but only using

AddRanges "Range1 Name", "Range2 Name", "Result Name"

is that correct?

F.

I think the short answer is YES

The code line

AddRanges "Range1 Name", "Range2 Name", "Result Name"

will automatically put the the calculated results in the "Result Name" range as values.

You could put a formula to add (subtract) the two elements in the results range by using the address property of the cells, similar to the INDIECT code in our other thread, if you prefer, but I digress.


MTB