10 学分
本帖最后由 追梦人亮 于 2013-11-1 15:46 编辑
大家好,希望大家帮助,下面的工作簿中每个表里数据的时间段相同,但有些表中的日期会少几天,会影响计算,怎样用程序实现循环补充缺少的日期,使所有表的日期都和表399300的一样,对于缺少的日期对应的价格用上一天的代替即可,希望大神帮助哈!非常感谢!
以下是我编,老不能运行,求大神看错在哪儿了,谢谢了Sub 补充日期()
Dim i
Dim sh
For Each sh In Sheets '
With sh '
If .Name Like "######" Then
For i = 3 To 400
If ActiveSheet.Cells(i, 1).Value <> Sheets("399300").Cells(i, 1).Value Then
Rows(i).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove 'xlFormatFromLeftOrAbove指定从上方复制单元格的格式
Sheets("399300").Cells(i, 1).Select
Selection.Copy
Cells(i, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Cells(i, 1), Cells(i, 2)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
End With
Rows(i).Select
Cells(i, 2).Value = 0
End If
Next i
End With
Next
End Sub 复制代码
Sub Test()
Dim A(), B(), C()
Dim i&, j&, x&, y&
'确定数组A
Sheets(1).Select
x = Application.Min(Range("a:a"))
y = Application.Max(Range("a:a"))
ReDim A(x To y, 1 To 2)
For i = LBound(A) To UBound(A)
A(i, 1) = i
Next i
C = A
'注:如果其它表的日期并不包含于数组A,下面可能出错
For j = 2 To Sheets.Count
A = C
With Sheets(j)
'1.输入
B = .Range("a1").CurrentRegion.Value
For i = 3 To UBound(B)
A(B(i, 1), 2) = B(i, 2)
Next i
'2.修改
For i = LBound(A) To UBound(A)
If A(i, 2) = "" Then A(i, 2) = A(i - 1, 2)
Next i
'3.输出
.Range("a3:b65536").ClearContents
.Range("a3").Resize(y - x + 1, UBound(A, 2)) = A
End With
Next j
End Sub 复制代码
证券2.rar
(101.54 KB, 下载次数: 12)
我来回答