Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Unanswered: calculations with ranges

    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

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Apr 2004
    Posts
    11
    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

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  5. #5
    Join Date
    Apr 2004
    Posts
    11
    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

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  7. #7
    Join Date
    Apr 2004
    Posts
    11
    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.

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •