excel - 连接特定列的字符串 (VBA)

Question

目标:将字符串“Z”添加到除标题之外的所有行的选定几列。仅在选择的 header 上连接，即在数组中定义的 header 。

Dim header As Range
ArrayCheck = Array("CarTime", "BusTime", "PlaneTime")
LastRow = desWS1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lcol = desWS1.Cells(1, Columns.Count).End(xlToLeft).Column

For Each header In desWS1.Range(desWS1.Cells(1, 1), desWS1.Cells(1, lcol))
    For i = LBound(ArrayCheck) To UBound(ArrayCheck)
        If header = ArrayCheck(i) Then
        desWS1.Range(desWS1.Cells(2, header.Column), desWS1.Cells(LastRow, header.Column)) & "Z"
        End If
    Next i
    Next

这些列中的所有条目的格式为:yyyy-mm-ddThh:mm:ss

Answer 1

@SiddharthRout the current cell is: 2020-09-07T13:08:46, and the output i want is: 2020-09-07T13:08:46Z. So yep, you're right, it's a string. – Jak Carty 2 mins ago

在我下面的代码中，我将采样存储为文本的日期和日期。我已经对代码进行了注释，因此您理解它应该没有问题。但是，如果您这样做，则只需回帖即可。
这是你正在尝试的吗？
代码:
方式 1

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, lCol As Long
    Dim ArrayCheck As Variant
    Dim i As Long, j As Long
    Dim rng As Range
    
    ArrayCheck = Array("CarTime", "BusTime", "PlaneTime")
    
    '~~> Set this to the relevant worksheet
    Set ws = Sheet1
    
    With ws
        '~~> Find last row
        lRow = .Cells.Find(What:="*", _
               After:=.Range("A1"), _
               Lookat:=xlPart, _
               LookIn:=xlFormulas, _
               SearchOrder:=xlByRows, _
               SearchDirection:=xlPrevious, _
               MatchCase:=False).Row
        
        '~~> Find last col
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        '~~> Loop though the cell in 1st row
        For i = 1 To lCol
            '~~> Loop through the array
            For j = LBound(ArrayCheck) To UBound(ArrayCheck)
                '~~> Check if they match
                If .Cells(1, i).Value2 = ArrayCheck(j) Then
                    '~~> Set your range from cell 2 onwards
                    Set rng = .Range(.Cells(2, i), .Cells(lRow, i))
                    
                    '~~> Add "Z" to the entire range in ONE GO i.e without looping
                    '~~> To understand this visit the url below
                    'https://stackoverflow.com/questions/19985895/convert-an-entire-range-to-uppercase-without-looping-through-all-the-cells
                    rng.Value = Evaluate("index(Concatenate(" & rng.Address & ",""Z""" & "),)")
                End If
            Next j
        Next i
    End With
End Sub

备注 : 为了清楚起见，我没有加入字符串 ",""Z""" & "),)") 在行动

方式 2
介绍第二种方式
此代码写入数组，然后使用它。

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, lCol As Long
    Dim ArrayCheck As Variant
    Dim i As Long, j As Long, k As Long
    Dim rng As Range
    Dim tmpAr As Variant
    
    ArrayCheck = Array("CarTime", "BusTime", "PlaneTime")
    
    '~~> Set this to the relevant worksheet
    Set ws = Sheet1
    
    With ws
        '~~> Find last row
        lRow = .Cells.Find(What:="*", _
               After:=.Range("A1"), _
               Lookat:=xlPart, _
               LookIn:=xlFormulas, _
               SearchOrder:=xlByRows, _
               SearchDirection:=xlPrevious, _
               MatchCase:=False).Row
        
        '~~> Find last col
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
        '~~> Loop though the cell in 1st row
        For i = 1 To lCol
            '~~> Loop through the array
            For j = LBound(ArrayCheck) To UBound(ArrayCheck)
                '~~> Check if they match
                If .Cells(1, i).Value2 = ArrayCheck(j) Then
                    '~> Set your range
                    Set rng = .Range(.Cells(2, i), .Cells(lRow, i))
                    
                    '~~> Store the value in array
                    tmpAr = rng.Value2
                    
                    '~~> Work with array
                    For k = 1 To UBound(tmpAr)
                        tmpAr(k, 1) = tmpAr(k, 1) & "Z"
                    Next k
                    
                    '~~> write the array back to worksheet
                    rng.Resize(UBound(tmpAr), 1).Value = tmpAr
                End If
            Next j
        Next i
    End With
End Sub

在行动