新聞財經娛樂♬資訊 News Economy

That makes sense ssaqibh, will remember that going fwd, thanks

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.

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.

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

Is there a way I can see the error happening? I am at a loss as to what is happening.

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

Do you get the error message on the real file only or the uploaded file as well?

Its not for me,

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

The macro is working just fine. It stops only at

Sheets("Analysis").Select

because there is no sheet called Analysis.

Yep, this is all you need i think



Thanks
Seamus

Is there any way to upload a sample which demonstrates the problem?

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

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?

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

=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

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.

It says

<expression not defined in context>

Thanks

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

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

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

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

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

set acell = activecell

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

Good post @write2mohit! I will definitely make use of this in the future!

Add this on the top of your code  or before your code