Run Time Error on Large Loop, Clear Cell Contents if it Contains

[OilEconomist]

Thanks in advance for everyone's help. I will post feedback on any possible solutions given.

I'm having a bit of trouble with some code I put together and I already have had some help with it.

First Issue:
The line within the following code is giving me a run time error, and when I hit debug it seems to complete, but it won't run the rest of the code.

Error: "Run-time error '1004': Application-defined or object-defined error"

Code of Line with Error:

 If Cells(i, 18).Value = "Total" And Cells(i - 1, 21) <> "" Then

Second Issue:
If the value "NonDisc. CFAnnual(M$)" appears in any cell within column R, and or the value " CumDisc.CF (M$) " appears in any cell within column S, I want to clear those cells, but nothing happens. Those cells are a part of a data set which come from a database program so the formatting is an issue. It has some hard returns, so I though maybe if I could just search for the first few letters such as “Non Disc.” versus "NonDisc. CFAnnual(M$)" and “Cum” versus " CumDisc.CF (M$) ", but with what I found on the interent and within this website, I could not piece anything together to work. Here is that code with that error:

                 If Cells(i, 18).Value = "NonDisc. CFAnnual(M$)" And Cells(i, 2).Value = "CumDisc.CF M$)" Then
                Cells(i, 18).Value = "" And Cells(i, 19).Value = ""

Code in it's Entirety

Sub Total()

    'Activate the sheet
        Worksheets("Data.Raw.2").Activate


'Find the last row.
        Dim LastRow As Long            
        'LastRow = Cells(Rows.Count, 18).End(xlUp).Row
            
        LastRow = Cells.Find(What:="*", _
        after:=Range("A1"), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
        
    'Find the last column.
        Dim LastColumn As Long
        
        LastColumn = Cells.Find(What:="*", _
        after:=Range("A1"), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
        
    
    'Fix Total Line within the Data Set
        
        Dim i As Long
        
        For i = LastRow To 1 Step -1
                    
           'Fixes total line if it is offset by one row
                If Cells(i, 18).Value = "Total" And Cells(i - 1, 21) <> "" Then
        
                    Range("U" & i - 1, Cells(i - 1, LastColumn)).Cut
                    Range("U" & i, Cells(i, LastColumn)).Select
                    ActiveSheet.Paste
                    
                End If
                
        Next i
                       
            
        'For i = LastRow To 1 Step -1
                
                If Cells(i, 18).Value = "NonDisc. CFAnnual(M$)" And Cells(i, 2).Value = "CumDisc.CF M$)" Then
                Cells(i, 18).Value = "" And Cells(i, 19).Value = ""
                
                    
                End If
                
        Next i
            


    'Move Total Line within the Data Set
        
        
        For i = LastRow To 1 Step -1
        
           If LCase(Cells(i, 18).Value) = "total" Then
        
        
           Range("R" & i, Cells(i, LastColumn)).Cut
             
           Range("A" & i + 1).Insert xlShiftDown
                      
           
        End If
        
        Next i
        


End Sub

Once again thanks and I look forward to anyone's help.

 

[Michael M]

For the first issue, change this line to

For i = LastRow To [color=red]2[/color] Step -1

If you step to line 1 then Cells(i - 1 doesn't exist therefore an error.
2nd issue
change this line

Cells(i, 18).Value = "" And Cells(i, 19).Value = ""

to

Range(Cells(i, 18),Cells(i, 19)).Value = ""

 

[OilEconomist]

Thanks so much Michael M!

Your first suggestion in Post #2 fixed the main issue and it also fixed the second issue because the rows with those values were eventually deleted once the code was able to run all the way through.

Now with that being said, your second suggestion to fix my second issue did not seem to work and i think it's the way the data is formatted. For now, I'm not concerned as I mentioned previously, it worked itself out. Since you did take the time out of your schedule to help, I am willing to delete everything from the spreadsheet except those cells and send to you if you really want the challenge of fixing it.

Once again, Thanks!

 

[Michael M]

Is it a formatting of the cell issue ?
If so whayt are the formats causing it not to work ?

 

[OilEconomist]

I am not sure. I offered up to send you the spreadsheet in the case you wanted to figure it out.