Searching a Range Of Cells
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Searching a Range Of Cells

  1. #1
    Guest

    Default

     
    I Have a sheet with a range of cells that i want to search so that i can find the last cell in the range that contains any data.
    ActiveCell.Offset(1, -13) = CODE
    Cells.Find(What:="*", After:=[A1], _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Select
    I am using the above code but this is searching the whole sheet and not just the range of cells that i want to look at within the sheet. These are (c2:t200). Does anyone know the best way to resolve this problem...

    Thx
    Roy

  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    Tilburg, the Netherlands
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    THIS LOOKS SOMEWHAT TO MY CURRENT PROBLEM:
    Perhaps the solution (which I do not have) can help us both...

    I have a query which runs each month and result in a result consisting of x lines
    It is a variable range because the number of lines differs each month.
    Now I want to establish a VLOOKUP formula which looks in the entire (variable) range.
    It always starts in "A1" but it ends in "Bx"

    I have a COUNTA formula which counts the number of lines. (Actually this looks at a large number of lines or the whole column.) Lets suppose this number is 1200.
    How can I get the VLOOKUP-formula to automatically take the relevant range "A1:B1200" ?
    So, I want Excel to fill in the 1200-value.
    Any help on this issue is highly appreciated ! Greetings from the Netherlands !!

  3. #3

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-22 02:56, Anonymous wrote:
    I Have a sheet with a range of cells that i want to search so that i can find the last cell in the range that contains any data.
    ActiveCell.Offset(1, -13) = CODE
    Cells.Find(What:="*", After:=[A1], _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Select
    I am using the above code but this is searching the whole sheet and not just the range of cells that i want to look at within the sheet. These are (c2:t200). Does anyone know the best way to resolve this problem...

    Thx
    Roy

    [C2:T201].Find(What:="*", After:=[T201], _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Select

  4. #4

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-22 05:38, Dr. Strangelove wrote:
    THIS LOOKS SOMEWHAT TO MY CURRENT PROBLEM:
    Perhaps the solution (which I do not have) can help us both...

    I have a query which runs each month and result in a result consisting of x lines
    It is a variable range because the number of lines differs each month.
    Now I want to establish a VLOOKUP formula which looks in the entire (variable) range.
    It always starts in "A1" but it ends in "Bx"

    I have a COUNTA formula which counts the number of lines. (Actually this looks at a large number of lines or the whole column.) Lets suppose this number is 1200.
    How can I get the VLOOKUP-formula to automatically take the relevant range "A1:B1200" ?
    So, I want Excel to fill in the 1200-value.
    Any help on this issue is highly appreciated ! Greetings from the Netherlands !!
    You could set your range like this :-

    Dim rw As Long, rng As Range
    rw = Columns("A:B").Find(What:="*", After:=[B65536], _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    Set rng = Range([A1], Cells(rw, 2))

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    SRC
    Posts
    165
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    does this help?

    Sub UsdRange()
    Dim LastRow As Integer
    Dim FLoop As Integer
    Dim LastAddress As String

    LastRow = Range(worksheets("sheet1").UsedRange.Address).Rows.Count

    For FLoop = 1 To LastRow
    If Range("C" & FLoop) = "*" Then
    LastAddress = Range("C" & FLoop).Address
    End If
    Next FLoop
    MsgBox LastAddress
    End Sub

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com