Trouble with If problem

[sitewolf]

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.

 

[sitewolf]

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]

Maybe you can use something like this

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

M.

 

[sitewolf]

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

 

[Marcelo Branco]

Could you post your code?

M.

 

[sitewolf]

not sure what more code is helpful....there'd be other unrelated find_range code before and after...all being in a loop

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]

This would work for you?

[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.

 

[Marcelo Branco]

Sorry, i think you do not want the code to get out of the Loop...
Take a look at
http://www.cpearson.com/excel/errorhandling.htm

M.

 

[sitewolf]

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]

Try a structure similar to this

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.