CODEWITHSUNDEEP
vbaexcelexcel-2003
MPD
MPD

Reputation: 23

Adding cells in two ranges which are located at two different sheets

I am trying to sum corresponding cell values in two ranges and replace the values in one of the ranges with the sum . See my code snippet below:

For i = 1 To daterow.Rows.count - 1
    If (outinter - diff) > 0.5 Then
         diff = diff + DateDiff("n", Indt(i - 1), Indt(i))
         Y = Sheets("output").Cells(Rows.count, 1).End(xlUp).row + 1
         Set inputRng = Sheets("Input").Range(Sheets("Input").Cells(i, 7), _
                        Sheets("input").Cells(i, ngag + 6))
         Set outputRng = Sheets("Output").Range(Sheets("Output").Cells(Y, 6), _
                        Sheets("Output").Cells(Y, ngag + 5))
         outputRng.value = Evaluate(outputRng.Address & "+" & inputRng.Address)
    Else
         'Some Code here
    End If
Next

So, I set up two ranges (inputRng and outputRng) in lines 5 and 6. inputRng is from sheets("Input") and outputRng is from Sheets("output"). When I run the code, the "Evaluate" function (line 7) sums the ranges but here is the problem:

Both ranges are derived from the same sheet and since I am in sheets("input") during this computation, values for both inputRng and outputRng are derived from sheets("input"). When I rewrite line 7 as follows:

sheets("output").outputRng.value = Evaluate(sheets("output").outputRng.Address _
                                  & "+" & sheets("input").inputRng.Address)

I get an error message "Object does not support this property or method". Is there any other way to force the code to read the outputRng from my Sheets("output")?

I could loop through each cell and sum the values, but it would take a long time to run the code as this macro has to handle a lot of data.

Any thought will be greatly appreciated. Thanks for looking into it.

MPD

Solution

Oops, the server did not let me answer the question, So, I am posting the solution here instead:

Thanks everyone for your input. In fact Excellll's last comment clicked my mind! So, here is how i got it working:

Dim value() As Variant
Dim shtIn As Worksheet, shtOut As Worksheet
Set shtIn = Sheets("Input")
Set shtOut = Sheets("Output")
Redim Value(daterow.Rows.count - 1)

For i = 1 To daterow.Rows.count - 1
    If (outinter - diff) > 0.5 Then
      diff = diff + DateDiff("n", Indt(i - 1), Indt(i))
      Y = shtOut.Cells(Rows.count, 1).End(xlUp).row + 1
      Set inputrng = shtIn.Range(shtIn.Cells(i, 7), shtIn.Cells(i, ngag + 6))
      Set outputRng = shtOut.Range(shtOut.Cells(Y, 6), shtOut.Cells(Y, ngag + 5))
      value = Evaluate("Output!" & outputRng.Address & "+" _
                  & "Input!" & inputrng.Address)
      shtOut.Range(shtOut.Cells(Y, 6), shtOut.Cells(Y, ngag + 5)) = value
    Else
      'Some Code here
    End If
Next

So, instead of writing out the sum into a range in another sheet, I stored that into an array and later transferred the array into the Output Range which I could do without looping.

Upvotes: 1

Views: 1642

Answers (1)

Excellll
Excellll

Reputation: 5785

Why not use this at line 7 instead?

 outputRng = outputRng.Value + inputRng.Value

EDIT:

You could do yourself a favor for debugging's sake by declaring a couple of Worksheet objects and a couple of Range objects.

Dim shtIn As Worksheet, shtOut As Worksheet
Dim rngIn As Range, rngOut As Range
Set shtIn = Sheets("Input")
Set shtOut = Sheets("Output")
Set rngIn = shtIn.Range("Input")
Set rngOut = shtOut.Range("Output")

Then use these ranges in your code instead of the repeated Sheets("...").Range("...") constructions.

Upvotes: 1

Related Questions