Err, Don't know how to fix

jmh2008

jmh2008

New Member
Joined
Sep 4, 2009
Messages
42
Can someone tell me how to fix this? I'm very new and can't figure it out. Thanks,

"Block if without End if"

Option Explicit

Sub FindMe()

Dim sCol
Dim c As Range
Dim msg As Long
Dim rng As Range
Dim roe As Long
Dim LastCol As Long
Dim LastRow As Long
Dim ToFind As String
Dim fRegion As String
Dim finstructor As String
Dim fExpertise As String
Dim fFormat As String
Dim fTravel As String
Dim fProgram As String
Dim fONA As String
Dim shtDest As Worksheet
Dim shtSource As Worksheet

'Sheet to put to
Set shtDest = Sheets(shtSearch)
'Sheet to look in
Set shtSource = Sheets(shtCollection)

'Use
With shtDest
'Get what to look for
If .Range("Region") <> "" Then
'Column to look in
sCol = ColRegion
'What to look for
ToFind = .Range("Region")
'Go search
GoTo SearchMe
ElseIf .Range("Program") <> "" Then
'Column to look in
sCol = ColProgram
'What to look for
ToFind = .Range("Program")
'Go search
GoTo SearchMe
ElseIf .Range("Instructor") <> "" Then
'Column to look in
sCol = ColInstructor
'What to look for
ToFind = .Range("Instructor")
'Go search
GoTo SearchMe
ElseIf .Range("Expertise") <> "" Then
'Column to look in
sCol = ColExpertise
'What to look for
ToFind = .Range("Expertise")
'Go search
GoTo SearchMe
ElseIf .Range("ONA") <> "" Then
'Column to look in
sCol = ColONA
'What to look for
ToFind = .Range("ONA")
'Go search
GoTo SearchMe
ElseIf .Range("Travel") <> "" Then
'Column to look in
sCol = ColTravel
'What to look for
ToFind = .Range("Travel")
'Go search
GoTo SearchMe
Else
'No data to find
msg = MsgBox("No data to find.", vbExclamation, " Try again!")
GoTo endo
End If
End With

SearchMe:

'Use
With shtSource
'Get last row of data
LastRow = .Cells(Rows.Count, sCol).End(xlUp).Row
'Where to look
If sCol = ColProgram Then
'All Program cols
Else
'One col
Set rng = .Range(Cells(2, sCol).Address, Cells(LastRow, sCol).Address)
End If
'Find it
Set c = rng.Find(ToFind, LookIn:=xlValues)
'If found
If Not c Is Nothing Then

Do
'Get row found in
roe = c.Row
'clear then put found info
shtDest.Range("Region") = ""
shtDest.Range("Region") = .Cells(roe, ColRegion)
'Clear then put column where found
shtDest.Range("Program") = ""
'If looking for Program
If sCol = ColProgram Then
'Put column where found
shtDest.Range("Program") = c.Value
Else
'Put first Program
shtDest.Range("Program") = .Cells(roe, ColProgram)
End If
shtDest.Range("Instructor") = ""
shtDest.Range("Instructor") = .Cells(roe, ColInstructor)
shtDest.Range("Expertise") = ""
shtDest.Range("Expertise") = .Cells(roe, ColExpertise)
shtDest.Range("ONA") = ""
shtDest.Range("ONA") = .Cells(roe, ColONA)
shtDest.Range("Travel") = ""
shtDest.Range("Travel") = .Cells(roe, ColTravel)
shtDest.Range("Resume") = ""
shtDest.Range("Resume") = .Cells(roe, ColResume)
shtDest.Range("Outline") = ""
shtDest.Range("Outline") = .Cells(roe, ColOutline)
shtDest.Range("Email") = ""
shtDest.Range("Email") = .Cells(roe, ColEmail)
'Find next one?
msg = MsgBox("Find next one?", vbQuestion + vbYesNo, " Again?")
If msg <> 6 Then GoTo endo

'Do all until back at beginning

'All done
msg = MsgBox("All found.", vbExclamation, "Done.")
GoTo endo

'Not found
msg = MsgBox("Not found.", vbExclamation, "Try again!")
GoTo endo
Loop
If msg <> 6 Then GoTo endo
'No data, come here
endo:

'Cleanup
Set rng = Nothing
Set shtDest = Nothing
Set shtSource = Nothing
End Sub
Sub Clear()

Dim sht As Worksheet

Set sht = Sheets(shtSearch)

With sht
'Clear the named ranges
.Range("Region").ClearContents
.Range("Program").ClearContents
.Range("Instructor").ClearContents
.Range("Expertise").ClearContents
.Range("ONA").ClearContents
.Range("Travel").ClearContents
.Range("Resume").ClearContents
.Range("Outline").ClearContents
.Range("Email").ClearContents

End With

'Cleanup
Set sht = Nothing

End Sub
 
Sort by date Sort by votes
Hi

While posting codes please use code tags (without spaces)

[ CODE ] Your code here [ /CODE ]

Your 2nd With does not have an End With

Code: 
With shtSource
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 
Upvote 0
Hi

If you were to make it a habit to indent your code as below :-

Code: 
With shtDest
'   Get what to look for
    If .Range("Region") <> "" Then
'           Column to look in
        sCol = ColRegion
'           What to look for
        ToFind = .Range("Region")
'       Go search
        GoTo SearchMe
    ElseIf .Range("Program") <> "" Then
'           Column to look in
        sCol = ColProgram
'           What to look for
        ToFind = .Range("Program")
'       Go search
        GoTo SearchMe
    ElseIf .Range("Instructor") <> "" Then
'               Column to look in
        sCol = ColInstructor
'               What to look for
        ToFind = .Range("Instructor")
'       Go search
        GoTo SearchMe
    ElseIf .Range("Expertise") <> "" Then
'               Column to look in
        sCol = ColExpertise
'               What to look for
        ToFind = .Range("Expertise")
'       Go search
        GoTo SearchMe
    ElseIf .Range("ONA") <> "" Then
'               Column to look in
        sCol = ColONA
'               What to look for
        ToFind = .Range("ONA")
'       Go search
        GoTo SearchMe
    ElseIf .Range("Travel") <> "" Then
'               Column to look in
        sCol = ColTravel
'               What to look for
        ToFind = .Range("Travel")
'       Go search
        GoTo SearchMe
    Else
'       No data to find
        msg = MsgBox("No data to find.", vbExclamation, " Try again!")
        GoTo endo
    End If
End With

you will find it is easier to read and therefore easier to identify the problem that you have.

hth

Mike
 
Upvote 0
Hi,

You can use the following code, wherein your code were missing out End If and End With as well in the end.;) Please refer the below code:

Code: 
Option Explicit
Sub FindMe()
    Dim sCol, ColRegion, ColProgram, ColInstructor
    Dim ColExpertise, ColONA, ColTravel, ColResume
    Dim ColOutline, ColEmail
    Dim c As Range
    Dim msg As Long
    Dim rng As Range
    Dim roe As Long
    Dim LastCol As Long
    Dim LastRow As Long
    Dim ToFind As String
    Dim fRegion As String
    Dim finstructor As String
    Dim fExpertise As String
    Dim fFormat As String
    Dim fTravel As String
    Dim fProgram As String
    Dim fONA As String
    Dim shtDest As Worksheet, shtSearch As Worksheet
    Dim shtSource As Worksheet, shtCollection As Worksheet
 
    'Sheet to put to
    Set shtDest = Sheets(shtSearch)
    'Sheet to look in
    Set shtSource = Sheets(shtCollection)
    'Use
    With shtDest
        'Get what to look for
        If .Range("Region") <> "" Then
            'Column to look in
            sCol = ColRegion
            'What to look for
            ToFind = .Range("Region")
            'Go search
            GoTo SearchMe
        ElseIf .Range("Program") <> "" Then
            'Column to look in
            sCol = ColProgram
            'What to look for
            ToFind = .Range("Program")
            'Go search
            GoTo SearchMe
        ElseIf .Range("Instructor") <> "" Then
            'Column to look in
            sCol = ColInstructor
            'What to look for
            ToFind = .Range("Instructor")
            'Go search
            GoTo SearchMe
        ElseIf .Range("Expertise") <> "" Then
            'Column to look in
            sCol = ColExpertise
            'What to look for
            ToFind = .Range("Expertise")
            'Go search
            GoTo SearchMe
        ElseIf .Range("ONA") <> "" Then
            'Column to look in
            sCol = ColONA
            'What to look for
            ToFind = .Range("ONA")
            'Go search
            GoTo SearchMe
        ElseIf .Range("Travel") <> "" Then
            'Column to look in
            sCol = ColTravel
            'What to look for
            ToFind = .Range("Travel")
            'Go search
            GoTo SearchMe
        Else
            'No data to find
            msg = MsgBox("No data to find.", vbExclamation, " Try again!")
            GoTo endo
        End If
    End With
 
