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

Answer 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.

Answer 2

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

Answer 3

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.