Run time error 13 advise please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,699
Office Version
  1. 2007
Platform
  1. Windows
Hi.
Today ive come across Run time error 13 on my worksheet.
SEE ATTACHED SCREENSHOTS TO ASSIS YOU

Clicking in cekk K4 i see this code in the address bar =SUM(#REF!:OFFSET(K28,,0))

The code in use is supplied below
When i debug the line in red below is shown in yellow


Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' THIS WILL COLOUR ACTIVE CELL & KEEP INTERIOR COLOUR ONCE LEFT HAS BEEN LEFT
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "NEVER" Or Target.Value = "TBA" Or Target.Value Like "2###" Then Exit Sub


    Application.ScreenUpdating = False
   
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "K"

'   *** Specify start row ***
    myStartRow = 7
   
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
   
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
   
    '   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
   
    With Target
        .Worksheet.Cells.FormatConditions.Delete
        .FormatConditions.Add xlExpression, , True
        .FormatConditions(1).Interior.Color = vbWhite
    End With
   
    With Sheet7.DTPicker1
    .Height = 40
    .Width = 40
    If Not Intersect(Target, Range("G7:H40")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.ADDRESS
    Else
      .Visible = False
    End If
  End With
     
End Sub

DOWNLOAD SAMPLE HERE

Its supposed to shown a outstanding balance in cell K4
 

Attachments

  • 3689.jpg
    3689.jpg
    19.9 KB · Views: 15
  • 3690.jpg
    3690.jpg
    70.3 KB · Views: 20

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You need to handle error values in cells separately. What do you want to happen if the cell contains an error? Exit the routine?
 
Upvote 0
I need to see a balance but if there is an issue at least be advised of it.

I think the code in cell K4 should be =SUM(K7:OFFSET(K35,,0))

Then apply what you were thinking of
Thanks
 
Upvote 0
That doesn't really answer my question. If the formula in the cell were valid, you wouldn't have the problem, so the question is what you want the code to do in this case? It's not going to fix that formula unless you can specify the logic for how it should handle any formula errors.
 
Upvote 0
Not sure how i should answr that.
Can we have a MsgBox ?
Or advise an option please
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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
Back
Top