Excel VBA break For...下一个循环

Question

我是 VBA 新手。如果我选择不同的行 (B8)，我正在尝试查找项目 ID (A3)。

如果所选行的“A”列的长度为 0，则转到一个单元格上方并重复，直到找到 ID(或当前，任何长度不为 0 的内容)。但是，我的逻辑失败了，因为它不起作用，如果找到某些东西，for 循环不会完成。我错过了什么？
谢谢，

Option Explicit
Sub Test()
    Dim SelectedData As Range, count As Long, ProjectID As String
    Set SelectedData = Selection
    count = SelectedData.count
    
    Dim wb As Workbook, ws As Worksheet, cell As Range
    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1)
    
    Dim FilePath    As String, i As Long
    Dim Filename As String
    ProjectID = SelectedData.EntireRow.Cells(1, "A").Text
    
    If Len(ProjectID) = 0 Then
        
        For i = 0 To -5 Step -1
        ProjectID = SelectedData.EntireRow.Cells(i, "A").Text
        Next
    Else
    MsgBox "ID found"
    End If
    
    'MsgBox Filename
    
    
End Sub

Answer 1

只需检查选择左侧的单元格是否具有某些值。如果不跳起来.End(xlUp)到下一个数据条目。

Option Explicit

Public Sub Example()
    
    Debug.Print GetProjectID(Selection)
    
End Sub


Public Function GetProjectID(ByVal SelectedData As Range) As String
    Dim RetVal As String
    
    If SelectedData.Offset(ColumnOffset:=-1).Value <> vbNullString Then
        RetVal = SelectedData.Offset(ColumnOffset:=-1).Value
    Else
        RetVal = SelectedData.Offset(ColumnOffset:=-1).End(xlUp).Value
    End If

    GetProjectID = RetVal
End Function

此函数期望在您运行它时选择 B 列中的某个单元格。

如果您希望能够选择任何列，请将其更改为:

Option Explicit

Public Sub Example()
    
    Debug.Print GetProjectID(Selection)
    
End Sub


Public Function GetProjectID(ByVal SelectedData As Range) As String
    Dim RetVal As String

    Dim CellInColumnA As Range
    Set CellInColumnA = SelectedData.Parent.Cells(SelectedData.Row, 1)

    If CellInColumnA.Value <> vbNullString Then
        RetVal = CellInColumnA.Value
    Else
        RetVal = CellInColumnA.End(xlUp).Value
    End If

    GetProjectID = RetVal
End Function