?
Solved

ignore warning vba

Posted on 2013-11-18
31
Medium Priority
?
430 Views
Last Modified: 2013-11-22
Hi,

I get the warning "unprotected formula" in a yellow box in the top left hand corner of my vlookups etc, i manually click on these to ignore, can i do this using VBA

My sub is below

Many thanks

--------------------------------

Sub FillOutFomrula()

Dim LastR As Long

Sheets("D2 Data").Select


    With ActiveSheet
        LastR = .Cells(.Rows.Count, "A").End(xlUp).Row



Range("DW2").Formula = "=VLOOKUP(B2,'Calculation D2'!A:BG,29,0)"
Range("DX2").Formula = "=VLOOKUP(D2,'Lookup tables'!$C$5:$E$30,2,0)"
Range("DY2").Formula = "=CONCATENATE(DQ2,"" to "",DW2)"
Range("DZ2").Formula = "=IF(OR(DY2=""STANDARD to Standard"",DY2=""MEDIUM to medium"",DY2=""HIGH to High""),""No Change"",DY2)"
Range("EA2").Formula = "='Calculation D2'!AM2"
Range("EB2").Formula = "='Calculation D2'!AS2"
Range("EC2").Formula = "='Calculation D2'!AU2"
Range("ED2").Formula = "='Calculation D2'!BG2"


   Range("DW2:ED2").AutoFill Destination:=Range("DW2:ED" & LastR)
    End With


Sheets("Analysis").Select
AllWorksheetPivots

MsgBox "Analysis Ready"

End Sub
0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 11
  • 2
  • +3
31 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 39656996
Add this on the top of your code  or before your code

Application.DisplayAlerts = False
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39657012
Also, I would recommend turning the warnings back on at the end of your code

'before you code executes
Application.DisplayAlerts = False

'your code

'after your code executres
Application.DisplayAlerts = True

Open in new window

0
 
LVL 11

Accepted Solution

by:
Guru Ji earned 501 total points
ID: 39657013
You don't want to disable to Alerts instead implement this in your code

Sub IgnoreErrCheckingOnRange(aRange As Range, bIgnore As Boolean)
    '-- This switch off/on all 8 types of error checking on each cell in a specified range.
    Dim aCell As Range, i As Long
    
    For Each aCell In aRange.Cells
        For i = 1 To 8
           aCell.Errors(i).Ignore = bIgnore
        Next
    Next
End Sub

Sub Test()
    IgnoreErrCheckingOnRange Sheet1.Range("A1:B10"), True
End Sub

Open in new window


Code found in vbforums posted by anhn.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 39657023
Good post @write2mohit! I will definitely make use of this in the future!
0
 

Author Comment

by:Seamus2626
ID: 39657040
That looks really good, im just struggling to work in acell and arange

I tried acell = activecell but that wont work

Do you know how i define the cell and the range?

The range will change according to
Range("DW2:ED2").AutoFill Destination:=Range("DW2:ED" & LastR)


Any advice?

Thanks
Seamus
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39657201
set acell = activecell
0
 

Author Comment

by:Seamus2626
ID: 39657244
Thanks ssaqibh, for the range, am i able to use a dynamic named range

eg

Set arange = Range

where range is a dynamic named range

Thanks
Seamus
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 501 total points
ID: 39658211
If you have a dynamic named range called myDynamicNamedRange, you could use:
Set arrange = ActiveWorkbook.Names("myDynamicNamedRange").RefersToRange

Open in new window

0
 

Author Comment

by:Seamus2626
ID: 39658660
Hi all,

My sub is being stopped at

 For Each aCell In Arrange.Cells

Error is "object doesnt support property or method"

The named range is working, so im not sure what the error is about....

many thanks

--------------------------------------

Sub FillOutFomrula()

Dim LastR As Long
Dim aCell As Range, i As Long

Set aCell = ActiveCell
Set Arrange = ActiveWorkbook.Names("Range").RefersToRange



Sheets("D2 Data").Select




    With ActiveSheet
        LastR = .Cells(.Rows.Count, "A").End(xlUp).Row



Range("DW2").Formula = "=VLOOKUP(B2,'Calculation D2'!A:BG,29,0)"
Range("DX2").Formula = "=VLOOKUP(D2,'Lookup tables'!$C$5:$E$30,2,0)"
Range("DY2").Formula = "=CONCATENATE(DQ2,"" to "",DW2)"
Range("DZ2").Formula = "=IF(OR(DY2=""STANDARD to Standard"",DY2=""MEDIUM to medium"",DY2=""HIGH to High""),""No Change"",DY2)"
Range("EA2").Formula = "='Calculation D2'!AM2"
Range("EB2").Formula = "='Calculation D2'!AS2"
Range("EC2").Formula = "='Calculation D2'!AU2"
Range("ED2").Formula = "='Calculation D2'!BG2"


   Range("DW2:ED2").AutoFill Destination:=Range("DW2:ED" & LastR)
    End With
 
 For Each aCell In Arrange.Cells
        For i = 1 To 8
           aCell.Errors(i).Ignore = bIgnore
        Next
    Next


Sheets("Analysis").Select
AllWorksheetPivots

MsgBox "Analysis Ready"

End Sub
0
 
LVL 81

Expert Comment

by:byundt
ID: 39660653
Try declaring Arrange:
Dim Arrange As Range

Open in new window


If that doesn't fix the problem, could you please post a sample workbook that replicates the problem?
0
 

Author Comment

by:Seamus2626
ID: 39661796
Thanks byundt, that never fixed the errors

