This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

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

End With

Sheets("Analysis").Select

AllWorksheetPivots

MsgBox "Analysis Ready"

End Sub

```
'before you code executes
Application.DisplayAlerts = False
'your code
'after your code executres
Application.DisplayAlerts = True
```

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

Any advice?

Thanks

Seamus

eg

Set arange = Range

where range is a dynamic named range

Thanks

Seamus

```
Set arrange = ActiveWorkbook.Names("myDynamicNamedRange").RefersToRange
```

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("Rang

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

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

```
Dim Arrange As Range
```

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

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

Sheets("Analysis").Select

because there was no sheet with that name.

I have a dynamic offset range

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

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

Instead of hovering the mouse, try selecting the Arange and press Shift-F9 to see what is happening.

Set Arrange = ActiveWorkbook.Names("Rang

and break down the formula

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

and see what each component is returning.

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

The problem is line

Set Arrange = ActiveWorkbook.Names("Rang

It is not setting Arrange correctly

Thanks

Dim aCell As Range, i As Long

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

Thanks

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

Sub FillOutFomrula()

Dim LastR As Long

Dim aCell As Range, i As Long

Set aCell = ActiveCell

Set aRange = ActiveWorkbook.Names("Rang

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

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

Sheets("Analysis").Select

because there is no sheet called Analysis.

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

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?!

Private Sub Workbook_Activate()

and the

Private Sub Workbook_Deactivate()

event handlers to enable/disable the error checking.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Open in new window

Code found in vbforums posted by anhn.