look up value in column

**JusCor** · 08-13-2008, 07:55 AM

I have a spreadsheet of data and I have one column sorted from highest to lowest number. From the top of my spreadsheet I want to search down this column till I get to the first instance of the number 1 or less, and then stop.

I can't use the value of 1 to search for, as sometimes the first number less than 1 might be 0.02, or 0.01, or .....

So using VBA, I can select the first cell, Q2, but how do I check the value of this cell =3,and if not, move on to Q3, Q4 etc.

Thanks,

**LauraGB** · 08-13-2008, 08:13 AM

You need a loop and to use offset to move you down a line.

Do until Activecell.value <=1'Move down a line
Activecell.Offset(1,0).SelectLoop

Laura GB

**royUK** · 08-13-2008, 08:22 AM

lauraGB, please use Code Tags when posting code.

**royUK** · 08-13-2008, 08:27 AM

Try

Option Explicit

Sub findValue()
    Dim cl     As Range
    Dim rng    As Range

    Set rng = ActiveSheet.Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))
    For Each cl In rng
        If cl.Value <= 1 Then
            cl.Select
            Exit Sub
        End If
    Next cl
End Sub

Thread: look up value in column

Thread Tools

Display

look up value in column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions