Find and select

  • Thread starter Thread starter Kash
  • Start date Start date
K

Kash

Hi, I need to find value which is in sheet1.range("B2") in range
sheets("Sep").range("C2:AG2"). Please help
 
Hi Ross, This is code is able to find only when the value is a number but the
content is actually date. It is saying Not found.

I also want to select the cell as well
 
The code works for me. You do realize that the following line refers to the
code name for the worksheet and not necessarily the given worksheet name. I
wonder if this is a problem.

Data = Sheet1.Range("B2")

is not necessarily the same as this (but it can be):
Data = Sheets("Sheet1").Range("B2")

In the VBA editor the code name is the name in your project explorer that is
not in brackets. The user given name is in brackets.

Anyway the following will select the cell if found. Goto is safer than
Select because it will change worksheets if necessary to select the cell
where as select only works if the sheet is active.

Also if the value to find is a date then you can dimension the variable as a
date.

Sub FindData()

Dim Data As Date
Dim FoundIt As Range
Dim Rng As Range

Data = Sheet1.Range("B2")

Set Rng = Sheets("Sep").Range("C2:AG2")

Set FoundIt = Rng.Find(What:=Data, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundIt Is Nothing Then
MsgBox Data & " not found."
Else
Application.Goto FoundIt
'Insert any other Code to handle selected cell
End If

End Sub
 
An afterthought when finding dates. Ensure that you are not mixing date/times
with purely dates when trying to find. For example =NOW() is a date and time
but the cell can be formatted to view just the date but still has the
underlying time in the value. =TODAY() is only a date.

If you try to find a combined date and time in a range with purely dates
then it requires a little more in the code.

Also different formats can cause a problem. I have found that you sometimes
need to format the date to find the same as in the range being searched.
Example:

Set FoundIt = Rng.Find(What:=Format(Data, "dd mmm yyyy"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

I have never quite entirely come to grips with dates in Excel. Every time I
think I am on top of them something different arises.
 
Replace xlValues by xlFormulas.

Set FoundIt = Rng.Find(Data, , xlFormulas, xlWhole, xlByColumns, xlNext,
False)

If Not FoundIt Is Nothing Then

FoundIt.Parent.Select
FoundIt.Select

Else
MsgBox Data & " not found."

End If

Is it working with xlFormulas?

Mishell
 
Back
Top