|
本帖最后由 lobai0124 于 2018-1-23 10:09 编辑
VBA 是以下
Sheets("DAILY").Select
y = Range("A20").End(xlUp).Row - 2
With Sheets("RECORD")
x = .Range("a65536").End(xlUp).Row + 1
For i = 0 To y
.Cells(x + i, 1) = "=now()"
.Cells(x + i, 2) = Cells(i + 15, 1)
.Cells(x + i, 3) = Cells(i + 15, 2)
.Cells(x + i, 4) = Cells(i + 15, 3)
.Cells(x + i, 5) = Cells(i + 15, 4)
请问如果A列是算式,应该不能用.有辨法变成可用吗??
如不能
以下是之前用的,能否简化? MSGBOX可不要
Sub FILL_RECORD()
Dim Row As Byte
Dim Lb()
Dim Ls(5)
With Sheets("DAILY")
Ls(0) = .Range("k2")
Ls(1) = .Range("l2")
Ls(2) = .Range("t2")
For Row = 1 To 5
If .Cells(1 + Row, 13) <> "" Then
Ls(3) = Ls(3) + 1
ReDim Preserve Lb(1 To 13, 1 To Ls(3))
Lb(1, Ls(3)) = Ls(0)
Lb(2, Ls(3)) = Format(Ls(1), "YYYYMMDD")
Lb(3, Ls(3)) = .Cells(1 + Row, 13)
Lb(4, Ls(3)) = .Cells(1 + Row, 14)
Lb(5, Ls(3)) = .Cells(1 + Row, 15)
Lb(6, Ls(3)) = .Cells(1 + Row, 16)
Lb(7, Ls(3)) = .Cells(1 + Row, 17)
Lb(8, Ls(3)) = .Cells(1 + Row, 18)
Lb(9, Ls(3)) = .Cells(1 + Row, 19)
Lb(10, Ls(3)) = .Cells(1 + Row, 20)
Lb(11, Ls(3)) = .Cells(1 + Row, 21)
Lb(12, Ls(3)) = .Cells(1 + Row, 22)
Lb(13, Ls(3)) = .Cells(1 + Row, 23)
End If
Next
End With
With Sheets("RECORD")
If Len(Ls(3)) > 0 Then
If IsError(Application.Match(Ls(0), .Range("B:B"), 0)) Then
Ls(4) = Application.CountA(.Range("A:A")) + 1
.Cells(Ls(4), 1).Resize(UBound(Lb, 2), UBound(Lb)) = Application.Transpose(Lb)
Else
MsgBox "ERROR", 16
End If
Else
MsgBox "ERROR", 16
End If
End With
Sheets("RECORD").Select
End Sub
|
|