Lounge - Macro for searching data - Asked By John on 15-Nov-10 05:19 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
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
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")
End If
End Sub

Parag Satpute replied to John on 15-Nov-10 07:11 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 01:58 AM
I changed the code as you given but not working. Can you please help me correct it with sample excel file.