Help with 'minimalizing' / 'trimming down' a working formula

[StephanV]

I've got 2 working formulas, but they are rather inefficient I guess. Because im new with vba, i just copy/pasted al the info i found on this board into one formula, and now it works!

I think its easy to follow what the formulas should do:

Private Sub CommandButton1_click()
Dim ans As Long
ans = MsgBox("Weet je zeker dat je het certificaat wilt verwijderen?? Is er een cel in de juiste rij geselecteerd?", vbYesNo)
If ans = vbYes Then
   Dim CopyRange As Range, NextCell As Range
   Dim lngRow As Long
   lngRow = ActiveCell.Row
   Set CopyRange = Worksheets("Certificate_Database").Range("A" & lngRow & ":W" & lngRow)
   Set NextCell = Worksheets("Prullenbak").Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
   CopyRange.Copy
   NextCell.PasteSpecial
   Application.CutCopyMode = False
   With Sheets("Certificate_Database")
      .Range("A" & lngRow & ":W" & lngRow).ClearContents
    End With
        With ActiveWorkbook.Worksheets("Certificate_Database").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Else
        Exit Sub
    End If
End Sub

Private Sub CommandButton2_click()
Dim ans As Long
ans = MsgBox("Weet je zeker dat je het certificaat wilt verlengen en kopieren naar NDO_Database? Is er een cel in de juiste rij geselecteerd?", vbYesNo)
If ans = vbYes Then
   Dim CopyRange As Range, NextCell As Range
   Dim lngRow As Long
   lngRow = ActiveCell.Row
   Set CopyRange = Worksheets("Certificate_Database").Range("A" & lngRow & ":D" & lngRow & ",T" & lngRow & ":W" & lngRow)
   Set NextCell = Worksheets("NDO_Database").Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
   CopyRange.Cut
   NextCell.PasteSpecial
   Application.CutCopyMode = False
   With Sheets("Certificate_Database")
      .Range("T" & lngRow & ":W" & lngRow).ClearContents
      .Range("O" & lngRow).Value = .Range("O" & lngRow).Value + 730
    End With
    Else
        Exit Sub
    End If
End Sub

How can i 'clean' these formulas (and write them more correctly the next time :P)

 

[Andrew Poulsom]

Those are VBA procedures not formulas. What makes you think they are inefficient?

 

[StephanV]

Those are VBA procedures not formulas. What makes you think they are inefficient?

Mmh, well my lack on VBA knowledge i guess :P

When I read all the VBA procedures (examples on this board), there is a certain way of writing them down, and I did not follow it. Furthermore, people more familliar with VBA could perhaps make the code simpler (like writing 100 x 100 x 100 x 100 as 100^4).

 

[StephanV]

Does anyone have 5 minutes spare time?