vba cells.find problem

**jeternyn1** · 07-24-2007, 04:25 PM

Im writing a macro that will read data from one workbook, find the data in another workbook, copy the column associated with that data, and paste it into a new sheet. Im having trouble with the cells.find function... when i record a macro the code comes out like this:

Cells.Find(what:="Node Info: 443", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True).Activate
Sheets("Sheet1").Select
Cells.FindNext(After:=ActiveCell).Activate

if i remove the sheet1.select line of code, it no longer works... but I dont know which sheet in the workbook the data will be on all the time so cannot include the sheet in my own code.

now my code:

Sub forloop()
c = 6

For i = 1 To 35
Windows("Monthly DP TC - Syracuse").Activate
Cells(i, 40).Select
If Not IsEmpty(Cells(i, 40).Value2) Then
mycell = ActiveCell.Value
mySearch = "Node Info: " & mycell
If Not UCase(Right(mycell, 5)) = "TOTAL" Then

Windows("VoIPReady - weekly - SYRNY - TW-CentralNY 2007-07-04.xls").Activate
Application.FindFormat.NumberFormat = "General"
'Sheets("SYRCNYAUB").Select

Set Z = Cells.Find(what:=mySearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True)

If Not Z Is Nothing Then
Z.Activate

myrow = ActiveWindow.RangeSelection.Row
Rows(myrow).Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet1").Activate
ActiveSheet.Range(Cells(c, 1), Cells(c, 24)).Select
ActiveSheet.Paste
c = c + 1

Else
MsgBox "Can't find " & mySearch
End If
End If
End If
Next i

End Sub

the problem is that the macro refuses to find the node number data using the cells.find command even though it exists on the searched workbook. It works when you provide it with the sheet that the data exists on, such as
Sheets("Sheet1").Select
but I do not know what sheets each node data exists on... thats why im doing a find.

thanks.

**royUK** · 07-25-2007, 02:39 AM

Wrap your code with Code tags, it's much easier to read. See the link at the bottom of my post.

Option Explicit

Sub searchAllSheets()
    Dim wbSource As Workbook
    Dim ws     As Worksheet
    Dim uRng   As Range
    Dim rCl    As Range
    Dim sAddress As String
    'change this to your workbook
    Set wbSource = Workbooks("Data")
    'check  each worksheet
    For Each ws In wbSource.Worksheets
        'limit search to UsedRange
        Set uRng = ws.UsedRange
        With uRng
            Set rCl = .Find("Node Info: 443", xlValues)
            If Not rCl Is Nothing Then
                sAddress = rCl.Address
                Do
                    'do something with cell, Copy or whatever
                    Set rCl = .FindNext(rCl)
                Loop While Not rCl Is Nothing And rCl.Address <> sAddress
            End If
        End With
    Next ws
End Sub

vba cells.find problem

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

vba cells.find problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions