Trouble with If problem

sitewolf

Active Member
Joined
May 4, 2012
Messages
289
I have a spreadsheet that grabs lead information from a site I have an account with. It's designed to go to a project page on the site, grab a group of information from the page, then manipulate the data I want into the form I want. All is good but for 2 problems that sometimes come up- easy to work around for me, but I'm trying to dummy the spread down for a salesman to use and therefore want to limit the 'what to do if this happens' list

The primary question I want to deal with is....occasionally, one of the items I am looking for is not there. When that happens now, my macro stops and I type the item in just so I can resume the macro. I would like to automate that.

I am utilizing a find_range function I found online to find items like project name, address, county, etc....project contact is the item that occasionally does not exist. I would like to somehow have the macro ignore and continue if the function does not find the words 'project contact'

Find_Range("Project Contacts", Range("A:A")).Select is the line that first deals with the item- the find_range function looks in column A for the words project contacts. It is part of a loop and I'd like the loop to finish so it can go on to the next record.

I'm thinking if I find the resolution for this, I can extrapolate a resolution to my other problem.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

sitewolf

Active Member
Joined
May 4, 2012
Messages
289
Code:
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

there's the find_range function I found that works quite nicely to find items within a range when they're not always in the same place

which makes me wonder....is there something I'm missing in the function itself where I could insert the IF I need...
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
Maybe you can use something like this

Code:
If Not Find_Range("Project Contacts", Range("A:A")) Is Nothing Then
    'do your stuff
End If

M.
 

sitewolf

Active Member
Joined
May 4, 2012
Messages
289
it might, thanks.........but I'd still not be sure of the Then verbage.... Is Nothing Then _______ ? How would I tell it what to do next if I just want it to move to the next line of code, not exit the sub altogether?
I did try just having it type Project Contacts and that kinda works...the ideal just being if it's not found, the macro ignores and continues
 

sitewolf

Active Member
Joined
May 4, 2012
Messages
289
not sure what more code is helpful....there'd be other unrelated find_range code before and after...all being in a loop

Code:
If Not Find_Range("Project Contacts", Range("A:A")) Is Nothing Then ActiveCell.FormulaR1C1 = "Project Contacts"
ActiveCell.Offset(2, 0).Range("A1:D4").Copy
    Sheets("rawlist").Select
    Range("I2").Select
    ActiveSheet.Paste
    Range("A2").Select
    ActiveCell.Replace What:="Project ID", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("P2").Select
    ActiveCell.Replace What:="Description ", Replacement:="", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407

ADVERTISEMENT

This would work for you?

Code:
[B]If Find_Range("Project Contacts", Range("A:A")) Is Nothing Then Exit For ' or Exit Do[/B]

ActiveCell.FormulaR1C1 = "Project Contacts"
ActiveCell.Offset(2, 0).Range("A1:D4").Copy
    Sheets("rawlist").Select
    Range("I2").Select
    ActiveSheet.Paste
    Range("A2").Select
    ActiveCell.Replace What:="Project ID", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("P2").Select
    ActiveCell.Replace What:="Description ", Replacement:="", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

M.
 

sitewolf

Active Member
Joined
May 4, 2012
Messages
289
Going that route, I wouldn't want to type in Project Contacts the 98% of the time it's already there, because the contacts are one of the most important pieces of info I'm gleaning from all this. This is just an attempt to auto bypass the 2% that doesn't.


If Not Find_Range("Project Contacts", Range("A:A")) Is Nothing Then Resume

going back to your original work around, is resume viable here? Because that's all I'm really wanting to do...have the macro not stop just because one record in 100 may not have contacts
</pre>
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
Try a structure similar to this

Code:
For i = 1 To 10 'beginning of the loop
    'code
    'code
    '....
    
    If Not Find_Range("Project Contacts", Range("A:A")) Is Nothing Then
        'insert here the code lines
        'that depend on Project Contacts be found
    End If
    
    'If there is code that still  should run
    'irrespective of Project Contacts be found or not
    'Insert here
    
Next i 'End of the loop

'Finalize the code
End Sub

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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
Top