Referencing a newly created Workbook in a Function

[cappla011]

Hello, I am terrible with functions but was looking for some help.

    Set OP = Workbooks.Add
    OP.Worksheets("Sheet1").Name = "Transaction Download"
    Set DT = OP.Worksheets("Transaction Download")
.
.
.
    SLookFor = "FUND VALUE"
    Set oLookin = DT.Rows(1)
    Set oFound = oLookin.Find(What:=SLookFor, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)
    If Not oFound Is Nothing Then
        FVCol = oFound.Column
    Else
        MsgBox "Could not find the '" & SLookFor & "' column in the Transaction Download sheet -- please review."
        Exit Sub
    End If
   
    SLookFor = "PROGRAM CODE"
    Set oLookin = DT.Rows(1)
    Set oFound = oLookin.Find(What:=SLookFor, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)
    If Not oFound Is Nothing Then
        PCCol = oFound.Column
    Else
        MsgBox "Could not find the '" & SLookFor & "' column in the Transaction Download sheet -- please review."
        Exit Sub
    End If

I have a bunch of SLookFor .Find code that look the same, so I wanted to convert it to a function so it would be cleaner. I wanted to do something like --

FindRow("FUND VALUE")
.
.
Function FindRow(SLookFor as String)
    Set oLookin = DT.Rows(1)
    Set oFound = oLookin.Find(What:=SLookFor, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)
    If Not oFound Is Nothing Then
        FVCol = oFound.Column
    Else
        MsgBox "Could not find the '" & SLookFor & "' column in the Transaction Download sheet -- please review."
        Exit Sub
    End If

But this is not working, it is giving an error at the "Set oLookin = DT.Rows(1)" code. I believe it is because it is referencing the DT Workbook, but it is also not letting me define that in the Function itself (or I am doing it wrong). Would anyone be able to help? Hope I explained this correctly.

 

[RoryA]

I'd add the search range as another argument:

Function FindCol(SLookFor as String, SearchRange as Range) As Long
   Dim oFound as Range
   Set oFound = SearchRange.Find(What:=SLookFor, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)
    If Not oFound Is Nothing Then
        FindCol = oFound.Column
    Else
        FindCol = 0
    End If
End Function

then call it with:

SLookFor ="FUND VALUE"
FVCol = FindCol(SLookFor, DT.Rows(1))
If FVCol = 0 then
        MsgBox "Could not find the '" & SLookFor & "' column in the Transaction Download sheet -- please review."
        Exit Sub
End If

 

[cappla011]

Wow that works great! Thank you so much!