Error handler - Null find

[MarkCBB]

hi there,


i am using the following code:

    Cells.Find(What:="Lump Sum Amounts").Select
    Set n4 = Selection
    Selection.Offset(1, -1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Set r4 = Selection
    Selection.Offset(, 2).Select
    Set v4 = Selection

however sometime the value that is being looked for does not exist on the database, I need some code that will move onto the next section of code if it is not found.

thanks

 

[pgc01]

Hi Mark

You don't usually use select in vba.

Check if this code suits your needs:

Dim n4 As Range, r4 As Range, v4 As Range
    
    Set n4 = Cells.Find(What:="Lump Sum Amounts")
    
    ' if found then execute some code
    If Not n4 Is Nothing Then
        
        With n4.Offset(1, -1)
            Set r4 = Range(.Cells, .Cells.End(xlDown))
        End With
        
        Set v4 = r4.Offset(, 2)
    
    End If

 

[MarkCBB]

HI VoG,

would something like this not work?

n Error Resume Next
    Cells.Find(What:="Lump Sum Amounts").Select
    Set n4 = Selection
    Selection.Offset(1, -1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Set r4 = Selection
    Selection.Offset(, 2).Select
    Set v4 = Selection
    On Error GoTo 0
 
'This is for the Normal Income

On Error Resume Next
    Cells.Find(What:="Normal Income").Select
    Set n5 = Selection
    Selection.Offset(1, -1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Set r5 = Selection
    Selection.Offset(, 2).Select
    Set v5 = Selection
    On Error GoTo 0

 

[RoryA]

It will work, yes. It will not be efficient or good practice though.

 

[MarkCBB]

Thanks, it took me awhile to understand the

If Not n4 Is Nothing Then

But i got it. thanks for the help and tips.

 

[MarkCBB]

Me again, I just found something that is not working correctly, I have tried to figure it out myself. but no luck, here is the code:

code1

 Set n4 = Cells.Find(What:="Lump Sum Amounts")
        If Not n4 Is Nothing Then
        With n4.Offset(1, -1)
        Set r4 = Range(.Cells, .Cells.End(xlDown))
        End With
        Set v4 = r4.Offset(, 2)
        End If

this code is the ranges that need to be copied, here is the code that does the pasting

code2

On Error Resume Next
r4.Copy
E4.Select
Call Up
ActiveSheet.Paste
v4.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n4
On Error GoTo 0

for some reason even when N4 = nothing V4 is still being pasted? but its being pasted twice, but the strange thing is that there is no data linked to "Lump Sum Amounts" its pasting data that was defined in this code.

code3

        Set n7 = Cells.Find(What:="Voluntary Deductions")
        If Not n7 Is Nothing Then
        With n7.Offset(1, -1)
        Set r7 = Range(.Cells, .Cells.End(xlDown))
        End With
        Set v7 = r7.Offset(, 2)
        End If

any idea what is going on?

 

[MarkCBB]

here is the full code if it helps in any way, as you will see I am not very good with VBA.

Sub IMPORT_DATA()

'These are the ranges of the data
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim r4 As Range
Dim r6 As Range
Dim r7 As Range
Dim r8 As Range
Dim r9 As Range
Dim r10 As Range
Dim r11 As Range
'These are the names of the sections that are being copied across
Dim n1 As Range
Dim n2 As Range
Dim n3 As Range
Dim n4 As Range
Dim n5 As Range
Dim n6 As Range
Dim n7 As Range
Dim n8 As Range
Dim n9 As Range
Dim n10 As Range
Dim n11 As Range
'This is all the values of the data ranges listed above
Dim v1 As Range
Dim v2 As Range
Dim v3 As Range
Dim v4 As Range
Dim v5 As Range
Dim v6 As Range
Dim v7 As Range
Dim v8 As Range
Dim v9 As Range
Dim v10 As Range
Dim v11 As Range
'This is all the new ranges for the new data to be pasted into the master database
Dim E1 As Range
Dim E2 As Range
Dim E3 As Range
Dim E4 As Range
Dim E5 As Range
'This is the master heading names of the excel sheets
Dim h1 As Range
Dim h2 As Range
'this is the master range of all the data
Dim M1 As Range

If Range("A5").Value = "" And Range("A6").Value = "" And Range("A7").Value = "" Then

MsgBox ("There is no data")
Exit Sub
Else

Sheets("INPUT").Select
Range("A2").Select

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Set M1 = Selection

With M1
.MergeCells = False
End With

M1.UnMerge

'This is for all the headings
Range("B5").Select
Set h1 = Selection
Range("B7").Select
Set h2 = Selection

'This is for the allocation data
Set n1 = Cells.Find(What:="Allowances")
If Not n1 Is Nothing Then
With n1.Offset(1, -1)
Set r1 = Range(.Cells, .Cells.End(xlDown))
End With
Set v1 = r1.Offset(, 2)
End If

'This is for the Deductions
Set n2 = Cells.Find(What:="Deductions")
If Not n2 Is Nothing Then
With n2.Offset(1, -1)
Set r2 = Range(.Cells, .Cells.End(xlDown))
End With
Set v2 = r2.Offset(, 2)
End If

'This is for the Involuntary Deductions
Set n3 = Cells.Find(What:="Involuntary Deductions")
If Not n3 Is Nothing Then
With n3.Offset(1, -1)
Set r3 = Range(.Cells, .Cells.End(xlDown))
End With
Set v3 = r3.Offset(, 2)
End If

'This is for the Lump Sum Amounts
Set n4 = Cells.Find(What:="Lump Sum Amounts")
If Not n4 Is Nothing Then
With n4.Offset(1, -1)
Set r4 = Range(.Cells, .Cells.End(xlDown))
End With
Set v4 = r4.Offset(, 2)
End If

'This is for the Normal Income
Set n5 = Cells.Find(What:="Normal Income")
If Not n5 Is Nothing Then
With n5.Offset(1, -1)
Set r5 = Range(.Cells, .Cells.End(xlDown))
End With
Set v5 = r5.Offset(, 2)
End If

'This is for the Statutory Deductions
Set n6 = Cells.Find(What:="Statutory Deductions")
If Not n6 Is Nothing Then
With n6.Offset(1, -1)
Set r6 = Range(.Cells, .Cells.End(xlDown))
End With
Set v6 = r6.Offset(, 2)
End If

'This is for the Voluntary Deductions
Set n7 = Cells.Find(What:="Voluntary Deductions")
If Not n7 Is Nothing Then
With n7.Offset(1, -1)
Set r7 = Range(.Cells, .Cells.End(xlDown))
End With
Set v7 = r7.Offset(, 2)
End If

'This is for the Employer Contributions
Set n8 = Cells.Find(What:="Employer Contributions")
If Not n8 Is Nothing Then
With n8.Offset(1, -1)
Set r8 = Range(.Cells, .Cells.End(xlDown))
End With
Set v8 = r8.Offset(, 2)
End If

'This is for the Fringe Benefits
Set n9 = Cells.Find(What:="Fringe Benefits")
If Not n9 Is Nothing Then
With n9.Offset(1, -1)
Set r9 = Range(.Cells, .Cells.End(xlDown))
End With
Set v9 = r9.Offset(, 2)
End If

'This is for the Statutory Information
Set n10 = Cells.Find(What:="Fringe Benefits")
If Not n10 Is Nothing Then
With n10.Offset(1, -1)
Set r10 = Range(.Cells, .Cells.End(xlDown))
End With
Set v10 = r10.Offset(, 2)
End If

'This is for the Total FNB EFT
On Error Resume Next
Cells.Find(What:="Total FNB EFT").Select
Set n11 = Selection
Columns("C").Replace What:="Count......*", Replacement:=""
Selection.Offset(, 3).Select
Selection.FormulaR1C1 = "=SUBSTITUTE(RC[-1],LEFT(RC[-1],3),"""")-0"
Selection.Copy
Selection.Offset(, -1).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.Offset(, 1).ClearContents
Set v11 = Selection
On Error GoTo 0

'This removes all the the commas so that the text is turned into values
Columns("C:C").Select
Selection.Replace What:=",", Replacement:=""
'Need to add the code from Mrexcel here


Sheets("DATA").Visible = True
Sheets("DATA").Select

'This is all the end ranges
Range("A1048576").Select
Set E1 = Selection

Range("B1048576").Select
Set E2 = Selection

Range("C1048576").Select
Set E3 = Selection

Range("D1048576").Select
Set E4 = Selection

Range("E1048576").Select
Set E5 = Selection

'This is the moving of the data from the INput sheets to the Data sheet

On Error Resume Next
r1.Copy
E4.Select
Call Up
ActiveSheet.Paste
v1.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n1
On Error GoTo 0

On Error Resume Next
r2.Copy
E4.Select
Call Up
ActiveSheet.Paste
v2.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n2
On Error GoTo 0

On Error Resume Next
r3.Copy
E4.Select
Call Up
ActiveSheet.Paste
v3.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n3
On Error GoTo 0

On Error Resume Next
r4.Copy
E4.Select
Call Up
ActiveSheet.Paste
v4.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n4
On Error GoTo 0

On Error Resume Next
r5.Copy
E4.Select
Call Up
ActiveSheet.Paste
v5.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n5
On Error GoTo 0

On Error Resume Next
r6.Copy
E4.Select
Call Up
ActiveSheet.Paste
v6.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n6
On Error GoTo 0

On Error Resume Next
r7.Copy
E4.Select
Call Up
ActiveSheet.Paste
v7.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n7
On Error GoTo 0

On Error Resume Next
r8.Copy
E4.Select
Call Up
ActiveSheet.Paste
v8.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n8
On Error GoTo 0

On Error Resume Next
r9.Copy
E4.Select
Call Up
ActiveSheet.Paste
v9.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n9
On Error GoTo 0

On Error Resume Next
r10.Copy
E4.Select
Call Up
ActiveSheet.Paste
v10.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n10
On Error GoTo 0

E4.Select
Call Up
Selection.FormulaR1C1 = "NUMBER PAID"
v11.Copy
E5.Select
Call Up
ActiveSheet.Paste
E3.Select
Call Up
Selection.Offset(, 1).Select
Selection.Offset(, -1).Select
Selection.FormulaR1C1 = n11

Columns("E").Replace What:="......", Replacement:=""
Columns("D").Replace What:="Total", Replacement:=""
On Error Resume Next
Columns("D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

E2.Select
Call Up
Selection.Offset(, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).Select
Selection.FormulaR1C1 = h2
Selection.Offset(0, -1).Select
Selection.FormulaR1C1 = h1

Call REMOVE

M1.ClearContents
'Sheets("DATA").Visible = False
Sheets("REPORT").Select
ActiveWorkbook.RefreshAll
MsgBox ("Done!")
End If
End Sub
Sub Up()
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
End Sub
Sub REMOVE()
Dim sh As Worksheet
Set sh = Worksheets("DATA")
'loop down column A
rowID = 5
While sh.Cells(rowID, 5) <> ""
'Remove A to Z
For i = 65 To 90
a = Replace(sh.Cells(rowID, 5), Chr(i), "")
sh.Cells(rowID, 5) = a
Next i
'Remove a to z
For i = 97 To 122
a = Replace(sh.Cells(rowID, 5), Chr(i), "")
sh.Cells(rowID, 5) = a
Next i
rowID = rowID + 1
Wend
End Sub