Run-time error '-2147417848 (80010108)

[SanFelippo]

Hi,

I am so confused as to why I am getting this error, and have no idea what it means. The code I am running is so, sooooo simple, so I don't understand why it is erroring out.

When the code below is run (aka when something on the sheet is changed), and C5 equals 1, it does set B6 equal to "Not Applicable." However, right after it changes B6 to "Not Applicable", I get this error:


Run-time error '-2147417848 (80010108)

Method 'Value' of object 'Range' failed.


If I change the code so that it changes a different cell to "Not Applicable" though, it works. It seems to only be happening when I try and change Cell B6....


I am literally stumped here. This error never occurred on any of the other sheets in the workbook. Now however, if I try and run this code on any of the other sheets, this error pops up. It seems like it came out of nowhere.

Any help?

Private Sub Worksheet_Change(ByVal Target As Range)
'Application.EnableEvents = False
    If Range("C5").Value = 1 Then
        Range("B10").Value = "Not Applicable"
    
    End If
'Application.EnableEvents = True
End Sub

 

[tonyyy]

SanFelippo,

One situation that will generate the error is if Conditional Formatting is applied to the cell. Try deleting Conditional Formatting, if it's on.

Cheers,

tonyyy

 

[DONALS]

hi! i'm also having the same error, but not exactly the same because its about pivot table. this is the error.

Run-time error '-2147417848 (80010108)

Method 'CreatePivotTable' of object 'PivotCache' failed


code:

Sub PIVOT()
'
' PIVOT Macro
'


'
    Sheets("TB DATA").Select
    Rows("1:9").Select
    Selection.Delete Shift:=xlUp
    Range("M1").Value = "Region"
    Range("L1").Copy
    Range("M1").PasteSpecial xlPasteFormats
    Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-12],'do not delete'!C[-12]:C[-11],2,0)"
    lastrow = Cells(Range("A:A").Rows.Count, 1).End(xlUp).Row
    Range("M2").Copy Destination:=Range(Cells(2, 13), Cells(lastrow, 13))
    
    'Sheets.Add
    'ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "1!R1C1:R948C13", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet3!R3C1", TableName:="PivotTable1", DefaultVersion:=6
    'Sheets("Sheet3").Select
    
    Sheets("TB DATA").Select
    
 
    lastrow = Cells(Range("A:A").Rows.Count, 1).End(xlUp).Row
    
    Range(Cells(1, 13), Cells(lastrow, 1)).Select
    
        Set PRangeTotal = Range(Selection, Selection)
        
    
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        PRangeTotal).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1"
        
      
      
    Cells(3, 1).Select


    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")
        .Orientation = xlRowField
        .Position = 1
    End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Business Unit")
        .Orientation = xlRowField
        .Position = 2
    End With


    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Period Amount"), "Sum of Period Amount", xlSum




    ActiveSheet.PivotTables("PivotTable1").PivotFields("Category").PivotItems( _
        "Net Asset").Position = 1
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Category").PivotItems( _
        "Revenue Operating").Position = 2
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Category").PivotItems( _
        "Account 49xxxx").Position = 3
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Category").PivotItems( _
        "Account 59xxxx").Position = 4
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Category").PivotItems( _
        "Revenue Non-Operating").Position = 5
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Category").PivotItems( _
        "Expense A/C50xxx to 5899xx").Position = 6




    With ActiveSheet.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").RepeatLabels = _
        True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Subtotals = Array( _
        True, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "Region[All;Total]", _
        xlDataAndLabel, True
    'delete ito pag mali
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With

 

[SanFelippo]

I was able to get around mine with a coding work around. Tried getting rid of the Cond. Formatting and it still popped up, so I just added this bit of coding so I could be done with it.

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#FF0000]On Error GoTo EndNow:
  Application.EnableEvents = False[/COLOR]
    If Range("C5").Value = 1 Then
        Range("B6").Value = "Not Applicable"
    
    End If

[COLOR=#FF0000]EndNow:
  Application.EnableEvents = True[/COLOR]
End Sub

 

[tonyyy]

SanFelippo - Glad you found a solution...