UDF to scan column for value, return offset string

[briancross]

Hello,

I am trying to create a user-defined function that accepts a string (ID), scans cells in a column (L) for the value "1", checks to see if the cell in the same column row immediately above equals ID, and returns a value stored in the row above, and 24 columns to the right. Returning the value that corresponds to the first occurrence of the conditions is fine.

A mess, I know...hopefully you can understand what I'm trying to do. Basically I'm sorting the data a certain way and using subtotal-count to find unique occurrences.

Here is what I have, if it runs at all I get #VALUE!

Public Function FINDACCT(ID As String) As String

Dim r As Range

For Each r In Range("L:L")
    If r.Value = 1 And r.Offset(-1, 0).Value = ID Then
        FINDACCT = r.Offset(-1, 24).Value
        Exit Function
    Else
        FINDACCT = "Not Found"
    End If
Next
        
End Function

 

[JPearson]

This looks closer to a function used in Excel. Are you using this in Access or Excel?

 

[briancross]

ugh, sorry...it's for excel. I do most of my VBA work in access and this is my go to site when I get hung up. Never even crossed my mind.

 

[JPearson]

I think i see apossiblity of the issue.. whats a snip of data look like?

 

[briancross]

ID	        acct string
12345	1234 - 123456789 - 2008/04/04
12345	1234 - 123456789 - 2008/04/04
12345	1234 - 123456789 - 2008/04/04
3	
54321	1234 - 123456789 - 2008/04/11
1	
98765	1234 - 123456789 - 2008/04/18
98765	1234 - 123456789 - 2008/04/18
98765	1234 - 123456789 - 2008/04/18
3

This is obviously just an example, as the entire tool is designed to use reports with many more (irrelevant) columns. The single digit numbers in the ID column are counts. Where the count = 1, I want to return the acct string of the row immediately above. Using the function against the data above for ID 54321 should return 1234 - 123456789 - 2008/04/11. For either 12345 or 98765 "Not found" should be returned.

Make sense? I really had to pare down the data for security reasons.

 

[JPearson]

Try this
Ive had problems with and in if statements... nest it seems to work

Public Function FINDACCT(ID As String) As String
Dim r As Range
For Each r In Range("L:L")
    If r.Value = 1 Then
        If r.Offset(-1, 0).Value = ID Then
            FINDACCT = r.Offset(-1, -24).Value
            Exit Function
        End If
    Else
        FINDACCT = "Not Found"
    End If
 
Next
End Function

and no problem cutting it down.. understand security reasons.

 

[briancross]

That did the trick!! Thanks so much!