loop through cells in a range and pick up corresponding cell values in another range

[patrice.cezzar]

A have a set of rectangular ranges (20 rows and 5 columns) and I want
to loop through each cell, taking the values in the various cells as
inputs and populating the corresponding cell in another range with the
output.

That is to say:

inputA = Range("C7:G26")
inputB = Range("K7:O26")
output = Range("C30:G49")

The output is the result of the worksheet functions.

Assuming my function is output = A + B, I want to loop through so that:

C30.value = C7.value + K7.value
D30.value = D7.value + L7.value
....
C31.value = C8.value + K8.value
....
G49.value = G26.value + O26.value

What's the best way to achieve this?

 

[Tom Ogilvy]

Sub ComputeSum()

with Range("C30").Resize(20,5)
.Formula = "=Sum(C7,K7)"
' optional to replace formulas with values (uncomment next line)
' .Formula = .Value
End with

end Sub

 

[Guest]

The best way is likely arguable. Two examples - the first reads the data
into arrays, performs the calculation, then outputs the data. The second
manipulates the ranges w/o using arrays. With large amounts of data - it
could be faster using array variables.


Sub Test()
Dim varInputA As Variant
Dim varInputB As Variant
Dim varOutput As Variant
Dim i As Long: Dim t As Long

varInputA = Range("C7:G26").Value
varInputB = Range("K7:O26").Value
ReDim varOutput(LBound(varInputA, 1) To _
UBound(varInputA, 1), LBound(varInputA, 2) To _
UBound(varInputA, 2))

For i = LBound(varInputA, 1) To UBound(varInputA, 1)
For t = LBound(varInputA, 2) To UBound(varInputA, 2)
varOutput(i, t) = varInputA(i, t) + varInputB(i, t)
Next t
Next i

Range("C30:G49").Value = varOutput

End Sub


Sub test2()
Dim rngInputA As Range
Dim rngInputB As Range
Dim rngOutput As Range

Set rngInputA = Range("C7:G26")
Set rngInputB = Range("K7:O26")
Set rngOutput = Range("C30:G49")

For i = 1 To rngInputA.Rows.Count
For t = 1 To rngInputA.Columns.Count
rngOutput(i, t).Value = rngInputA(i, t) + _
rngInputB(i, t).Value
Next t
Next i

End Sub

 

[patrice.cezzar]

Thanks for the suggestions. I may be able to use JMB's Sub test2 as a
starting point and see if I can work with it, but let me give you some
more info. I think I simplified too much for the sake of the example.

What I'm really doing is copying the values of each of the cells in my
ranges into cells within an excel model that takes a number of other
assumptions and the output is several return metrics that I'm trying to
put into the grid. I'm not actually calculating anything within the
macro.

So each cell of my ranges represent an assumption that changes with
different criteria ("buckets" for loan size, FICO score and CLTV).

So for each combination of loan size/FICO/CLTV, I'm changing my
assumptions for interest rate, avg line size, loan expenses, loss
rates, etc... (I have a 20x5 range for each of these assumptions).
These assumptions, along with many other things, are fed into my model.

So in words, what I'm doing is:

Copy the upper-leftmost cell value in range (A, B, C....n) and paste
into the correct assumption cell in the model (identified
individually). Then copy the output cell and paste the value into the
upper-leftmost cell in range O.

Then do the same with the next cell in each range (top row, 2nd column)
and so on, through the bottom-rightmost cell.

Sheesh. I don't know why I'm having such a hard time explaining this.
Sorry for being so ineloquent!

 

[patrice.cezzar]

OK, never mind. First of all, thanks JMD - your Sub test2() will work
for what I'm doing.

BUT... I was sure I had done this before. I couldn't remember where,
and I couldn't remember how. Today, I finally found it. I used the
"Item" method, as below. Is there any reason you know of that this
wouldn't be reliable?


Sub runSummary()

cellCount = [rateMatrix].Cells.Count

For counter = 1 To cellCount
' update the assumptions from my input grids:
[cellRate].value = [rateMatrix].Item(counter).Value
[cellNCL].value = [nclMatrix].Item(counter).Value * 100
[cellSize].value = [lineSizeMatrix].Item(counter).Value
[cellExp].value = [loanExpMatrix].Item(counter).Value

' populate the results matrix:
[roecMatrix].Item(counter).Value = [roecLOL].Value

Next counter

End Sub

 

[Guest]

You should be able to use item - although its not needed when referencing
array elements or cells within ranges (row/col references). Honestly, I've
never used it so can't give an example of where it is required, but it's
certainly not wrong to use it.

 

[Guest]

Although if your computations can be done as an excel formula, Tom's
suggestion cuts to the chase.

 

[trice-nae]

Hi - thanks again.

I don't really think I can use Tom's example in this case, because
there are too many inputs/calucations to take into the macro.

Item *seems* to work, so I'll run with it. I just kind of stumble on
these things, and never know if there's something i"m not taking into
consideration that'll make things work funny later....

thanks once more.

 

[Tom Ogilvy]

I agree. You could only use it for the problem you originally described
which doesn't appear to have much correlation to what you are now asking.

 

[Guest]

But interestingly VBA automatically adjusts the range references in your
formula. I'd have thought all of the cells in the range would refer to cells
C7, K7 and would have put the formula in C30 and then filled or copied the
rest.