2012/06/26

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.

如果你想要在Excel內循序取出具有某種欄位規則的值,這份文件會告訴你如何透過Excel的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 欄,其餘依此類推。

沒有留言:

張貼留言