Excel VBA break For...Next cycle

user_14092000 0
Viewed 92

I'm new to VBA. I'm trying to find a project ID (A3) if I select a different row (B8).

VBA For

If the selected row's "A" column's length is 0, go above one cell and repeat until it finds the ID (or currently, anything that length is different than 0). However, my logic fails, because it doesn't work, the for cycle doesn't finish if it found something. What am I missing?

Thanks,

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
vba excel
1 Answers

Just check if the cell left of selection has some value. If not jump up .End(xlUp) to the next data entry.

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

This function expects that some cell in column B is selected when you run it.

If you want to be able to select any column change it to:

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
user_32196130
Related