Need to search for the first "numbers" in a range in Excel 2003

Razor0014 · 09-11-2008, 10:08 AM

I need to figure out a formula that I can use in Excel 2003 that will let me look through a row and give me the data in the first cell that is not blank. In other words, if I am in cell M36, I want to search L36-A36 until it finds some data that has been entered, and then put that number that it finds in Cell M36. It needs to search right to left until it comes across a cell that is not null.

Any help that can be provided is appreciated. Thank you for your help!

Jason

David M58 · 09-11-2008, 05:12 PM

Here's a user-defined function that can do what you described.

Code:

Function find_data() As Variant
    Application.Volatile True
    Dim idx As Long
    find_data = "**NO DATA FOUND**"
    For idx = Application.Caller.Column - 1 To 1 Step -1
        If Not IsEmpty(ActiveSheet.Cells(Application.Caller.Row, idx)) Then
            find_data = ActiveSheet.Cells(Application.Caller.Row, idx).Value
            Exit For
        End If
    Next idx
End Function

As an example, after placing this function in the workbook, suppose row 36 is completely empty and you put =find_data() in cell M36. Then you enter the number 250 in cell G36. The find_data function displays 250. Modify as necessary.

09-11-2008, 10:08 AM	#1 (permalink)
Razor0014 Registered User Join Date: Sep 2008 Posts: 1 OS: xp sp2	Need to search for the first "numbers" in a range in Excel 2003 I need to figure out a formula that I can use in Excel 2003 that will let me look through a row and give me the data in the first cell that is not blank. In other words, if I am in cell M36, I want to search L36-A36 until it finds some data that has been entered, and then put that number that it finds in Cell M36. It needs to search right to left until it comes across a cell that is not null. Any help that can be provided is appreciated. Thank you for your help! Jason

09-11-2008, 05:12 PM	#2 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Need to search for the first "numbers" in a range in Excel 2003 Here's a user-defined function that can do what you described. Code: Function find_data() As Variant Application.Volatile True Dim idx As Long find_data = "NO DATA FOUND" For idx = Application.Caller.Column - 1 To 1 Step -1 If Not IsEmpty(ActiveSheet.Cells(Application.Caller.Row, idx)) Then find_data = ActiveSheet.Cells(Application.Caller.Row, idx).Value Exit For End If Next idx End Function As an example, after placing this function in the workbook, suppose row 36 is completely empty and you put =find_data() in cell M36. Then you enter the number 250 in cell G36. The find_data function displays 250. Modify as necessary. Last edited by David M58; 09-11-2008 at 05:15 PM.