如何为 For 循环中的每个结果分配一个新变量？

如果你知道不超过 5 个，那么简单的答案就是使用一个数组，如下所示：

Dim xDats(5) As String
Dim d = 1
For i = 186 To 235
    If Not wsElog2.Range("H" & i) Is Nothing Then
        'get result from column C, and assign to a variable (xDat + the d value).
        xDats(d) = wsElog2.Range("C" & i).Text
        'the above is showing red error - not sure how to put xDat and d together
        d += 1
    End If
Next i

但是，最好使用 a，因为这会为您提供更干净的代码，并且可以在超过 5 个的情况下消除错误。List(Of String)

Dim xDats As New List(Of String)
For i = 186 To 235
    If Not wsElog2.Range("H" & i) Is Nothing Then
        'get result from column C, and assign to a variable (xDat + the d value).
        xDats.Add(wsElog2.Range("C" & i).Text)
        'the above is showing red error - not sure how to put xDat and d together
    End If
Next i

你的主要问题是线

   If Not wsElog2.Range("H" & i) Is Nothing Then

它不会检查相应的单元格是否为空... 应该仅用于对象，并且您尝试检查对象的值（范围），该值将始终不是 Nothing。Is Nothing

第二个问题是你不能通过串联来构建变量名......

所以，请测试下一个改编的代码。它将使用能够保留您需要的任意数量的变量：Scripting.Dictionar

Sub testUseVariables()
   Dim wsElog2 As Worksheet, i As Long, dict As Object

    Set wsElog2 = ActiveSheet 'please, use the necessary sheet

    Set dict = CreateObject("Scripting.Dictionary")
    For i = 186 To 235
        If wsElog2.Range("H" & i).Value <> "" Then
            dict("H" & i) = wsElog2.Range("C" & i).text 'I used Text only because you wonted that.
                                                        'but, if the cell/column is not AutoFit, it will return only what you can see!
        End If
    Next i
    'you can test the result in the next way:
    If dict.count > 0 Then
        Debug.Print Join(dict.keys, "|")     'just to see where from the it comes (in H:H)
        Debug.Print Join(dict.Items, "|")    'the returned result
        Debug.Print dict.keys()(0), dict.keys()(1) 'a way of returning the item of a specific key number
    End If
End Sub

下一个版本能够模拟构建变量的方式，但现在，作为一个字符串，它可以工作：

Sub testUseVars()
   Dim wsElog2 As Worksheet, d As Long, i As Long, dict As Object
   Const xD As String = "xDat"
   
    Set wsElog2 = ActiveSheet 'please, use the necessary sheet
    
    Set dict = CreateObject("Scripting.Dictionary")
    For i = 186 To 235
        If wsElog2.Range("H" & i).Value <> "" Then
            d = d + 1
            dict(xD & d) = wsElog2.Range("C" & i).text 'I would suggest using Value instead of Text...
        End If
    Next i

    'you can test the result in the next way:
    If dict.count > 0 Then
        Debug.Print Join(dict.keys, "|")     'just to see where from the it comes (in H:H)
        Debug.Print Join(dict.Items, "|")    'the returned result
        Debug.Print dict("xDat1"), dict("xDat2") 'a way of returning the item for a specific key
    End If
End Sub

使用数组而不是许多编号变量

快速修复（对于...下一个）

Sub Test()
    
    Dim rCount As Long: rCount = 235 - 186 + 1
    
    Dim dArr() As String: ReDim dArr(1 To rCount)
    
    Dim i As Long, dCount As Long
    
    For i = 186 To 235
        If Len(CStr(wsELog2.Range("H" & i).Value)) > 0 Then
            dCount = dCount + 1
            'get result from column C, and assign to a variable (xDat + the dCount value).
            dArr(dCount) = CStr(wsELog2.Range("C" & i).Value)
            'the above is showing red error - not sure how to put xDat and dCount together
        End If
    Next i

    ' Check if all lookup cells were blank.
    If dCount = 0 Then
        MsgBox "All lookup cells were blank.", vbCritical
        Exit Sub
    End If
    
    ' Check if any blanks were found.
    If dCount < rCount Then ' not all lookup cells were not blank
        ReDim Preserve dArr(1 To dCount) ' resize the array
    'Else ' all lookup cells were not blank; do nothing
    End If
    
    ' Continue using the array...
    
    ' Return the comma-separated results in the Immediate window (Ctrl+G).
    Debug.Print Join(dArr, ", ")
    
    Dim d As Long
    
    ' Loop through the results and return one per row
    ' in the Immediate window.
    For d = 1 To dCount
        Debug.Print dArr(d)
    Next d

End Sub

更详细地说：一个完整的例子（对于每个...下一个）

Sub MatchesToArray()

    ' Reference the workbook and the worksheet.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("ELog2") ' adjust!
    
    ' Reference the (same-sized) single-column ranges.
    Dim lrg As Range: Set lrg = ws.Range("H186:H235") ' Lookup Range
    Dim rrg As Range: Set rrg = lrg.EntireRow.Columns("C") ' Return Range
    
    ' Write the number of rows to a variable.
    Dim rCount As Long: rCount = rrg.Rows.Count ' or lrg.Rows.Count
    
    ' Define a 1D one-based array of the same size as there are rows.
    Dim dArr() As Variant: ReDim dArr(1 To rCount)
    
    Dim lCell As Range, r As Long, dCount As Long
    
    ' Loop through the cells of the lookup range.
    For Each lCell In lrg.Cells
        r = r + 1 ' current (lookup/return) row index
        ' Check if the lookup cell is not blank.
        If Len(CStr(lCell.Value)) > 0 Then ' the lookup cell is not blank
            ' Write the value from the current row index of the return range
            ' to the next (current) element of the array.
            dCount = dCount + 1
            dArr(dCount) = rrg.Cells(r).Value
        'Else ' the lookup cell is blank; do nothing
        End If
    Next lCell
    
    ' Check if all lookup cells were blank.
    If dCount = 0 Then
        MsgBox "All lookup cells were blank.", vbCritical
        Exit Sub
    End If
    
    ' Check if any blanks were found.
    If dCount < rCount Then ' not all lookup cells were not blank
        ReDim Preserve dArr(1 To dCount) ' resize the array
    'Else ' all lookup cells were not blank; do nothing
    End If
    
    ' Continue using the array...
    
    ' Return the comma-separated results in the Immediate window (Ctrl+G).
    Debug.Print Join(dArr, ", ")
    
    Dim d As Long
    
    ' Loop through the results and return one per row
    ' in the Immediate window.
    For d = 1 To dCount
        Debug.Print dArr(d)
    Next d

End Sub

结果

1, 5, 7, 10, 13
 1 
 5 
 7 
 10 
 13