循环数组 [英] Looping an Array
问题描述
我的Excel sheet1的A2:A9中具有以下数组公式.
I have the following array formula/s in A2:A9 of my excel sheet1.
= eLink | Bars！'IBM，D，100，DTC'
=eLink|Bars!'IBM,D,100,DTC'
= eLink | Bars！'C，D，100，DTC'
=eLink|Bars!'C,D,100,DTC'
= eLink | Bars！'T，D，100，DTC'
=eLink|Bars!'T,D,100,DTC'
= eLink | Bars！'Q，D，100，DTC'
=eLink|Bars!'Q,D,100,DTC'
= eLink | Bars！'SPY，D，100，DTC'
=eLink|Bars!'SPY,D,100,DTC'
= eLink | Bars！'QQQQ，D，100，DTC'
=eLink|Bars!'QQQQ,D,100,DTC'
= eLink | Bars！'V，D，100，DTC'
=eLink|Bars!'V,D,100,DTC'
= eLink | Bars！'B，D，100，DTC'
=eLink|Bars!'B,D,100,DTC'
每个公式获得相同的数据/字段，但用于不同的股票/股票行情代码.
each formula gets the same data/fields, but for different stocks/tickers.
eLink–是/来自数据源/供应商的DDE链接
eLink – is the DDE link for/from the datasource/vendor
酒吧– 是源数据库的文件名
Bars – is the filename for the source database
IBM– 是股票行情/股票(这是唯一更改–代表特定 股票的字段)
IBM – is the ticker/stock (this is the only field that changes – representing the specific stock)
D– 是间隔/时间段=每天
D – is the interval/period = Daily
100– 是持续时间/范围–持续100天(从最近的 开始返回100天)
100 – is the duration/range – for a 100 days (starting from the most recent and going back a 100 days)
DTC- 是数据字段–日期/日期/时间/C/收盘价
DTC - are the data fields – D/Date, T/Time, C/Closing price
是否可以创建一个宏，以便我只能激活/运行数组公式/函数一次，然后该数组将通过A2:A9运行/循环，并获取/排序数据(通过DDE链接) )分别针对同一工作簿中新表上的每种股票/符号?
Is it possible for me to create a macro so that I can activate/run the array formula/function only once, the array would then run/loop through A2:A9, and fetch/sort the data (via the DDE link) for each stock/symbol separately on a new sheet in the same workbook?
请记住，对于一个数组，必须提前选择单元格范围，在这种情况下，所需区域将是3列(日期，时间，收盘价)和100行(因为我已经选择了一个范围) /持续时间为100天).目的地/位置对我来说并不重要，只要它在相应表上的每个股票/符号都相同(例如可以为D1:F100)
Keeping in mind that, for an array, one has to select the cell range in advance, in this case the required area would be 3 columns (date, time, close price) and 100 rows (since I have selected a range/duration of a 100 days). The destination/location is not important for me, as long as it is the same for each stock/symbol on it’s respective sheet (eg. could be D1:F100)
推荐答案
我的Excel sheet1的A2:A9中具有以下数组公式.
I have the following array formula/s in A2:A9 of my excel sheet1.
= eLink | Bars！'IBM，D，100，DTC'
=eLink|Bars!'IBM,D,100,DTC'
= eLink | Bars！'C，D，100，DTC'
=eLink|Bars!'C,D,100,DTC'
= eLink | Bars！'T，D，100，DTC'
=eLink|Bars!'T,D,100,DTC'
= eLink | Bars！'Q，D，100，DTC'
=eLink|Bars!'Q,D,100,DTC'
= eLink | Bars！'SPY，D，100，DTC'
=eLink|Bars!'SPY,D,100,DTC'
= eLink | Bars！'QQQQ，D，100，DTC'
=eLink|Bars!'QQQQ,D,100,DTC'
= eLink | Bars！'V，D，100，DTC'
=eLink|Bars!'V,D,100,DTC'
= eLink | Bars！'B，D，100，DTC'
=eLink|Bars!'B,D,100,DTC'
每个公式获得相同的数据/字段，但用于不同的股票/股票行情代码.
each formula gets the same data/fields, but for different stocks/tickers.
eLink–是/来自数据源/供应商的DDE链接
eLink – is the DDE link for/from the datasource/vendor
酒吧– 是源数据库的文件名
Bars – is the filename for the source database
IBM– 是股票行情/股票(这是唯一更改–代表特定 股票的字段)
IBM – is the ticker/stock (this is the only field that changes – representing the specific stock)
D– 是间隔/时间段=每天
D – is the interval/period = Daily
100– 是持续时间/范围–持续100天(从最近的 开始返回100天)
100 – is the duration/range – for a 100 days (starting from the most recent and going back a 100 days)
DTC- 是数据字段–日期/日期/时间/C/收盘价
DTC - are the data fields – D/Date, T/Time, C/Closing price
是否可以创建一个宏，以便我只能激活/运行数组公式/函数一次，然后该数组将通过A2:A9运行/循环，并获取/排序数据(通过DDE链接) )分别针对同一工作簿中新表上的每种股票/符号?
Is it possible for me to create a macro so that I can activate/run the array formula/function only once, the array would then run/loop through A2:A9, and fetch/sort the data (via the DDE link) for each stock/symbol separately on a new sheet in the same workbook?
请记住，对于一个数组，必须提前选择单元格范围，在这种情况下，所需区域将是3列(日期，时间，收盘价)和100行(因为我已经选择了一个范围) /持续时间为100天).目的地/位置对我来说并不重要，只要它在相应工作表上的每种股票/符号都相同(例如可以为D1:F100)
Keeping in mind that, for an array, one has to select the cell range in advance, in this case the required area would be 3 columns (date, time, close price) and 100 rows (since I have selected a range/duration of a 100 days). The destination/location is not important for me, as long as it is the same for each stock/symbol on it’s respective sheet (eg. could be D1:F100)
这是找到VBA和Office论坛的链接，这将是您提出问题的正确位置........
对于VBA，Office(VSTO) )，宏和VBScript问题
:)
Here's a link to find the VBA and Office forums which would be the correct place for your question........
For VBA, Office (VSTO), Macros and VBScript Questions
:)
这篇关于循环数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
