• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in creating Macro

P

prazad82

Member
Here are my questions (I have a very little knowledge on Macros). Your help will be much appreciated. I have attached the file for your reference.


http://www.2shared.com/file/Y6p6sfku/Workbook1.html


1. If the "Overall" scoring for a monitoring is a "Fail" on a particular date, then a "Follow-up" needs to be done within 48 hours.

2. If the "Overall" scoring is a "Fail", then a new workbook needs to be created with some predefined data. Every subsequent "Fails" needs to be added to this new workbook, in separate sheets.


Thanks in advance!
 
Hi Prasad,


My understanding on your thread is you wanted to move all rows which have "Fail" status under Overall column to new sheet and Type column should be Follow up date extended to 48 Hrs.


Please correct me if i am wrong.


and one more thing do you want to run a macro manually or macro should run when ever you open the sheet.


Please confirm.


Regards,

Satish.
 
Hi,


You can please download the below file and use control+q run the report. (i am clear about the second condition)


http://www.2shared.com/document/MqzYjl4X/Workbook.html


Thanks,

Suresh Kumar S
 
Hello Suresh and Satish,


Thank you for your help (Suresh, your file had some new information, which will be helpful in future).


But I think, I need to be a little more clear in explaining my requirement. I have two questions. I will ask them one after the other.


Let say, today I do a monitoring and it is a "fail". So when I come to work after 48 hours, the tracker should show "Follow-up" in the "Type" column for that corresponding EMP Code.


I hope to hear from you soon.
 
Hi Prazad,


Thanks for you feedback.


Can you please download the below file and try.


http://www.2shared.com/document/nrXAHqYh/Workbook_1_.html


(Press Alt+F8 and select Followup code and run (The Inputbox will ask you to enter the date with greatherthan (>) symbol please enter date after > symbol)


Thanks,

Suresh Kumar S
 
Hello Suresh,


Thank you for your kind attention to this. I will explain my scenario.


In our company, if a monitoring is a "Fail", then a "Followup" needs to be done within 48 hours. But most of the times, this "Followup" is missed, as we cannot remember. To avoid this, I wanted to create a macro, so the "Followup" is notified, probably after 24 hours but not later than 48hrs (it can be in the form of a reminder also).


I am sorry for any miscommunication.
 
Dear prazad82,

Have you tried to solve this problem by pivot table?

First group will be Overall field

Second group will be Date field


Regards,

Muneer
 
Hello Nazmul,


I have not tried pivot because I want that to be updated in the tracker itself. As I do monitoring everyday and if any monitoring scored a "Fail" in the past 48 hours, it should automatically pop up, asking me to do a follow up for that "Fail".


I appreciate your help.
 
Hi Prazad82.


Do you really want someone will POKE you, in each Hour or whenever you open the file, by showing you a message, "Please open file Follow up Need to be Done .xlsx to know which one need to be Follow Up Today"..


Or a Conditional Format will enough to highlight which one need to be Follow Up..


Select A:G, go to Conditional Formatting, Use Formula as

Code: 
=AND($A1>=TODAY()-2,$A1<=TODAY()-1,$G1="Fail")


https://dl.dropbox.com/u/78831150/Excel/Highlight%20Follow%20up%20within%2024-48%20%28prazad82%29.xls


Please let me know, if you really need a Macro for the above POKE..

Regards,

Deb
 
Hello Deb,


I was looking for the macro "poke" that you mentioned. You said, the prompt will appear every hour. So if I complete the follow up, will that prompt get removed?


I tried your suggestion (conditional formatting), but it does not remove the highlighting, even after the follow is completed.


I am looking forward for that macro "poke". Thank you for your help :)
 
Hi Prazad,


I have changed the conditional formatting to match your requirement..

Code: 
=AND($A1>=TODAY()-2,$A1<=TODAY()-1,$G1="Fail",$B1<>"Follow-up") Now if you change Column B to "Follow-UP", it will not display.. :)


Regards,

Deb..


PS:

What!! .. you still need that POKE Macro .. OK..

[pre]'---------- In Workbook Module
Private Sub Workbook_Open()
PokeMacro
End Sub
'----------- In a New Module
Option Explicit
'-----------
Sub PokeMacro()
Dim lrints As Long
Dim StartDate, EndDate
Dim MsgShow As Boolean, dTime As Date
MsgShow = False
StartDate = Format(Now() - 2, "dd/mm/yyyy"): EndDate = Format(Now() - 1, "dd/mm/yyyy")
With ThisWorkbook.Sheets("Sheet1")
lrints = .Range("A" & .Cells.Rows.Count).End(xlUp).Row
.Range("A1:G" & lrints).AutoFilter _
Field:=1, Criteria1:=">=" & Format(Now() - 2, "dd/mmm/yyyy"), _
Operator:=xlAnd, Criteria2:="<=" & Format(Now() - 1, "dd/mmm/yyyy")
.Range("A1:G" & lrints).AutoFilter _
Field:=7, Criteria1:="=Fail"
.Range("A1:G" & lrints).AutoFilter _
Field:=2, Criteria1:="<>Follow-up"
If WorksheetFunction.Subtotal("3", .Range("A1:G" & lrints)) > 1 Then
Sheets.Add.Name = "Follow-UP"
.Range("A1:G" & lrints).SpecialCells(xlVisible).Copy Sheets("Follow-UP").Range("A1")
Sheets("Follow-up").Move
ActiveWorkbook.SaveAs ThisWorkbook.Path & "Follow up Need to be Done", xlWorkbookDefault
ActiveWorkbook.Close
MsgShow = True
End If
.AutoFilterMode = False
End With
If MsgShow = True Then
MsgBox "Please open file Follow up Need to be Done.xls to know which one need to be Follow Up Today.."
Else
MsgBox "Have some Coffee.. No Employee to follow up today.. :)"
End If

dTime = Now + TimeSerial(0, 5, 0)
Application.OnTime dTime, "PokeMacro"
End Sub
[/pre]

https://dl.dropbox.com/u/78831150/Excel/Highlight%20Follow%20up%20within%2024-48%20%28prazad82%29.xlsm


* Save the file in a folder,

* Open and enable Macro..

* It will POKE you in each 5 Minute.. and create a file (Follow up Need to be Done) for you in the same folder..

* Within 5 Min change anything in the file and confirm if anything else need to be done..


PPS:

dTime = Now + TimeSerial(0, 5, 0)

in the above area change (0,5,0) to (1,0,0) to remind you in every hour, instead of 5 Min..
 
Helle Deb,


I am getting an error while opening the file.


"Compile Error:


Variable not defined"


PS: I am using excel in a Mac, if this has anything to do with the error.
 
You must log in or register to reply here.
Back
Top