VBA没有循环我的for循环，日期/时间的格式没有改变

Question

我想将excel格式从逗号更改为小数点，但是，当我试图运行vba代码时，它从"For j=3到LastCol"结束潜艇，为什么会这样?此外，它没有改变我的范围A1到1/12/2020 9:00:00 am的格式。

Sub checkformat()
Dim OriginalText As String
Dim CorrectedText As String
Dim i As Integer, j As Integer
Dim LastCol As Long, LastRow As Long
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Worksheets("RawData").Range(Cells(3, 1), Cells(LastRow, 1)).Select
Range(Cells(3, 1), Cells(LastRow, 1)).NumberFormat = "dd/mm/yyyy hh:mm:ss am/pm"

For j = 3 To LastCol
For i = 3 To LastRow
OriginalText = Worksheets("RawData").Cells(i, j).Value
CorrectedText = Replace(OriginalText, ",", ".")
Worksheets("RawData").Cells(i, j).Value = CorrectedText
Next i
Next j
End sub

我的原文总是出现在每一列的第三行

Answer 1

试试这个:

编辑:我已经更新了LastCol行，以查看第2行。你让它在第一行查找如果在第一行的列中没有任何东西它就会返回1。您需要查找相应行中的最后一列。

Sub checkformat()
Application.ScreenUpdating = False
Dim OriginalText As String, CorrectedText As String
Dim i As Long, j As Long
Dim LastCol As Long, LastRow As Long
Dim ws As Worksheet
Set ws = Worksheets("RawData")
LastCol = ws.Cells(2, Columns.Count).End(xlToLeft).Column 'This should be a 2 if this is where your headers are. The 2 is the row number your looking
LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

ws.Range(ws.Cells(3, 1), ws.Cells(LastRow, 1)).NumberFormat = "dd/mm/yyyy hh:mm:ss am/pm"

For j = 3 To LastCol
For i = 3 To LastRow
OriginalText = ws.Cells(i, j).Value
CorrectedText = Replace(OriginalText, ",", ".")
ws.Cells(i, j).Value = CorrectedText
Next i
Next j

Application.ScreenUpdating = True
End Sub

Answer 2

你的问题是在添加值变量，单元格是默认属性的活动表，如果你没有写其他

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

然后再更改sheet

Worksheets("RawData").Range(Cells(3, 1), Cells(LastRow, 1)).Select

如果列和行计数器应该在工作表RawData中，那么将变量更改为

LastCol = Worksheets("RawData").Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Worksheets("RawData").Cells(Rows.Count, 1).End(xlUp).Row

，然后应该工作良好

您还可以假设一些值来检查列和行计数器的最大值，如下所示

Sub RowsColumnsCounter()
Dim i_lr As Integer, i_tempLr As Integer, i_lc As Integer, i_tempLc As Integer, i_assumption As Integer
i_lc = 0
i_lr = 0
i_assumption = 20
For i = 1 To i_assumption
For j = 1 To i_assumption
i_tempLc = ActiveSheet.Cells(i, Columns.Count).End(xlToLeft).Column
i_tempLr = ActiveSheet.Cells(Rows.Count, j).End(xlUp).Row
If i_tempLc > i_lc Then i_lc = i_tempLc:   i_tempLc = 0
If i_tempLr > i_lr Then i_lr = i_tempLr: i_tempLr = 0
Next j
Next i
MsgBox "last row is: " & i_lr & vbCrLf & "last column is: " & i_lc
End Sub