We help IT Professionals succeed at work.
Get Started

Failed Excel 2007 Macro

94 Views
Last Modified: 2015-08-24
The code below is a part of an Excel 2007 Macro. Its purpose is to Select Column D2:(Last populated Cell in Column D) and find the string "Admin Fee". If it is not found, the code ends and it goes on to the next instruction.

If it is found, the code CUTS the text from the cell populated by "Admin Fee". The code them MOVES two cells to the right and populates that cell with "1". Next, the code moves three cells to the left and pastes "Admin Fee" into the active cell. It then moves back one cell to the right and populates that cell with the value of =Mode(D2:D & LR & ")".

This code is stopping at "If Not AFFound is Nothing Then" and after a few moments it breaks. Debug shows that the last command It ran was the line before "If Not AFFound is Nothing Then". The Macro has worked fine up until today.

The only changes I made were in the Reference Libraries; I added a couple to the list. (See embedded image)

Any help will be appreciated.

 'Move "Admin Fee" to name field and populate field cell with current tax period
    
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    
     Set AFFound = Cells.Find(What:="Admin Fee", After:=ActiveCell, LookIn:=xlFormulas, _
         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
         MatchCase:=False, SearchFormat:=False)
    If Not AFFound Is Nothing Then
    
    ' the code you want to have if you find it
    
    Cells.Find(What:="Admin Fee", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Selection.Cut
    ActiveCell.Offset(0, 2).Select
    ActiveCell.Value = "1"
    ActiveCell.Offset(0, -3).Select
    ActiveSheet.Paste
    LR = ActiveCell.row - 1
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Formula = "=MODE(D2:D" & LR & ")"
    ActiveCell.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
    End With
    End If

Open in new window


Access 2007 References
Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2015
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE