How to get value sequentlly in Excel by VBA 如何在Excel內用VBA循序地取值

If you want sequentlly get value from cell which has certain patent in Excel. This document will tell you how to do it in Excel by VBA.


Prepare VBA environment 準備VBA環境

To enable the Developer environment in Excel, you can refer to this document "Getting Started with VBA in Excel 2010".

如果要開啟Excel內的「開發人員」模式,請參考《[Office2010]在 Excel 寫 VBA》一文。

Run this VBA script 執行這段VBA

Public Sub GetValue()
Dim val
Dim i
Dim j
j = 1

For i = 8 To 300000 Step 34
val = Cells(i, 5).Value
Cells(j, 11).Value = val
j = j + 1

Next i

j = 0
End Sub

In this code, you could change certain part by real case.

  • "8" is the row number of the first get value. "34" is the row number between the first and second get value. "300000" is the row number of the last get value.
    8 是第一個取值的列數,34 則是第一個取值和第二個取值的間隔,300000 是最後一個取值的列數。
  • "5" is the column number of the first get value."5" is column E. And so on.
    5 是第一個取值的欄位,也就是 E 欄,其餘依此類推。
  • "11" is the column number which you want to store getting value sequently. "11" is column K. And so on.
    11 是你要儲存取值的欄位,也就是 K 欄,其餘依此類推。