Look for values into Ranges

[Angeles]

Hi , I am using this formula for getting the last row with values

ws.Cells(Rows.count, rng.Column).End(xlUp).row

Now how can I look for some value in that range ?

Thank you in advance.

 

[Otto Moehrbach]

Something like this perhaps. This macro finds the cell in the range from A2
down to the last entry in Column A. The value it is looking for is
"TheValue". HTH Otto
Sub FindValue()
Dim TheRng As Range
Dim TheFoundCell As Range
Set TheRng = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set TheFoundCell = TheRng.Find(What:="The Value", LookAt:=xlWhole)
MsgBox TheFoundCell.Address(0, 0)
End Sub

 

[JLGWhiz]

Assuming that your variable rng.Column is equal to a valid column reference,
the row returned by your statement will be based on the last cell with data
in that column. So you can use that row as a reference by assigining it to a
variable like:

myLastRw = ws.Cells(Rows.count, rng.Column).End(xlUp).row

Now you can use that variable in a cell or range configuration like:

Cells(myLastRw, 1).Activate 'activates the cell on that row in col A.
Range("B2:B" & myLastRw).Select 'Selects cells in col B from B2 to that row.
Range("A1").Value = Cells(myLastRw, rng.Column).Value 'The value of the
cell that marked the row.

 

[Angeles]

It works !!!!

Thank you very much

Something like this perhaps. This macro finds the cell in the range from A2
down to the last entry in Column A. The value it is looking for is
"TheValue". HTH Otto
Sub FindValue()
Dim TheRng As Range
Dim TheFoundCell As Range
Set TheRng = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set TheFoundCell = TheRng.Find(What:="The Value", LookAt:=xlWhole)
MsgBox TheFoundCell.Address(0, 0)
End Sub