Hi,
I think you can use the following VBA macro.
'-----------------------------------------
Sub findVar()
Dim i As Byte, n As Byte
Dim findVar
n = ActiveWorkbook.Sheets.Count
Dim var_no As String
Dim sh As String 'sheet name where first occurrence is found
var_no = Sheets(1).Range("A3") 'variable is in the cell A3
' On Error Resume Next
'You will need to search from sheet-2 as sheet-1 already has the search
string.
For i = 2 To n
Sheets(i).Activate
With Worksheets(i).Cells
Set findVar = .Find(What:=var_no, After:=ActiveCell,
LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False)
If Not findVar Is Nothing Then
sh = ActiveSheet.Name
Exit For
End If
End With
Next i
msgbox sh
End Sub
'----------------------------------------
On Sun, Aug 31, 2008 at 4:36 AM, Abhijat <[EMAIL PROTECTED]> wrote:
>
> Hi Group,
> I need some help.
> I have a number being input in Sheet1, which I have to find in rest of
> the worksheets.
> I am recording a macro for this.For recording I do Ctrl+F, and type in
> the input number in "Find What" textbox. The other settings of the
> Find box are:
> Within: Workbook
> Search : By Rows
> Look in : Values
> Mach enitre cell contents
>
> Obviously , the first time I hit "Find Next", I stay in the same sheet
> and the cell highlighted is the same as the cell in which input is
> being done. Therefore, I have to hit "Find Next" again to reach the
> first occurance of this number in the rest of the worksheets.
> The macro generates the following snippet:
>
> Sheets("Sheet1").Select
> var_no = Range("A3").Value
> Cells.Find(What:=var_no, After:=ActiveCell, LookIn:=xlValues,
> LookAt _
> :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:= _
> True, SearchFormat:=False).Activate
> Sheets("Sheet2").Select
> Cells.FindNext(After:=ActiveCell).Activate)
>
> In this case, the input number was lying in Sheet2
>
> Now, what I want is simply, the name of the sheet (Sheet2 in this
> case) where the first occurance of that number is, to be captured in
> some variable (say, var_SheetName).
>
> Please help me with the appropriate code for this.
>
> Thanks and Regards,
> Abhijat
>
> >
>
--
Akhilesh Kumar Karna
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
-~----------~----~----~----~------~----~------~--~---
