Getting the Row of a Selection after filling down with VBA

[gravanoc]

Hi, I have a VBA Function that I would like to work appropriately if someone were to drag and fill down the formula in the column. Here is the full formula, but the important part I'm trying to work out is:

FindMatch = Left(custRg2.Offset(Selection.Row, 0).Value, InStr(1, custRg2.Value, " "))

I want to offset the formula so that it adds one for each row the formula is copied down, but when using Debug.Print it will just show A3:A4 for the address, and the formula won't work as is.

In the picture, the formula will always reference a cell in Row 2, only changing the column when dragged across (which works fine). I just need a way to account for the row differences. Maybe there is an easier way I'm not thinking of. Thanks for any help!

Function FindMatch(ByVal Target As Range) As Variant
    Dim aSheet As Worksheet
    Dim tahSheet As Worksheet
    Dim insSheet As Worksheet
    Dim mergeSheet As Worksheet  
 
    Dim insRg As Range   
    Dim brokerRg1 As Range
    Dim brokerRg2 As Range
    Dim custRg1 As Range
    Dim custRg2 As Range 

    Dim custStr As String
    Dim lendStr As String
    Dim lendStr2 As String
    Dim lendStr3 As String
    
    Dim strNo As Integer

    Dim lineNo As Long
    Dim lineNo2 As Long

    Dim customerMatch As Boolean
    
    Set aSheet = Worksheets("Applications with Loan Split")
    Set tahSheet = Worksheets("TrailAgreementHolder")
    Set insSheet = Worksheets("Instructions")
    
    Set brokerRg1 = aSheet.Range("J19")
    Set brokerRg2 = tahSheet.Range("E6")
    
    Set custRg1 = aSheet.Range("E19")
    Set custRg2 = tahSheet.Range("G6")
    
    Set insRg = insSheet.Range("J9")
    
    strNo = InStr(1, custRg1.Value, " ")
    custStr = Right(custRg1.Value, strNo)
    
    lendStr = brokerRg1.Value
    
    Select Case lendStr
    
        Case insRg.Offset(0, 0).Value
        
            lendStr2 = insRg.Offset(0, 1).Value
            lendStr3 = insRg.Offset(0, 2).Value
            
        Case insRg.Offset(1, 0).Value
        
            lendStr2 = insRg.Offset(1, 1).Value
            
        Case insRg.Offset(2, 0).Value
        
            lendStr2 = insRg.Offset(2, 1).Value
            
        Case insRg.Offset(3, 0).Value
        
            lendStr2 = insRg.Offset(3, 1).Value
        
        Case insRg.Offset(4, 0).Value
        
            lendStr2 = insRg.Offset(4, 1).Value
        
        Case insRg.Offset(5, 0).Value
        
            lendStr2 = insRg.Offset(5, 1).Value
        
        Case insRg.Offset(6, 0).Value
        
            lendStr2 = insRg.Offset(6, 1).Value
            
        Case insRg.Offset(7, 0).Value
        
            lendStr2 = insRg.Offset(7, 1).Value
        
        Case insRg.Offset(8, 0).Value
        
            lendStr2 = insRg.Offset(8, 1).Value
            
        Case insRg.Offset(9, 0).Value
        
            lendStr2 = insRg.Offset(9, 1).Value
            
        Case insRg.Offset(10, 0).Value
        
            lendStr2 = insRg.Offset(10, 1).Value
            
        Case insRg.Offset(11, 0).Value
        
            lendStr2 = insRg.Offset(11, 1).Value
        
        Case insRg.Offset(12, 0).Value
        
            lendStr2 = insRg.Offset(12, 1).Value
        
        Case insRg.Offset(13, 0).Value
        
            lendStr2 = insRg.Offset(13, 1).Value
        
        Case insRg.Offset(14, 0).Value
        
            lendStr2 = insRg.Offset(14, 1).Value
            
        Case insRg.Offset(15, 0).Value
        
            lendStr2 = insRg.Offset(15, 1).Value
          
    End Select
        Debug.Print (InStr(custStr, custRg2.Offset(i, 0).Value))
        
     For i = 0 To 268
     
        If InStr(1, custRg2.Offset(i, 0).Value, custStr) And (lendStr2 = brokerRg2.Offset(i, 0).Value Or lendStr3 = brokerRg2.Offset(i, 0).Value) Then
        
                customerMatch = True
                lineNo = custRg2.Row
                Set custRg2 = tahSheet.Range("B" & lineNo)
                Exit For
                
        End If
        
    Next
        
    If customerMatch = True Then
        
        Select Case Target.Value
        
            Case "Broker First Name"
            
                FindMatch = Left(custRg2.Offset(Selection.Row, 0).Value, InStr(1, custRg2.Value, " "))
                
            Case "Broker Last Name"
            
                FindMatch = Right(custRg2.Value, InStr(1, custRg2.Value, " ") + 1)
                
             Case "Customer First Name"
            
                FindMatch = Left(custRg1.Value, InStr(1, custRg1.Value, " "))
                
            Case "Customer Last Name"
            
                FindMatch = Right(custRg1.Value, InStr(1, custRg1.Value, " "))
                
            Case "Email"
            
                FindMatch = custRg1.Offset(0, 1).Value
                
            Case "Mobile"
            
                FindMatch = custRg1.Offset(0, 2).Value
                
            Case "Street Number"
            
                FindMatch = custRg1.Offset(0, 17).Value
                
            Case "Street Address"
            
                FindMatch = custRg1.Offset(0, 18).Value
            
            Case "Street Type"
            
                FindMatch = custRg1.Offset(0, 19).Value
            
            Case "City"
            
                FindMatch = custRg1.Offset(0, 20).Value
            
            Case "Postcode"
            
                FindMatch = custRg1.Offset(0, 21).Value
            
            Case "Country"
            
                FindMatch = custRg1.Offset(0, 22).Value
            
            Case "Lender Loan Number"
            
                FindMatch = custRg2.Offset(0, 6).Value
            
            Case "Broker Loan Number"
            
                FindMatch = custRg1.Offset(0, 7).Value
            
            Case "Settlement Amount"
            
                FindMatch = custRg1.Offset(0, 8).Value
                
            Case "Owing Amount"
            
                FindMatch = custRg2.Offset(0, 7).Value
                
            Case "Settlement Date"
            
                FindMatch = custRg2.Offset(0, 1).Value
            
            Case "Loan Type"
            
                FindMatch = custRg1.Offset(0, 11).Value
                
            Case "Finance Type"
            
                FindMatch = custRg1.Offset(0, 10).Value
                
            Case "Is Owner Occupier"
            
                FindMatch = "N/A"
                
            Case "Property Value"
            
                FindMatch = custRg1.Offset(0, 23).Value
                
            Case "Interest Rate"
            
            FindMatch = custRg1.Offset(0, 12).Value
            
        End Select
            
    End If

End Function

 

[GWteB]

Change your function in a way that it accepts two arguments:

Public Function MyFindMatch(ByRef argTarget As Range, ByVal argRow As Long) As Variant

to be able to pass on the actual row in which the formula is in. In your code you can act on that row.

Change the formula on your sheet accordingly: =MyFindMatch(A$2,ROW())