Excel VBA: How to create loop and save output for each value in range?

Question

I'm trying to create a loop in excel, but I'm stuck.

The purpose of my loop is to - Go through a range of values, e.g A1:A8760 and insert it in cell e.g B10 - For each range of values, I want to save the output and copy it in a new column, e.g C.

I tried to record a macro and create a loop from this. But it just went wrong,

gg = 1

   Dim myRange As Range
   Dim i As Long, j As Long

   Set myRange = Range("AJ4:AJ8763")
   For i = 1 To myRange.Rows.Count
      For j = 1 To myRange.Columns.Count
        myRange.Cells(i, j).Select
    Selection.Copy
    Range("D10").Select
    ActiveSheet.Paste
    Range("O7").Select
    Application.CutCopyMode = False
    Selection.Copy
  myRange.Cells(i, j + gg).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Next j
   Next i
End Sub

The code sample above is what I have added so far. So the idea is I go through a range of value from AJ4 to AJ8763 and insert it in cell D10, Next step is to copy the output from cell O7 and insert it to cell AK4 to AK8763.

Added corrected version

Please add what you have tried so far, and what is the problem with it. — ArnonZ, Apr 16 '15 at 08:26
Dear Arnon, I just added my code sample in the orignal post. — SKkumar, Apr 16 '15 at 08:41
What is the formula that recomputes the new values in column O? This might be better done completely in memory. — , Apr 16 '15 at 08:50
@Jeeped is right. Or better if you abandon the loop and then just right the formula in one go the convert the formula as values. — L42, Apr 16 '15 at 08:52
@Jeeped Exactly - I mean AK4 to AK8763. The problem is the the formula in O4 is very complex and is dependent on several other parameters. To be more specific, the formula calculates Net Present Value (NPV) for each hour in a year. — SKkumar, Apr 16 '15 at 08:54

Avishay Cohen · Answer 1 · 2015-04-16T09:02:02.167

1

Sorry - edited now, i ran it on my machine should

myRange.Cells(i, j).Select

i think this should do it, i haven't actually checked if it work on a logic level, just making sure it compiles. you have 2 lines like that.

if i'm not mistaken - i'm still new to helping others, the string in

Range("myRange.Cells(i, j)").Select

is problematic - you have to add the variables with '&' in the code like this:

Range("myRange.Cells(" & i & ", " & j & ")").Select

hope that helps.

edited Apr 16 '15 at 09:02

answered Apr 16 '15 at 08:55

Avishay Cohen

1,978
2
21
34

You are right that the line is problematic but what you have written here will also not work. myrange is an object. the VBA will interpret what tyou have written as a string and not make any sense of it. myRange.Cells(i,j).Select should work better but I think there are more fundamental issues being explored here in the comments. – Dannyg9090 Apr 16 '15 at 08:57
Hello guys, I managed to get it work by using "myRange.Cells(i,j).Select" in stead. Thx for your quick responses! And I naturally had to add myRange.Cells(i+1,j+1).Select for the output. – SKkumar Apr 16 '15 at 09:03
yes, i noticed it while you were commenting :) thanks – Avishay Cohen Apr 16 '15 at 09:07

score 0 · Answer 2 · answered Apr 16 '15 at 09:04

You are making thing unnecessary complicated with myRange, try this:

Sub S()
   Dim i As Long, j As Long

   For i = 1 To 8763
      For j = 36 To 36'AJ=36
        Cells(i, j).Select
        Selection.Copy
        Range("D10").Select
        ActiveSheet.Paste
        Range("O7").Select
        Application.CutCopyMode = False
        Selection.Copy
        Cells(i, j).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
      Next j
   Next i
End Sub

I could not make your VBA sample code to work? It gives an error regarding 'Flash Fill'. Also, the output should be pasted in the row next to it, so I assumes it should be Cells(i+1,j), right? — SKkumar, Apr 16 '15 at 09:20

score 0 · Accepted Answer · answered Apr 16 '15 at 09:17

As I understand it, there is a formula in O10 that has one or more precedents that include D10. This means that when a new value goes into D10, a new result is in O10 and you want to save that result into a new column.

The fastest way to do this is to collect all of the values in AJ4:AJ8763 into a variant array. Loop through the variant array within VBA and recompute the value using math and functions just like the formula in O10.

When it is done, simply drop the new values in the variant array back into AK4:AK8763.

Dim rws As Long, cls As Long, v As Long, vAJs As Variant

rws = 8760
cls = 1
With ActiveSheet
    vAJs = .Range("AJ4").Resize(rws, cls).Value2
    For v = LBound(vAJs) To UBound(vAJs)
        'something with Application.NPV on next line
        vAJs(v, 1) = vAJs(v, 1) * 2  'simple computation. If you need help with this line, show us the formula being used in O10.
    Next v
    .Range("AK4").Resize(rws, cls) = vAJs
End With

I've used a very simple computation as a placeholder to where the actual work should happen. Running your code this way is blinding fast compared to looping through each cell, copying it to a new location then picking up the result of a formula and pasting it into a new column.

Thanks for the suggestion. My problem is that the used NPV function is from another sheet. The function is: `NPV($D$9;I82:AG82)+H82.`. — SKkumar, Apr 16 '15 at 09:28
@SKkumar - On the first leg of the loop the value from AJ4 gets copied into D10 and the new calculated result in O7 gets copied into AK4 (that's from your original question). I'm sorry but I have no idea where $D$9, I82:AG82 or H82 come from. I suspect that 'Another sheet'!$D$9 is where the [NPV function](https://support.office.com/en-us/article/NPV-function-5c52df05-07cb-48e0-a006-97225eb960bc)'s *rate* parameter comes from but I don't see how that changes O7 when you put a new value into D10. — , Apr 16 '15 at 09:43
I know, it's a bit more complicated that just that. D10 refers to amount of hours an energy system is operating - changing the cash flow for each year. This mean, the NPV results changes dependent on the changed cash flows, which are dependent on D10. I hope this was a bit better clarified. $D9$ remains constant as you say, but I82:AG82 (Cash flow) changes with AK4:AK8763 — SKkumar, Apr 16 '15 at 09:50

Excel VBA: How to create loop and save output for each value in range?

3 Answers3

Linked