Run-time error '-2147417848 (80010108)'

N

nasa09

Registered User.
Local time
Today, 09:24
Joined
Jun 12, 2015
Messages
41
I'm having a problem that is driving me batty.

I have a workbook that has two worksheets with corresponding data, named Input and Data. I've written some code that is designed to update a particular cell in the active row on Input (using data from a cell in the corresponding row on Data) when a cell within a particular range is updated.

The code is very simple, but I'm receiving an error message that says the following:

Run-time error '-2147417848 (80010108)':
Method 'Range' of object '_Worksheet' failed
Click to expand...

The relevant section of code is directly below. Both inputRow and dataRow are declared and assigned earlier in the code. The rest of the code deals with changes in other cells, which are irrelevant to this operation.

Code: 
If Target.Column >= 10 And Target.Column <= 21 And (Target.Row >= 20 And Target.Row <= 119) Then


    Range("U" & inputRow) = Sheets("Data").Range("BL" & dataRow)

End If


To give full context, here's the entire sub. I don't see any conflicts, but perhaps someone else will.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim inputRow As Integer
Dim dataRow As Integer


inputRow = ActiveCell.Row
dataRow = inputRow - 5


If Target.Column = 6 Then
    
    If Target <> "Partial Payoff" Then
    
    Sheets("Data").Range("AS" & dataRow & ":AZ" & dataRow).ClearContents
    Sheets("Data").Range("BM" & dataRow & ":BV" & dataRow).ClearContents
    
    End If
    
End If





