VBA find

[sutclpa]

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.

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?

 

[sutclpa]

Hi Richard,

This seems to work perfectly, thank you so much.

Two quick questions though,

1) if i wanted to use the paste special feature, where would i amend the code? I assume it is in this section:

.Range("A" & rFound1.Row, "G" & rFound2.Row).Copy _
                    Destination:=wsSummary.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)

but am unsure what to put as it doesn't look like what i normally use.

2) If the sheets which contained the value 'ABC' also contained a seperate value in say cell A1 which i wanted to copy and place into the summary sheet adjacent to the information the code you provided copies, would this be possible?

Thanks again for all your help.

 

[Richard Schollar]

Hi

Give this a try:

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
Application.ScreenUpdating = False
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
                With wsSummary.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
                    .Resize(rFound2.Row - rFound1.Row + 1, 7).PasteSpecial xlPasteValues 'I have assumed that you have eg a header in B7 of summary sheet
                    .Offset(0, -1).Resize(rFound2.Row - rFound1.Row + 1).Value = ws.Range("A1").Value  'this places the value in range A1 of individual sheets
                    'in column A of summary
                End With
            End If
        End If
    End With
Next ws
End Sub

It now just copies & pastes values (rather than all) - so formulkas get converted to values. Formatting could also be added if requiored.

Also, whatever value is contained in range A1 of the individual sheets is now copied into the corresponding A column cells of the Summary sheet.

Hope it helps!

 

[sutclpa]

Hi Richard,

Apologies for not coming back sooner, but i just wanted to thank you again for all your help with this. The code you have posted works perfectly and has saved me many hours of tediously producing this manually. Thank You