[RESOLVED] Excel VBA - corrupted??

[direktoren]

Hi

Just a quick question;

Is it possible for a code in a module to get corrupted via immense copy/pasting?

I have a module with a large sub - it's a fairly simple code, which is repeated through out the sub. Now I just discovered that it doesn't work! OR actually it does, but only the first instance - the next 20 instances doesn't function (no errors, they just don't do the trick!).
I've printed out the codes and verified that they are completely identical (or at least to my eye).

What happened? Did I kill it with copy/paste???

[Comintern]

Can you post the module? I'll take a look at it.

[direktoren]

Ok It's pretty long, so I'll just bring the two first instances...

The first one is working, the second one isn't;

Sub KngrAdder1()
 
'First instance - this one is working
 
With Worksheets("Grund").Range("A5:R400")
    For i = 1 To .Rows.Count
        If .Cells(i, 1) = "BFORR" Then
        .Cells(i, 1).CurrentRegion.Copy
        Worksheets("Beregninger").Range("A1").PasteSpecial
        End If
    Next i
End With
Application.CutCopyMode = False
 
With Worksheets("Beregninger").Range("A1:R20")
    For i = 1 To .Rows.Count
        If .Cells(i, 2) = "Merchant Bank Private" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("21").PasteSpecial
        End If
    Next i
End With
Application.CutCopyMode = False
 
With Worksheets("Beregninger").Range("A1:R20")
    For i = 1 To .Rows.Count
        If .Cells(i, 2) = "04 Fonde/investeringsselskaber" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("22").PasteSpecial
        End If
    Next i
End With
Application.CutCopyMode = False
 
With Worksheets("Beregninger").Range("A1:R20")
    For i = 1 To .Rows.Count
        If .Cells(i, 2) = "Mindre Erhverv" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("23").PasteSpecial
        End If
    Next i
End With
Application.CutCopyMode = False
 
With Worksheets("Beregninger").Range("A1:R20")
    For i = 1 To .Rows.Count
        If .Cells(i, 2) = "Ukendt" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("24").PasteSpecial
        End If
    Next i
End With
Application.CutCopyMode = False
 
'Merchant bank
Worksheets("Beregninger").Range("C26:R26").formula = "=Beregninger!C21-Beregninger!C22"
 
'Mindre Erhverv
Worksheets("Beregninger").Range("C27:R27").formula = "=Beregninger!C22+Beregninger!C23+Beregninger!C24"
 
With Worksheets("Beregninger").Range("A1:R50")
    For i = 1 To .Rows.Count
        If .Cells(i, 2) = "Kundegrupper" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("31").PasteSpecial
        End If
        If .Cells(i, 2) = "Finansielle kunder" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("32").PasteSpecial
        End If
        If .Cells(i, 2) = "Kreditinstitutter" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("33").PasteSpecial
        End If
        If .Cells(i, 2) = "Mellemstore Erhverv" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("34").PasteSpecial
        End If
        If .Cells(i, 2) = "Merchant Bank Private" Then
        .Range("A21:B21").Copy
        .Range("A35:B35").PasteSpecial
        .Range("C35:R35").formula = "=$C$26:$R$26"
        End If
        If .Cells(i, 2) = "Mindre Erhverv" Then
        .Range("A23:B23").Copy
        .Range("A36:B36").PasteSpecial
        .Range("C36:R36").formula = "=$C$27:$R$27"
        End If
        If .Cells(i, 2) = "Offentlige kunder" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("37").PasteSpecial
        End If
        If .Cells(i, 2) = "Porteføljer" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("38").PasteSpecial
        End If
        If .Cells(i, 2) = "Private" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("39").PasteSpecial
        End If
        If .Cells(i, 2) = "Retssager" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("40").PasteSpecial
        End If
        If .Cells(i, 2) = "Storkunder" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("41").PasteSpecial
        End If
        Next i
End With
Application.CutCopyMode = False
 
Worksheets("Tilrettet").Rows("5:15").Value = Worksheets("Beregninger").Rows("31:41").Value
Application.CutCopyMode = False
 
'Second instance - the one not working!
 
With Worksheets("Grund").Range("A5:R400")
    For i = 1 To .Rows.Count
        If .Cells(i, 1) = "W3744 REALKREDIT DANMARK" Then
        .Cells(i, 1).CurrentRegion.Copy
        Worksheets("Beregninger").Range("A51").PasteSpecial
        End If
    Next i
End With
Application.CutCopyMode = False
 
With Worksheets("Beregninger").Range("A51:R70")
    For i = 1 To .Rows.Count
        If .Cells(i, 2) = "Merchant Bank Private" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("71").PasteSpecial
        End If
    Next i
End With
Application.CutCopyMode = False
 
With Worksheets("Beregninger").Range("A51:R70")
    For i = 1 To .Rows.Count
        If .Cells(i, 2) = "04 Fonde/investeringsselskaber" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("72").PasteSpecial
        End If
    Next i
