Searching a Range Of Cells

[Guest]

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

 

[Dr. Strangelove]

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 !!

 

[Legacy 344]

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

 

[Legacy 344]

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))

 

[JohnG]

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