• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

ignore warning vba

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
Seamus2626
Asked:
Seamus2626
  • 14
  • 11
  • 2
  • +3
3 Solutions
 
duncanb7Commented:
Add this on the top of your code  or before your code

Application.DisplayAlerts = False
0
 
Anthony BerenguelCommented:
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
 
Guru JiCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Anthony BerenguelCommented:
Good post @write2mohit! I will definitely make use of this in the future!
0
 
Seamus2626Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
set acell = activecell
0
 
Seamus2626Author Commented:
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
 
byundtCommented:
If you have a dynamic named range called myDynamicNamedRange, you could use:
Set arrange = ActiveWorkbook.Names("myDynamicNamedRange").RefersToRange

Open in new window

0
 
Seamus2626Author Commented:
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
 
byundtCommented:
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
 
Seamus2626Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Seamus2626Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
Instead of hovering the mouse, try selecting the Arange and press Shift-F9 to see what is happening.
0
 
Seamus2626Author Commented:
It says

<expression not defined in context>

Thanks
0
 
Saqib Husain, SyedEngineerCommented:
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
 
Seamus2626Author Commented:
=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
 
Seamus2626Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Seamus2626Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
Is there any way to upload a sample which demonstrates the problem?
0
 
Seamus2626Author Commented:
Yep, this is all you need i think



Thanks
Seamus
Analysis-Template-EE.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
The macro is working just fine. It stops only at

Sheets("Analysis").Select

because there is no sheet called Analysis.
0
 
Seamus2626Author Commented:
Its not for me,

the error message "unprotected Formula" is still in all the cells in the range
0
 
Saqib Husain, SyedEngineerCommented:
Do you get the error message on the real file only or the uploaded file as well?
0
 
Seamus2626Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
Is there a way I can see the error happening? I am at a loss as to what is happening.
0
 
Seamus2626Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Seamus2626Author Commented:
That makes sense ssaqibh, will remember that going fwd, thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 14
  • 11
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now