I am running Excel 2007, and I am having trouble writing a macro using the find method. I want a macro that will find a specific number and if number is found it will create another workbook with those results. I found a code that does just that, but it doesn't work well with Excel 2007. I get an overflow error. Any help will be great.
Here is the code:
Code:
Option Explicit
Sub testme01()
Dim curWkbk As Workbook
Dim wks As Worksheet
Dim RptWks As Worksheet
Dim oRow As Long
Dim MaxRows As Long
Dim oCol As Long
Dim FoundCell As Range
Dim FirstAddress As String
Dim FindWhat As String
FindWhat = InputBox(Prompt:="Find What?")
If FindWhat = "" Then
Exit Sub
End If
Set curWkbk = ActiveWorkbook
Set RptWks = Workbooks.Add(1).Worksheets(1)
MaxRows = 40000
oRow = 99999
oCol = -3
For Each wks In curWkbk.Worksheets
With wks.Cells
Set FoundCell = .Find(what:=FindWhat, lookat:=xlPart, _
LookIn:=xlFormulas, _
after:=.Cells(.Cells.Count), _
searchdirection:=xlNext, MatchCase:=False)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
If oRow > MaxRows - 1 Then
If oCol > 252 Then
Set RptWks = RptWks.Parent.Worksheets.Add
oCol = -3
End If
oCol = oCol + 4
RptWks.Cells(1, oCol).Resize(1, 4).Value _
= Array("Worksheet Name", "Address", _
"Value", "Formula")
oRow = 1
End If
oRow = oRow + 1
With RptWks.Cells(oRow, oCol)
.Value = "'" & FoundCell.Parent.Name
.Offset(0, 1).Value = FoundCell.Address
With .Offset(0, 2)
.Value = "'" & FoundCell.Text
End With
If FoundCell.HasFormula Then
.Offset(0, 3).Value = "'" & FoundCell.Formula
End If
End With
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With
Next wks
End Sub
Find with overflow error
