Microsoft Excel - Macro for searching data - Asked By John on 15-Nov-10 05:17 AM

I got a macro for searching data in a workbook if found it should copy to new workbook. Macro as given below. But its not working. Error showing while running the macro. Can anyone give a solution. Can anyone correct this Macro as suitable for me

Sub Find_Data()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
If IsDate(x) Then x = DateValue(x)
wb As Workbook
Set wb = Workbooks.add
With sht
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then .Range("A1").CurrentRegion.EntireRow.Copy Destination:=wb.Sheets(1).Range("A" & counter + 1)
sheetCount = ActiveWorkbook.Sheets.Count
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub


Parag Satpute replied to John on 15-Nov-10 08:48 AM
If IsDate(x) Then x = DateValue(x)

What is "IsDate" and "DateValue" and if they are variables then why are they not declared? they should be declared...
------------------------------------------------------------------------------------------------------------------------------------------
With sht

Replace the line with below lines of code,
Set sht=activesheet
With sht
John replied to Parag Satpute on 20-Nov-10 02:01 AM
I changed the code as you given but showing error. Can you help me with sample excel file.