连接3个字符串，结果随机变化

Question

VBA编码新手，也是我第一次问一些问题，所以如果你看到一些伪代码，请放松一下。

我有一个Excel表格，在A栏中的日期格式为mm/dd/年。我想把它改为dd/mm/year，为了在VBA中进行一些练习，我尝试通过代码来完成(我尝试过"设置单元格格式"选项，但没有成功(。首先，我尝试使用以下代码用所需格式重写H列中的Date列：

Public Sub ChangingDateFormat()
Dim dest As Range
Dim dindx As Integer
Dim cll As Range
Dim x As String
Dim y As String
Dim z As String

'Establish where output goes
Set dest = Range("H2")

'Set counter for output rows
dindx = 0

'Establish your North Star reference
Set cll = Range("A2")

'Loop as long as anchor cell down isn't blanc
While cll.Offset(dindx, 0) <> ""

'Test if not empty; if not empty populate destination with data
If cll.Offset(dindx, 0) <> "" Then

x = Left(cll.Offset(dindx, 0), 2)
y = Mid(cll.Offset(dindx, 0), 4, 3)
z = Right(cll.Offset(dindx, 0), 5)

'True case: Output date
dest.Offset(dindx, 0) = y & x & z

'Increment row counter
dindx = dindx + 1

End If

'End While statement
Wend

End Sub 

但是，当宏运行时，某些单元格具有所需的格式，而其他单元格则没有。

Excel工作表如下所示：

我的工作表

你知道问题出在哪里吗？如果能给我指个方向，我将不胜感激。

感谢

Answer 1

尝试使用类似的Format函数(这只是示例(，并尝试在代码中实现

Sub Test()
Dim r As Range
Set r = Range("A1")
r.Offset(, 3).Value = Format(r.Value, "dd/mm/yyyy")
End Sub

Answer 2

Excel表格(ListObject(

• 以下仅涵盖部分我有一个Excel表，该表在A列中的日期格式为mm/dd/年。我想把它改成dd/mm/年即A列中的值是实际日期，而不是文本
• 适当调整图纸和表格名称
• 由于您正在处理ListObject(表(，您可能会考虑使用它的属性
• 以下过程显示了如何在两种情况下利用其中的一些
• 第一个过程将更改表第一列中的数字格式(不一定是工作表的第一列(。当列总是第一列时使用它，因此可以更改标题
• 第二个过程将尝试查找给定的标头(Header(。如果成功，它将对找到标题的列应用数字格式。当表头(标题(永远不会改变，但列在表中的位置可能会改变时，就会使用它

代码

Sub IOnlyKnowTheColumnNumber()

' The n-th column of the Table, not the worksheet.
Const DateColumn As Long = 1

Dim wb As Workbook
Set wb = ThisWorkbook ' The workbook containing this code.

Dim ws As Worksheet
Set ws = wb.Worksheets("Sheet1")

Dim tbl As ListObject
Set tbl = ws.ListObjects("Table1")

' Define Date Column Range ('rng').
Dim rng As Range
Set rng = tbl.DataBodyRange.Columns(DateColumn)
' Apply formatting to Date Column Range.
rng.NumberFormat = "dd/mm/yyyy" ' "dd/mm/yyyy" for international.
' Inform user.
MsgBox "Number format applied.", vbInformation, "Success"
End Sub
Sub IOnlyKnowTheColumnTitle()

Const Header As String = "Date"
Dim wb As Workbook
Set wb = ThisWorkbook ' The workbook containing this code.

Dim ws As Worksheet
Set ws = wb.Worksheets("Sheet1")

Dim tbl As ListObject
Set tbl = ws.ListObjects("Table1")

' Define Header Row Range (Headers, Titles).
Dim hRng As Range
Set hRng = tbl.HeaderRowRange

' Try to calculate the Date Column Number.
Dim Temp As Variant
Temp = Application.Match(Header, hRng, 0)

If Not IsError(DateColumn) Then
' Define Date Column.
Dim DateColumn As Long
DateColumn = CLng(Temp)
' Define Date Column Range ('rng').
Dim rng As Range
Set rng = tbl.DataBodyRange.Columns(DateColumn)
' Apply formatting to Data Column Range.
rng.NumberFormat = "dd/mm/yyyy" ' "dd/mm/yyyy" for international.
' Inform user.
MsgBox "Number format applied.", vbInformation, "Success"
Else
' inform user.
MsgBox "Could not find '" & Header & "' column.", vbExclamation, "Fail"
End If
End Sub