End With
Application.CutCopyMode = False
 
With Worksheets("Beregninger").Range("A51:R70")
    For i = 1 To .Rows.Count
        If .Cells(i, 2) = "Mindre Erhverv" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("73").PasteSpecial
        End If
    Next i
End With
Application.CutCopyMode = False
 
With Worksheets("Beregninger").Range("A51:R70")
    For i = 1 To .Rows.Count
        If .Cells(i, 2) = "Ukendt" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("74").PasteSpecial
        End If
    Next i
End With
Application.CutCopyMode = False
 
'Merchant bank
Worksheets("Beregninger").Range("C76:R76").formula = "=Beregninger!C71-Beregninger!C72"
 
'Mindre Erhverv
Worksheets("Beregninger").Range("C77:R77").formula = "=Beregninger!C72+Beregninger!C73+Beregninger!C74"
 
With Worksheets("Beregninger").Range("A51:R100")
    For i = 1 To .Rows.Count
        If .Cells(i, 2) = "Kundegrupper" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("81").PasteSpecial
        End If
        If .Cells(i, 2) = "Finansielle kunder" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("82").PasteSpecial
        End If
        If .Cells(i, 2) = "Kreditinstitutter" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("83").PasteSpecial
        End If
        If .Cells(i, 2) = "Mellemstore Erhverv" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("84").PasteSpecial
        End If
        If .Cells(i, 2) = "Merchant Bank Private" Then
        .Range("A71:B71").Copy
        .Range("A85:B85").PasteSpecial
        .Range("C85:R85").formula = "=$C$76:$R$76"
        End If
        If .Cells(i, 2) = "Mindre Erhverv" Then
        .Range("A73:B73").Copy
        .Range("A86:B86").PasteSpecial
        .Range("C86:R86").formula = "=$C$77:$R$77"
        End If
        If .Cells(i, 2) = "Offentlige kunder" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("87").PasteSpecial
        End If
        If .Cells(i, 2) = "Porteføljer" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("88").PasteSpecial
        End If
        If .Cells(i, 2) = "Private" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("89").PasteSpecial
        End If
        If .Cells(i, 2) = "Retssager" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("90").PasteSpecial
        End If
        If .Cells(i, 2) = "Storkunder" Then
        .Cells(i, 2).EntireRow.Copy
        Worksheets("Beregninger").Rows("91").PasteSpecial
        End If
        Next i
End With
Application.CutCopyMode = False
 
Worksheets("Tilrettet").Rows("25:35").Value = Worksheets("Beregninger").Rows("81:91").Value
Application.CutCopyMode = False

The part that doesn't work in the second instance (and all of the following) is the following;

If .Cells(i, 2) = "Merchant Bank Private" Then
        .Range("A71:B71").Copy
        .Range("A85:B85").PasteSpecial
        .Range("C85:R85").formula = "=$C$76:$R$76"
        End If
        If .Cells(i, 2) = "Mindre Erhverv" Then
        .Range("A73:B73").Copy
        .Range("A86:B86").PasteSpecial
        .Range("C86:R86").formula = "=$C$77:$R$77"
        End If

And yes - it is a sloppy code, but its old!!

Nick

[Comintern]

I can't see anything obviously wrong with it, but I do have a couple of suggestions. First would be to replace all of the If...End if statements with a Select Case structure, i.e.:

With Worksheets("Beregninger").Range("A1:R50")
    For i = 1 To .Rows.Count
        Select Case .Cells(i, 2).Value
        Case "Kundegrupper"
            .Cells(i, 2).EntireRow.Copy
            Worksheets("Beregninger").Rows("31").PasteSpecial
        Case "Finansielle kunder"
            .Cells(i, 2).EntireRow.Copy
            Worksheets("Beregninger").Rows("32").PasteSpecial
        Case "Kreditinstitutter"
            .Cells(i, 2).EntireRow.Copy
            Worksheets("Beregninger").Rows("33").PasteSpecial
        Case "Mellemstore Erhverv"
            .Cells(i, 2).EntireRow.Copy
            Worksheets("Beregninger").Rows("34").PasteSpecial
        Case "Merchant Bank Private"
            .Range("A21:B21").Copy
            .Range("A35:B35").PasteSpecial
            .Range("C35:R35").Formula = "=$C$26:$R$26"
        '...
        End Select
    Next i
End With

You can also speed things up a little bit by early loop exits. These sections are looking for a specific value, so when you find it, there is no reason to continue the loop:

With Worksheets("Grund").Range("A5:R400")
    For i = 1 To .Rows.Count
        If .Cells(i, 1) = "BFORR" Then
            .Cells(i, 1).CurrentRegion.Copy
            Worksheets("Beregninger").Range("A1").PasteSpecial
            Exit For   'Add this
        End If
    Next i
End With

As far as not functioning goes, I would just step through it in the debugger and make sure that all the values are what you expect. It's a lot easier to nail down what is happening this way.