[已解决]各位大神关于VBA的问题

北漂的猪

1.我想在D列输入下列公式=TEXT(B1,"MM/DD/YYYY")&" "&TEXT(C1,"HH:MM:SS")，然后自动填充到D列，怎么弄
2.完成第一步后，删除A和B列。怎么弄，貌似还得消掉D列的公式只保留数值。

最佳答案

月排行榜 / 总排行榜

suye1010

2012-12-1 11:01

1. Sub Transform()
   
2. Dim arr0, arr, i As Integer
   
3. arr0 = Range("A1").CurrentRegion
   
4. ReDim arr(1 To UBound(arr0), 1 To 1)
   
5. For i = 1 To UBound(arr0)
   
6.     arr(i, 1) = Format(arr0(i, 2), " MM/DD/YYYY ") & Format(arr0(i, 3), "HH:MM:SS")
   
7. Next i
   
8. Columns("A:C").Clear
   
9. Range("D1").Resize(UBound(arr), 1).NumberFormatLocal = "@"
   
10. Range("D1").Resize(UBound(arr), 1) = arr
    
11. End Sub

复制代码

跳转到最佳答案楼层

孤单无所谓

Sub 填充公式()
Dim a, b
For a = 1 To Application.CountA([a:a])
Cells(a, "d") = Evaluate("=TEXT(" & Cells(a, "b") & ",""MM/DD/YYYY"")&"" ""&TEXT(" & Cells(a, "b") & ",""HH:MM:SS"")")
Next
End Sub

ppp710715

学习了，不错，谢谢！

ppp710715

Sub 删除AB列()
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
End Sub

孤单无所谓

ppp710715 发表于 2012-12-1 08:55
Sub 删除AB列()
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft

Sub 删除()
[a:b].Clear
End Sub

suye1010

1. Sub Transform()
   
2. Dim arr0, arr, i As Integer
   
3. arr0 = Range("A1").CurrentRegion
   
4. ReDim arr(1 To UBound(arr0), 1 To 1)
   
5. For i = 1 To UBound(arr0)
   
6.     arr(i, 1) = Format(arr0(i, 2), " MM/DD/YYYY ") & Format(arr0(i, 3), "HH:MM:SS")
   
7. Next i
   
8. Columns("A:C").Clear
   
9. Range("D1").Resize(UBound(arr), 1).NumberFormatLocal = "@"
   
10. Range("D1").Resize(UBound(arr), 1) = arr
    
11. End Sub

复制代码

北漂的猪

Sub 测试()
Dim i As Integer
For i = 1 To Range("a65536").End(xlUp).Row
Range("D" & i).Formula = "=TEXT(" & Range("B" & i) & ",""MM/DD/YYYY"")&"" ""&TEXT(" & Range("C" & i) & ",""HH:MM:SS"")"
Next
End Sub
我这个哪错了。 好像是公式里的Range错了吧

北漂的猪

Sub j()
Range("A:A").Replace What:="AM", Replacement:=" AM"
End Sub

我用上面的公式替换  12/8/2012  12:15:45AM， 替换完成后AM 消失了。。。