Macro run fail, run-time error 2147417848(80010108)

[f8alex]

Hi all,

having issues with excel VBA and any input for this will be much appreciated. i have searched existing threads and not able to find the fix for this issue.

here are the issues :

1 After saving the model, the macro run would fail, with error message below:
Run-time error ‘-2147417848(80010108)’:
Method ‘ClearContents’ of object ‘Range’ failed.

2 Once you’ve stopped a macro (VBA) (hence, the macro run didn’t successfully complete), the macro run will always fail later and hence, users will no longer be able to run the macro unless they re-open the model.

Thanks

 

[RoryA]

Welcome to the forum.

Are there merged cells in the model?

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-pr...ail-run-time-error-2147417848-80010108-a.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Michael M]

It also might help by posting the code !!

 

[f8alex]

Hi guys,

thanks and FYI, here is my another posting

https://www.mrexcel.com/forum/excel...ail-run-time-error-2147417848-80010108-a.html


Michael M,
will post the code in a while and thanks in advance.

Thanks

 

[RoryA]

Hi guys,

thanks and FYI, here is my another posting

https://www.mrexcel.com/forum/excel...ail-run-time-error-2147417848-80010108-a.html

No, that's this thread!

 

[f8alex]

No, that's this thread!

Hi Rory,

my bad and thanks for letting me know

https://www.mrexcel.com/forum/excel...ail-run-time-error-2147417848-80010108-a.html

Thanks

 

[f8alex]

Hi Michael,

Here are the codes for one of the macro file that giving intermittent errors code. i'm not sure if the uploaded code looked ok?

Sub Portfolio()
 
Dim TotalRun As Integer, StartNo As Integer
 
Dim i As Integer
Dim r As Integer
 
Dim t As Date
t = Now()
 
Dim j As Integer
Dim WB As Workbook
Dim Checklist As Variant
Dim temp, temp1, temp2 As Variant
 
Checklist = MsgBox("Have you finished the user checklist?", vbYesNo, "Checklist")
 
If Checklist = vbYes Then
    GoTo Start
    Else
    GoTo TheEnd
End If
 
Start:
 
Application.Calculation = xlCalculationManual
 
Application.ScreenUpdating = False
 
Application.Calculate
 
StartTime = Timer
 
    TotalRun = Range("TotalRun")
    startrunno = Range("StartRunNo")
    endrunno = Range("EndRunNo")
   
    Range("AggProfitTest").ClearContents
    Range("ROC_output").ClearContents
   
If endrunno > TotalRun Then
MsgBox ("EndRunNo is bigger than TotalRunNo, please check again!")
Exit Sub
 
End If
 
Application.DisplayStatusBar = True
status_temp = Application.StatusBar
 
For i = startrunno To endrunno
  
    
    Application.StatusBar = "Running Model Point " & i
   
    Worksheets("Input").Range("SerialNo") = i
   
    Application.Calculate
   
    temp = Worksheets("Aggregate").Range("AggProfitTest").Value
    temp1 = Worksheets("ProfitTest").Range("IndProfitTest").Value
   
    ReDim temp2(1 To UBound(temp, 1), 1 To UBound(temp, 2)) As Double
   
    
    For x = LBound(temp, 1) To UBound(temp, 1)
    For y = LBound(temp, 2) To UBound(temp, 2)
        
    temp2(x, y) = Nz(temp(x, y), 0#) + Nz(temp1(x, y), 0#)
   
    Next y
    Next x
 
    Worksheets("Aggregate").Range("AggProfitTest").Value = temp2
   
    Worksheets("Output").Range("A9:E9").Offset(i, 0).Value = Worksheets("ProfitTest").Range("IndivProfitTest").Value
   
    
    
Next i
 
Application.Calculate
 
Application.ScreenUpdating = True
Application.StatusBar = status_temp
 
MsgBox Format(Now() - t, "hh:mm:ss")
 
TheEnd:
 
End Sub
 
Public Function Nz(Value As Variant, ValueIfNull As Variant) As Variant
If IsError(Value) Then
Nz = ValueIfNull
ElseIf IsNull(Value) Or IsEmpty(Value) Or Value = "" Then
Nz = ValueIfNull
Else
Nz = Value
End If
 
End Function

 

[RoryA]

Hi Rory,

my bad and thanks for letting me know

https://www.mrexcel.com/forum/excel...ail-run-time-error-2147417848-80010108-a.html

Thanks

That's still this thread...

 

[f8alex]

That's still this thread...

Rory, sorry.

https://www.excelforum.com/excel-pr...ail-run-time-error-2147417848-80010108-a.html