Error message index is out of interval

[wal_verin]

Hi please, could someone help me out, I have a vba code where i am trying to delete the entries not found in sheet 2 but found in sheet 1. The only problem is they are not of the same order meaning som rows are empty in sheet 1 but basically almost the same data. This is the code i have for this but it says error message 9 "Subscript out of range" or "Index is out of interval"

ElseIf Kontonr = w.Range("A" & LRow & ":C" & LRow).Value <> v.Range("A" & Cell.Row & ":C" & Cell.Row).Value Then
Kontonr.EntireRow.Delete

What can i do to get this running?

 

[gallen]

You don't show your full code. Is it in a loop? If so you have to ensure you loop from the bottom up because you are deleting rows and as such will change row numbers of all items below the deleted row

 

[wal_verin]

Hi Gallen, Thanks for taking the time to help this is my entire code

Sub Button_replace_and_check()
Dim v As Worksheet, w As Worksheet
Dim LRow As Long, EndRow As Long
Dim Cell As Range, i As Long
Dim Kontonr As Variant
Dim Target As Variant




Application.ScreenUpdating = False
Application.EnableEvents = False


'Change the link to where you saved your files
Set v = Worksheets("Sheet 2")
Set w = Worksheets("Sheet 1")




'Avoiding the buttons and rows with headings
LRow = w.Cells(w.Rows.Count, "A").End(xlUp).Row


'This is the for Statement
For Each Cell In v.Range("A1:A" & LRow)
For i = 8 To LRow
'If there is an Error do this
On Error Resume Next
'Set whenever you are dealing with Variant or Range
Set Kontonr = w.Range("A8:A200").Find(Cell.Value)
Set Target = v.Range("A:A").Find(w.Cells(i, 1), lookat:=xlWhole, LookIn:=xlValues)


If Not Kontonr Is Nothing Then
w.Range("C" & Kontonr.Row + 1).Value = v.Range("F" & Cell.Row & ":F" & Cell.Row).Value
ElseIf Target Is Nothing Then
w.Cells(i, 1).EntireRow.Interior.Color = vbYellow
Else
LRow = w.Cells(w.Rows.Count, "B").End(xlUp).Row + 1
w.Range("A" & LRow & ":C" & LRow).Value = v.Range("A" & Cell.Row, ":C" & Cell.Row).Value
End If
Next i
Next Cell
MsgBox "De gula markerade rader finns inte i den aktuella balansen, då ska de tas bort"
Application.ScreenUpdating = True
Application.EnableEvents = True

 

[wal_verin]

This is an update as to what is happening now. I was forced to change from deleting rows to coloring them since i had difficulties to sort out the problem. The problem now that i have is that the Else statement is not working. Please see the codes below, hope someone can help

Hi Gallen, Thanks for taking the time to help this is my entire code

Sub Button_replace_and_check()
Dim v As Worksheet, w As Worksheet
Dim LRow As Long, EndRow As Long
Dim Cell As Range, i As Long
Dim Kontonr As Variant
Dim Target As Variant




Application.ScreenUpdating = False
Application.EnableEvents = False


'Change the link to where you saved your files
Set v = Worksheets("Sheet 2")
Set w = Worksheets("Sheet 1")




'Avoiding the buttons and rows with headings
LRow = w.Cells(w.Rows.Count, "A").End(xlUp).Row


'This is the for Statement
For Each Cell In v.Range("A1:A" & LRow)
For i = 8 To LRow
'If there is an Error do this
On Error Resume Next
'Set whenever you are dealing with Variant or Range
Set Kontonr = w.Range("A8:A200").Find(Cell.Value)
Set Target = v.Range("A:A").Find(w.Cells(i, 1), lookat:=xlWhole, LookIn:=xlValues)


If Not Kontonr Is Nothing Then
w.Range("C" & Kontonr.Row + 1).Value = v.Range("F" & Cell.Row & ":F" & Cell.Row).Value
ElseIf Target Is Nothing Then
w.Cells(i, 1).EntireRow.Interior.Color = vbYellow
Else
LRow = w.Cells(w.Rows.Count, "B").End(xlUp).Row + 1
w.Range("A" & LRow & ":C" & LRow).Value = v.Range("A" & Cell.Row, ":C" & Cell.Row).Value
End If
Next i
Next Cell
MsgBox "De gula markerade rader finns inte i den aktuella balansen, då ska de tas bort"
Application.ScreenUpdating = True
Application.EnableEvents = True

 

[wal_verin]

Hello,
I got the problem of Error message index is out of interval sorted out. The updated code is above but i still have a few challenges.

I have spent hours of trying to figure out how to get this running effectively but to no avail. I received valuable help from Tim_Excel_ but i have modified the codes to match my needs and they are a few challenges i am facing now: It refuses to highlight the rows that are not in sheet 2 and then it takes like forever to give the output.

I appreciate any efforts to help me out. THanks in advance