VBA find

sutclpa

Board Regular
Joined
Dec 21, 2006
Messages
83
Hi all,

I am trying to use the following code as part of a macro i am writing, however it doesn't seem to like the curr refernce in the find code. I have set it up this way as it need to look it other sheets for the value which is contained in cell B4 of the summary sheet.

Code:
curr = Sheets("Summary").Range("B4")

Selection.Find(What:=curr, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate

Does anybody know how to amend this?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi

What error are you getting? What do you have curr defined as? Are you sure that the value of curr exists in the selected range?
 
Upvote 0
Thats explains part of it.

The value wasn't located in the range. What i would need to do, is if the the value isn't located continue to look at the remaining sheets. Is this possible?
 
Upvote 0
Well, if you use a construction usch as:

Dim rngFound as Range, curr, ws as Worksheet
curr = Sheets("Summary").Range("B4").Value
For Each ws in Worksheets
With ws
If ws.Name<>"Summary" Then
Set rngFound = .Cells.Find(What:=curr, After:=.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False
If Not rngFound Is Nothing Then Exit For
End If
End With
Next ws
If rngFound Is Nothing Then Msgbox "Value " & curr & " not found in sheets!": Exit Sub
'code to do stuff with rngFound eg:
Msgbox "Value found at sheet " & rngFound.Parent.Name & " cell " & rngFound.Address[/code]

Make sense?
 
Upvote 0
Ok, thanks for that.

The code i was using is as follows:
Code:
For Each ws In Worksheets
    If ws.Name <> "Summary" Then
    ws.Activate
On Error Resume Next **** NEED TO CORRECT ERROR HANDLER
        Range("C2:C5000").Select
    Selection.Find(What:=curr, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
        
Name = ActiveCell.Value
addr1 = ActiveCell.Offset(0, -2).Address

Do
    ActiveCell.Offset(1, 0).Activate

    Loop Until ActiveCell.Value <> Name

    addr2 = ActiveCell.Offset(-1, 4).Address 'change offset to copy more cells
    Range(addr1, addr2).Copy

    Worksheets("Summary").Activate

    Range("B8").Select

        If IsEmpty(ActiveCell) Then

         ActiveCell.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
            , Transpose:=False
       
        Else

        Range("B7").End(xlDown).Offset(1, 0).Select ' selects next blank cell in column A

        ActiveCell.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
                , Transpose:=False
                    
        End If
      
    Else: End If

Next ws

Essentially, if the 'find' function is unable to find the specified value i need the macro to go to the last line of the posted code 'Next ws'

Is this possible?
 
Upvote 0
One thing you don't want to do is use a variable with a name which is a protected VBA keyword (like 'Name') - you're asking for trouble doing this. Could you explain in simple terms what your code is meant to do - it will make it much easier to make suggestions then.
 
Upvote 0
Hi Richard,

Basically i have a workbook which contains approx 80 sheets. I need the macro to loop through each sheet (excluding the summary sheet) looking in the range C5:C5000 for the value which is contained in cell B4 of the summary sheet.

If this value is found, the macro should step down through the cells until it reaches a cell containing a different value (hence Loop Until ActiveCell.Value <> Name).

The macro then copies all the information from two columns to left (column A) accross to 4 rows to the right (column G) and pastes this into the summary sheet from the next blank cell in column B. - so if sheet 1 contained the value ABC in cells C5>C10 the macro would copy the range A5:G10 and paste this onto the summary sheet.

it would then continue to loop through the remaining sheets performing this action. Where the macro is unable to match the values, it should just move onto the next sheet.
 
Upvote 0
Right. Taking your example with the lookup value (ie ABC) in cells C5:C10, would ABC exist anywhere else in the C column of this sheet, or only in these cells? The reason I ask is that if it only exists in C5:C10, we can use another Find to find the last instance of ABC, then we know that the range encompassed by these two results of Find are what we want to copy to the Summary sheet (this would be an awful lot quicker than performing a Do...Loop). If however you might have C5:C10 with ABC, but also another instance of ABC in C100 say, then this approach won't work, and the Do Loop becomes more credible.
 
Upvote 0
Hi,

The lookup value - ABC - will always be grouped together in column C, so won't appear any where else. That said, it may not ultimately be in cells C5:C10, but could be anywhere within the range of say C5:C5000.

Regards,
 
Upvote 0
Hi Sutclpa

Try out this code:

Code:
Sub Create_Summary()
Dim vToFind As Variant, rFound1 As Range, rFound2 As Range, ws As Worksheet, wsSummary As Worksheet

Set wsSummary = ThisWorkbook.Worksheets("Summary")  'create ref to summary sheet
vToFind = Sheets("Summary").Range("B4").Value   'store the ToFind value

For Each ws In ThisWorkbook.Worksheets
    With ws
        If .Name <> "Summary" Then
        
            Set rFound1 = .Range("C:C").Find(what:=vToFind, After:=.Range("C1"), _
                LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
                searchdirection:=xlNext, MatchCase:=False)  'this will find the first instance of vToFind
            
            If Not rFound1 Is Nothing Then 'check if vToFind actually found in worksheet
            
                Set rFound2 = .Range("C:C").Find(what:=vToFind, After:=.Range("C1"), _
                LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, _
                searchdirection:=xlPrevious, MatchCase:=False)  'this will find the LAST instance of vToFind (searchdirection has been_
                    'changed to xlPrevious, but starting in Range("C1") still (ie it then goes to C65536 first in xl2003 and below)
                
                'no need to test if rFound2 is nothing - we know by this point that it will find vToFind
                .Range("A" & rFound1.Row, "G" & rFound2.Row).Copy _
                    Destination:=wsSummary.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) 'I have assumed that you have eg a header in B7 of summary sheet
            End If
        End If
    End With
Next ws
End Sub

It should be very fast - I have commented where I thought appropriate.

Please note that there is one thing it doesn't do:

1. It doesn't clear the summary sheet of any preexisting values from B8 downwards - not sure if you wanted this or not.

It also makes a couple of assumptions about the Summary sheet:

1. It assumes that the summary sheet already has a header (doesn't matter what, just some value) in B7. This ensures that the first copy&paste places data in B8 and that subsequent ones will go in the next available cell in the B column

2. It assumes that initially, from B8 downwards, the Summary sheet has no other data in the B column (which is why you probably want to have some form of cells clearing routine before running the code).

Anyway, let me know what you think!
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top