Hi, I need to find value which is in sheet1.range("B2") in range
sheets("Sep").range("C2:AG2"). Please help

Kash;477897 Wrote:
Hi, I need to find value which is in sheet1.range("B2") in range
sheets("Sep").range("C2:AG2"). Please help

Hello Kash,

Here is sample macro to give you an idea.

================================
Sub FindData()

Dim Data As Variant
Dim FoundIt As Range
Dim Rng As Range

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

Set FoundIt = Rng.Find(Data, , xlValues, xlWhole, xlByColumns,
xlNext, False)
If FoundIt Is Nothing Then
MsgBox Data & " not found."
Exit Sub
End If

'Insert Code to handle found data here

End Sub
================================


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131832

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


--
Regards,

OssieMac

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.

--
Regards,

OssieMac

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


"Kash" a écrit dans le message de news:
...
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