Attached is the ss, i have removed sensitive data, so currently the lookups etc have ref errors, but with the correct data, it is the "unprotected formula error"

Many thanks

Seamus
Analysis-Template-EE.xlsm
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 498 total points
ID: 39663164
The code worked just fine with meon the given sheet. It only stopped at

Sheets("Analysis").Select

because there was no sheet with that name.
0
 

Author Comment

by:Seamus2626
ID: 39665918
Its my range, its not reading my range.

I have a dynamic offset range

=OFFSET('D2 Data'!$DW$2,0,0,COUNTA('D2 Data'!$DW:$DW),8)

That works fine and picks up the correct range.

When i hover over Arange in the code, it does not have anything there, whereas aCell refers to the current cell, is there anything wrong with my offset or is there any reason why the range is not being found?

Many thanks
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39666165
Instead of hovering the mouse, try selecting the Arange and press Shift-F9 to see what is happening.
0
 

Author Comment

by:Seamus2626
ID: 39666273
It says

<expression not defined in context>

Thanks
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39666451
Put a stop before the line
Set Arrange = ActiveWorkbook.Names("Range").RefersToRange

and break down the formula
=OFFSET('D2 Data'!$DW$2,0,0,COUNTA('D2 Data'!$DW:$DW),8)

and see what each component is returning.
0
 

Author Comment

by:Seamus2626
ID: 39666535
=OFFSET('D2 Data'!$DW$2,0,0,COUNTA('D2 Data'!$DW:$DW),8)

 that works fine, that is returning the correct range of data

The problem is line
Set Arrange = ActiveWorkbook.Names("Range").RefersToRange


It is not setting Arrange correctly

Thanks
0
 

Author Comment

by:Seamus2626
ID: 39666554
Dim LastR As Long
Dim aCell As Range, i As Long

Is this part of the sub correct, where does it set Arange as a data type?

Thanks
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39666579
If you do not have option explicit set then the program will work even without the dim

also make sure that you have the spelling correct. Is it Arange or Arrange?
0
 

Author Comment

by:Seamus2626
ID: 39666587
Spelling looks good and i have no option explicit

Sub FillOutFomrula()

Dim LastR As Long
Dim aCell As Range, i As Long

Set aCell = ActiveCell
Set aRange = ActiveWorkbook.Names("Range").RefersToRange



Sheets("D2 Data").Select




    With ActiveSheet
        LastR = .Cells(.Rows.Count, "A").End(xlUp).Row



Range("DW2").Formula = "=VLOOKUP(B2,'Calculation D2'!A:BG,29,0)"
Range("DX2").Formula = "=VLOOKUP(D2,'Lookup tables'!$C$5:$E$30,2,0)"
Range("DY2").Formula = "=CONCATENATE(DQ2,"" to "",DW2)"
Range("DZ2").Formula = "=IF(OR(DY2=""STANDARD to Standard"",DY2=""MEDIUM to medium"",DY2=""HIGH to High""),""No Change"",DY2)"
Range("EA2").Formula = "='Calculation D2'!AM2"
Range("EB2").Formula = "='Calculation D2'!AS2"
Range("EC2").Formula = "='Calculation D2'!AU2"
Range("ED2").Formula = "='Calculation D2'!BG2"


   Range("DW2:ED2").AutoFill Destination:=Range("DW2:ED" & LastR)
    End With
 
 For Each aCell In aRange.Cells
        For i = 1 To 8
           aCell.Errors(i).Ignore = bIgnore
        Next
    Next


Sheets("Analysis").Select
AllWorksheetPivots

MsgBox "Analysis Ready"

End Sub
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39666607
Is there any way to upload a sample which demonstrates the problem?
0
 

Author Comment

by:Seamus2626
ID: 39666622
Yep, this is all you need i think



Thanks
Seamus
Analysis-Template-EE.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39666647
The macro is working just fine. It stops only at

Sheets("Analysis").Select

because there is no sheet called Analysis.
0
 

Author Comment

by:Seamus2626
ID: 39666681
Its not for me,

the error message "unprotected Formula" is still in all the cells in the range
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39666731
Do you get the error message on the real file only or the uploaded file as well?
0
 

Author Comment

by:Seamus2626
ID: 39666766
YOu see the uploaded file is going to have ref and value errors where i have got rid of the underlying data

So for my purposes i can just untick the option in error checking for "unprotected Formula" in options. But in other peoples spreadsheets they will have that option automatically checked, so it will show the error sign

Its not one of the #NA, REF errors etc, its the error that just says the formula is not protected
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39666784
Is there a way I can see the error happening? I am at a loss as to what is happening.
0
 

Author Comment

by:Seamus2626
ID: 39666856
So if you go to excel options, then formulas, you will see a sextion for error checking rules.

There is 9 of them including the error "unlocked cells containing formula"

I want would like to in essence turn that option off for a user, so they dont have the error messages, but yet i dont want to change their settings, so only want that error not to display for the instance they use my spreadsheet

Does that make sense?!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39666910
You might like to consider using the

Private Sub Workbook_Activate()

and the

Private Sub Workbook_Deactivate()

event handlers to enable/disable the error checking.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39668338
Apparently you have not got a solution to your problem and that is why you have given a B grade. If this is the case then it would have been better to delete the question instead of accepting as it is misleading to anyone reading this question in future.
0
 

Author Comment

by:Seamus2626
ID: 39668346
That makes sense ssaqibh, will remember that going fwd, thanks
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question