Find statement help when it doesn't find anything

**hockeyadc** · 12-11-2012, 11:09 AM

I've written the following code for a form I'm using. I'm using the find statement to locate information in a selection, but when it doesn't find it, the program errors and closes. I've tried many different things and nothing is working. I am thinking it has something to do with the What:=Volts.Text section.

Dim WallV

Dim Manu1 As String
Dim Part1 As String
Dim Model1 As String
Dim Link1 As String
Dim Watt1 As Double
Dim Watt2 As Double
Dim Watt3 As Double
Dim Watt4 As Double



nextline = Chr(13)


If Wall.Value = True And AC.Value = True Then
     Sheets("Wall Mounts").Columns("C:C").Select
     Selection.Find(What:=Volts.Text, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
        
            
            Watt1 = ActiveCell.Offset(0, 3).Value
                           
                If Watt1 >= Watts.Text And Watt1 <= Watts.Text + 10 Then
                    Model1 = ActiveCell.Offset(0, -1)
                    Part1 = ActiveCell.Offset(0, 2)
                    Manu1 = ActiveCell.Offset(0, -2)
                    Link1 = ActiveCell.Offset(0, 4)
                 Else
                 Model1 = ""
                 Manu1 = "Nothing found"
                 Part1 = ""
                 Link1 = ""
                 
            
                
                End If
                
                    
                    Selection.FindNext(After:=ActiveCell).Activate
                
                
                    Watt2 = ActiveCell.Offset(0, 3).Value
                    
                    If Watt2 >= Watts.Text And Watt2 <= Watts.Text + 10 Then
                        Model2 = ActiveCell.Offset(0, -1)
                        Part2 = ActiveCell.Offset(0, 2)
                        Manu2 = ActiveCell.Offset(0, -2)
                        Link2 = ActiveCell.Offset(0, 4)
                       
                     Else
                     Model2 = ""
                 Manu2 = "Nothing found"
                 Part2 = ""
                 Link2 = ""
               
                 End If
 
                
         Selection.FindNext(After:=ActiveCell).Activate
                
                
                    Watt3 = ActiveCell.Offset(0, 3).Value
                    
                    If Watt3 >= Watts.Text And Watt3 <= Watts.Text + 10 Then
                        Model3 = ActiveCell.Offset(0, -1)
                        Part3 = ActiveCell.Offset(0, 2)
                        Manu3 = ActiveCell.Offset(0, -2)
                        Link3 = ActiveCell.Offset(0, 4)
                       
                     Else
                     Model3 = ""
                 Manu3 = "Nothing found"
                 Part3 = ""
                 Link3 = ""
               
                 End If
                 
                   Selection.FindNext(After:=ActiveCell).Activate
                
                
                    Watt4 = ActiveCell.Offset(0, 3).Value
                    
                    If Watt4 >= Watts.Text And Watt4 <= Watts.Text + 10 Then
                        Model4 = ActiveCell.Offset(0, -1)
                        Part4 = ActiveCell.Offset(0, 2)
                        Manu4 = ActiveCell.Offset(0, -2)
                        Link4 = ActiveCell.Offset(0, 4)
                       
                     Else
                     Model4 = ""
                 Manu4 = "Nothing found"
                 Part4 = ""
                 Link4 = ""
               
                 End If
                                  
                 MsgBox "Option 1: " & nextline & Manu1 & " " & Model1 & " " & Part1 & nextline & Link1 & nextline & nextline & "Option 2: " & nextline & Manu2 & " " & Model2 & " " & Part2 & nextline & Link2 & nextline & nextline & "Option 3: " & nextline & Manu3 & " " & Model3 & " " & Part3 & nextline & Link3 & nextline & nextline & "Option 4: " & nextline & Manu4 & " " & Model4 & " " & Part4 & nextline & Link4
                 
                 Else
                 
                 If DC.Value = True Then
                 MsgBox "DC Input Not Valid For Wall Mounts"
                 Else
                 MsgBox "Please Select an Input/Output"
                 End If
                 
                 
           
           
      End If

**HaHoBe** · 12-11-2012, 01:34 PM

Hi, hockeyadc,

set a range to the cell if a value is found and check for the range like

If Wall.Value = True And AC.Value = True Then
     Sheets("Wall Mounts").Columns("C:C").Select
     Set rngFound = Sheets("Wall Mounts").Columns("C:C").Find(What:=Volts.Text, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
        
     If Not rngFound Is Nothing Then
        'your macro code when item is found

I woud avoid the use of ActiveCell in a macro if I can.

Ciao,
Holger