VBA代码流循环遍历数据范围

Question

我是VBA的新手，需要帮助。我构建了一个传统的输入输出excel模型(传统excel中的电子表格计算，而不是VBA。)我有一系列大约200,000个数据点，我需要为行中的8个单元格提取一个值(将表1个单元格A1替换为value Sheet2 value单元格B1)，提取结果，然后遍历剩余的200,000行。有没有人可以指导我这样做？

我用下面的代码运行了一个宏，以反映第一行的替换。我现在需要它来遍历其余的列：

Sub PermutationResults2()
'
' PermutationResults2 Macro
'

'
Sheets("Investments").Select
Range("I9").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[20]C[-8]"
Range("I10").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[19]C[-8]"
Range("I11").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[18]C[-8]"
Range("I12").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[17]C[-8]"
Range("I13").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[16]C[-8]"
Range("I14").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[15]C[-8]"
Range("I15").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[14]C[-8]"
Range("I16").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[13]C[-8]"
Range("I17").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[12]C[-7]"
Range("J9").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[20]C[-7]"
Range("J10").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[19]C[-7]"
Range("J11").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[18]C[-7]"
Range("J12").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[17]C[-6]"
Range("J13").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[16]C[-6]"
Range("J14").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[15]C[-6]"
Range("J15").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[14]C[-6]"
Range("J16").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[13]C[-6]"
Range("J17").Select
ActiveCell.FormulaR1C1 = "=' Monte Carlo Data'!R[12]C[-5]"
Range("J18").Select
End Sub

​

​

Answer 1

让我们开始吧

   Sub Demo
     Dim r as range
     dim t  as range
     Set r = Sheets("Investments").Range("I9")  'point to your start cell
     set t = sheets(" Monte Carlo Data").range("I9")  'pointer to your destination
      Do Until r = ""  'keep going till you find a blank cell
          t = r  'copy value in r into t
          set r = r.offset(1,0) 'move pointer down one
          set t = t.offset(1,0)  'ditto for the other pointer
      Loop
 end sub

现在玩它，直到它做你想做的事情。