Unknown error - crashes workbook everytime."

[georgep93]

Hi all,

Sorry to bother people with this, but I cannot seem to find what keeps crashing Excel in the following code. I already know the code is horrendous to look at as i'm not amazing at this, so if anyone could provide tips on keeping it efficient that would be awesome too.

Private Sub updateSheets()
Dim ws As Worksheet
Dim iRegion As Integer
Dim rRegionRange As Range
Dim iCurRow As Integer
Dim sBranch As String
Dim sBranchCol As String


iRegion = 0


Application.ScreenUpdating = False




For Each ws In ActiveWorkbook.Worksheets
If Right(Trim(ws.Name), 4) = "data" Then


Cells(1, 1).Select


Do Until ActiveCell.Value = ""
    
        If InStr("Region", ActiveCell.Value) Then
            iRegion = 1
            
            ActiveCell.Offset(1, 0).Select
            Do Until ActiveCell.Value = ""
                iCurRow = ActiveCell.Row
                sBranch = sBranchCol & iCurRow
                
                ActiveCell.Value = Application.WorksheetFunction.Index(Sheets("MATCH").Range("C:C"), Application.WorksheetFunction.Match(sBranch, Sheets("MATCH").Range("A:A"), 0), 1)
                ActiveCell.Offset(1, 0).Select
            Loop


        End If
        
        If InStr("Property_Branch", ActiveCell.Value) Then
            
            sBranchCol = ActiveCell.Column
              
        End If
        
        If InStr("Management_Service", ActiveCell.Value) Then
            Do Until ActiveCell.Value = ""
             If Not InStr("CRL - Letting & Full Management", ActiveCell.Value) Or InStr("L&P - Full Management", ActiveCell.Value) Or InStr("L&P - Full Management (Upfront)", ActiveCell.Value) Or InStr("CRL - Managed Upfront", ActiveCell.Value) Then
                    ActiveCell.EntireRow.Delete Shift:=xlUp
                    ActiveCell.Offset(1, 0).Select
             End If
            Loop


        End If
        
ActiveCell.Offset(0, 1).Select


Loop


    If iRegion = 0 Then
        
    Cells(1, 1).Select
        Do Until ActiveCell.Value = ""
            ActiveCell.Offset(1, 0).Select
            
        Loop
            ActiveCell.Value = "Region"
            ActiveCell.Offset(1, 0).Select
                    
                Do Until ActiveCell.Value = ""
                    iCurRow = ActiveCell.Row
                    sBranch = sBranchCol & iCurRow
                    
                    ActiveCell.Value = Application.WorksheetFunction.Index(Sheets("MATCH").Range("C:C"), Application.WorksheetFunction.Match(sBranch, Sheets("MATCH").Range("A:A"), 0), 1)
                    ActiveCell.Offset(1, 0).Select
                Loop
    End If
End If
Next






Application.ScreenUpdating = True
End Sub

Just to add, im not sure how but it keeps writing "Region" in a sheet that doesnt havnt the word "data" in, although my if statement says only sheets with the word data in?

Thanks.

 

[tonyyy]

georgep93,

Just to add, im not sure how but it keeps writing "Region" in a sheet that doesnt havnt the word "data" in, although my if statement says only sheets with the word data in?

[COLOR=#ff0000]If iRegion = 0 Then[/COLOR]
    Cells(1, 1).Select
    Do Until ActiveCell.Value = ""
        ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.Value = "Region"
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.Value = ""
        iCurRow = ActiveCell.Row
        sBranch = sBranchCol & iCurRow
        ActiveCell.Value = Application.WorksheetFunction.Index(Sheets("MATCH").Range("C:C"), Application.WorksheetFunction.Match(sBranch, Sheets("MATCH").Range("A:A"), 0), 1)
        ActiveCell.Offset(1, 0).Select
    Loop
End If

In the above, try changing this line...

If iRegion = 0 Then

to...

If iRegion = 1 Then

Cheers,

tonyyy

 

[georgep93]

Hi Tonyyy,

Thanks for coming back to me! In the end I got rid of that part of the code as I realised the data coming in didn't need it. However I do have another issue with the same script if you would be able to part with some of your VBA wisdom:

        manageService = ws.Range("1:1").Find(What:="Management_Service", LookAt:=xlWhole).Column
        
            Set dltRange = Range(Cells(Lastrow, manageService), Cells(2, manageService))


            With dltRange
                For dltctr = Lastrow To 1 Step -1
                MsgBox dltRange(dltctr)
                    If dltRange(dltctr).Value <> "CRL - Letting & Full Management" _
                    Or dltRange(dltctr).Value <> "L&P - Full Management" _
                    Or dltRange(dltctr).Value <> "L&P - Full Management (Upfront)" _
                    Or dltRange(dltctr).Value <> "CRL - Managed Upfront" Then dltRange(dltctr).EntireRow.Delete
    
                Next dltctr
            End With

As you can see, all i'm trying to do is loop through a range and delete the row if it does not contain one of the values above. I have set up the MsgBox so I can see what value it is looking at, and it seems to show the correct values... however it just deletes everything? Where have I gone wrong?

Thanks.

 

[Fluff]

Where you have Or, try changing it to And

 

[georgep93]

Ignore me... i'm an idiot.

 

[georgep93]

Hey Fluff,

Thanks for coming back to me! I cant stop laughing.. think I need to stop looking at this for a bit.