Error Checking in Excel
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

Some videos you may like

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
  •