Running code deletes If Error Formula

[ipbr21054]

Morning,
In my workbook i use two worksheets of which i transfer value from one to the other.
One is called DATABASE & the other INVOICE.

On the DATABASE sheet in cell B6 when i double click values are collected from certain cells in the same row & pasted to various cells on the INVOICE sheet.

On the INVOICE sheet i have If Error Formulas in various cells.

My issue is at the time when i double click should one cell not have a value "but it will later on" then the cell gets a pasted empty value on the INVOICE sheet thus deleting / overwritten the If Error Formula.
When i then enter that missing value later on becuase there is no If Error Formula then the cell stays empty.

Here is the double click code.
Is there a way that should a cell be empty of its value to then ignore it thus keeping / preserve the If Error Formula on the INVOICE sheet.

Basically add to the code If cell value is empty ignor the part to paste to the INVOICE sheet

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

   If Not Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then
 
      Cancel = True
 
      Database.LoadData Me, Target.Row
    
   ElseIf Not Intersect(Range("B6", Cells(Rows.Count, "B").End(xlUp)), Target) Is Nothing Then
  
      If Worksheets("INV").Range("G13").Value >= 1 Then
      MsgBox "UNABLE TO TRANSFER AS CUSTOMERS NAME FIELD ISNT EMPTY" & vbNewLine & vbNewLine & "YOU WILL NOW BE TAKEN TO THE INV SHEET", vbCritical, "INVOICE SHEET CELL ISNT EMPTY"
                                               
      Sheets("INV").Select
      Exit Sub
     
      Else
      Worksheets("INV").Range("G14:G18").Value = Application.Transpose(Worksheets("DATABASE").Cells(Target.Row, "R").Resize(, 5).Value)
     
      Worksheets("INV").Range("G13").Value = Worksheets("DATABASE").Cells(Target.Row, "A").Value ' CUSTOMER
      Worksheets("INV").Range("L14").Value = Worksheets("DATABASE").Cells(Target.Row, "D").Value ' VEHICLE
      Worksheets("INV").Range("L15").Value = Worksheets("DATABASE").Cells(Target.Row, "B").Value ' REGISTRATION
      Worksheets("INV").Range("L16").Value = Worksheets("DATABASE").Cells(Target.Row, "L").Value ' VIN
      Worksheets("INV").Range("L17").Value = Worksheets("DATABASE").Cells(Target.Row, "W").Value ' CONTACT NUMBER
      Sheets("INV").Select
     
   End If
   End If

End Sub

 

[James006]

Hi,

You can test following

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Range("A6", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then
      Cancel = True
      Database.LoadData Me, Target.Row
   ElseIf Not Intersect(Range("B6", Cells(Rows.Count, "B").End(xlUp)), Target) Is Nothing Then
      If Worksheets("INV").Range("G13").Value >= 1 Then
        MsgBox "UNABLE TO TRANSFER AS CUSTOMERS NAME FIELD ISNT EMPTY" & vbNewLine & vbNewLine & "YOU WILL NOW BE TAKEN TO THE INV SHEET", vbCritical, "INVOICE SHEET CELL ISNT EMPTY"
        Sheets("INV").Select
        Exit Sub
      Else
        If Not IsEmpty(Target.Value) Then
           With Worksheets("INV")
                .Range("G14:G18").Value = Application.Transpose(Worksheets("DATABASE").Cells(Target.Row, "R").Resize(, 5).Value)
                .Range("G13").Value = Worksheets("DATABASE").Cells(Target.Row, "A").Value ' CUSTOMER
                .Range("L14").Value = Worksheets("DATABASE").Cells(Target.Row, "D").Value ' VEHICLE
                .Range("L15").Value = Worksheets("DATABASE").Cells(Target.Row, "B").Value ' REGISTRATION
                .Range("L16").Value = Worksheets("DATABASE").Cells(Target.Row, "L").Value ' VIN
                .Range("L17").Value = Worksheets("DATABASE").Cells(Target.Row, "W").Value ' CONTACT NUMBER
                .Select
          End With
        End If
      End If
   End If
End Sub

 

[ipbr21054]

Hi
Same result in that the If formulas are still deleted etc

 

[James006]

Where are located these formulas which are getting deleted ... ?

Macro was modified to comply with your statement "Basically add to the code If cell value is empty ignor the part to paste to the INVOICE sheet"

 

[ipbr21054]

The If formulas are on the INVOICE sheet in the cells as in my first post.

G14:G18
L14

Etc etc

Example.
I complete all the cells on DATABASE worksheet.
The code copies the values from specific cells on but at present contact number isn’t present in its cell as I do t know the contact number.

I run the code & all the values copied are now in the correct place on the INVOICE sheet.
But remember I didn’t know the contact number so this cell at present has no value.
But I think the original code copy nothing & thus pasted nothing thus deleting the IF formula.

Makes sense ?