Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Failed Excel 2007 Macro
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.
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This works for me without any problem..
Saurabh...
Dim AFFOUND As Range
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
Saurabh...
one important thing is that you need to tell us what's the error message you received? and which line of codes is suspected to cause that error?
As stated in my question:
"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"."
There is no error code, I use Step Into (F8) to Debug my code. I get bo error message.
"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"."
There is no error code, I use Step Into (F8) to Debug my code. I get bo error message.
The code could be written more efficiently., you don't need to select the ranges and searcing a specific range would be better.
Can you attach an example workbook
Can you attach an example workbook
Thank you Mr. Cox.
Unfortunately the data I work with is confidential tax information and I am forbidden by law to share it. However I will try to select 25 records that I can change the names and Tax ID numbers to show you what I am doing and why I am doing it.
May take me a day or two to get that done. Thanks again
Unfortunately the data I work with is confidential tax information and I am forbidden by law to share it. However I will try to select 25 records that I can change the names and Tax ID numbers to show you what I am doing and why I am doing it.
May take me a day or two to get that done. Thanks again
Saurabh,
Thanks you for providing the script. Not sure what happened to mine, but yours works so I am using it.
How could I incorporate a "Do While" to have it go through the process until no more "ADMIN FEE" records are found then skip to the next line of code?
Thanks you for providing the script. Not sure what happened to mine, but yours works so I am using it.
How could I incorporate a "Do While" to have it go through the process until no more "ADMIN FEE" records are found then skip to the next line of code?
Yes..You need to incorporate a DO while loop if you are looking for multiple search values of the same item..
And yours wasn't working because you didn't declared what AFFound was and it assumed to be variant which at times can be misleading for macro to calculate and in the macro i just told what it was and that fixed the problem..
Saurabh...
And yours wasn't working because you didn't declared what AFFound was and it assumed to be variant which at times can be misleading for macro to calculate and in the macro i just told what it was and that fixed the problem..
Saurabh...
Premium Content
You need an Expert Office subscription to comment.Start Free Trial