Solved

ignore warning vba

Posted on 2013-11-18
31
394 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
  • 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 167 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
 
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 80

Assisted Solution

by:byundt
byundt earned 167 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 80

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 166 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now