Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through cells in a range and pick up corresponding cell values in another range
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through cells in a range and pick up corresponding cell values in another range
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 -- Regards, Tom Ogilvy wrote in message oups.com... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through cells in a range and pick up corresponding cell value
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 " wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through cells in a range and pick up corresponding cell value
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through cells in a range and pick up corresponding cell value
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through cells in a range and pick up corresponding cell v
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. " wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through cells in a range and pick up corresponding cell v
Although if your computations can be done as an excel formula, Tom's
suggestion cuts to the chase. " wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through cells in a range and pick up corresponding cell v
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through cells in a range and pick up corresponding cell v
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. -- Regards, Tom Ogilvy "trice-nae" wrote in message oups.com... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through cells in a range and pick up corresponding cell v
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. "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "trice-nae" wrote in message oups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import values from a cell or a range of cells | Excel Worksheet Functions | |||
How do i pick up a list of invoice numbers on a range of cells | Excel Discussion (Misc queries) | |||
how to pick from a range of table values | Excel Worksheet Functions | |||
How do I Pick a cell with highest value from a range of cells? | Excel Worksheet Functions | |||
using cell values to select range of cells | Excel Programming |