Looking to improve procedure

[AlexanderBB]

Goal: To obtain a vblf delimited string containing all the values in FoundCol.

Private Function Obtain(r As Range) As String
    Dim Data()
    Dim y As Long
    Dim FoundCol As String
    Dim lRow As Long

    If Left(r(, 3), 2) = "LP" Then  'Match Confirmed
        With Worksheets("Tracks").Range("A:Z")
            ' If .Find(What:=r(, 3), lookIn:=xlValues) Is Not Nothing Then
            FoundCol = ColLetter(.Find(What:=r(, 3), lookIn:=xlValues).Column)
            lRow = .Range(FoundCol & "200").End(xlUp).Row 'No rows > 200
            Data = Worksheets("Tracks").UsedRange.Columns(FoundCol).Value
            For y = 1 To lRow
                Obtain = Obtain & Data(y, 1) & vbLf
            Next
        End With
    End If
End Function
Function colLetter(intNumber) As String
    If Val(intNumber) = 0 Then
        colLetter = ""
    Else
        colLetter = Left(Cells(1, Val(intNumber)).address(0, 0), 2 + (Val(intNumber) <= 26))
    End If
End Function

The Line remmed out went to error with Nothing as invalid use of object. Odd?
.UsedRange.Columns("G").Value incuded the highest row in the sheet, not just rows in FoundCol so lrow number was needed. Should it be?
And is the y loop required or there a better way ?

 

[Alex Blakenburg]

Try:

Private Function Obtain(r As Range) As String

    Dim iFoundCol
    Dim rData As Range
    Dim lRow As Long
    
    If Left(r(, 3), 2) = "LP" Then  'Match Confirmed
        On Error Resume Next
        With Worksheets("Tracks").Range("A:Z")
            iFoundCol = .Find(What:=r(, 3), LookIn:=xlValues).Column
        End With
        If Err <> 0 Then
            Obtain = ""
            Exit Function
        End If
        On Error GoTo 0
        lRow = Cells(Rows.Count, iFoundCol).End(xlUp).Row  'No rows > 200
        Set rData = Range(Cells(1, iFoundCol), Cells(lRow, iFoundCol))
        Obtain = Join(Application.Transpose(rData.Value), vbLf)
    End If

End Function

 

[AlexanderBB]

Much more concise. Thanks for the example. Really good to compare and see better ways.

 

[Alex Blakenburg]

I know we discussed using UsedRange in the last thread but I can see that you are missing the fact that referencing Range, Cells, Column, Rows are a sub-set of what comes before it.
If you put nothing before it they all assume that you are working with the currently active sheet and as such the top left corner is A1.
So Range("A1") or Cells(1,1) is the same as ActiveSheet.Range("A1") or ActiveSheet.Cells(1,1) and will look at the "A1" that you are expecting.

If you prefix that with UsedRange or a nominated range then the Top Left corner is now something different all your references count from there.
Since you mentioned column G in your post I have used that in the example

You could see if this helps but I would suggest you only read down to and including the section "The Shape of the Range" (2nd section) and focus on what UsedRange is actually selecting.

Controlling Your Spreadsheet With VBA UsedRange - wellsr.com

The VBA UsedRange property stores a sheet's used cells to help you find the first and last used rows and columns on your sheet and navigate through your range.

wellsr.com

 

[AlexanderBB]

Thanks again Alex, that's just the sort of thing I need.