I'm new to VBA. I'm trying to find a project ID (A3) if I select a different row (B8).
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
CodePudding user response:
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.