If Target.Column = 9 Then
    
    If Target = "Check" Then
    
    Sheets("Data").Range("M" & dataRow & ":U" & dataRow).ClearContents


    ElseIf Target = "Wire" Then

    Sheets("Data").Range("L" & dataRow).ClearContents
    Sheets("Data").Range("N" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("M" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)

    ElseIf Target = "Internal Transfer" Then

    Sheets("Data").Range("L" & dataRow & ":M" & dataRow).ClearContents
    Sheets("Data").Range("O" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("N" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)


    ElseIf Target = "Net Funding - FRB" Then

    Sheets("Data").Range("L" & dataRow & ":N" & dataRow).ClearContents
    Sheets("Data").Range("P" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("O" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    ElseIf Target = "Net Funding - INV" Then

    Sheets("Data").Range("L" & dataRow & ":O" & dataRow).ClearContents
    Sheets("Data").Range("Q" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("P" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)

    ElseIf Target = "Lockbox Reject" Then

    Sheets("Data").Range("L" & dataRow & ":P" & dataRow).ClearContents
    Sheets("Data").Range("R" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("Q" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    ElseIf Target = "Email" Then

    Sheets("Data").Range("L" & dataRow & ":Q" & dataRow).ClearContents
    Sheets("Data").Range("S" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("R" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    ElseIf Target = "HELOC in Repay - 0 Balance" Then

    Sheets("Data").Range("L" & dataRow & ":R" & dataRow).ClearContents
    Sheets("Data").Range("T" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("S" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    ElseIf Target = "Charged Off" Then

    Sheets("Data").Range("L" & dataRow & ":S" & dataRow).ClearContents
    Sheets("Data").Range("U" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("T" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    ElseIf Target = "Shortage GL" Then

    Sheets("Data").Range("L" & dataRow & ":T" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("U" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    Else

    
    Sheets("Data").Range("L" & dataRow & ":AZ" & dataRow).ClearContents

    
    End If
    
End If






If Target.Column >= 10 And Target.Column <= 21 And (Target.Row >= 20 And Target.Row <= 119) Then


    Range("U" & inputRow) = Sheets("Data").Range("BL" & dataRow)

End If


End Sub
 
Looks like this is a purely Excel VBA question? You've actually posted in the Access VBA forum ;)

I would use explicit references:
Code: 
[COLOR=Blue]Sheets("SheetName").[/COLOR]Range("U" & inputRow)[COLOR=Blue].Value[/COLOR] = Sheets("Data").Range("BL" & dataRow)[COLOR=Blue].Value[/COLOR]

Here's a tip (if you don't already know this):
Code: 
With Sheets("Data")
    .Range("L" & dataRow).ClearContents
    .Range("N" & dataRow & ":U" & dataRow).ClearContents
    .Range("V" & dataRow) = .Range("M" & dataRow)
End With
 
Oops...sorry for the error. I should have been a bit more aware.

Thank you both for your help and the tip. I'm still a novice at this stuff, obviously.

I'd actually tried something similar to your suggestion prior to posting, but I made the changes you recommended anyway. At first it seemed to work perfectly, but when I closed and the reopened the workbook I got the same error.
 
Ok, paste the entire Change event here with the suggested changes.
 
Here you go.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim inputRow As Integer
Dim dataRow As Integer


inputRow = ActiveCell.Row
dataRow = inputRow - 5


If Target.Column = 6 Then
    
    If Target <> "Partial Payoff" Then
    
    Sheets("Data").Range("AS" & dataRow & ":AZ" & dataRow).ClearContents
    Sheets("Data").Range("BM" & dataRow & ":BV" & dataRow).ClearContents
    
    End If
    
End If





If Target.Column = 9 Then
    
    If Target = "Check" Then
    
    Sheets("Data").Range("M" & dataRow & ":U" & dataRow).ClearContents


    ElseIf Target = "Wire" Then

    Sheets("Data").Range("L" & dataRow).ClearContents
    Sheets("Data").Range("N" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("M" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)

    ElseIf Target = "Internal Transfer" Then

    Sheets("Data").Range("L" & dataRow & ":M" & dataRow).ClearContents
    Sheets("Data").Range("O" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("N" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)


    ElseIf Target = "Net Funding - FRB" Then

    Sheets("Data").Range("L" & dataRow & ":N" & dataRow).ClearContents
    Sheets("Data").Range("P" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("O" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    ElseIf Target = "Net Funding - INV" Then

    Sheets("Data").Range("L" & dataRow & ":O" & dataRow).ClearContents
    Sheets("Data").Range("Q" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("P" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)

    ElseIf Target = "Lockbox Reject" Then

    Sheets("Data").Range("L" & dataRow & ":P" & dataRow).ClearContents
    Sheets("Data").Range("R" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("Q" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    ElseIf Target = "Email" Then

    Sheets("Data").Range("L" & dataRow & ":Q" & dataRow).ClearContents
    Sheets("Data").Range("S" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("R" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    ElseIf Target = "HELOC in Repay - 0 Balance" Then

    Sheets("Data").Range("L" & dataRow & ":R" & dataRow).ClearContents
    Sheets("Data").Range("T" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("S" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    ElseIf Target = "Charged Off" Then

    Sheets("Data").Range("L" & dataRow & ":S" & dataRow).ClearContents
    Sheets("Data").Range("U" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("T" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    ElseIf Target = "Shortage GL" Then

    Sheets("Data").Range("L" & dataRow & ":T" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow & ":U" & dataRow).ClearContents
    Sheets("Data").Range("V" & dataRow) = Sheets("Data").Range("U" & dataRow)
    Sheets("Input").Range("J" & Target.Row) = Sheets("Data").Range("V" & dataRow)
    

    Else

    
    Sheets("Data").Range("L" & dataRow & ":AZ" & dataRow).ClearContents

    
    End If
    
End If






If Target.Column >= 10 And Target.Column <= 21 And (Target.Row >= 20 And Target.Row <= 119) Then


    Sheets("Input").Range("U" & inputRow).Value = Sheets("Data").Range("BL" & dataRow).Value

End If


End Sub
 
You must log in or register to reply here.

Similar threads

Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
356
Gasman
Gasman
L
Retrieve excel from access (vol 4) , extended format , alternative color
Replies
7
Views
891
arnelgp
arnelgp
R
Solved Data Entry Without Userform
Replies
4
Views
749
Rania01
R
R
Solved Getting Error Intersect of Object Globad Failed
Replies
9
Views
1,027
Isaac
Isaac
G
Solved Increment a value
Replies
3
Views
646
goncalo
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom