HELP: VBA Run-time error '-2147417848 (80010108)'

[xunda_gunda]

Hello,

I am trying to run simple macro but I get this error:

VBA Run-time error '-2147417848 (80010108)':
Method 'Range' of object '_Worksheet' failed

My procedure is following:

Sub Fill_Empty_Cells_with_Formulas()
    Application.ScreenUpdating = False
    Dim rng As Range
    Dim i As Long
    Set rng = Workbooks("Book1").Worksheets(1).Range("D15:D139")
        For Each cell In rng
            If cell.Value = "" Then
                cell.Offset(0, 0).Value = "=IF(ISTEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)),"""",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)<12,"""",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)<=$D$8,"""",IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE),-12,2)<>"""",MIN(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE),-12,2),INDIRECT(ADDRESS(ROW()-1,COLUMN()+3,4),TRUE)),""""))))"
            End If
        Next
    Application.OnTime Now + TimeValue("00:00:05"), "Fill_Empty_Cells_with_Formulas"
    Application.ScreenUpdating = True
End Sub

I just want that this code run in range mentioned above - "D15:D139".

What am I doing wrong?

Please help

 

[MickG]

Perhaps:-

    Set rng = Workbooks("Book1.xlsm").Worksheets(1).Range("D15:D139")

 

[Colorations]

Is there a chance that your workbook/worksheet is protected? If not maybe you have error or invalid values in that range

 

[xunda_gunda]

Perhaps:-

    Set rng = Workbooks("Book1[COLOR=#FF0000].xlsm[/COLOR]").Worksheets(1).Range("D15:D139")

I corrected but same error.

Sub Fill_Empty_Cells_with_Formulas()    Application.ScreenUpdating = False
    Dim rng As Range
    Dim i As Long
    Set rng = Workbooks("Book1.xlsm").Worksheets(1).Range("D15:D139")
        For Each cell In rng
            If cell.Value = "" Then
                cell.Offset(0, 0).Value = "=IF(ISTEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)),"""",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)<12,"""",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)<=$D$8,"""",IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE),-12,2)<>"""",MIN(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE),-12,2),INDIRECT(ADDRESS(ROW()-1,COLUMN()+3,4),TRUE)),""""))))"
            End If
        Next
    Application.OnTime Now + TimeValue("00:00:05"), "Fill_Empty_Cells_with_Formulas"
    Application.ScreenUpdating = True
End Sub

Is there a chance that your workbook/worksheet is protected? If not maybe you have error or invalid values in that range

No workbook is not protected. In that range I have Formulas or Numbers. This code should find blank cells (if there are any) and insert formula.

 

[Colorations]

try changing

for each cell in rng

to

for each cell in rng.cells

 

[xunda_gunda]

try changing

for each cell in rng

to

for each cell in rng.cells

Thanks for trying but same error

[COLOR=#333333]Set rng = Workbooks("Book1.xlsm").Worksheets(1).Range("D15:D139")[/COLOR]

This part have problem
But can't understand what exactly

 

[xunda_gunda]

Any help ?