Re: $$Excel-Macros$$ Getting Array out of Bound Error
Dear Saggi, Pl share the file with us. Warm Regards, Gawli Anil Thanks Regards, Gawli Anil Narayan Software Developer, Abacus Software Services Pvt Ltd On Mon, Sep 2, 2013 at 11:04 AM, shyam.utha...@gmail.com shyam.utha...@gmail.com wrote: Sent from my HTC - Reply message - From: saggi realsa...@gmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Getting Array out of Bound Error Date: Mon, Sep 2, 2013 10:34 am Dear All, Don't know what happened all of sudden, below given code giving Arrgy Out of bound error. Worked fine till yesterday. Below given code i am using for download attachment from outlook to my specific folder. debug n highlighted in yellow color where i am getting above mention error. Sub SaveOlAttachments(). Dim olFolder As Outlook.MAPIFolder Dim msg As Outlook.MailItem Dim msg2 As Outlook.MailItem Dim att As Outlook.Attachment Dim strFilePath As String Dim strTmpMsg As String Dim fsSaveFolder As String fsSaveFolder = D:\Vijay\ 'path for creating attachment msg file for stripping strFilePath = D:\Vijay\ strTmpMsg = KillMe.msg 'My testing done in Outlok using a temp folder underneath Inbox Set olFolder = Application.GetNamespace(MAPI).GetDefaultFolder(olFolderInbox) Set olFolder = olFolder.Folders(Dentsply india sales) If olFolder Is Nothing Then Exit Sub For Each msg In olFolder.Items If msg.Attachments.Count 0 Then While msg.Attachments.Count 0 bflag = False If Right$(msg.Attachments(1).FileName, 3) = msg Then bflag = True msg.Attachments(1).SaveAsFile strFilePath strTmpMsg Set msg2 = Application.CreateItemFromTemplate(strFilePath strTmpMsg) End If If bflag Then sSavePathFS = fsSaveFolder msg2.Attachments(1).FileName msg2.Attachments(1).SaveAsFile sSavePathFS msg2.Delete Else sSavePathFS = fsSaveFolder msg.Attachments(1).FileName msg.Attachments(1).SaveAsFile sSavePathFS End If msg.Attachments(1).Delete Wend msg.Delete End If Next End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1)
Re: $$Excel-Macros$$ Keep Excel in Full Screen
PFA,and try same in your file. From: Excel_Lover idforex...@gmail.com To: excel-macros@googlegroups.com Sent: Sunday, September 1, 2013 8:15 PM Subject: $$Excel-Macros$$ Keep Excel in Full Screen Dear All, I am using the below code in the Workbook_Open event to display the excel in full screen. Application.DisplayFullScreen = True But, I need the excel to remain in full screen even if the user press 'Esc' button. Appreciate your suggestions. Best Regards Excel_Lover -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. wkbopenfullmode.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Macro to calculate Attendance from a Monthly Schedule
Hello All, I have duty schedule for various Staffs on various Stores. All Sheets are of same pattern. I am enclosing a Sheet with my Problem only for one Store as a Sample. I have many sheets in the workbook (50+). Each Staff name appears in a Row. Sometimes two Staff Names are in a Row (See Row 59 in the sample attached). Staff Names will be varying for other Stores. At present I am using Index Function with Countif to count the Names of Staff. (Please see in the sample). As there are more than 50+ Sheet I have to do it on each sheet manually which is time consuming and also when more than one name comes on a Row my Countif gives wrong answer. I have color coded the Staff name Rows for sake of easy explanation of my problem. I need a macro which will run on all the Sheets in this Workbook and give me the Staff Names, their count, Sheet name and the Month Value from Cell of F4. See the desired output required in Range A70:D75. Any help would be greatly appreciated. TIA Rashid -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. APR SCHEDULE - group.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ If cell is blank, it should give error msg
Try this Sub check() Dim Birth As Range, PANo As Range, UserInput, Arr Set Birth = [G2] Set PANo = [j2] If IsEmpty(Birth) Then Birth.Interior.Color = 255 Birth.Interior.Pattern = xlSolid If MsgBox(Birth Date is empty, Do you want to enter data now ?, vbYesNo + vbQuestion) = vbYes Then UserInput = Application.InputBox(Enter the Bith Date using format DD/MM/, Birth Date, Type:=2) If Not UserInput = False Then Arr = Split(UserInput, /) Birth.Formula = =DATE( Arr(2) , Arr(1) , Arr(0) ) Birth.Interior.Pattern = xlNone End If End If End If If IsEmpty(PANo) Then PANo.Interior.Color = 255 PANo.Interior.Pattern = xlSolid If MsgBox(PANo is empty, Do you want to enter data now ?, vbYesNo + vbQuestion) = vbYes Then UserInput = Application.InputBox(Input PANo !, PANo, Type:=1) If Not UserInput = False Then PANo = UserInput PANo.Interior.Pattern = xlNone End If End If End If End Sub On 02-09-2013 12:13, ITP Abdulgani Shaikh wrote: Please guide me to sort out this issue. On Sat, Aug 31, 2013 at 3:32 PM, ITP Abdulgani Shaikh itpabdulg...@gmail.com mailto:itpabdulg...@gmail.com wrote: Dear Excel Masters, I want macro for following : 01. There are two cells in attached sheet G2 J2. When I will run macro it should check that whether values are entered in these both cells. If any one or both cells are blank, cell should be RED and give message that values are not entered. and then it should ask Do you want to enter data now, if yes input box for entering value. 02. This sheet is used by me for calculating monthly tax from Salary of employees. In each file there are number of sheets having details of TDS to be deducted from salary of each employee (Pl.see cell Q46 to U46). Number of sheet may change based on new joining or retiring employee. Every month I have to update manually *Challan Detail* sheet. = Is it possible to update *Challan Detail* sheet using macro for all months. = Is it possible to update data of particular month *Challan Detail* sheet using macro. Please guide. Regards -- Shaikh AbdulGani A R ITP, STP, TRP, STRP -- -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ need help
Hi Thameem, Suppose you have the total for everyone of the 40 sheets on cell B100. The following formula would sum every total of the 40 sheets you have. I guess this is what you want. =SUM(Plan2!B100;Plan3!B100;Plan4!B100) Best regards. OS 2013/8/12 ashish koul koul.ash...@gmail.com can u share the sample file ... On Mon, Aug 12, 2013 at 1:05 PM, thameem thamee...@gmail.com wrote: Dear all, for example I have one excel with 40 sheets, in those sheets I have one total r sum... I need formuale or codes, to sum all total of 40 sheets in one sheet... -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- *Regards* * * *Ashish Koul* *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- ITWare -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Loop to check if cell is blank, if so copy the cell above
Got it. Much appreciated. I think I grasped it. Clark On Sunday, September 1, 2013 8:30:31 PM UTC-5, Sam Mathai Chacko wrote: I'm sure you've figured out the 'with' part. The formular1c1 just passes the formula mentioned there to all blank cells. The trick is in using it as an r1c1 formula. If you look closer, all it is passing is the equivalent of one row above, denoted by the minus one in the square brackets and the same column, and hence no square brackets after the C. And then just remove the formulas by passing value as value. Remove that line and test the code, you'll figure it out. Sam On Sep 2, 2013 1:38 AM, Clark Feusier clark@creativetrust.comjavascript: wrote: Ah perfect!! Thanks so much Sam. I had hacked together a recorded macro that used function f5 goto special blanks and then inputing the start of the range and control+copying. Your way is much better. If you end up having a second, could you explain what this is doing and how? If not, I totally understand -- I want to learn it though, not just use it :) Thanks Sam, Clark On Sunday, September 1, 2013 3:03:53 PM UTC-5, Sam Mathai Chacko wrote: Try this Clark With Range(A1:A Cells(Rows.Count, A).End(xlUp).Row).** SpecialCells(xlCellTypeBlanks) .FormulaR1C1 = =R[-1]C .Value2 = .Value2 End With On Sun, Sep 1, 2013 at 11:55 PM, Clark Feusier clark@creativetrust. **com wrote: I am very new to excel VBA macros and I am trying to figure out how to go through all of the cells in a single column starting from the top and and checking each cell to see if it's blank/empty. If blank, I would like the macro to paste the cell contents from directly above. If the cell isn't empty, it would move to the next cell down. This would repeat through the whole column. Until they all the cells of that column are full. I have started putting something together but this is beyond me so far. I can't figure out what to put in the DO SOMETHING lines. Any guidance is very much appreciated, even if it is just calling me an idiot and pointing me to some information where I can learn this quickly. Thanks!! See below: Sub FillBlanks() Dim rRange1 As Range, rRange2 As Range Dim iReply As Integer If Selection.Cells.Count = 1 Then DO SOMETHING Exit Sub ElseIf Selection.Columns.Count 1 Then DO SOMETHING Exit Sub End If Set rRange1 = Range(Selection.Cells(1, 1), _ Cells(65536, Selection.Column).End (xlUp) On Error Resume Next Set rRange2 = rRange1.SpecialCells(**xlCellTypeBlanks) On Error GoTo 0 If rRange2 Is Nothing Then DO SOMETHING Exit Sub End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/**discussexcelhttps://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros...@**googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/**group/excel-macroshttp://groups.google.com/group/excel-macros . For more options, visit https://groups.google.com/**groups/opt_outhttps://groups.google.com/groups/opt_out . -- Sam Mathai Chacko -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not
Re: $$Excel-Macros$$ Help needed in Excel
Hi Pankaj, On column C you could write the following formula =trim(a1) trim(b1) You will have no leading or trailing spaces when paste the content of column C in the Notepad for instance. HTH, OS 2013/8/14 Pankaj pankajdhamij...@gmail.com Thanx for your great help. Only one issue - I was able to merge the contents of the 2 columns as needed by me. But when I paste the content of the resultant cell in a text editor I get in the beginning and the end of the text. Why is it appearing and how to prevent it from appearing? On Tuesday, August 13, 2013 1:26:31 PM UTC+5:30, Ravinder Negi wrote: pfa...first select area which you want to merge...example select range A1:B4 then click on button -- *From:* Pankaj pankajd...@gmail.com *To:* excel-...@googlegroups.com *Cc:* ravinder negi ravi_c...@yahoo.com *Sent:* Tuesday, August 13, 2013 8:49 AM *Subject:* Re: $$Excel-Macros$$ Help needed in Excel I want to merge contents of column A B as shown in column C. Please see the sample file enclosed On Wednesday, August 7, 2013 2:34:57 PM UTC+5:30, Ravinder Negi wrote: pls provide excel or explain more about your query... -- *From:* Pankaj pankajd...@gmail.com *To:* excel-...@googlegroups.com *Sent:* Wednesday, August 7, 2013 9:23 AM *Subject:* $$Excel-Macros$$ Help needed in Excel I have 2 columns in excel - A and B. I want to merge the contents of a horizontal row one below the other as: A B please tell me a formula to do this as the columns contain 900 cells each and I cannnot merge the contents manually using Alt + Enter. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/ discussexcel https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros...@ googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/ group/excel-macroshttp://groups.google.com/group/excel-macros . For more options, visit https://groups.google.com/ groups/opt_outhttps://groups.google.com/groups/opt_out . -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/**discussexcelhttps://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros...@**googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/**group/excel-macroshttp://groups.google.com/group/excel-macros . For more options, visit https://groups.google.com/**groups/opt_outhttps://groups.google.com/groups/opt_out . -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive,
Re: $$Excel-Macros$$ If cell is blank, it should give error msg
Ups Sorry, i've forgot a line code for unlocking you sheet protection See at Line 4 and last line that i've bold Sub check() Dim Birth As Range, PANo As Range, UserInput, Arr Set Birth = [G2] Set PANo = [j2] *Sheet5.Unprotect *If IsEmpty(Birth) Then Birth.Interior.Color = 255 Birth.Interior.Pattern = xlSolid If MsgBox(Birth Date is empty, Do you want to enter data now ?, vbYesNo + vbQuestion) = vbYes Then UserInput = Application.InputBox(Enter the Bith Date using format DD/MM/, Birth Date, Type:=2) If Not UserInput = False Then Arr = Split(UserInput, /) Birth.Formula = =DATE( Arr(2) , Arr(1) , Arr(0) ) Birth.Interior.Pattern = xlNone End If End If End If If IsEmpty(PANo) Then PANo.Interior.Color = 255 PANo.Interior.Pattern = xlSolid If MsgBox(PANo is empty, Do you want to enter data now ?, vbYesNo + vbQuestion) = vbYes Then UserInput = Application.InputBox(Input PANo !, PANo, Type:=1) If Not UserInput = False Then PANo = UserInput PANo.Interior.Pattern = xlNone End If End If End If *Sheet5.Protect** *End Sub Pada 02/09/2013 20:05, ITP Abdulgani Shaikh menulis: Thank you for your response, but it gives error as, pl find attached file. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Excel File Date Contents Deletion Macro
Thank You Ravi So Much for providing this code. this code can be used by Excel users whose data is used for presentations but they do not get credit for data compiling. On Fri, Aug 30, 2013 at 11:32 AM, Ravi Kumar excellearn2...@gmail.comwrote: Dear Pankaj, ** ** ** ** There is some changes in the code and u can use the attached file as well* *** ** ** Private Sub Workbook_Open() ** ** On Error Resume Next Application.DisplayAlerts = False If 2013-08-31 = Format(Now(), -mm-dd) Then MsgBox File will expire on 2013-08-31 Else Worksheets(Sheet3).Activate ** ** For Each sh In ActiveWorkbook.Sheets Cells.Select Selection.ClearContents sh.Activate sh.ChartObjects.Delete Next ActiveWorkbook.Save ** ** End If ** ** Application.DisplayAlerts = True End Sub ** ** ** ** * * *Warm Regards,* *Ravi Kumar.* ** ** *From:* Ravi Kumar [mailto:excellearn2...@gmail.com] *Sent:* Friday, August 30, 2013 11:22 AM *To:* 'excel-macros@googlegroups.com' *Subject:* RE: $$Excel-Macros$$ Excel File Date Contents Deletion Macro*** * ** ** Hi Pankaj, ** ** ** ** PFA solution… ** ** Note: Press “alt+f11” then go to “Thisworkbook”. Select “*workbook*” from the left drop down and select “*open*” from the right drop down then paste the below code in this. You can use the attached file as well ** ** Private Sub Workbook_Open() ** ** On Error Resume Next Application.DisplayAlerts = False If Format(Now(), -mm-dd) = 2013-08-31 Then MsgBox File will expire on 2013-08-31 Else Worksheets(Sheet3).Activate ** ** For Each sh In ActiveWorkbook.Sheets Cells.Select Selection.ClearContents sh.Activate sh.ChartObjects.Delete Next ActiveWorkbook.Save ** ** End If ** ** Application.DisplayAlerts = True End Sub ** ** ** ** ** ** ** ** ** ** * * *Warm Regards,* *Ravi Kumar.* ** ** *From:* excel-macros@googlegroups.com [ mailto:excel-macros@googlegroups.com excel-macros@googlegroups.com] *On Behalf Of *pankajinfo *Sent:* Friday, August 30, 2013 9:58 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Excel File Date Contents Deletion Macro ** ** Dear All, ** ** ** ** I want to create a excel file with Date limit. ** ** Like in a file there are some contents(Graphs, Dashboards Etc) with 3 sheets. ** ** What i wants is after a time period like 31-Aug13 it gets expired and no one can open it or if someone opens after 31-Aug-13 it will delete all the file contents and automatically saves without confirmation. ** ** -- Thanks Pankaj Kumar 9910075248 -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you
Re: $$Excel-Macros$$ copy data paste into new worksheet or book save into folder
Dear Prajakt Pande, Has it has lots of rows init. It will take nearly 5 min, so have patients. Warm Regards, Gawli Anil. Thanks Regards, Gawli Anil Narayan Software Developer, Abacus Software Services Pvt Ltd On Tue, Sep 3, 2013 at 10:07 AM, अनिल नारायण गवली gawlianil8...@gmail.com wrote: Dear Prajakt Pande, Run the Macro from view tab. Warm Regards, Gawli Anil Thanks Regards, Gawli Anil Narayan Software Developer, Abacus Software Services Pvt Ltd On Mon, Sep 2, 2013 at 4:59 PM, black panther prajakt.pa...@gmail.com wrote: Dear Team, I have one excel sheet (attached), what I am looking for:- 01)Depend upon the “r” macro need to filter one by one. For better understanding mark column in green 02)Copy entire data paste in to new worksheet. 03)Now new worksheet need to save with folder location. Note :- sample data attached. Please help -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
RE: $$Excel-Macros$$ Excel File Date Contents Deletion Macro
Ur welcome friend, yaa it happens most of the time and with most of the people also. Warm Regards, Ravi Kumar. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pankajinfo Sent: Tuesday, September 03, 2013 9:21 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Excel File Date Contents Deletion Macro Thank You Ravi So Much for providing this code. this code can be used by Excel users whose data is used for presentations but they do not get credit for data compiling. On Fri, Aug 30, 2013 at 11:32 AM, Ravi Kumar excellearn2...@gmail.com mailto:excellearn2...@gmail.com wrote: Dear Pankaj, There is some changes in the code and u can use the attached file as well Private Sub Workbook_Open() On Error Resume Next Application.DisplayAlerts = False If 2013-08-31 = Format(Now(), -mm-dd) Then MsgBox File will expire on 2013-08-31 Else Worksheets(Sheet3).Activate For Each sh In ActiveWorkbook.Sheets Cells.Select Selection.ClearContents sh.Activate sh.ChartObjects.Delete Next ActiveWorkbook.Save End If Application.DisplayAlerts = True End Sub Warm Regards, Ravi Kumar. From: Ravi Kumar [mailto:excellearn2...@gmail.com mailto:excellearn2...@gmail.com ] Sent: Friday, August 30, 2013 11:22 AM To: 'excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com ' Subject: RE: $$Excel-Macros$$ Excel File Date Contents Deletion Macro Hi Pankaj, PFA solution. Note: Press alt+f11 then go to Thisworkbook. Select workbook from the left drop down and select open from the right drop down then paste the below code in this. You can use the attached file as well Private Sub Workbook_Open() On Error Resume Next Application.DisplayAlerts = False If Format(Now(), -mm-dd) = 2013-08-31 Then MsgBox File will expire on 2013-08-31 Else Worksheets(Sheet3).Activate For Each sh In ActiveWorkbook.Sheets Cells.Select Selection.ClearContents sh.Activate sh.ChartObjects.Delete Next ActiveWorkbook.Save End If Application.DisplayAlerts = True End Sub Warm Regards, Ravi Kumar. From: excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of pankajinfo Sent: Friday, August 30, 2013 9:58 AM To: excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Excel File Date Contents Deletion Macro Dear All, I want to create a excel file with Date limit. Like in a file there are some contents(Graphs, Dashboards Etc) with 3 sheets. What i wants is after a time period like 31-Aug13 it gets expired and no one can open it or if someone opens after 31-Aug-13 it will delete all the file contents and automatically saves without confirmation. -- Thanks Pankaj Kumar 9910075248 -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com mailto:excel-macros+unsubscr...@googlegroups.com . To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com . Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data
