Exit sub with error message pop up

[iceblocks]

Hi All,

I have been given a file with this code. My understanding is that this macro update pivot tables when the correct "ID" is entered. However, because the ID are entered in manually, we had user entering un-identified ID or ID with missing digits and then the macro stop working and it requires some debugs action - which is freaking out staff.

As I am not great with macro, greatly appreciate advice on how to change this macro so that it will stop the macro and show a message box to say something like 'ID not found.' Many thanks in advance.

Sub update()

Application.ScreenUpdating = False
 
Dim control As Worksheet
Dim dataLink As Worksheet
Set control = Worksheets("control")
Set dataLink = Worksheets("data")
For i = 2 To 11
Sheets(control.Range("A" & i).Value).PivotTables(control.Range("B" & i).Value).PivotFields("ID").ClearAllFilters
Sheets(control.Range("A" & i).Value).PivotTables(control.Range("B" & i).Value).PivotFields("ID").CurrentPage = Sheets("control").Range("control!E1").Value
Next i
Application.ScreenUpdating = True
 
End Sub

 

[NARAYANK991]

Hi ,

Can you upload your workbook ?

Narayan

 

[iceblocks]

Hi Narayan,
As I just got given the workbook, I don't think I can upload the workbook as I am not quite sure how to delete some of the confidential data. I can do screen dump if this helps???
Iceblocks

 

[NARAYANK991]

Hi ,

Surely , anything that is not a formula can be deleted.

Debugging code can be done by reading through the code , visualizing the possible causes of the problem and making suggestions ; you will try out the suggestions and either report that the problem is solved or it is not. Based on this feedback , more suggestions can be given till your problem is resolved.

This is an iterative way of doing things , which can take days to resolve the problem.

If the complete workbook is available , it is far easier to execute the code , and debug the problem when program execution halts ; resolving your problem may happen in a few hours.

You can wait and see if any suggestions are forthcoming.

Narayan

 

[vletm]

@iceblocks
with out sample file, You could test this 'test_update()'
I couldn't test it ... as You know,
but it would give a message if something 'not good',
Good Luck.

Sub test_update()
    Application.ScreenUpdating = False
    With Worksheets("control")
        For i = 2 To 11
            On Error Resume Next
                .Sheets(.Range("A" & i)).PivotTables(.Range("B" & i)).PivotFields("ID").ClearAllFilters
            If Err.Number = 0 Then
                On Error Resume Next
                    .Sheets(.Range("A" & i)).PivotTables(.Range("B" & i)).PivotFields("ID").CurrentPage = .Range("E1")
                    MsgBox ("i(2): " & i & " gives " & Err.Number)
            Else
                MsgBox ("i(1): " & i & " gives " & Err.Number)
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

 

[iceblocks]

Thanks Vletm (and Narayan)!

Vletm, your 'On Error' codes prompted me to change to something rather more simple:

On Error Goto NoData
....
NoData:
MsgBox "Data not available.", vbOKOnly

Thank you so much Vletm
Iceblocks

 

[vletm]

@iceblocks - thank You
Sometimes it would be good to know, where do error comes ...
not just 'error'.