Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 20th, 2007, 10:34 AM
Registered User
Join Date: Jan 2007
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Find with overflow error

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:

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
                    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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Arithmetic overflow error gregalb Reporting Services 1 June 24th, 2008 02:42 PM
Unsolved OverFlow Error maggiefeng Classic ASP Basics 6 December 4th, 2006 07:45 PM
Numeric Field Overflow - Error -- HELP! timmaher Access 1 July 8th, 2005 06:17 AM
Overflow Error mateenmohd Classic ASP Basics 4 July 5th, 2005 02:12 AM
can anybody tell why i am getting overflow error tulsi15 SQL Server 2000 1 December 15th, 2004 07:58 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.