Macro

**royUK** · 09-03-2007, 08:42 AM

You aren't using Aaron's Function correctly

Find_Range("Yes", Range("W13:W100")).EntireRow.Select
Selection.Copy Worksheets("Journal").Cells(Rows.Count, 1).End(xlUp)

**royUK** · 09-03-2007, 08:46 AM

Your full code to use that Function would be

Sub PrepareUpload()
    Dim ws     As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Journal"    'do nothing
            Case Else
                Find_Range("Yes", Range("W13:W100")).EntireRow.Select
                Selection.Copy Worksheets("Journal").Cells(Rows.Count, 1).End(xlUp)
        End Select
    Next ws
End Sub

Note: rhe UDF (Function) is the work of Aaron Blood)

**biggs** · 09-03-2007, 08:53 AM

Thanks for your reply RoyUK, i tried using that code last week after looking around on here but couldnt get it to work, i kept getting an error message

when i posted this thread i made some changes to the spreadsheet to make it easier to understand, but didnt make the changes to the code to reflect it

ive made the changes you suggested but im now getting a run time error 91

object variable or with block variable not set

it seems to have a problem with the line

Find_Range("Yes", Range("W13:W100")).EntireRow.Select

**royUK** · 09-03-2007, 08:58 AM

Have you still got the Function in there?

Option explicit
Sub PrepareUpload()
    Dim ws     As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Journal"    'do nothing
            Case Else
                Find_Range("Yes", Range("W13:W100")).EntireRow.Select
                Selection.Copy Worksheets("Journal").Cells(Rows.Count, 1).End(xlUp)
        End Select
    Next ws
End Sub
Function Find_Range(Find_Item As Variant, _
                    Search_Range As Range, _
                    Optional LookIn As Variant, _
                    Optional LookAt As Variant, _
                    Optional MatchCase As Boolean) As Range

    Dim c      As Range
    If IsMissing(LookIn) Then LookIn = xlValues    'xlFormulas
    If IsMissing(LookAt) Then LookAt = xlPart    'xlWhole
    If IsMissing(MatchCase) Then MatchCase = False

    With Search_Range
        Set c = .Find( _
                What:=Find_Item, _
                LookIn:=LookIn, _
                LookAt:=LookAt, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=MatchCase, _
                SearchFormat:=False)
        If Not c Is Nothing Then
            Set Find_Range = c
            firstAddress = c.Address
            Do
                Set Find_Range = Union(Find_Range, c)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

End Function

**biggs** · 09-03-2007, 09:02 AM

missed a line when i copied and pasted it!

now im getting a compile error, variable not defined for

firstAddress = c.Address

**royUK** · 09-03-2007, 09:06 AM

Aaron doesn't use Option Explicit, so you need to place

Dim FirstAddress as String

in the Function or remove Option Explicit.

Function Find_Range(Find_Item As Variant, _
                    Search_Range As Range, _
                    Optional LookIn As Variant, _
                    Optional LookAt As Variant, _
                    Optional MatchCase As Boolean) As Range

    Dim c      As Range
    Dim firstAddress As String
 'etc

**biggs** · 09-03-2007, 09:49 AM

thanks for your help RoyUK

im still getting the runtime 91 error though, before i input the Dim firstAddress As String line i was getting the error in my previous post, but since ive inserted this line im getting the 91 error again

my code at the moment is:

Option Explicit
Sub PrepareUpload()
    Dim ws     As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Journal"    'do nothing
            Case Else
                Find_Range("Yes", Range("W13:W100")).EntireRow.Select
                Selection.Copy Worksheets("Journal").Cells(Rows.Count, 1).End(xlUp)
        End Select
    Next ws
End Sub
Function Find_Range(Find_Item As Variant, _
                    Search_Range As Range, _
                    Optional LookIn As Variant, _
                    Optional LookAt As Variant, _
                    Optional MatchCase As Boolean) As Range

    Dim c      As Range
    Dim firstAddress As String
    If IsMissing(LookIn) Then LookIn = xlValues    'xlFormulas
    If IsMissing(LookAt) Then LookAt = xlPart    'xlWhole
    If IsMissing(MatchCase) Then MatchCase = False

    With Search_Range
        Set c = .Find( _
                What:=Find_Item, _
                LookIn:=LookIn, _
                LookAt:=LookAt, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=MatchCase, _
                SearchFormat:=False)
        If Not c Is Nothing Then
            Set Find_Range = c
            firstAddress = c.Address
            Do
                Set Find_Range = Union(Find_Range, c)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

End Function

any ideas?

**royUK** · 09-03-2007, 10:11 AM

If it isn't finding any Rows it errors. This seems to work

Sub PrepareUpload()
    Dim ws     As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Application.ScreenUpdating = False
        On Error Resume Next
        Select Case ws.Name
            Case "Journal"    'do nothing
            Case Else
                ws.Select
                Find_Range("Yes", ws.Range("W13:W100")).EntireRow.Select
                Selection.Copy Worksheets("Journal").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        End Select
    Next ws
    Application.ScreenUpdating = True
    On Error GoTo 0
End Sub

Macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions