使用使用VBA
特定单元格的值插入整行我有这种格式的问题使用使用VBA
from hrs to hrs quantum rate
12:00:00 6:00:00 100 1.8
12:00:00 5:00:00 125 1.6
我想这个数据在给定的格式一样
from hrs to hrs quantum rate
12:00:00 6:00:00 -50 1800.00
12:00:00 6:00:00 -50 1800.00
12:00:00 5:00:00 -50 1600.00
12:00:00 5:00:00 -50 1600.00
12:00:00 5:00:00 -25 1600.00
,我使用下面的代码:
Option Explicit
Sub main()
Dim data As Variant
Dim iData As Long, datum As Long, iRow As Long
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
data = .Resize(, 4).Value
iData = LBound(data)
Do
datum = data(iData, UBound(data, 2) - 1)
Do While datum > 0
iRow = iRow + 1
.Cells(iRow).Resize(, 4) = Application.Index(data, iData, 0)
.Cells(iRow, UBound(data, 2) - 1).Value = WorksheetFunction.Min(50, datum)
datum = datum - 50
Loop
iData = iData + 1
Loop While iData <= UBound(data)
.Resize(1).Copy
.Resize(iRow).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End Sub
那就是:
Option Explicit
Sub main()
Dim data As Variant
Dim iData As Long, datum As Long, iRow As Long
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
data = .Resize(, 4).Value
iData = LBound(data)
Do
datum = data(iData, UBound(data, 2) - 1)
Do While datum > 0
iRow = iRow + 1
.Cells(iRow).Resize(, 4) = Application.Index(data, iData, 0)
.Cells(iRow).Offset(, 3).Value = .Cells(iRow).Offset(, 3).Value * 1000
.Cells(iRow, UBound(data, 2) - 1).Value = -WorksheetFunction.Min(50, datum)
datum = datum - 50
Loop
iData = iData + 1
Loop While iData <= UBound(data)
.Resize(1, 4).Copy
.Resize(iRow, 4).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End Sub
@satyendrasharma,尝试此代码后有看看[这里](http://*.com/help/someone-answers) – user3598756
先生,当我运行宏它显示类型不匹配错误,因为我使用列标题也请帮助 –
不准确,但你可以尝试改变'Range(“A1”,Cells(...')'Range(“A2”,Cells(...')让我知道 – user3598756
@ user3598756 recoginze this code? –
@ShaiRado,是的,我这样做:-) – user3598756
你还没有问过一个具体的问题。 – Squashman