英语不是我的母语,所以我希望你能理解我
BeaCuse我对编程一无所知,我向您寻求帮助:Excel Pros;(
我有一个带有两张纸的工作簿(数据馈送和记录(
在" datafeed",B列,单元B2至B400上,我正在从Internet捕获实时价格。
目前我使用以下内容:
Sub my_onTime()
Application.OnTime Now + TimeValue("00:00:01"), "my_Procedure"
End Sub
Sub my_Procedure()
With Sheets("record")
rw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Range(.Cells(rw, 1), .Cells(rw, 2)).Value = Sheets("datafeed").Range("a2:b2").Value
End With
ThisWorkbook.RefreshAll
my_onTime
End Sub
目前从" datafeed"到"记录"中的行中的价格。
另一个问题是仅记录一个来自" datafeed"的单元格;细胞B2。我不知道如何设置它,因此它将记录来自单元格b2:b400
的所有价格我的愿望是,Excel会将"记录"表的价格更改为"列"而不是行。
" Graphic"显示我想要的内容:
Sheet1-" datafeed" ... b2 = 155 ................................................................................."记录"单元格:f2 = 155 ..... g2 = 150 .....H2-145 ..... i2-140,....等等
Sheet1-" datafeed" ... B3 = 66 ......................................................................................................................."记录"单元格:f3 = 66 ..... G3 = 67 .... 67 ....H3-66 ..... i3-65,....等等
Sheet1-" datafeed" ... b4 = 1015 ................................................................................................................."记录"单元格:f4 = 1015 ..... g4 = 1025 ....H4-1035 .... i4-1045,....等等
也必须将最后一个记录的价格放入第一列表2-"记录"中;单元格:,F2,F3,F4 .......(现在最后一个记录的值放在最后一行中(
任何帮助将不胜感激。
谢谢!
P.S
我添加了3张图片以进行澄清
1。Sheet DataFeed
2。纸记录
3。我希望表记录
替换过程的这一部分
With Sheets("record")
rw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Range(.Cells(rw, 1), .Cells(rw, 2)).Value = Sheets("datafeed").Range("a2:b2").Value
End With
使用此
With Sheets("record")
rw = .Cells(.Rows.Count, 5).End(xlUp).Row + 1
.Cells(rw, 5).Value = Sheets("datafeed").Cells(2, 1).Value 'write name from column A
iColumn = 6 'set you want to start in column F with prices
For iRow = Sheets("datafeed").Cells(Sheets("datafeed").Rows.Count, 1).End(xlUp).Row To 2 Step -1
.Cells(rw, iColumn).Value = Sheets("datafeed").Cells(iRow, 2).Value
iColumn = iColumn + 1
Next iRow
End With
如果您希望将新行添加到第一行而不是最后一行
With Sheets("record")
iColumn = 6 'set you want to start in column F with prices
iRecordRow = 3 'set to what row number to put the current price
.Rows(iRecordRow & ":" & iRecordRow).Insert Shift:=xlDown
For iRow = Sheets("datafeed").Cells(Sheets("datafeed").Rows.Count, 1).End(xlUp).Row To 2 Step -1
.Cells(iRecordRow, iColumn).Value = Sheets("datafeed").Cells(iRow, 2).Value
iColumn = iColumn + 1
Next iRow
.Cells(iRecordRow, 5).Value = Sheets("datafeed").Cells(2, 1).Value 'write name from column A
End With
如果您想将其添加到不同的行中,而不是3只需更改iRecordRow = 3