SearchMe:
 
    'Use
    With shtSource
        'Get last row of data
        LastRow = .Cells(Rows.Count, sCol).End(xlUp).Row
        'Where to look
        If sCol = ColProgram Then
        'All Program cols
        Else
        'One col
        Set rng = .Range(Cells(2, sCol).Address, Cells(LastRow, sCol).Address)
        End If
        'Find it
        Set c = rng.Find(ToFind, LookIn:=xlValues)
        'If found
        If Not c Is Nothing Then
            Do
                'Get row found in
                roe = c.Row
                'clear then put found info
                shtDest.Range("Region") = ""
                shtDest.Range("Region") = .Cells(roe, ColRegion)
                'Clear then put column where found
                shtDest.Range("Program") = ""
                'If looking for Program
                If sCol = ColProgram Then
                    'Put column where found
                    shtDest.Range("Program") = c.Value
                Else
                    'Put first Program
                    shtDest.Range("Program") = .Cells(roe, ColProgram)
                End If
                shtDest.Range("Instructor") = ""
                shtDest.Range("Instructor") = .Cells(roe, ColInstructor)
                shtDest.Range("Expertise") = ""
                shtDest.Range("Expertise") = .Cells(roe, ColExpertise)
                shtDest.Range("ONA") = ""
                shtDest.Range("ONA") = .Cells(roe, ColONA)
                shtDest.Range("Travel") = ""
                shtDest.Range("Travel") = .Cells(roe, ColTravel)
                shtDest.Range("Resume") = ""
                shtDest.Range("Resume") = .Cells(roe, ColResume)
                shtDest.Range("Outline") = ""
                shtDest.Range("Outline") = .Cells(roe, ColOutline)
                shtDest.Range("Email") = ""
                shtDest.Range("Email") = .Cells(roe, ColEmail)
                'Find next one?
                msg = MsgBox("Find next one?", vbQuestion + vbYesNo, " Again?")
                If msg <> 6 Then GoTo endo
                'Do all until back at beginning
                'All done
                msg = MsgBox("All found.", vbExclamation, "Done.")
                GoTo endo
               'Not found
                msg = MsgBox("Not found.", vbExclamation, "Try again!")
                GoTo endo
            Loop
            If msg <> 6 Then GoTo endo
                'No data, come here
        End If
    End With
endo:
    'Cleanup
    Set rng = Nothing
    Set shtDest = Nothing
    Set shtSource = Nothing
End Sub
Sub Clear()
    Dim sht As Worksheet
    Set sht = Sheets(shtSearch)
    With sht
        'Clear the named ranges
        .Range("Region").ClearContents
        .Range("Program").ClearContents
        .Range("Instructor").ClearContents
        .Range("Expertise").ClearContents
        .Range("ONA").ClearContents
        .Range("Travel").ClearContents
        .Range("Resume").ClearContents
        .Range("Outline").ClearContents
        .Range("Email").ClearContents
    End With
    'Cleanup
    Set sht = Nothing
End Sub
:)

Also, note that when ever you are using "Option Explicit" you have declare each and every variable you are using in your code. Otherwise, it will give you a compile time error saying "Variable not defined" :cool:
 
Last edited:
Upvote 0
Thank you for responding so quick. I really don't know what I'm doing, and I'm under a deadline to have this done today.

I tried your code and now line:

Set shtDest = Sheets(shtSearch) comes up with an err: Type Mismatch.

If I take out the
' shtSearch As Worksheet, and
' shtCollection As Work from the Dim line I get the follow err:

LastRow = .Cells(Rows.Count, sCol).End(xlUp).Row

Is coming up with and err "object defined"

What does the Dim sCol pointing to?

What does this line mean?

Set c = rng.Find(ToFind, LookIn:=xlValues)

Thanks again
 
Upvote 0
You must log in or register to reply here.

Share this page

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