問題描述
在位於兩個不同工作表的兩個範圍內添加單元格 (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.
‑‑‑‑‑
參考解法
方法 1:
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.