Re: $$Excel-Macros$$ Sort VBA Code Modified
Share your workbook in which you are applying this method and let me check why it is not working. On Tuesday, 1 July 2014 11:05:27 UTC+5:30, amar takale wrote: Dear lalitji code not working On Tue, Jul 1, 2014 at 8:00 AM, Lalit Mohan Pandey mohan.p...@gmail.com javascript: wrote: I have checked it and it is working fine but give a try to this code: Sub SECEPSDES() With ThisWorkbook.Worksheets(Sector Valn) .Sort.SortFields.Clear .Range(A6:U21).Sort Key1:=.Range(E7:E21), Order1:=xlDescending, Header:=xlYes End With End Sub Sub SECEPSASC() With ThisWorkbook.Worksheets(Sector Valn) .Sort.SortFields.Clear .Range(A6:U21).Sort Key1:=.Range(E7:E21), Order1:=xlAscending, Header:=xlYes End With End Sub On Monday, 30 June 2014 10:19:08 UTC+5:30, amar takale wrote: Dear latit mohan, when i use above code which you provide show error object does not support this property or method in excel.Pls give me proper guidance Regards Amar On Mon, Jun 30, 2014 at 10:17 AM, amar takale amart...@gmail.com wrote: Dear Bé Trần Văn Thanks very much sir. but i ask you small issue can we use one code to all filed like lalit mohanji code.I use this code but erroe show object does not support this property or method Thanks once again Regards Amar On Sat, Jun 28, 2014 at 3:34 PM, Bé Trần Văn betnmtd...@gmail.com wrote: Sort data, row 7:2000 2014-06-28 2:55 GMT+07:00 Lalit Mohan Pandey mohan.p...@gmail.com: Yes it will work in excel 2003 and above On Friday, 27 June 2014 15:31:54 UTC+5:30, amar takale wrote: Dear Lalit Mohan Then it code not work in excel 2003? On Fri, Jun 27, 2014 at 3:24 PM, Lalit Mohan Pandey mohan.p...@gmail.com wrote: change your code as i have changed it: Sub SECEPSDES() With ActiveWorkbook.Worksheets(Sector Valn) .Sort.SortFields.Clear .Range(A6:U21).Sort Key1:=.Range(E7:E21), Order1:=xlDescending, Header:=xlYes End With End Sub Sub SECEPSASC() With ActiveWorkbook.Worksheets(Sector Valn) .Sort.SortFields.Clear .Range(A6:U21).Sort Key1:=.Range(E7:E21), Order1:=xlAscending, Header:=xlYes End With End Sub Do changes for other procedure this is excel 2003 sorting method which is supported by any version above 2003 but what you are using in your code is 2007 data sorting method which is not supported by 2003 :) Hope you understand On Friday, 27 June 2014 10:56:51 UTC+5:30, amar takale wrote: Dear all pls help me on this topic. regards amar On Thu, Jun 26, 2014 at 1:21 PM, amar takale amart...@gmail.com wrote: Dear Experts I have below attached excel which open in excel 2007 then sort VBA code work perfect but when it open in excel 2003 then show error.So pls guide me about it error work also open it in excel 2003. I use Excel 2003 So I want this file work in excel 2003 So I continue use it with range change only in code Thanks Regards Amar -- 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...@googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
Re: $$Excel-Macros$$ Sort VBA Code Modified
change your code as i have changed it: *Sub SECEPSDES()* *With ActiveWorkbook.Worksheets(Sector Valn)* *.Sort.SortFields.Clear* *.Range(A6:U21).Sort Key1:=.Range(E7:E21), Order1:=xlDescending, Header:=xlYes* *End With* *End Sub* *Sub SECEPSASC()* *With ActiveWorkbook.Worksheets(Sector Valn)* *.Sort.SortFields.Clear* *.Range(A6:U21).Sort Key1:=.Range(E7:E21), Order1:=xlAscending, Header:=xlYes* *End With* *End Sub* Do changes for other procedure this is excel 2003 sorting method which is supported by any version above 2003 but what you are using in your code is 2007 data sorting method which is not supported by 2003 :) Hope you understand On Friday, 27 June 2014 10:56:51 UTC+5:30, amar takale wrote: Dear all pls help me on this topic. regards amar On Thu, Jun 26, 2014 at 1:21 PM, amar takale amart...@gmail.com javascript: wrote: Dear Experts I have below attached excel which open in excel 2007 then sort VBA code work perfect but when it open in excel 2003 then show error.So pls guide me about it error work also open it in excel 2003. I use Excel 2003 So I want this file work in excel 2003 So I continue use it with range change only in code Thanks Regards Amar -- 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...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout.
$$Excel-Macros$$ Re: URGENT Help! How to display Period the data has been selected through the timeline.
What you want whenever you scroll down the timline should also scroll down? On Friday, 27 June 2014 14:59:45 UTC+5:30, Prayank Vidyarthi wrote: Hi Excel Gurus, I need your help as I am really stuck over here. I have data starting from Nov 2012 till March 2014. I have created various charts for my dashboard and have also inserted a timeline slicer to my pivots. The orientation of this dashboard is portrait and the timeline is given on top so that once the period has been selected, all the charts and numbers below will change accordingly. The challenge is that how do I display the time period I have selected through the time line on my sections below because as you scroll down the timeline also moves up. Please help! Regards Prayank -- 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/d/optout.
Re: $$Excel-Macros$$ Sort VBA Code Challenging difficult work
Try this, I have created this for only MCap column: *Option Explicit* *Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)* *If Target.Address = $D$7 Then* *Call FilterData* *Application.Goto Me.Cells(1, 1)* *End If* *End Sub* *Sub FilterData()* *Dim r As Range* *Dim i As Long* *Dim ii As Long* *Dim k As Long* *With Sheet1* *Set r = .Range($B$7:$I$29)* *i = .Range(D5).Value* *ii = .Range(D6).Value* *k = 3* *With r* *If .Resize(1, 1).Offset(, k - 1).ID = Then* *.Resize(1, 1).Offset(, k - 1).ID = asc* *End If* *On Error Resume Next* *.Sort.SortFields.Clear* *On Error GoTo -1: On Error GoTo 0: Err.Clear* *If .Resize(1, 1).Offset(, k - 1).ID = asc Then* *.Sort Key1:=.Resize(, 1).Offset(, k - 1), Order1:=xlAscending, Header:=xlYes* *.Resize(1, 1).Offset(, k - 1).ID = desc* *ElseIf .Resize(1, 1).Offset(, k - 1).ID = desc Then* *.Sort Key1:=.Resize(, 1).Offset(, k - 1), Order1:=xlDescending, Header:=xlYes* *.Resize(1, 1).Offset(, k - 1).ID = asc* *End If* *.AutoFilter* *.AutoFilter Field:=3, Criteria1:== i, Operator:=xlAnd, Criteria2:== ii* *End With* *End With* *End Sub* On Friday, 27 June 2014 15:22:44 UTC+5:30, amar takale wrote: Dear Lali Mohan Exactly i want data by sort based on multiple criteria range. Regards Amar On Fri, Jun 27, 2014 at 3:20 PM, amar takale amart...@gmail.com javascript: wrote: Dear Pramod First thanks for reply.If you see Raw sheet (Row No 7) cell D7,here are I double click then mkt cap will sort as per criteria between 1000 to 5000 automatically which show output in output sheet,like this i will click each criteria Price,Roe... etc. data sort as per criteria range in cell E5 E6,F5 F6etc. I required for this VBA code to sort data. Only I double click Row 7. I hoe you understand my issue Regards Amar On Fri, Jun 27, 2014 at 3:02 PM, Pramod Singh pram...@gmail.com javascript: wrote: Not Sure What you are saying. Need more description. On Fri, Jun 27, 2014 at 11:28 AM, amar takale amart...@gmail.com javascript: wrote: Dear All friends Can anyone suggestion pls? On Wed, Jun 25, 2014 at 12:17 PM, amar takale amart...@gmail.com javascript: wrote: Dear Expert team I Required code for sort data as per criteria range in between numbers.PFA with more detailed in sheet. Regards Amar -- 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...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- 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
Re: $$Excel-Macros$$ Sort VBA Code Modified
Yes it will work in excel 2003 and above On Friday, 27 June 2014 15:31:54 UTC+5:30, amar takale wrote: Dear Lalit Mohan Then it code not work in excel 2003? On Fri, Jun 27, 2014 at 3:24 PM, Lalit Mohan Pandey mohan.p...@gmail.com javascript: wrote: change your code as i have changed it: Sub SECEPSDES() With ActiveWorkbook.Worksheets(Sector Valn) .Sort.SortFields.Clear .Range(A6:U21).Sort Key1:=.Range(E7:E21), Order1:=xlDescending, Header:=xlYes End With End Sub Sub SECEPSASC() With ActiveWorkbook.Worksheets(Sector Valn) .Sort.SortFields.Clear .Range(A6:U21).Sort Key1:=.Range(E7:E21), Order1:=xlAscending, Header:=xlYes End With End Sub Do changes for other procedure this is excel 2003 sorting method which is supported by any version above 2003 but what you are using in your code is 2007 data sorting method which is not supported by 2003 :) Hope you understand On Friday, 27 June 2014 10:56:51 UTC+5:30, amar takale wrote: Dear all pls help me on this topic. regards amar On Thu, Jun 26, 2014 at 1:21 PM, amar takale amart...@gmail.com wrote: Dear Experts I have below attached excel which open in excel 2007 then sort VBA code work perfect but when it open in excel 2003 then show error.So pls guide me about it error work also open it in excel 2003. I use Excel 2003 So I want this file work in excel 2003 So I continue use it with range change only in code Thanks Regards Amar -- 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...@googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- 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...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- 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
$$Excel-Macros$$ Re: Fw: Query on Text to Columns function
Try this Option Explicit Sub SplitValues() Dim lngLoop As Long Dim vararrRawData() As Variant Dim vararrSplitData As Variant 'Const variable change accordingly as per your requirement Const strDataRangeToSplit As String = C1:C65536 Const strDataShtNameAs String = Sheet1 Const strDataOutputShtName As String = Sheet1 Const strDataOutputCell As String = J1 With ThisWorkbook With .Worksheets(strDataShtName) vararrRawData = .Range(strDataRangeToSplit).Value End With With .Worksheets(strDataOutputShtName) For lngLoop = LBound(vararrRawData) To UBound(vararrRawData) .Range(strDataOutputCell).Offset(lngLoop - 1, 0).Value = vararrRawData(lngLoop, 1) vararrSplitData = Split(Replace(vararrRawData(lngLoop, 1), ., .'), .) If UBound(vararrSplitData) = 0 Then vararrSplitData(0) = vbNullString End If .Range(strDataOutputCell).Offset(lngLoop - 1, 1).Resize(, UBound(vararrSplitData) + 1).Value = vararrSplitData Next lngLoop End With End With lngLoop = Empty Erase vararrRawData vararrSplitData = Empty End Sub On Thursday, 29 August 2013 13:32:17 UTC+5:30, Pooja wrote: Hi All, Please advice on the below query. Regards Pooja Sharma Sent from BlackBerry® on Airtel -- *From: * Pooja Sharma pooja@dc.ibm.com javascript: *Date: *Thu, 29 Aug 2013 13:19:08 +0530 *To: *vatspo...@gmail.com javascript: *Subject: *Query on Text to Columns function Hi All, I am seeking for your support on one of my file (attached), wherein I have to separate the values in different columns, the separator (Delimitor) value is the . sign. I was trying to do this with the function text to columns, but it removes the zero value and I need these 0 values too. Please advice. -- 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.
$$Excel-Macros$$ Re: Export 2 coloums data from 1 to 30 sheet to one new excel or in a different new sheet
If it possible please attach sample data so that we can help you On Thursday, 29 August 2013 14:38:39 UTC+5:30, Dhamo dharan wrote: Dear Friends Kindly help to export data like name and percentage from all the sheet (30 sheets) to a new file or in a different sheet. E.g Name project target ,weekly %, and monthly percent% ,present, absent are there in all sheet but i want to export only Name and monthly percentage to different excel or different sheet in same excel. Kindly help me.. -- 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.
$$Excel-Macros$$ Re: VBA getting data from web site
Yes you can download data but only thing you need to do is to arrange data in excel file by using excel vba. :) On Wednesday, 28 August 2013 18:59:19 UTC+5:30, Mithun Dhar wrote: I am trying to import data from IE sharepoint web site. The sharepoint website just contains one table with headings and data. If I go to the developers tools, I can see the contents of data under the Class Name. Is it possible to download those data into excel? -- 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$$ I need to delete entire rows which contain exact matches from a static list on a seperate sheet
Please check hope this is what you are looking for: Option Explicit Sub RemoveData() Dim wbkFile As Workbook Dim vararrDataToRemove()As Variant Dim lngLoop As Long Dim rngData As Range Dim rngTemp As Range 'Const variable change accordingly as per your need like path and sheet name Const strDataFilePath As String = D:\Users\llm82928\Desktop\example file.xlsx Const strCompareFilePathAs String = D:\Users\llm82928\Desktop\SLP.xlsx Const strCompareDataStartCell As String = A3 Const strDataShtNameAs String = GM00410.BUY.HOLD.WASH Const strCompDataShtNameAs String = slp Set wbkFile = OpenFile(strCompareFilePath, False, True) If Not wbkFile Is Nothing Then With wbkFile.Worksheets(strCompDataShtName) Set rngData = .Range(strCompareDataStartCell) If .Cells(.Rows.Count, rngData.Column).End(xlUp).Row = rngData.Row Then Set rngData = .Range(rngData.Address, .Cells(.Rows.Count, rngData.Column).End(xlUp).Address) vararrDataToRemove = rngData.Value wbkFile.Close 0 Set rngData = Nothing Set wbkFile = OpenFile(strDataFilePath, False, False) If Not wbkFile Is Nothing Then With wbkFile.Worksheets(strDataShtName) For lngLoop = LBound(vararrDataToRemove) To UBound(vararrDataToRemove) Set rngTemp = Nothing If LenB(vararrDataToRemove(lngLoop, 1)) 0 Then On Error Resume Next Set rngTemp = .UsedRange.Resize(, 1).Find(vararrDataToRemove(lngLoop, 1), LookIn:=xlValues, LookAt:=xlWhole) On Error GoTo -1: On Error GoTo 0: Err.Clear If Not rngTemp Is Nothing Then If rngData Is Nothing Then Set rngData = rngTemp Else Set rngData = Union(rngData, rngTemp) End If End If End If Next lngLoop If Not rngData Is Nothing Then rngData.EntireRow.Delete End If If MsgBox(Do you want to save the file?, vbQuestion + vbYesNo + vbDefaultButton2) = vbYes Then wbkFile.Save End If End With End If End If End With End If Set wbkFile = Nothing Erase vararrDataToRemove lngLoop = Empty Set rngData = Nothing Set rngTemp = Nothing End Sub Function OpenFile(ByVal strFilePath As String, ByVal blnUpdateLink As Boolean, ByVal blnReadOnly As Boolean) As Object Set OpenFile = Nothing On Error Resume Next Set OpenFile = Workbooks.Open(strFilePath, blnUpdateLink, blnReadOnly) On Error GoTo -1: On Error GoTo 0: Err.Clear End Function On Thursday, 29 August 2013 04:15:27 UTC+5:30, sven...@gmail.com wrote: I have done a little work on my own and have a portion of it done. I can now automatically perform a vlookup against the parts listing by selecting the column the parts are located in and starting my macro. The code I used to do this is as follows: Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove ActiveCell.Formula = =VLOOKUP(RC[1],SLP.xlsx!C1,1,FALSE) Range(ActiveCell, ActiveCell.Offset(6, 0)).Select Selection.FillDown End Sub Now I need it to compare the values of the activecell and the activecell.offset(0,1) and if it is identical to delete the entire row. -- 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
$$Excel-Macros$$ Re: Idle timer that saves, closes and logs off user after 5 min
You can try below link: http://www.vbaexpress.com/kb/getarticle.php?kb_id=515 On Wednesday, 14 August 2013 03:36:42 UTC+5:30, Tom B. wrote: Hi I have a spreadsheet that records user inputs and then stores the inputs along with Date/Time and user ID. Since this will be accessed on a shared computer, that User ID for each data set needs to belong to the person entering the data. So, to at least increase the potential that each user logs on as themselves, I have been searching for VBA code that is triggered by an inactivity (Idle) timer that can be set to about 5 minutes. I have inserted code that is not working but I may be close, so I have the spreadsheet attached. Any help would be appreciated. Thanks TomTBD -- 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.
$$Excel-Macros$$ Re: Run time error 6
Instead of sharing Screen shot it would be nice if you can share file On Tuesday, 13 August 2013 20:01:06 UTC+5:30, Chandru wrote: Hi, While running macro am getting run time error 6 as shown in the attached file. I am using huge data which contains 9 rows. Could you please let me know how to run macro with hugre data. Thanks, Chandra -- 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$$ Macro that will leave data and new data from query when ran
Can you share your file and database so that we can check. On Monday, 12 August 2013 22:03:35 UTC+5:30, christy palmer wrote: Thank you for all your help. I really appreciate it. The macro is still over writing the data in the spreadsheet :-( I swear I've tried everything. I think i'm just over it now :-( On Fri, Aug 9, 2013 at 8:42 PM, Anoop K Sharma aks.sh...@gmail.comjavascript: wrote: Hey, Use the Attached one... I can see the code written here a little bit slow... it can be made faster if I could get your database structure... Thanks, Anoop Sr. Developer On 8/9/13, Lalit Mohan Pandey mohan.p...@gmail.com javascript: wrote: Replace line *Erow = Me.Range(A1).End(xlDown).row + 1* with this one *If LenB(Trim(Me.Cells(13, 2).Value)) = 0 Then Me.Cells(13, 2).Value = DATES* *Erow = Me.Cells(Me.Rows.Count, 2).End(xlUp).Offset(1).row* If it will not work then you need to share your database as well so that we can run this utility. On Thursday, 8 August 2013 21:59:19 UTC+5:30, christy palmer wrote: the same thing happens. the data is over written. using your code i do not get an overflow error :-) but i still can't get the data to add rather than replace :-( On Wed, Aug 7, 2013 at 6:13 PM, Anoop K Sharma aks.sh...@gmail.comjavascript: wrote: Replace your code with below one... This will be great if you could provide your original file. Dim state As String Dim startdate As String Dim enddate As String, Erow as long Sub btn1_click() state = Me.Cells(3, 3) startdate = Me.Cells(4, 3) enddate = Me.Cells(5, 3) MsgBox Input parameters - State = state Date = startdate to enddate Erow=Me.range(A1).End(xlDown).row+1 MsgBox Depending on the volume of data, this report would take 5-10 minutes to fetch results, vbInformation callDB End Sub Sub callDB() On Error GoTo ErrHandler Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim query As String Dim strqry As String Dim txt As String Dim row As Integer Dim col As Integer Set con = CreateObject(ADODB.Connection) Set rs = CreateObject(ADODB.Recordset) strCon = Provider=OraOLEDB.Oracle;Data Source=enCPR2;User Id=***;Password=***!! con.ConnectionString = strCon con.Open strqry = strqry SELECT strqry = strqry DATES, rs.Open strqry, con If Not rs.EOF Then rs.MoveFirst End If row = 14 col = 2 Do While Not rs.EOF For I = 0 To rs.Fields.Count - 1 Me.Cells(Erow, col) = rs.Fields(I) col = col + 1 Next I Erow = Erow + 1 col = 2 rs.MoveNext Loop ExitHandler: rs.Close con.Close Set rs = Nothing Set con = Nothing MsgBox Report generation is completed Exit Sub On Thu, Aug 8, 2013 at 12:54 AM, christy palmer chris...@gmail.comjavascript: wrote: I have a macro that works perfectly except for everytime it's ran it wipes the spreadsheet clean and inserts the new values. I need it to insert the new data while leaving the old data without giving me a stackover flow error :-/ Any help would be greatly appreciated. Dim state As String Dim startdate As String Dim enddate As String Sub btn1_click() state = Me.Cells(3, 3) startdate = Me.Cells(4, 3) enddate = Me.Cells(5, 3) MsgBox Input parameters - State = state Date = startdate to enddate For row = 14 To 100 For col = 2 To 100 Me.Cells(row, col) = Next col Next row MsgBox Depending on the volume of data, this report would take 5-10 minutes to fetch results, vbInformation callDB End Sub Sub callDB() On Error GoTo ErrHandler Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim query As String Dim strqry As String Dim txt As String Dim row As Integer Dim col As Integer Set con = CreateObject(ADODB.Connection) Set rs = CreateObject(ADODB.Recordset) strCon = Provider=OraOLEDB.Oracle;Data Source=enCPR2;User Id=***;Password=***!! con.ConnectionString = strCon con.Open strqry = strqry SELECT strqry = strqry DATES, rs.Open strqry, con If Not rs.EOF Then rs.MoveFirst End If row = 14 col = 2 Do While Not rs.EOF For I = 0 To rs.Fields.Count - 1 Me.Cells(row, col) = rs.Fields(I) col = col + 1 Next I row = row + 1 col = 2 rs.MoveNext Loop
Re: $$Excel-Macros$$ Macro that will leave data and new data from query when ran
Replace line *Erow = Me.Range(A1).End(xlDown).row + 1* with this one *If LenB(Trim(Me.Cells(13, 2).Value)) = 0 Then Me.Cells(13, 2).Value = DATES* *Erow = Me.Cells(Me.Rows.Count, 2).End(xlUp).Offset(1).row* If it will not work then you need to share your database as well so that we can run this utility. On Thursday, 8 August 2013 21:59:19 UTC+5:30, christy palmer wrote: the same thing happens. the data is over written. using your code i do not get an overflow error :-) but i still can't get the data to add rather than replace :-( On Wed, Aug 7, 2013 at 6:13 PM, Anoop K Sharma aks.sh...@gmail.comjavascript: wrote: Replace your code with below one... This will be great if you could provide your original file. Dim state As String Dim startdate As String Dim enddate As String, Erow as long Sub btn1_click() state = Me.Cells(3, 3) startdate = Me.Cells(4, 3) enddate = Me.Cells(5, 3) MsgBox Input parameters - State = state Date = startdate to enddate Erow=Me.range(A1).End(xlDown).row+1 MsgBox Depending on the volume of data, this report would take 5-10 minutes to fetch results, vbInformation callDB End Sub Sub callDB() On Error GoTo ErrHandler Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim query As String Dim strqry As String Dim txt As String Dim row As Integer Dim col As Integer Set con = CreateObject(ADODB.Connection) Set rs = CreateObject(ADODB.Recordset) strCon = Provider=OraOLEDB.Oracle;Data Source=enCPR2;User Id=***;Password=***!! con.ConnectionString = strCon con.Open strqry = strqry SELECT strqry = strqry DATES, rs.Open strqry, con If Not rs.EOF Then rs.MoveFirst End If row = 14 col = 2 Do While Not rs.EOF For I = 0 To rs.Fields.Count - 1 Me.Cells(Erow, col) = rs.Fields(I) col = col + 1 Next I Erow = Erow + 1 col = 2 rs.MoveNext Loop ExitHandler: rs.Close con.Close Set rs = Nothing Set con = Nothing MsgBox Report generation is completed Exit Sub On Thu, Aug 8, 2013 at 12:54 AM, christy palmer chris...@gmail.comjavascript: wrote: I have a macro that works perfectly except for everytime it's ran it wipes the spreadsheet clean and inserts the new values. I need it to insert the new data while leaving the old data without giving me a stackover flow error :-/ Any help would be greatly appreciated. Dim state As String Dim startdate As String Dim enddate As String Sub btn1_click() state = Me.Cells(3, 3) startdate = Me.Cells(4, 3) enddate = Me.Cells(5, 3) MsgBox Input parameters - State = state Date = startdate to enddate For row = 14 To 100 For col = 2 To 100 Me.Cells(row, col) = Next col Next row MsgBox Depending on the volume of data, this report would take 5-10 minutes to fetch results, vbInformation callDB End Sub Sub callDB() On Error GoTo ErrHandler Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim query As String Dim strqry As String Dim txt As String Dim row As Integer Dim col As Integer Set con = CreateObject(ADODB.Connection) Set rs = CreateObject(ADODB.Recordset) strCon = Provider=OraOLEDB.Oracle;Data Source=enCPR2;User Id=***;Password=***!! con.ConnectionString = strCon con.Open strqry = strqry SELECT strqry = strqry DATES, rs.Open strqry, con If Not rs.EOF Then rs.MoveFirst End If row = 14 col = 2 Do While Not rs.EOF For I = 0 To rs.Fields.Count - 1 Me.Cells(row, col) = rs.Fields(I) col = col + 1 Next I row = row + 1 col = 2 rs.MoveNext Loop ExitHandler: rs.Close con.Close Set rs = Nothing Set con = Nothing MsgBox Report generation is completed Exit 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
$$Excel-Macros$$ Re: Preventing opening a macro enabled excel file to be opened in OpenOffice or Similar opensource products
I am not sure about this becuse Microsoft excel vba doesn't work in Openoffice and if you really want to prevent your code then go for COM addin Using VSTO. On Wednesday, 31 July 2013 23:58:25 UTC+5:30, Aashish Watve wrote: Dear All, I have a very particular question. Is there a way to prevent opening a macro enabled excel spreadsheet being opened in OpenOffice products? One method suggested on some other forum was to check if the file is opened by excel application or other. If it is not excel then close it. 1) How to check whether it is excel application or not? 2) Will the hidden and protected worksheets remain hidden in Openoffice even if the macro doesn't run at the time of opening the file? Let me know, if someone has any experience in this. Aashish -- 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.
$$Excel-Macros$$ Re: Need a Fastest way
How you highlighted cells in a range? On Wednesday, 19 June 2013 17:58:39 UTC+5:30, Rajan_Verma wrote: can anyone suggest a faster way to know which cells are highlighted in a Range ? suppose i have data of 1 lac cell and i have some cells highlighted , i want to know address of that cells. -- Regards Rajan verma +91 9158998701 -- 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.
$$Excel-Macros$$ Re: List in Userform
Why don't you use listview form control. On Friday, 7 June 2013 18:38:48 UTC+5:30, Chandru wrote: Hi, How to get List as attached in the userform Regards, Chandra -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Functions returning an integer
Share your file. On Monday, 29 April 2013 14:30:13 UTC+5:30, colmkav wrote: Hi, I am trying to execute this function to return the column number of a heading. However although the value is correctly 3 when it reaches the end of the function it becomes 0 when I set it to iFund2Col after leaving the function. What am I doing wrong here? Sub test() Dim iFund2Col As Integer Dim ws As Worksheet Dim wb As Workbook Set wb = Workbooks.Open(H:\\fnd_CRLQRPT020130424.xls) Set ws = wb.Worksheets(1) If (iFund2Col = GetHeadingColumnNumber(ws, Fund)) 0 Then MsgBox ERROR End If End Sub Function GetHeadingColumnNumber(ByVal ws As Worksheet, sHeading As String) As Integer Dim rg As Range Dim iColumnNumber As Integer GetHeadingColumnNumber = -1 Set rg = Nothing On Error Resume Next Set rg = ws.Range(1:1).Find(What:=sHeading, LookAt:=xlWhole) On Error GoTo ERROR_HANDLER If rg Is Nothing Then Err.Raise -99, GetHeadingColumnNumber, Unable to find heading: sHeading in worksheet: ws.Name End If GetHeadingColumnNumber = rg.Column Exit Function ERROR_HANDLER: ErrorHandler Err End Function -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: FW: $$Excel-Macros$$ Re: Need expert advise for multipal Selection
Find below link for solution: http://www.excelfox.com/forum/f2/need-array-formula-help-912/ On Thursday, 18 April 2013 10:50:04 UTC+5:30, Dhaval Shah wrote: Hello Lalitji, Thanks for you formula sir i modified with formula littel bit now its not working actually while i run formula for one single stocks its work the same time i run formula for too many stocks its dosent work Here with atteach the sheet of Main sheet ( single stocks ) another sheet main sheet revised ( too many stocks ) pls have a look Thanks Dhaval Shah *Warm Regards,* *Dhaval Shah * M) 98240 35253 | O) 079 - 40055253 BBM : 2A33C8FB | What's up : 98240 35253 [image: email_icon_small] javascript:[image: index]http://www.facebook.com/todashah[image: dd] http://www.linkedin.com/profile/view?id=37508924trk=tab_pro[image: Small-images-Twitter-Logo] https://twitter.com/dhaval4205 *Please consider the enviroment before priting this email * On Wed, Apr 3, 2013 at 12:25 PM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: Hi Dhaval, Copy below formula at cell address C11 and confirm it with CSE *=MAX(($B$2:$B$68=$S$10)*($A$2:$A$68 = FUTSTK)*$I$2:$I$68)* Select cell R14:R19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),1),)* Select cell S14:S19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),3),)* Select cell T14:T19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),4),)* Select cell U14:U19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),12),)* Select cell V14:V19, press F2 and paste below formuls and confirm it with CSE * =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68=$S$11)*($E$1:$E$68=$R$13)*($M$1:$M$680),0,))*ROW($A$1:$A$68),),ROW($A$1:$A$68)),),13),) * Select cell W14:W19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),1),)* Select cell X14:X19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),3),)* Select cell Y14:Y19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),4),)* Select cell Z14:Z19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),12),)* Select cell AA14:AA19, press F2 and paste below formuls and confirm it with CSE * =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68=$S$11)*($E$1:$E$68=$W$13)*($M$1:$M$680),0,))*ROW($A$1:$A$68),),ROW($A$1:$A$68)),),13),) * Hope it will give you the same you expact. On Tuesday, 2 April 2013 12:53:21 UTC+5:30, Dhaval Shah wrote: Dear Thanks for u r prompt reply but out put is not as per my requirement pls check the reference sheet On Tue, Apr 2, 2013 at 10:49 AM, xlstime xls...@gmail.com wrote: Hi Dhaval, * dhaval shah (1).xlsxhttps://docs.google.com/file/d/0B4LBSzxr1QTiVjZoYkRneFFUY1k/edit?usp=drive_web * https://docs.google.com/file/d/0B4LBSzxr1QTiVjZoYkRneFFUY1k/edit?usp=drive_web PFA...https://docs.google.com/file/d/0B4LBSzxr1QTiVjZoYkRneFFUY1k/edit?usp=drive_web https://docs.google.com/file/d/0B4LBSzxr1QTiVjZoYkRneFFUY1k/edit?usp=drive_web https://docs.google.com/file/d/0B4LBSzxr1QTiVjZoYkRneFFUY1k/edit?usp=drive_web . Enjoy Team XLShttps://docs.google.com/file/d/0B4LBSzxr1QTiVjZoYkRneFFUY1k/edit?usp=drive_web https://docs.google.com/file/d/0B4LBSzxr1QTiVjZoYkRneFFUY1k/edit?usp=drive_web On Tue, Apr 2, 2013 at 10:39 AM, Dhaval Shah *todhav...@gmail.com* wrote:https://docs.google.com/file/d/0B4LBSzxr1QTiVjZoYkRneFFUY1k/edit?usp=drive_web cmp
$$Excel-Macros$$ Re: Move formula to another sheet
Change your formula with this one * =IF(ISERROR(SMALL(IF(Sheet1!C$1:C$145=Sheet1!M$3,ROW(Sheet1!A$1:A$145),),ROW()-(ROW()-1))),,INDEX(Sheet1!D$1:D$145,SMALL(IF(Sheet1!C$1:C$145=Sheet1!M$3,ROW(Sheet1!A$1:A$145),),ROW()-(ROW()-1 * On Wednesday, 17 April 2013 18:45:10 UTC+5:30, amar takale wrote: Dear Experts I want to Move formula to another sheet for output (yellow Color output).But when i move output to another sheet then answer is blank bcos of Sheet name address missing. I want to output move sheet2 (Cell Range B28:C45).I also try in sheet 2 but it show cell blank.Pls tell me correct formula. Appreciate the help. Regards Amar -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Click Option for Multiple Criteria
Follow steps and try this. Hope this will work for you: 1. *Right Click on Cell C5* 2. *Create a hyperlink with cell refrence C5 in the same (output sheet )* 3. *Do same for Cell D5 and create hyperlink with cell refrence D5 in the same (output sheet )* 4. *Right click on output sheet and view code and copy-paste below code to get the output you want.* * * *Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)* * * *Dim wksSht As Worksheet* *Dim rngRangeAs Range* *Dim varData() As Variant* ** *Const strOutPutShtName As String = Output* *Const strDataStartCell As String = K7* *Const strOutPutDataStartCellAs String = B9* *Const lngTotalDataCol As Long = 5* ** *Set rngRange = Nothing* *On Error Resume Next* *Set rngRange = Worksheets(strOutPutShtName).Range(Target.SubAddress)* *On Error GoTo -1: On Error GoTo 0: Err.Clear* *If Not rngRange Is Nothing Then* *If Len(Trim(rngRange.Value)) 0 Then* *Set wksSht = Nothing* *On Error Resume Next* *Set wksSht = Worksheets(Trim(rngRange.Value))* *On Error GoTo -1: On Error GoTo 0: Err.Clear* *If Not wksSht Is Nothing Then* *With wksSht* *Set rngRange = .Range(strDataStartCell)* *Set rngRange = rngRange.Resize(.Cells(.Rows.Count, rngRange.Column).End(xlUp).Row - rngRange.Row + 1)* *Set rngRange = rngRange.Resize(, .Cells(rngRange.Row, .Columns.Count).End(xlToLeft).Column - rngRange.Column + 1)* *If rngRange.Rows.Count 1 Then* *Set rngRange = Intersect(rngRange, rngRange.Offset(1))* *varData = rngRange.Value* *With Worksheets(strOutPutShtName)* *Set rngRange = .Range(strOutPutDataStartCell).CurrentRegion* *Set rngRange = Intersect(rngRange, rngRange.Offset(1, 1))* *rngRange.ClearContents* *rngRange.Resize(1, 1).Resize(UBound(varData), UBound(varData, 2)).Value = varData* *End With* *End If* *End With* *End If* *End If* *End If* ** *Set wksSht = Nothing* *Set rngRange = Nothing* *Erase varData* ** *End Sub* On Tuesday, 16 April 2013 12:14:42 UTC+5:30, amar takale wrote: Dear Chaya Thanks very much for Struggle for my Solution but yet not got output as I wish.You know that Yesterday I attached Sheet (Final+3) for Remove NA error in that sheet I want click Option for output not Data validation.I attached sheet here.Pls see it Regards Amar On Tue, Apr 16, 2013 at 11:48 AM, Chaya chaya...@gmail.com javascript:wrote: Dear Amar, PFA... Like this you are expecting right ? Regards, Chaya On Tue, Apr 16, 2013 at 11:43 AM, amar takale amart...@gmail.comjavascript: wrote: Dear Chaya It's working good Good one attempt also useful for me. But exact which I want to solution not attempt.I want only click on criteria name then show data as like any webpage show.So Pls try to understand me give Only click solution. Regards Amar On Tue, Apr 16, 2013 at 11:05 AM, Chaya chaya...@gmail.comjavascript: wrote: Dear Amit, PFA, i hope it will meet your requirement. Regards, Chaya On Tue, Apr 16, 2013 at 10:36 AM, amar takale amart...@gmail.comjavascript: wrote: Dear Experts I want help all our members of group who solved my issue of Click option. We also use Data validation.But I required Click option on criteria. Appreciate the help Regards Amar -- 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...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.comjavascript: .
Re: $$Excel-Macros$$ Document number to become automatically
Do you want the Doc. No. after you fill column a and c or what and what should be the doc no how we decide. On Monday, 15 April 2013 11:09:35 UTC+5:30, Pravin Gunjal wrote: *Hi, I want the result : Doc. No. to be come when the stockist and location is getting typed in column C A accordingly as per the new stockist. * * * *For every new location I will fix / give the first document no.* [image: Inline image 2] *Regards* *Pravin Gunjal* -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: cell formula
Change this line activecell.FormulaR1C1 = =D6 YEAR(MonthEndDate) . rg.value . \Outputs\ with activecell.Formula = =D6 YEAR(MonthEndDate) . rg.value . \Outputs\ On Monday, 15 April 2013 13:28:30 UTC+5:30, colmkav wrote: Hi, I am trying to set the formula of a cell to be the following: =D6 YEAR(MonthEndDate) . MONTH(MonthEndDate) . \Outputs\ However the following code gives this forumula instead: = 'D6' YEAR(MonthEndDate) . rg.value . \Outputs\ How can I correct this to give the formula I want? Sub test() Dim sMonthEnd As String Dim iMonthEnd As Integer Dim rg As Range sMonthEnd = Month(Range(MonthEndDate)) Set rg = Range(MonthlyFolders).Find(What:=sMonthEnd).Offset(0, 1) Range(OutputFolder).FormulaR1C1 = =D6 YEAR(MonthEndDate) . rg.value . \Outputs\ 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ print all URL details in a PDF file
Please give me some time i am working on this and this can be done. so don't worry man. I will come up with a solution by eod. On Monday, 15 April 2013 12:58:16 UTC+5:30, lakshm...@yahoo.com wrote: still waiting *Lakshman Prasad* Sr. Manager (Finance) GAURSONS INDIA LIMITED Ph: +91 12 456777 Mob. No. 9582279261 Website: http://www.gaursonsindia.com/ *From:* LAKSHMAN PRASAD lakshm...@yahoo.com javascript: *To:* excel-...@googlegroups.com javascript: excel-...@googlegroups.com javascript: *Cc:* gawlia...@gmail.com javascript: gawlia...@gmail.comjavascript: *Sent:* Saturday, April 13, 2013 10:45 AM *Subject:* Re: $$Excel-Macros$$ print all URL details in a PDF file Dear Anil, I don’t to print excel details, I want to print column D all details after clicked URL, like when I click cell d2 a detail will open, I want print that details. See the attached file. *Lakshman Prasad* *From:* अनिल नारायण गवली gawlia...@gmail.com javascript: *To:* excel-...@googlegroups.com javascript: excel-...@googlegroups.com javascript:; lakshm...@yahoo.comjavascript: *Sent:* Friday, April 12, 2013 3:12 PM *Subject:* Re: $$Excel-Macros$$ print all URL details in a PDF file Dear Lakshman, Pls see the attached sheet. and Kindly run then macro. Warm Regards, Gawli Anil On Fri, Apr 12, 2013 at 2:07 PM, Chaya chaya...@gmail.com javascript:wrote: Dear Lakshman, I got your point, i am not sure i can do, but i can only try as i am a very new learner. After all our experts will surely tackle out this query. Regards, Chaya On Fri, Apr 12, 2013 at 1:14 PM, LAKSHMAN PRASAD lakshm...@yahoo.comjavascript: wrote: Dear chaya Thanks for prompt reply, but I want to print column D all details, like when I click cell d2 a detail will open, I want print that details. See the attached file. *Lakshman Prasad* *From:* Chaya chaya...@gmail.com javascript: *To:* excel-...@googlegroups.com javascript: excel-...@googlegroups.com javascript: *Sent:* Friday, April 12, 2013 1:00 PM *Subject:* Re: $$Excel-Macros$$ print all URL details in a PDF file Dear Lakshman, If you have a PDF creater Installed, you can use this code, Just i recorded. Sub PrintUrl() Sheets(Sheet1).Select Range(D2:D63).Select Selection.Copy Workbooks.Add ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False End Sub Regards, Chaya On Fri, Apr 12, 2013 at 12:36 PM, LAKSHMAN PRASAD lakshm...@yahoo.comjavascript: wrote: Dear expert I have a excel file, in a sheet contain some details with URL, is there any formula add-on or marco to print all URL details in a PDF file. *Lakshman Prasad* -- 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 mailto:excel-macros%2bunsubscr...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- Regards, Chaya -- 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
Re: $$Excel-Macros$$ Number to words
You can also try this http://www.excelfox.com/forum/f12/number-into-words-rupees-45/ On Monday, 15 April 2013 14:08:29 UTC+5:30, Kuldeep Singh wrote: Hi Abhishek, PFA by Noorain Sir. Regards, Kuldeep Singh Info Edge India Limited (naukri.com) Phone.: +91-0120-4841100, Extn.: 2467, 9716615535 naukri...@gmail.com javascript: || www.naukri.com *Please* *Consider the environment. Please don't print this e-mail unless you really need to.* * * On Mon, Apr 15, 2013 at 1:44 PM, Abhishek Verma ave...@salujaandassociates.com javascript: wrote: Hi If there any formula in excel to convert a numeric figure into words -- Thanks Regards *CA Abhishek Verma* *ACA, DISA, MBA, B.Com (Hons)* *Partner* *SALUJA ASSOCIATES.* *CHARTERED ACCOUNTANTS* 69,Desh Bandhu Gupta Road, Jolly Building, 2nd Floor Pahar Ganj New Delhi - 110 055. Mobile No. 0-9818-522-747 Email: ave...@salujaandassociates.com javascript: Web Site : www.salujaandassociates.com skype : caabhishekverma * * *Think before you print. Go Green.* ** The contents of this e-mail are confidential. If you are not the named addressee or if this transmission has been addressed to you in error, please notify the sender immediately and then delete this e-mail. Any unauthorized copying and transmission is forbidden. E-Mail transmission cannot be guaranteed to be secure. If verification is required, please request a hard copy version. Please note, that incoming email is not checked regularly. This may result in a failure to comply with legal or contractual terms. Therefore it is not sufficient, to sent any legal or contractual declarations by email. In no event will Saluja Associates or named person be liable to you or any third party for any direct, indirect, consequential, special or exemplary damages or lost profit resulting from this failure. -- 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...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/excel-macros?hl=en. 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Identify occurances unique to after a date and not before
I think the output would containd three values Cards, Ballons, Streamers and for this output below is the formula. Confirm it with CSE * =IFERROR(INDEX($A$1:$A$8,SMALL(IF(((VALUE($B$2:$B$8)=VALUE($D$1))*ROW($A$2:$A$8))=0,,((VALUE($B$2:$B$8)=VALUE($D$1))*ROW($A$2:$A$8))),ROW($A$1:$A$8))),) * On Friday, 12 April 2013 05:25:14 UTC+5:30, Minties wrote: Hi, I was hopiong somebody could help me with a formula that would identify anyoccurences that happen after a particular date but not before. I.e. I havea column of values (say product orders) that occur accross dates. Bearing in mind that more than one order can occur on a particular date (so a particular date may appear more than once the date column). Also more than one product of the same kind can be ordered on the same date (therefore more than one instance of the same product may appear on the same date and also on different dates. I want to identify what products were ordered after a particular date but not before. As an example I will use the table below Order Product Date Balloons 01/01/2013 Cards 01/01/2013 Balloons 01/01/2013 Balloons 02/01/2013 Cards 03/01/2013 Balloons 03/01/2013 Streamers 03/01/2013 In this example I want to identify what products were ordered on or after 03/01/2013 but not before. In this example my expected result would return Streamers as a text string. Would appreciate any help with this. Regards Pete -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: how to search for max value in alpha neumeric range or col.
Is there any pattern of ID's like starting 3 characters are string and the rest part is Numeric or something like this. Please explain. On Thursday, 11 April 2013 03:51:42 UTC+5:30, shau...@gmail.com wrote: I have a sheet with lot IDs in Col A AAA100 CPP100 AAA101 AAA102 CPP101 and so on. I need to find max value on AAA series or in CPP series, I can change that to suite my needs. if there can be a vb code it would be nice. Thank you -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Re: Remove Duplicate with Criteria
) Then* *varArrItem(0, 0) = varItemCount(lngLoop1, 0)* *varArrItem(0, 1) = varItemCount(lngLoop1, 1)* *ElseIf varArrItem(0, 0) varItemCount(lngLoop1, 0) Then* *varArrItem(0, 0) = varItemCount(lngLoop1, 0)* *varArrItem(0, 1) = varItemCount(lngLoop1, 1)* *End If* *Next lngLoop1* *objDicItem.Item(varArrData(varArrItem(0, 1), 5)) = Null* *varArrFinalData(objDicItem.Count, 1) = varArrData(varArrItem(0, 1), 1)* *varArrFinalData(objDicItem.Count, 2) = varArrData(varArrItem(0, 1), 2)* *varArrFinalData(objDicItem.Count, 3) = varArrData(varArrItem(0, 1), 3)* *varArrFinalData(objDicItem.Count, 4) = varArrData(varArrItem(0, 1), 4)* *varArrFinalData(objDicItem.Count, 5) = varArrData(varArrItem(0, 1), 5)* *Erase varItemCount* *Erase varArrItem* *End If* *End If* *Erase varArrTemp* *Next lngLoop* *.Range(strFinalDataCell).CurrentRegion.ClearContents* *.Range(strFinalDataCell).Offset(-1).Resize(1, UBound(varArrFinalData, 2)).Value = .Range(strDataRange).Resize(1).Offset(-1).Value* *.Range(strFinalDataCell).Resize(UBound(varArrFinalData), UBound(varArrFinalData, 2)).Value = varArrFinalData* *.Range(strFinalDataCell).Resize(UBound(varArrFinalData), UBound(varArrFinalData, 2)).EntireColumn.AutoFit* *End If* *End With* ** *Erase varArrData* *Erase varArrFinalData* *Erase varArrKeys* *Erase varItemCount* *Erase varArrItem* *varArrTemp = Empty* *lngLoop = Empty* *lngLoop1 = Empty* *Set objDic = Nothing* *Set objDicItem = Nothing* * * *End Sub* On Thursday, 4 April 2013 10:18:11 UTC+5:30, chaya moni wrote: Sir, Might contains data in second row that which might not contains in the first row in duplicate. Ragards, Chaya On Thu, Apr 4, 2013 at 10:06 AM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: What is the criteria to not delete duplicates. On Thursday, 4 April 2013 09:34:49 UTC+5:30, chaya moni wrote: Dear Lalit Sir, Thanks for the reply. i tried to explain a bit more in the attached file. please refer sir. Regards, Chaya On Thu, Apr 4, 2013 at 8:58 AM, Lalit Mohan Pandey mohan.p...@gmail.com wrote: I am not able to understand your both conditions :(. First select all column then remove duplicate. (Only 29 duplicate value found and removed.) On Wednesday, 3 April 2013 18:37:15 UTC+5:30, chaya moni wrote: Dear Experts, I have another problem arise in excel while removing duplicate. i have attached the sample and explained in the attachment. Please find the attachment. Thank you in advance Regards, Chaya -- 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-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en . 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
$$Excel-Macros$$ Re: Unhide Sheets
Try this code. Hope it will help you *Sub Test_Lalit_Pandey()* * * *Dim wksSht As Worksheet* ** *For Each wksSht In ThisWorkbook.Worksheets* *If wksSht.Visible = xlSheetHidden Or wksSht.Visible = xlSheetVeryHidden Then* *wksSht.Visible = xlSheetVisible* *End If* *Next wksSht* ** *Set wksSht = Nothing* * * *End Sub* On Thursday, 4 April 2013 12:26:23 UTC+5:30, amar takale wrote: Dear All I want to unhide sheets from attached sheets Regards Amar -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Re: Need expert advise for multipal Selection
Hi Dhaval, Copy below formula at cell address C11 and confirm it with CSE *=MAX(($B$2:$B$68=$S$10)*($A$2:$A$68 = FUTSTK)*$I$2:$I$68)* * * Select cell R14:R19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),1),)* Select cell S14:S19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),3),)* Select cell T14:T19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),4),)* Select cell U14:U19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),12),)* Select cell V14:V19, press F2 and paste below formuls and confirm it with CSE * =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68=$S$11)*($E$1:$E$68=$R$13)*($M$1:$M$680),0,))*ROW($A$1:$A$68),),ROW($A$1:$A$68)),),13),) * Select cell W14:W19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),1),)* Select cell X14:X19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),3),)* Select cell Y14:Y19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),4),)* Select cell Z14:Z19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),12),)* Select cell AA14:AA19, press F2 and paste below formuls and confirm it with CSE * =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68=$S$11)*($E$1:$E$68=$W$13)*($M$1:$M$680),0,))*ROW($A$1:$A$68),),ROW($A$1:$A$68)),),13),) * Hope it will give you the same you expact. On Tuesday, 2 April 2013 12:53:21 UTC+5:30, Dhaval Shah wrote: Dear Thanks for u r prompt reply but out put is not as per my requirement pls check the reference sheet On Tue, Apr 2, 2013 at 10:49 AM, xlstime xls...@gmail.com javascript:wrote: Hi Dhaval, dhaval shah (1).xlsxhttps://docs.google.com/file/d/0B4LBSzxr1QTiVjZoYkRneFFUY1k/edit?usp=drive_web PFA... . Enjoy Team XLS On Tue, Apr 2, 2013 at 10:39 AM, Dhaval Shah todhav...@gmail.comjavascript: wrote: cmp is current market price ( CLOSE PRICE ) On Tue, Apr 2, 2013 at 10:18 AM, xlstime xls...@gmail.com javascript: wrote: what is CMP ?... you can use sumifs or sumproduct . Enjoy Team XLS On Tue, Apr 2, 2013 at 9:56 AM, Dhaval Shah todhav...@gmail.comjavascript: wrote: Hello all experts any solution for my query On Mon, Apr 1, 2013 at 4:37 PM, Dhaval Shah todhav...@gmail.comjavascript: wrote: Dear Experts I want to make out multipul selection for my project pls refer my sheet Thanks Dhaval Shah -- 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...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.comjavascript: . Visit this group at
Re: $$Excel-Macros$$ Re: Need expert advise for multipal Selection
They all are array forumlas did you applied with Control + Shift + Enter. Please check. I applied all these formulas in your attached workbook and its working. On Wednesday, 3 April 2013 12:39:39 UTC+5:30, Dhaval Shah wrote: Lalit Sir didt work can u send me the sample file Thanks Dhaval Shah 079 - 40055253 On Wed, Apr 3, 2013 at 12:25 PM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: Hi Dhaval, Copy below formula at cell address C11 and confirm it with CSE *=MAX(($B$2:$B$68=$S$10)*($A$2:$A$68 = FUTSTK)*$I$2:$I$68)* * * Select cell R14:R19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),1),)* Select cell S14:S19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),3),)* Select cell T14:T19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),4),)* Select cell U14:U19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),12),)* Select cell V14:V19, press F2 and paste below formuls and confirm it with CSE * =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68=$S$11)*($E$1:$E$68=$R$13)*($M$1:$M$680),0,))*ROW($A$1:$A$68),),ROW($A$1:$A$68)),),13),) * Select cell W14:W19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),1),)* Select cell X14:X19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),3),)* Select cell Y14:Y19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),4),)* Select cell Z14:Z19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),12),)* Select cell AA14:AA19, press F2 and paste below formuls and confirm it with CSE * =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68=$S$11)*($E$1:$E$68=$W$13)*($M$1:$M$680),0,))*ROW($A$1:$A$68),),ROW($A$1:$A$68)),),13),) * Hope it will give you the same you expact. On Tuesday, 2 April 2013 12:53:21 UTC+5:30, Dhaval Shah wrote: Dear Thanks for u r prompt reply but out put is not as per my requirement pls check the reference sheet On Tue, Apr 2, 2013 at 10:49 AM, xlstime xls...@gmail.com wrote: Hi Dhaval, dhaval shah (1).xlsxhttps://docs.google.com/file/d/0B4LBSzxr1QTiVjZoYkRneFFUY1k/edit?usp=drive_web PFA... . Enjoy Team XLS On Tue, Apr 2, 2013 at 10:39 AM, Dhaval Shah todhav...@gmail.comwrote: cmp is current market price ( CLOSE PRICE ) On Tue, Apr 2, 2013 at 10:18 AM, xlstime xls...@gmail.com wrote: what is CMP ?... you can use sumifs or sumproduct . Enjoy Team XLS On Tue, Apr 2, 2013 at 9:56 AM, Dhaval Shah todhav...@gmail.comwrote: Hello all experts any solution for my query On Mon, Apr 1, 2013 at 4:37 PM, Dhaval Shah todhav...@gmail.comwrote: Dear Experts I want to make out multipul selection for my project pls refer my sheet Thanks Dhaval Shah -- 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
Re: $$Excel-Macros$$ Re: Need expert advise for multipal Selection
Copy this code and run it and get the output. After getting expected result please check what you were doing manually which is not giving you correct output. and sorry for not sharing file with you. Hope you understand: *Sub ApplyFormula()* * * *With ThisWorkbook.Worksheets(1)* *.Range(S11).FormulaArray = =MAX(($B$2:$B$68=$S$10)*($A$2:$A$68 = FUTSTK)*$I$2:$I$68)* *.Range(R14:R19).FormulaArray = =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),1),)* *.Range(S14:S19).FormulaArray = =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),3),)* *.Range(T14:T19).FormulaArray = =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),4),)* *.Range(U14:U19).FormulaArray = =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),12),)* *.Range(V14:V19).FormulaArray = =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),13),)* *.Range(W14:W19).FormulaArray = =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),1),)* *.Range(X14:X19).FormulaArray = =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),3),)* *.Range(Y14:Y19).FormulaArray = =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),4),)* *.Range(Z14:Z19).FormulaArray = =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),12),)* *.Range(AA14:AA19).FormulaArray = =IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),13),)* *End With* * * *End Sub* On Wednesday, 3 April 2013 12:50:55 UTC+5:30, Dhaval Shah wrote: yes sir me applied with CSE but ... if u have sheet pls mail me On Wed, Apr 3, 2013 at 12:42 PM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: They all are array forumlas did you applied with Control + Shift + Enter. Please check. I applied all these formulas in your attached workbook and its working. On Wednesday, 3 April 2013 12:39:39 UTC+5:30, Dhaval Shah wrote: Lalit Sir didt work can u send me the sample file Thanks Dhaval Shah 079 - 40055253 On Wed, Apr 3, 2013 at 12:25 PM, Lalit Mohan Pandey mohan.p...@gmail.com wrote: Hi Dhaval, Copy below formula at cell address C11 and confirm it with CSE *=MAX(($B$2:$B$68=$S$10)*($A$2:$A$68 = FUTSTK)*$I$2:$I$68)* * * Select cell R14:R19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),1),)* Select cell S14:S19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),3),)* Select cell T14:T19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),4),)* Select cell U14:U19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $R$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),12),)* Select cell V14:V19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT( SUBSTITUTE(($D$1:$D$68=$S$11)*($E$1:$E$68=$R$13)*($M$1:$M$ 680),0,))*ROW($A$1:$A$68),),ROW($A$1:$A$68)),),13),)* Select cell W14:W19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N$68,IFERROR(SMALL(IFERROR(INT(SUBSTITUTE(($D$1:$D$68 =$S$11)*($E$1:$E$68 = $W$13)*($M$1:$M$68 0),0,))*ROW($A$1:$A$68),), ROW($A$1:$A$68)),),1),)* Select cell X14:X19, press F2 and paste below formuls and confirm it with CSE *=IFERROR(INDEX($B$1:$N
$$Excel-Macros$$ Re: Remove Duplicate with Criteria
I am not able to understand your both conditions :(. First select all column then remove duplicate. (Only 29 duplicate value found and removed.) On Wednesday, 3 April 2013 18:37:15 UTC+5:30, chaya moni wrote: Dear Experts, I have another problem arise in excel while removing duplicate. i have attached the sample and explained in the attachment. Please find the attachment. Thank you in advance Regards, Chaya -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Re: Remove Duplicate with Criteria
What is the criteria to not delete duplicates. On Thursday, 4 April 2013 09:34:49 UTC+5:30, chaya moni wrote: Dear Lalit Sir, Thanks for the reply. i tried to explain a bit more in the attached file. please refer sir. Regards, Chaya On Thu, Apr 4, 2013 at 8:58 AM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: I am not able to understand your both conditions :(. First select all column then remove duplicate. (Only 29 duplicate value found and removed.) On Wednesday, 3 April 2013 18:37:15 UTC+5:30, chaya moni wrote: Dear Experts, I have another problem arise in excel while removing duplicate. i have attached the sample and explained in the attachment. Please find the attachment. Thank you in advance Regards, Chaya -- 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...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/excel-macros?hl=en. 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Arrangement head of the table in the macro code
Use it like this: For Each Wks In ThisWorkbook.Worksheets If IsNumeric(Wks.Name) Then Wks.Name = Year Wks.Name end if If Wks.Name = 2010 And Wks.Name = Year(Date) Then end if next wks On Tuesday, 2 April 2013 20:01:41 UTC+5:30, artforart artforart wrote: If I add this bold part in code I've got End if without block if For Each Wks In ThisWorkbook.Worksheets If IsNumeric(Wks.Name) Then *Wks.Name = Year Wks.Name* If Wks.Name = 2010 And Wks.Name = Year(Date) Then Thank u for 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: array formula
Just write your formula and comfirm it with CSE. Or you can go through below link: http://www.cpearson.com/excel/ArrayFormulas.aspx http://office.microsoft.com/en-in/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx http://www.excelfox.com/forum/f2/count-consecutive-value-range-using-formula-391/ On Tuesday, 2 April 2013 22:33:07 UTC+5:30, kushy wrote: I am using multiple sheets in a file. I want to know how to write array formulas(those that are combination of formulas or have a lot of brackets). any simple lead with basic formulas would be very fulfilling. -- Regards Always, Kushlesh Sharma 9907517169 -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Macro-with example file
You can go through below links: http://www.excel-vba-easy.com/ http://www.excel-vba.com/ On Wednesday, 3 April 2013 09:56:27 UTC+5:30, prafull jadhav wrote: Dear All, Very Good Morning , I am very familiar with excel ..but I have never use Macro in my work . I want to learn Macro ..I am getting lots of mail which from our group but I am not able to understand the same as lack of knowledge of Macro . I want learn Macro but like beginnersI have read the books also which I have got the same from our group . If I get small example for Macro then I can understand the macro properly . such as Do Loop,Case,if ,if not ...etc.. Can you provide me details of website where I can get the very easy examples of Macro ... if any excel file which contain easy macros. Thanks in Advance . Regards, Prafull Jadhav. 9920553518 -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Need expert advise for multipal Selection
Colud you please elaborate. On Tuesday, 2 April 2013 09:56:37 UTC+5:30, Dhaval Shah wrote: Hello all experts any solution for my query On Mon, Apr 1, 2013 at 4:37 PM, Dhaval Shah todhav...@gmail.comjavascript: wrote: Dear Experts I want to make out multipul selection for my project pls refer my sheet Thanks Dhaval Shah -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Print settings to change at a time for multiple sheets
By using below event of workbook *Private Sub Workbook_BeforePrint(Cancel As Boolean)* *End Sub* you can specify the different print settings for different sheets On Monday, 1 April 2013 15:39:17 UTC+5:30, Pravin Gunjal wrote: Can we change the print settings at a time for multiple sheets, as i have an unique data in each sheet. Please help. Regards Pravin Gunjal. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Arrangement head of the table in the macro code
To change worksheet name you are doing right and except this what you want is not clear so if there is something for which you need help you need to specify it clearly. On Tuesday, 2 April 2013 00:30:06 UTC+5:30, artforart artforart wrote: I must change a head in the original table. I have some trouble with doing a change in code I've attached original file (macro it's ok) and the second what I need And another question what do I need to do to change worksheets names for example 2010 into year 2010 I suppose I must doing something with this If IsNumeric(Wks.Name) Then Thank u for help Regards -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Before_Close event to reset Calc to xlCalculationAutomatic recalcuate workbook again, can it stopped
I think you need to replace formula with vba code and this will make your report lite and as per my knowledge this is the only way to get rid of your issue. On Friday, 29 March 2013 19:58:09 UTC+5:30, Divaker Pandey wrote: I'm running into one problem with this. My workbook is pretty massive, so I've turned Application.Calculation to Manual when opening, and only calculate specified areas when necessary while file is open. (I also have CalculateBeforeSave set to False.) This works great, except users get annoyed when Calc is still manual once they've finished using my workbook. Of course I used a Before_Close event to reset Calc to xlCalculationAutomatic -- but now it takes forever to close, as it tries to recalculate the entire workbook before shutting it down. Users just see an unexplained hourglass that seems to sit there doing nothing. They can escape by clicking the Close X button again, but unless I throw in a clumsy message box explaining this, most won't know that. Isn't there a more elegant solution? It seems like a catch-22 -- I want calculation back on before closing, but I don't want it to actually * calculate* before closing. Can I force it to stop calculating within the macro? Is there some other simple solution I'm not seeing? It's roundabout, but I also considered opening a new workbook, copying a Workbook_Close macro into it to turn on calculation, closing my workbook first, then closing the new Book1 without saving, just to fire the Workbook_Close event in a workbook that would close quickly. Simple in concept, but a quick survey of the documentation hasn't shown me a simple way to copy a macro or module to a new workbook -- if this is the only solution to my dilemma, could someone point me in the right direction as far as that goes? -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Vba Code to extract Data from one sheet to another
Hi Hilary, Below code will help you Option Explicit Sub Copy_Visited_Client_Data() Dim wksModule As Worksheet Dim wksDashBoardAs Worksheet Dim wksUserSht As Worksheet Dim rngDataRangeAs Range Dim rngDstRange As Range Dim objDropDown As DropDown Dim varData() As Variant Dim lngCountAs Long Const strDataStartCell As String = C2 Const strDestinationCellAs String = J1 Const strUserControlListNameAs String = Drop Down 2 With ThisWorkbook Set wksModule = .Worksheets(Module) Set wksDashBoard = .Worksheets(Dashboard) End With With wksDashBoard Set objDropDown = .DropDowns(strUserControlListName) End With With ThisWorkbook Set wksUserSht = Nothing On Error Resume Next Set wksUserSht = .Worksheets(objDropDown.List(objDropDown.ListIndex)) On Error GoTo -1: On Error GoTo 0: Err.Clear End With If Not wksUserSht Is Nothing Then With wksUserSht Set rngDataRange = Nothing On Error Resume Next Set rngDataRange = .Range(strDataStartCell) Set rngDataRange = rngDataRange.Resize(.Cells(.Rows.Count, rngDataRange.Column).End(xlUp).Row, 1) If rngDataRange.Rows.Count 1 Then Set rngDataRange = Intersect(rngDataRange, rngDataRange.Offset(1)) Else Set rngDataRange = Nothing End If On Error GoTo -1: On Error GoTo 0: Err.Clear End With If Not rngDataRange Is Nothing Then If rngDataRange.Rows.Count 0 Then lngCount = 0 Erase varData For Each rngDstRange In rngDataRange If rngDstRange.Value Then lngCount = lngCount + 1 ReDim Preserve varData(1 To lngCount) varData(lngCount) = rngDstRange.Value End If Next rngDstRange With wksModule Set rngDstRange = .Range(strDestinationCell) Set rngDstRange = rngDstRange.Resize(.Cells(.Rows.Count, rngDstRange.Column).End(xlUp).Row, 1) With rngDstRange If .Rows.Count 1 Then .Offset(1).ClearContents End If If lngCount 0 Then .Offset(1).Resize(UBound(varData)).Value = Application.Transpose(varData) End If End With End With End If End If End If Set wksModule = Nothing Set wksDashBoard = Nothing Set wksUserSht = Nothing Set rngDataRange = Nothing Set rngDstRange = Nothing Set objDropDown = Nothing Erase varData lngCount = Empty End Sub On Thursday, 28 March 2013 17:41:33 UTC+5:30, hilary lomotey wrote: Hi Experts i have attempted to write a VBA code to extract data from one sheet to another but am getting a debug error, kindly assist. What am trying to achieve is if i select a name from the list of users in the dashboard, that name is always a sheet name, i want to copy all the list of institution visited from the selected sheet name (or member of the user list) and paste in the module sheet range J2, but this has to be automatic ie when i select a user it shd run the code and paste it in that range. thanks -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Re: Source Link updation error
you can do one thing just copy and past data and its formatting. No need to move and copy sheet. If you did that. On Tuesday, 26 March 2013 10:34:50 UTC+5:30, Pravin Gunjal wrote: Hi, I am getting the following error message, while opening an attached file. As I am unable find out the reason and where the data is pasted which contains link. Please help me on this issue. Thank you. [image: Inline image 1] Regards Pravin Gunjal. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Probability
Are you sure about the output you mentioned in attached file. or output will be Starting Place A Starting place B Starting place C Starting place D Starting place Ending Place A B A C A D B A B C B D B Ending Place C A C B C D C Ending Place D A D B D C D Ending Place Regards, Lalit Mohan On Thursday, 10 January 2013 16:04:59 UTC+5:30, Smitha S R wrote: Hi, Can any one help on this -- Forwarded message -- From: Smitha S R sperd...@gmail.com javascript: Date: Thu, Jan 10, 2013 at 2:52 PM Subject: $$Excel-Macros$$ Probability To: excel-...@googlegroups.com javascript: Hi, PFA the file which shows distance in KMS between different places. First Table 'Data' mentions the kms from one place to another and total kms from starting place to ending place. Is it possible to get all probabilities of places in the second table with the kms derived from the first table 'data'. Please help in solving this Regards Smitha -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Prince Kumar - Most Helpful Member December'12
Congrats Pricekeep helping man... On Thursday, 10 January 2013 22:19:58 UTC+5:30, Ayush Jain wrote: Dear members, Prince Kumar has been selected as 'Most Helpful Member' for the month of Dec'12. He has posted 92 posts last month and helped forum members through his excel expertise. I truly appreciate his technical knowledge, consistency and commitment to the group. He has been consistently contributing in the forum since last 6 months. *Prince, please find enclosed the award certificate in honor of your contribution. Congratulations and thanks for your commitment. We are honored to have you in this forum. * Thanks to Ashish, Pravin, Paul, Amar, Anoop and all other members for helping excel enthusiasts voluntarily !! Keep posting. Regards, Ayush Jain Group Manager P.S. If you have any feedback, please share your thoughts by filling the survey formhttps://docs.google.com/spreadsheet/viewform?fromEmail=trueformkey=dHpYdmtTeWZBcEhDWDhmandNWDlpYXc6MQ. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: How to get max number from a database ?
Hi Karan, Apply this formula with CSE in cell C4 =MAX(IF((($A$4:$A$9567)=$A4),$B$4:$B$9567,0)) and copied down Regards, Lalit Mohan On Wednesday, 9 January 2013 15:35:50 UTC+5:30, karan wrote: Hi Can anyone pls let me know how do i get the max value or date (using a formula and not pivot) from the database that i have. Attached is the database. Thanks. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Excel file corrupt in Outlook
Hi Ashish, Here is the fix It actually isn't a Protected view issue, but a DCOM issue. Somehow the DCOM permission got changed, so it wouldn't allow certain applications access to simple things like Preview mode on Outlook 2010 / 2007, or opening up attachments without it saying file corrupt. I also was having an issue with Pop ups in IE8, where javascript messages about security tokens were not authenticated. The solution is as follows, and then you can turn on your protected mode back on in word, excel, and your Preview mode in outlook will start working again. Go to start, then run. Type dcomcnfg and push o.k. Component Services should come up. Expand Component Services, click on Computers, then Right click on My Computer and go to properties. Click on Default Properties tab, and make sure the following is set: Default Authentication Level: Should say Connect, not None And Default Impersonation Level: SHould say Identify. When those are set properly, hit o.k and turn on your protected mode back on your Office applications. Everything works as expected :) Cheers :) Regards, Lalit Mohan On Wednesday, 9 January 2013 15:44:15 UTC+5:30, Ashish_Bhalara wrote: Dear Expert, I am using outlook 2010 I receive the error in all attachment of excel file during view of preview and also file show corrupted during open the same file, the error message is mention in below snap, I also re installed whole MS Office but there are same problem continue, so kindly suggest how to solve this problem. [image: Inline image 1] -- Regards. Ashish Bhalara 9624111822 P*Please do not print this email unless it is absolutely necessary. Spread environmental üawareness.♣♣♣* -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Excel file corrupt in Outlook
Hi Ashish, Find below link may be it help you http://lds.netdimensions.com/ldslive/nd/fresco/repository/html/kit/Self%20Support%20Articles/Mail/AttachmentPreviewers.pdf Regards, Lalit Mohan On Wednesday, 9 January 2013 16:17:06 UTC+5:30, Ashish_Bhalara wrote: Thanks for reply sir, but my problem is still there, i did setting as you say but there are not solve my problem, kindly suggest another solution, Regards Ashish Bhalara On Wed, Jan 9, 2013 at 3:58 PM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: Hi Ashish, Here is the fix It actually isn't a Protected view issue, but a DCOM issue. Somehow the DCOM permission got changed, so it wouldn't allow certain applications access to simple things like Preview mode on Outlook 2010 / 2007, or opening up attachments without it saying file corrupt. I also was having an issue with Pop ups in IE8, where javascript messages about security tokens were not authenticated. The solution is as follows, and then you can turn on your protected mode back on in word, excel, and your Preview mode in outlook will start working again. Go to start, then run. Type dcomcnfg and push o.k. Component Services should come up. Expand Component Services, click on Computers, then Right click on My Computer and go to properties. Click on Default Properties tab, and make sure the following is set: Default Authentication Level: Should say Connect, not None And Default Impersonation Level: SHould say Identify. When those are set properly, hit o.k and turn on your protected mode back on your Office applications. Everything works as expected :) Cheers :) Regards, Lalit Mohan On Wednesday, 9 January 2013 15:44:15 UTC+5:30, Ashish_Bhalara wrote: Dear Expert, I am using outlook 2010 I receive the error in all attachment of excel file during view of preview and also file show corrupted during open the same file, the error message is mention in below snap, I also re installed whole MS Office but there are same problem continue, so kindly suggest how to solve this problem. [image: Inline image 1] -- Regards. Ashish Bhalara 9624111822 P*Please do not print this email unless it is absolutely necessary. Spread environmental üawareness.♣♣♣* -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Regards. Ashish Bhalara 9624111822 P*Please do not print this email unless it is absolutely necessary. Spread environmental üawareness.♣♣♣* -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Extracting Zip Code from Address
Enjoy buddy. ;) On Thursday, 10 January 2013 12:18:12 UTC+5:30, Jai wrote: Thank you all Prince, Lalit, Noorain, The Viper and all if I missed anyone... It worked!! *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *Prince *Sent:* Thursday, January 10, 2013 9:44 AM *To:* excel-...@googlegroups.com javascript: *Cc:* jai_...@msn.com javascript: *Subject:* $$Excel-Macros$$ Re: Extracting Zip Code from Address Hi Jai, here is your solutoon =TRIM(RIGHT(SUBSTITUTE(A1, ,REPT( ,255)),510)) Regards Prince On Thursday, January 10, 2013 12:58:58 AM UTC+5:30, Jai wrote: Hi group, I need some help in extracting zip code from address and hope u all will provide a good help as always….. I need some formula to extract text after second last space because town name might have 2-3 words.. Town Zip Code Result Required LINCOLN LN4 4SY LN4 4SY LINCOLN LN6 3TA LN6 3TA MAIDSTONE Wells ME14 4EA ME14 4EA LEEDS LS3 1LY LS3 1LY PORTSMOUTH PO3 5RT PO3 5RT READING RG7 4AQ RG7 4AQ BLACKWOOD NP12 1ED NP12 1ED PERSHORE WR10 2JH WR10 2JH FLEETWOOD Wells FY7 8LG FY7 8LG TUNBRIDGE WELLS TN2 3EF TN2 3EF TEWKESBURY GL20 8LZ GL20 8LZ Thanks a lot in advance… *Jaideep Panchal* -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Select Unique values
@Prince Instead of using dictionary or array a single line can do it If No Header ActiveSheet.Range($A$1:$A$23).RemoveDuplicates Columns:=1, Header:=xlNo If Header ActiveSheet.Range($A$1:$A$23).RemoveDuplicates Columns:=1, Header:=xlYes Regards, Lalit Mohan On Thursday, 10 January 2013 12:46:14 UTC+5:30, Prince wrote: If yes then please check this one I have programmed below code just cut and paste this and run Public Sub removeDuplicateId() Dim vardata As Variant Dim varRes As Variant Dim lngRow As Long Dim objDic As Object Set objDic = CreateObject(Scripting.Dictionary) With ThisWorkbook.Worksheets(Sheet1) vardata = Application.WorksheetFunction.Transpose(Intersect(.Range(A1).CurrentRegion, .Range(A1).CurrentRegion.Offset(1))) For lngRow = LBound(vardata) To UBound(vardata) If objDic.exists(vardata(lngRow)) = False Then objDic.Add vardata(lngRow), vardata(lngRow) End If Next varRes = Application.WorksheetFunction.Transpose(objDic.items) .Range(B2).Resize(UBound(varRes), 1) = varRes End With End Sub Note: 1: Changed the name of sheet as i have used sheet1 you can replace the sheet name according your sheet name 2: I have Assumed all the data in A coloumn from A2 and A1 as Header text 3: It will provide result in column B at B2 Regards Prince On Thursday, January 10, 2013 12:44:31 PM UTC+5:30, Prince wrote: so do u need any macro ? regards Prince On Thursday, January 10, 2013 12:30:42 PM UTC+5:30, kasper wrote: Hi Thanks for reply,I know about duplicate removeal but I am serching for a permanent formula . Regards Rajesh On Tuesday, January 1, 2013 9:23:20 PM UTC+5:30, Prince wrote: Hi Kasper, Dear instead of giving solution i just want to give the idea required to accomplish your task.just follow the below mentioned steps and it help you to do it from your own. 1:Select the columns of employee name . 2:click : Data Tab 3: Spot the option : Remove duplicate 4: it will ask extend your selection or continue with selection 5:Choose Continue with selection 6:And thats it. Note:if you want this unique data to be displayed in some other column then select your whole data and copy it some other place then follow above steps and paste the result at the required place Regards Prince On Tuesday, January 1, 2013 6:30:24 PM UTC+5:30, kasper wrote: Hi Experts, Please refer the attached file tell me How to select employee names from the column B and display in Column D after removing the duplicate. Regards Rajesh -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Passing array of shapes to udf using vba
Hi Amol, *Sub test(ParamArray Arr() As Variant)* * * *'Code here* * * *End Sub* Regards, Lalit Mohan On Tuesday, 8 January 2013 13:40:56 UTC+5:30, Amol J wrote: Hi Experts, I need help I have multiple shapes in sheet so i want color only one shapes in that perticular sheet. So I created a function which Change the color of provided set of shapes. I want know how to pass array of shapes to function. Please help me to solve this problem Thanks Amol -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Passing array of shapes to udf using vba
Hi Anmol, you can pass multiple shapes as an array using my previos post's method. Regards, Lalit Mohan On Tuesday, 8 January 2013 13:57:46 UTC+5:30, Lalit Mohan Pandey wrote: Hi Amol, *Sub test(ParamArray Arr() As Variant)* * * *'Code here* * * *End Sub* Regards, Lalit Mohan On Tuesday, 8 January 2013 13:40:56 UTC+5:30, Amol J wrote: Hi Experts, I need help I have multiple shapes in sheet so i want color only one shapes in that perticular sheet. So I created a function which Change the color of provided set of shapes. I want know how to pass array of shapes to function. Please help me to solve this problem Thanks Amol -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Fw: significance of using classes in VBA
Hi Prashant, *Advantages when using class modules :* 1. Class modules makes it possible to separate complicated source code for advanced processes. This makes it easier for other to use the source code without understanding how the process is performed. 2. Class modules makes the development of complicated tasks easier by breaking up the code in smaller and easier manageable parts. This has been possible earlier, but a class module forces you to separate the code from the ordinary procedures, resulting in a more obvious function separation. 3. Class modules let you create reusable components. Because of the obvious separation between classes and the procedures using them, the classes contains independent code components that can easily be shared between different projects. 4. Class module sis the foundation of other component technologies, Visual Basic can be used to create X Automation servers and ActiveX controls. *When to use class modules* Here are some examples on when you would want to use class modules : 1. Database management Class modules makes it easier to create objects that can manage a database with VBA. You can create a class that contains code for reading or writing to a database table. This class can be used in your macro without the user knowing how and where the data comes from. 2. Wrapping in API procedures Class modules makes it easier to use Windows API-functions in your macros. Wrapping the API-functions in a class module makes the development easier for people that are not that familiar with the more complicated function in Windows. 3. Managing reading and writing to text files This is really not that complicated, but who goes around and remember how this is done in VBA? By creating a class that manage low level reading to and writing from a text file, the properties and methods in the class can easily be used in your macros. *Regards,* *Lalit Mohan* On Wednesday, 9 January 2013 09:20:16 UTC+5:30, Enrique Martin wrote: Paul described wonderfully. Class Module are very Important part of VBA. By using this you can create your own event or event driven procedure. This comes in Advance programming. You can run a program by clicking a command button or by changing the worksheets or there are number of events which you can use for your purpose. Lets have a simple example of a label on userform. Suppose you click on the label and want to open another userform or want to clear a corresponding text box or anything else. This is what which can be done by class module. Remember Label is the inbuilt object so you can do above incident happened without using Class module but if you create your own Object (lets say a bitmap image) then the role of class module comes into play. Regards, Anoop On Tue, Jan 8, 2013 at 11:19 PM, Paul Schreiner schrein...@att.netjavascript: wrote: Here is a somewhat vague description/analogy. It's not ENTIRELY accurate, but I think will get the idea across. First, let's look at an Object. Objects can have properties and mothods associated with them. Let's take, for example, the Range object. A Range object can have a Value, but it can also have Borders, Interior Color, Font, Formula, Hidden property, a Name, and many more properties you can also DO stuff with a Range object. Range(A1).Copy will copy the range to the Windows Clipboard. this isn't the same as copying the VALUE of a cell to the clipboard, because it also copies the border, colors, fonts, etc as well. So when you PASTE the clipboard contents, it carries with it all of the Properties that were copied from the original Range Object. So, let's say you wanted to create your own special Range object that looks just like the standard Range Object. The Range object is actually unique.. because technically, Range is designed as a Class of objects So you can use Dim MyRange as Range So, MyRange takes on all of the Attributes (Properties) and Methods as the Range Class. You could then use: Set MyRange = Range(A1:Z500) To Create an object called MyRange. Now, what if the Range Object just didn't have all of the things you look for in an Object? (I have no idea what that could be. The Range object is pretty comprehensive) But let's say you wanted to create an object that did different things (Methods) depending on how you acted on it. Say, if you clicked it, it did one thing, but if you Double-Clicked it, it did something else, and if you right-clicked it, it did yet something else. Then you could create a type or Class of object that behaved the way you wanted it. Chip Pearson (Pearson Software Consulting, LLC) has done a nice job describing Classes: http://www.cpearson.com/excel/classes.aspx As far as: an example where classes are useful than normal programming ??
$$Excel-Macros$$ Re: [partially OT] Struggling with a Laptop buy decision; could some1 pls suggest good windows user grp?
Either you can go for dell or hp http://www.dell.com/in/p/inspiron-laptops?~ck=bt http://www8.hp.com/in/en/products/laptops/index.html?facet=Everyday-computing Regards, Lalit Mohan On Wednesday, 9 January 2013 11:21:01 UTC+5:30, .. wrote: any views / comments folks ? Also, are there other windows forum where I can ask this question ? thanks in adv regards Subu On Saturday, January 5, 2013 8:56:31 PM UTC+4, .. wrote: Dear Folks Could someone please suggest a good Ms Windows user group ? Why do I ask ? Yesterday I went to store to buy a working Laptop ... NOT all bells and whistles, but something with a good (I5 or so ) processor, reasonable HDD (500+GB), Windows , etc - sturdy working laptop I am an ordinary Laptop user, quite a windows + MS Excel fan. I've Been using these stuff for 15+ years . I have quite a few Windows based software and almost all files and backups are from the Windows ecosystem what was my experience at the store (buying laptop)? === Win 8 is - more for a touch interface than keyboard and mouse - a pain on the hands (too much scrolling with a mouse) *without* a touch interface - has a learning curve Win 8 Laptops *with* touch + keyboard etc becomes quite costly - you are paying for the touch screen + mouse pad + key pad + Win 7 Laptops are in short supply / limited models / costly if bundled with other freebies that I don't want etc etc I returned without buying a Laptop What are my options ? - share experience and learn - this mail - wait till win 8 tablets become more popular and the price falls ? will it happen ? - move out of windows to something else that support MS office + browsing + mail .. really appreciate inputs here Is there anyone else with a similar experience ? could you please share your views any tips / tricks / pointers in doing something else than waiting would be very much appreciated I have never used Unix, Linux, Ubuntu etc .. I am NOT I.T. savvy like most folks here I am posting in slight desperation I should say ...so moderators please moderate IF this is totally out of synch here THANKS in advance -- Regards Subu -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Need a macro to copy cells based upon a values in other cell.
Please check the data ranges you provided and it will not activate the sheet it will run for all sheets in the workbook. On Monday, 7 January 2013 23:43:51 UTC+5:30, Best Of Luck wrote: Thank you Lalit, The macros is running but does not select the activesheet and does not insert a comment in cell that already have a value in the target cell. On Thursday, January 3, 2013 2:53:00 AM UTC-6, Best Of Luck wrote: hi, Happy new year, I need a macro that would copy cells based upon values in other cells. I am using Excel 10. I have a worksbook that has about 20 worksheets in it. Each of these worksheets has 2 columns A8:A108 B8:B108.. I want a macro that would do the following: 1. Select the activesheet read the values in the source columns B8:B108 and if there are no corrosponding values in the target columns A8:A108 to copy cells from the source B8:B108 to target A8:A108 and clear the contents of source cells B8:B108 . 2. But if there are values in the target cells A8:A108 to leave the values in those cells alone and still clear the contents of source cells B8:B108 but Insert a comment saying Old File in the corrosponding target cells A8:A108 . Both source cells B8:B108 target cells A8:A108 are formated as text, thanks... -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Vlookup for two same values
Hi Harshad, Could you please explain how can you defrenciate two same value like for which value i want data. Regards, Lalit Mohan On Friday, 4 January 2013 14:52:15 UTC+5:30, Prince wrote: Share your workbook. Regards Prince On Friday, January 4, 2013 2:47:55 PM UTC+5:30, harshad shukla wrote: I was stuck up with a problem.i have to use vlookup .but in case of two same values i want distinct values as answer.i.e. if table has two common values .the vlookup should give values as separately. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Copy of Inventry control plan.xls
Hi Harshad, First of all this thread should be comes under you previous thread as you are asking same thing in both thread https://groups.google.com/forum/#!topic/excel-macros/Y2PNmj1V-ZU%5B1-25-false%5D And second what is the parameter to differentiate two same values. Regards, Lalit Mohan On Friday, 4 January 2013 21:58:57 UTC+5:30, harshad shukla wrote: if on using VLOOKUP and for same item code and repeated twice in the list,it should generate the corresponding inventory value .In actual only the first value is taken repeatedly if the vlookup value is coming twice in the list. On Fri, Jan 4, 2013 at 5:00 PM, Prince prince...@gmail.com javascript:wrote: explain bit more could't understand ur requirment. Regards Prince On Friday, January 4, 2013 4:52:35 PM UTC+5:30, harshad shukla wrote: Please find attachd file.if two values of material code is same then corresponding target inventory value should be displayed.ordinary only one value is repeated twice. -- Forwarded message -- From: HARSHAD SHUKLA,DD,OMAX,LKO harshad...@omaxauto.com Date: Jan 4, 2013 3:58 PM Subject: Copy of Inventry control plan.xls To: er.harsh...@gmail.com DISCLAIMER: This e-mail including attachment, contains confidential and privileged information intended for exclusive use ofthe addressee. The company does not assume liability including inadvertent virus infestation, arising out of this mail, unless specifically agreed to, with the recipient.If you are not the intended recipient, please contact the sender by reply e-mail and delete original and all copies of this message. Any dissemination, use, reliance on, review, distribution, printing or copying of this message in whole or in part is unauthorized and strictly prohibited. -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ office stationery template
Type *excel stationary inventory template* in google.com and find which suits you. Regards, Lalit Mohan On Monday, 7 January 2013 08:02:44 UTC+5:30, Smitha S R wrote: Hi, Anyone please send me some good excel template to maintain the office stationery items, their consumption, opening stock , closing stock, and reports on monthly basis and daily basis On Sun, Jan 6, 2013 at 1:40 PM, Smitha S R sperd...@gmail.comjavascript: wrote: Hi, Anyone please send me ome good excel template to maintain the office stationery items, their consumption, opening stock , closing stock, and reports on monthly basis and daily basis On Sun, Jan 6, 2013 at 9:30 AM, Smitha S R sperd...@gmail.comjavascript: wrote: HI, Can anyone send some good excel template to maintain the office stationery items, their consumption, opening stock , closing stock, and reports on monthly basis and daily basis -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Copy of Inventry control plan.xls
Hi Harshad, Provided details is not according to the file you shared. Regards, Lalit Mohan On Monday, 7 January 2013 10:53:25 UTC+5:30, harshad shukla wrote: The parametr is vendor.i want vendorwise quantity detail of stock item and item code is primary differentiating factor On Jan 7, 2013 9:59 AM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: Hi Harshad, First of all this thread should be comes under you previous thread as you are asking same thing in both thread https://groups.google.com/forum/#!topic/excel-macros/Y2PNmj1V-ZU%5B1-25-false%5D And second what is the parameter to differentiate two same values. Regards, Lalit Mohan On Friday, 4 January 2013 21:58:57 UTC+5:30, harshad shukla wrote: if on using VLOOKUP and for same item code and repeated twice in the list,it should generate the corresponding inventory value .In actual only the first value is taken repeatedly if the vlookup value is coming twice in the list. On Fri, Jan 4, 2013 at 5:00 PM, Prince prince...@gmail.com wrote: explain bit more could't understand ur requirment. Regards Prince On Friday, January 4, 2013 4:52:35 PM UTC+5:30, harshad shukla wrote: Please find attachd file.if two values of material code is same then corresponding target inventory value should be displayed.ordinary only one value is repeated twice. -- Forwarded message -- From: HARSHAD SHUKLA,DD,OMAX,LKO harshad...@omaxauto.com Date: Jan 4, 2013 3:58 PM Subject: Copy of Inventry control plan.xls To: er.harsh...@gmail.com DISCLAIMER: This e-mail including attachment, contains confidential and privileged information intended for exclusive use ofthe addressee. The company does not assume liability including inadvertent virus infestation, arising out of this mail, unless specifically agreed to, with the recipient.If you are not the intended recipient, please contact the sender by reply e-mail and delete original and all copies of this message. Any dissemination, use, reliance on, review, distribution, printing or copying of this message in whole or in part is unauthorized and strictly prohibited. -- 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 post to this group, send email to excel-...@googlegroups.com. To unsubscribe from this group, send email to excel-macros...@** googlegroups.com. Visit this group at http://groups.google.com/**group/excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en . -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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
Re: $$Excel-Macros$$ Slow processing of Data *
Hi hilary, you can share your file by uploding it to other public file sharing website and share the link to download it. Regards, Lalit Mohan On Thursday, 3 January 2013 16:14:52 UTC+5:30, hilary lomotey wrote: This Ȋ̝̊̅§ very valuable info unfortunately this forum limits the size of data I can send as ♍Ɣ file Ȋ̝̊̅§ about 6mg. So I can't send the file but can you let me have the VBA code to do this or any option to send the file to you. Thanks Sent from my BlackBerry® smartphone from Airtel Ghana -- *From: * Prajakt Pande prajak...@gmail.com javascript: *Sender: * excel-...@googlegroups.com javascript: *Date: *Thu, 3 Jan 2013 14:28:42 +0400 *To: *excel-...@googlegroups.com javascript: *ReplyTo: * excel-...@googlegroups.com javascript: *Subject: *Re: $$Excel-Macros$$ Slow processing of Data * [image: Boxbe] https://www.boxbe.com/overview This message is eligible for Automatic Cleanup! (prajak...@gmail.com javascript:) Add cleanup rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3DgeNjsaRJOT4QNvh70DFfe4Z3K84o%252FHM8Ih8kI1D5X8YcIUYw1W%252Fm8f7LM%252FsLuTdwyMc3X1efnmRcpdtlEKkGIaqY4PHmKvWkwzMZipWq%252FrB71V0Kq5oPArU%252FbZWjzEQU8idLTADZwqgPBKt%252F1x8dGw%253D%253D%26key%3DpE5ifWbEQl0Yq1dvS943H%252BnVM9VtuFWVuxzvOMLYxFc%253Dtc=13269054966_1226933075| More infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=13269054966_1226933075 -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: How to use vlookup on two parameters simultaneously
Hi harshad, May be this link help you for applying vlookup. It contains tutorial with pictures http://www.timeatlas.com/5_minute_tips/general/learning_vlookup_in_excel Video tutorial http://www.youtube.com/watch?v=HjRjRnzy-hA Regards, Lalit Mohan On Thursday, 3 January 2013 16:09:23 UTC+5:30, harshad shukla wrote: Dear all. I was stuck up while using vlookup on two parameters.please help. Urgent requirement. Harshad Shukla -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Login to gmail/yahoo and send email using excel vba
Hi Ankur, You can find an example to login in gmail account using vba at below mentioned link:- http://powerofexcel.wordpress.com/2012/09/12/google-login-automation-through-excel-vba/ let us know for further assistance. Regards, Lalit Mohan On Thursday, 3 January 2013 19:11:36 UTC+5:30, ankur aggarwal wrote: Hi Team, I wanted to run an excel macro which can automate yahoo/gmail process using excel vba. Upon clicking of the button, it will send an email from a web browser ( IE ) using yahoo/gmail to a given email id. Can anybody please help in doing that ? I have written a macro which actually opens IE and g mail web site, then I have created the HTML object and its going to gmail website. But I am unable to proceed further (attach file and send it to recipient ). Thanks in Advance ! Warm Regards -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Fw: Conflict between Local and Global variable
Hi Prashant, Can you explain why you take same name variable in Public and Private scope. Regards, Lalit Mohan On Thursday, 3 January 2013 23:15:17 UTC+5:30, prashant shinde wrote: Original message Subject: Conflict between Local and Global variable From: Shinde, Hanumant hanuman...@capgemini.com javascript: To: arsfa...@yahoo.co.in javascript: arsfa...@yahoo.co.injavascript: CC: Hi friends I have below query. There is variable *xyz* which has global scope as well as local scope. In below sub I am able to change the value of local xyz. How can I change the value of public xyz. ‘Global variable xyz Public xyz As Integer Sub test(abc As Integer) ‘Local variable xyz Dim xyz As Integer xyz = abc + 10 Debug.Print xyz 'xyz = 20 this will be output End Sub Sub calling() xyz = 10 Call test(xyz) Debug.Print xyz 'xyz = 10 this will be output End Sub This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Macro to create attendance Sheet for each Employee
Hi Rashid, Paste below code in the same module where the previous procedure is saved and assign this procedure to the shape. *Sub StartProcess()* * * *Dim varData() As Variant* *Dim rngRangeAs Range* * * *Const strRawDataShtName As String = Master* *Const intNameColNo As Integer = 2* *Const intDateTimeColNo As Integer = 1* *Const strNameColCellAs String = B1* *Const strNameColHeading As String = Name* *Const strDateTimeColHeading As String = Date/ Time* ** *ReDim varData(0)* *If Application.ScreenUpdating Then Application.ScreenUpdating = False* *With ThisWorkbook.Worksheets(strRawDataShtName)* *If .Cells(1, intNameColNo).Value = strNameColHeading Then* *.Cells(1, intNameColNo).EntireColumn.Cut* *.Cells(1, intDateTimeColNo).EntireRow.Insert* *End If* *Call GetEmployeeAttendance* *If .Cells(1, intDateTimeColNo).Value = strNameColHeading Then* *.Cells(1, intNameColNo).EntireColumn.Cut* *.Cells(1, intDateTimeColNo).EntireRow.Insert* *End If* *End With* *If Not Application.ScreenUpdating Then Application.ScreenUpdating = True* * * *End Sub* Regards, Lalit Mohan On Friday, 4 January 2013 01:48:16 UTC+5:30, prkhan56 wrote: **Thanks Lalit I can see the sample sheet and the code works fine. As I requested in my previous post, can you please amend your code to look for *Col A - Date/Time* (the present code looks for Date/Time in Col B) *Col B - Name* (the present code looks for Name in Col A) If you can please amend it to suit my requirement, I would be really really grateful. Thanks a million for all your help and time. Regards Rashid Khan On Thursday, January 3, 2013 2:56:46 PM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, There is a sheet to make it visible just press alt + f11 in left side there is a sheet named sample goto its properties visible and select visible Regards, Lalit Mohan Sent from BlackBerry® on Airtel -- *From: * prkhan56 prkh...@gmail.com *Sender: * excel-...@googlegroups.com *Date: *Thu, 3 Jan 2013 02:31:17 -0800 (PST) *To: *excel-...@googlegroups.com *ReplyTo: * excel-...@googlegroups.com *Subject: *$$Excel-Macros$$ Re: Macro to create attendance Sheet for each Employee Hello Lalit Thanks for your response. There is no sheet *Sample* in the file you sent me. However, can you also please the amend the code to take Col A - Date/Time Col B - Name Your code looks for name in Col A and Date/Time and Col B Thanks a lot once again for your time and help Regards Rashid Khan On Thursday, January 3, 2013 10:08:24 AM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, The file which i have sent you contains a sheet named *Sample *so if you have copied only the code then it will give you an error because the sheet is not available. Please check the same. Reagrds, Lalit Mohan On Thursday, 3 January 2013 00:57:11 UTC+5:30, prkhan56 wrote: Hello Lalit, I tried the macro and it works perfectly on the sample data I uploaded in my first post. But very very sorry, I missed to say that Col A has date/Time ad Col B has the Name and not the other way round. Can the code be amended accordingly please. However, if I run your code by interchanging Col A and Col B; I get an error as follows: Run time error '9' - Subscript out of range and it highlights the following line in the code *Worksheets(strSampleFileName).Visible = True* Can you please rectify this problem and thanks a million for all your time and help sorry for my error. Regards Rashid Khan I On Tuesday, January 1, 2013 8:07:35 PM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, PFA. Regards, Lalit Mohan On Tuesday, 1 January 2013 15:16:42 UTC+5:30, Lalit Mohan Pandey wrote: Hi Rashid, Sorry for that i will send you the file by today evening itself Regards, Lalit Mohan On Tuesday, 1 January 2013 13:37:21 UTC+5:30, prkhan56 wrote: Hello Lalit I cannot see the complete code. If I go and check the original and paste the code I get lot of unwanted characters in the code If you cannot send me in a file then can you please send the code to me as a text file. thanks for your time and help Rashid Khan On Monday, December 31, 2012 3:09:26 PM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, Sorry i can't upload file so please follow the given steps: *Step 1:- First create a sample sheet for output report with all the formatting and formula and please make sure it should be for 31 rows as * *max day in a month is 31.* *Step 2:- Press Alt + F11 and Alt + I + M and paste below code * * * *Option Explicit Sub GetEmployeeAttendance() Dim wksSht As Worksheet Dim wksReport
$$Excel-Macros$$ Re: Need a macro to copy cells based upon a values in other cell.
Hi Dear, Can you please share it again with the output or result you want to see for better clearity. Regards, Lalit Mohan On Friday, 4 January 2013 01:18:39 UTC+5:30, Best Of Luck wrote: Lalit thanks for the macro. I am loading an example sheet for you . I need to run it for each activesheet using a button on a form. On Thursday, January 3, 2013 4:47:27 AM UTC-6, Lalit Mohan Pandey wrote: Hi Dear, May be blow code will work as i understand but for better clearity share sample workbook. *Sub FillWithSource()* * * *Dim wksSht As Worksheet* *Dim rngRangeAs Range* ** *Const strSourceRangeAs String = B1:B19* *Const strDestiRange As String = A1:A19* ** *For Each wksSht In ThisWorkbook.Worksheets* *With wksSht* *If WorksheetFunction.Count(.Range(strSourceRange)) 0 Then* *On Error Resume Next* *Set rngRange = Nothing* *Set rngRange = .Range(strDestiRange).SpecialCells(xlCellTypeBlanks)* *rngRange.FormulaR1C1 = =RC[1]* *On Error GoTo 0: Err.Clear* *.Range(strSourceRange).ClearContents* *End If* *End With* *Next wksSht* ** *End Sub* * * *Regards,* *Lalit Mohan* On Thursday, 3 January 2013 14:23:00 UTC+5:30, Best Of Luck wrote: hi, Happy new year, I need a macro that would copy cells based upon values in other cells. I am using Excel 10. I have a worksbook that has about 20 worksheets in it. Each of these worksheets has 2 columns A8:A108 B8:B108.. I want a macro that would do the following: 1. Select the activesheet read the values in the source columns B8:B108 and if there are no corrosponding values in the target columns A8:A108 to copy cells from the source B8:B108 to target A8:A108 and clear the contents of source cells B8:B108 . 2. But if there are values in the target cells A8:A108 to leave the values in those cells alone and still clear the contents of source cells B8:B108 but Insert a comment saying Old File in the corrosponding target cells A8:A108 . Both source cells B8:B108 target cells A8:A108 are formated as text, thanks... -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
Hi, Sorry for the long formula if sombody found somthing in short please let me know ;) If value is in cell A1 paste below code on cell A2 with Ctrl + Shift + Enter =REPLACE(A1,MATCH(1,N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),4,0)+1,MAX(N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),1*ROW(INDIRECT(1:LEN(A1-MATCH(1,N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),4,0),SUBSTITUTE(MID(A1,MATCH(1,N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),4,0),MAX(N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),1*ROW(INDIRECT(1:LEN(A1-MATCH(1,N(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT(1:LEN(A1))),4,0)+1), ,)) Regards, Lalit Mohan On Wednesday, 2 January 2013 13:25:54 UTC+5:30, Manoj Kumar wrote: Thanks Anoop. Solved My Problem. but it would be nice if formula also return the name along with phone number. On Wednesday, 2 January 2013 13:17:35 UTC+5:30, Enrique Martin wrote: Paste the following code in the module. this will extract all the numbers removing spaces. Please see the example in attached file. Function RemoveSpaces(str As String) As String Dim myLen As Long Dim myChar As String Dim myWrd As String Dim iCount As Integer Dim myGet As String myLen = Len(str) myWrd = str For iCount = 1 To myLen myChar = Mid$(myWrd, iCount, 1) If IsNumeric(myChar) Then myGet = myGet myChar End If Debug.Print myChar Next iCount RemoveSpaces = myGet End Function Regards, Anoop On Wed, Jan 2, 2013 at 1:01 PM, Prince prince...@gmail.com wrote: In this case we need to have a fomula that can count the existence of number in the text so in that case we need to modify this formula. that i let u know how. Regards Prince On Wednesday, January 2, 2013 12:54:34 PM UTC+5:30, Francis Mukobi wrote: What if it is a list of phone numbers and these numbers have different lengths? I mean some phone numbers are from India (10 digits ) and some numbers are from say Indonesia (14 digits ) etc. How do I remove spaces from such a list of numbers? Over to you Prince. Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net For every domain purchased from us, we host you for the first 3 months FREE! Prince prince...@gmail.com wrote: Hi Francis, This formula cut the string from first charactor up the number of numeric value inside that text i mean i have assumed that numeric value is a mobile number and every mobile number contains 10 digits in india so i cut the last 12 digit including space and concat that with first string that i got from left. Regards Prince On Wednesday, January 2, 2013 12:00:56 PM UTC+5:30, Francis Mukobi wrote: Could you please elaborate on the formula you have given, Prince? Francis Mukobi Frank Web Hosts Your affordable quality web hosts http://frankhost.net Manoj Kumar manoj...@gmail.com javascript: wrote: there is some text at end also. Sorry for inconvenience. On Wed, Jan 2, 2013 at 11:20 AM, Prince prince...@gmail.com**javascript: wrote: Hi Manoj, use this this may help you incase if the Number is any Mobile number: =MID(A1,1,LEN(A1)-12)**SUBSTITUTE(RIGHT(A1,12), ,) regards Prince On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote: Hi, Is there any way out for removing spaces between numbers only. Spaces between the text should be removed. For example. Manoj Kumar 9015 408 078 Manoj Kumar 9015418078 The text at the front is not always fixed. It can contain any number text and spaces. Regards, Manoj -- 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 post to this group, send email to excel-...@googlegroups.com* *javascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.**com javascript:. Visit this group at http://groups.google.com/**
Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
Nice formula viper but the last space is missing.
On Wednesday, 2 January 2013 15:07:20 UTC+5:30, §»VIPER«§ wrote:
=LEFT(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),))-1)SUBSTITUTE(MID(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),)),255),
,)
this should work with CSE
On Wed, Jan 2, 2013 at 2:55 PM, Pravin Gunjal isk...@gmail.comjavascript:
wrote:
It's absolutely working fine.
Pravin Gunjal
-- Forwarded message --
From: Prince prince...@gmail.com javascript:
Date: Wed, Jan 2, 2013 at 11:20 AM
Subject: $$Excel-Macros$$ Re: how to remove space between numbers only.
To: excel-...@googlegroups.com javascript:
Hi Manoj,
use this this may help you incase if the Number is any Mobile number:
=MID(A1,1,LEN(A1)-12)SUBSTITUTE(RIGHT(A1,12), ,)
regards
Prince
On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote:
Hi,
Is there any way out for removing spaces between numbers only. Spaces
between the text should be removed.
For example.
Manoj Kumar 9015 408 078 Manoj Kumar 9015418078
The text at the front is not always fixed. It can contain any number
text and spaces.
Regards,
Manoj
--
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 post to this group, send email to excel-...@googlegroups.comjavascript:
.
To unsubscribe from this group, send email to
excel-macros...@googlegroups.com javascript:.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
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 post to this group, send email to excel-...@googlegroups.comjavascript:
.
To unsubscribe from this group, send email to
excel-macros...@googlegroups.com javascript:.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
*Great day,*
*viper*
--
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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ How to find special characters in a cell
Hi Danny, May be this code will work for you: *Private Sub Worksheet_SelectionChange(ByVal Target As Range)* * * *If Target.Resize(1, 1) = ThisWorkbook.Worksheets(JIBase).Range(G4) Then* *If Len(Trim(Target.Resize(1, 1).Value)) 0 Then* *If Not CreateFile(Target.Resize(1, 1)) Then* *MsgBox Name is not valid as it contains some special character., vbCritical, File name is not valid.* *Target.Value = vbNullString* *End If* *End If* *End If* * * *End Sub* * * *Function CreateFile(ByVal strFileName As String) As Boolean* ** *Dim objFSO As Object* *Dim objTFC As Object* *Dim strFilePath As String* ** *strFilePath = Environ(temp) Application.PathSeparator strFileName .txt* *Set objFSO = CreateObject(Scripting.FileSystemObject)* *On Error Resume Next* *Set objTFC = Nothing* *Set objTFC = objFSO.CreateTextFile(strFilePath, True)* *On Error GoTo 0: Err.Clear* ** *If Not objTFC Is Nothing Then* *CreateFile = True* *On Error Resume Next* *Kill strFilePath* *On Error GoTo 0: Err.Clear* *Else* *CreateFile = False* *End If* ** *Set objFSO = Nothing* *Set objTFC = Nothing* *strFilePath = vbNullString* ** *End Function* Regards, Lalit Mohan On Wednesday, 2 January 2013 21:12:55 UTC+5:30, Danny Mariens wrote: I send workbook in annex. Protection is on because my collegues use the file with protection on. I removed password so you can easily switch it on or off. The cell containing the info that I use to save is G4 on JIBase. 2013/1/2 Anoop K Sharma aks.sh...@gmail.com javascript: can you share workbook? On Wed, Jan 2, 2013 at 8:34 PM, dannym...@gmail.com javascript:wrote: In excel 2012 I'm using the name typed in a cell as a part of the filename when saved via a macro. Because the following characters /\*?| are not allowed in a filename I want to do a check in the cell. The idea is to put a warning in a free cell that the name isn't allowed when it contains one or more of the forbidden characters. I used the =IF function in combination with =SEARCH but I don't know how to use the formulas when I want to check on * ? or Can somebody help me with that? Thanks. Danny -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Regards, Anoop Sr. Developer -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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
Re: $$Excel-Macros$$ Re: how to remove space between numbers only.
Hi Viper,
No issue.. :) i am not pointing you just letting you know. thanks for
the nice formula.
Regards,
Lalit Mohan
On Thursday, 3 January 2013 09:49:40 UTC+5:30, §»VIPER«§ wrote:
Hi lalit
I don't find any need for that. just working as required by OP
On Wed, Jan 2, 2013 at 3:15 PM, Lalit Mohan Pandey
mohan.p...@gmail.comjavascript:
wrote:
Nice formula viper but the last space is missing.
On Wednesday, 2 January 2013 15:07:20 UTC+5:30, §»VIPER«§ wrote:
=LEFT(A1,MIN(IFERROR(FIND({0,**1,2,3,4,5,6,7,8,9},A1),))-1)**
SUBSTITUTE(MID(A1,MIN(**IFERROR(FIND({0,1,2,3,4,5,6,7,**8,9},A1),)),255),
,)
this should work with CSE
On Wed, Jan 2, 2013 at 2:55 PM, Pravin Gunjal isk...@gmail.com wrote:
It's absolutely working fine.
Pravin Gunjal
-- Forwarded message --
From: Prince prince...@gmail.com
Date: Wed, Jan 2, 2013 at 11:20 AM
Subject: $$Excel-Macros$$ Re: how to remove space between numbers only.
To: excel-...@googlegroups.com
Hi Manoj,
use this this may help you incase if the Number is any Mobile number:
=MID(A1,1,LEN(A1)-12)**SUBSTITUTE(RIGHT(A1,12), ,)
regards
Prince
On Wednesday, January 2, 2013 11:04:37 AM UTC+5:30, Manoj Kumar wrote:
Hi,
Is there any way out for removing spaces between numbers only. Spaces
between the text should be removed.
For example.
Manoj Kumar 9015 408 078 Manoj Kumar 9015418078
The text at the front is not always fixed. It can contain any number
text and spaces.
Regards,
Manoj
--
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 post to this group, send email to excel-...@googlegroups.com.
To unsubscribe from this group, send email to excel-macros...@**
googlegroups.com.
Visit this group at
http://groups.google.com/**group/excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en
.
--
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 post to this group, send email to excel-...@googlegroups.com.
To unsubscribe from this group, send email to excel-macros...@**
googlegroups.com.
Visit this group at
http://groups.google.com/**group/excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en
.
--
*Great day,*
*viper*
--
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 post to this group, send email to excel-...@googlegroups.comjavascript:
.
To unsubscribe from this group, send email to
excel-macros...@googlegroups.com javascript:.
Visit this group at http://groups.google.com/group/excel-macros?hl=en
$$Excel-Macros$$ Re: Macro to create attendance Sheet for each Employee
Hi Rashid, The file which i have sent you contains a sheet named *Sample *so if you have copied only the code then it will give you an error because the sheet is not available. Please check the same. Reagrds, Lalit Mohan On Thursday, 3 January 2013 00:57:11 UTC+5:30, prkhan56 wrote: Hello Lalit, I tried the macro and it works perfectly on the sample data I uploaded in my first post. But very very sorry, I missed to say that Col A has date/Time ad Col B has the Name and not the other way round. Can the code be amended accordingly please. However, if I run your code by interchanging Col A and Col B; I get an error as follows: Run time error '9' - Subscript out of range and it highlights the following line in the code *Worksheets(strSampleFileName).Visible = True* Can you please rectify this problem and thanks a million for all your time and help sorry for my error. Regards Rashid Khan I On Tuesday, January 1, 2013 8:07:35 PM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, PFA. Regards, Lalit Mohan On Tuesday, 1 January 2013 15:16:42 UTC+5:30, Lalit Mohan Pandey wrote: Hi Rashid, Sorry for that i will send you the file by today evening itself Regards, Lalit Mohan On Tuesday, 1 January 2013 13:37:21 UTC+5:30, prkhan56 wrote: Hello Lalit I cannot see the complete code. If I go and check the original and paste the code I get lot of unwanted characters in the code If you cannot send me in a file then can you please send the code to me as a text file. thanks for your time and help Rashid Khan On Monday, December 31, 2012 3:09:26 PM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, Sorry i can't upload file so please follow the given steps: *Step 1:- First create a sample sheet for output report with all the formatting and formula and please make sure it should be for 31 rows as * *max day in a month is 31.* *Step 2:- Press Alt + F11 and Alt + I + M and paste below code * * * *Option Explicit Sub GetEmployeeAttendance() Dim wksSht As Worksheet Dim wksReport As Worksheet Dim rngRangeAs Range Dim varData() As Variant Dim varName() As Variant Dim varDate() As Variant Dim varFinal() As Variant Dim lngLoopName As Long Dim lngLoopDate As Long Dim lngCountAs Long Const strFormulaAs String = =A1 | TEXT(B1,m/d/) Const strTmpSht As String = Temp_Sht Const strDataStartCell As String = A1 Const strFinalDataStartCell As String = D8 Const strReportMonthCellAs String = C2 Const strEmpNameCellAs String = C5 Const strSampleFileName As String = Sample ReDim varData(0) With ThisWorkbook.Worksheets(Master) Set rngRange = .Range(strDataStartCell).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 2) varData = rngRange.Value End With If UBound(varData) 0 Then Application.DisplayAlerts = False On Error Resume Next Worksheets(strTmpSht).Delete On Error GoTo 0: Err.Clear Set wksSht = Worksheets.Add With wksSht .Name = strTmpSht With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData .Resize(UBound(varData), 1).RemoveDuplicates Columns:=1, Header:=xlYes End With varName = Intersect(.Range(strDataStartCell, .Cells(.Rows.Count, 1).End(xlUp)), .Range(strDataStartCell, .Cells(.Rows.Count, 1).End(xlUp)).Offset(1)).Value varName = Application.Transpose(varName) With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Formula = strFormula .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Value = .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Value .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).RemoveDuplicates Columns:=1, Header:=xlYes End With varDate = .Range(.Range(strDataStartCell).Offset(1, UBound(varData, 2)), .Cells(.Rows.Count, UBound(varData, 2) + 1).End(xlUp)).Value varDate = Application.Transpose(varDate) .Cells.Clear With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData End With End With With wksSht Set rngRange = .Range(strDataStartCell).CurrentRegion
$$Excel-Macros$$ Re: Macro to create attendance Sheet for each Employee
Hi Rashid, Sorry for that i will send you the file by today evening itself Regards, Lalit Mohan On Tuesday, 1 January 2013 13:37:21 UTC+5:30, prkhan56 wrote: Hello Lalit I cannot see the complete code. If I go and check the original and paste the code I get lot of unwanted characters in the code If you cannot send me in a file then can you please send the code to me as a text file. thanks for your time and help Rashid Khan On Monday, December 31, 2012 3:09:26 PM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, Sorry i can't upload file so please follow the given steps: *Step 1:- First create a sample sheet for output report with all the formatting and formula and please make sure it should be for 31 rows as * *max day in a month is 31.* *Step 2:- Press Alt + F11 and Alt + I + M and paste below code * * * *Option Explicit Sub GetEmployeeAttendance() Dim wksSht As Worksheet Dim wksReport As Worksheet Dim rngRangeAs Range Dim varData() As Variant Dim varName() As Variant Dim varDate() As Variant Dim varFinal() As Variant Dim lngLoopName As Long Dim lngLoopDate As Long Dim lngCountAs Long Const strFormulaAs String = =A1 | TEXT(B1,m/d/) Const strTmpSht As String = Temp_Sht Const strDataStartCell As String = A1 Const strFinalDataStartCell As String = D8 Const strReportMonthCellAs String = C2 Const strEmpNameCellAs String = C5 Const strSampleFileName As String = Sample ReDim varData(0) With ThisWorkbook.Worksheets(Master) Set rngRange = .Range(strDataStartCell).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 2) varData = rngRange.Value End With If UBound(varData) 0 Then Application.DisplayAlerts = False On Error Resume Next Worksheets(strTmpSht).Delete On Error GoTo 0: Err.Clear Set wksSht = Worksheets.Add With wksSht .Name = strTmpSht With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData .Resize(UBound(varData), 1).RemoveDuplicates Columns:=1, Header:=xlYes End With varName = Intersect(.Range(strDataStartCell, .Cells(.Rows.Count, 1).End(xlUp)), .Range(strDataStartCell, .Cells(.Rows.Count, 1).End(xlUp)).Offset(1)).Value varName = Application.Transpose(varName) With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Formula = strFormula .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Value = .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).Value .Resize(UBound(varData), 1).Offset(, UBound(varData, 2)).RemoveDuplicates Columns:=1, Header:=xlYes End With varDate = .Range(.Range(strDataStartCell).Offset(1, UBound(varData, 2)), .Cells(.Rows.Count, UBound(varData, 2) + 1).End(xlUp)).Value varDate = Application.Transpose(varDate) .Cells.Clear With .Range(strDataStartCell) .Resize(UBound(varData), UBound(varData, 2)).Value = varData End With End With With wksSht Set rngRange = .Range(strDataStartCell).CurrentRegion rngRange.Resize(1, 1).Offset(, 7).Formula = =IFERROR(TEXT(SUBTotal(105, rngRange.Resize(, 1).Offset(, 1).Address(, , , 1) ),), ) 'Year rngRange.Resize(1, 1).Offset(, 8).Formula = =IFERROR(TEXT(SUBTotal(105, rngRange.Resize(, 1).Offset(, 1).Address(, , , 1) ),), ) 'Month rngRange.Resize(1, 1).Offset(, 9).Formula = =IFERROR(INT(SUBTotal(105, rngRange.Resize(, 1).Offset(, 1).Address(, , , 1) )), ) 'Date rngRange.Resize(1, 1).Offset(, 10).Formula = =IFERROR(SUBTotal(105, rngRange.Resize(, 1).Offset(, 1).Address(, , , 1) ), ) 'Min rngRange.Resize(1, 1).Offset(, 11).Formula = =IFERROR(SUBTotal(104, rngRange.Resize(, 1).Offset(, 1).Address(, , , 1) ), ) 'Max With rngRange.Resize(1, 2) .AutoFilter For lngLoopName = LBound(varName) To UBound(varName) ReDim varFinal(1 To 31, 1 To 3) lngCount = 0 rngRange.AutoFilter Field:=1, Criteria1:=varName(lngLoopName) For lngLoopDate = LBound(varDate) To UBound(varDate
$$Excel-Macros$$ Re: Excel Fun Video
Animation rocks On Tuesday, 1 January 2013 16:16:52 UTC+5:30, amar takale wrote: Dear my all Excel Group Members, I found Excel fun Video on Internet while searching Excel Solution I like very much.I hope you all like it fun Video.So I attached this Excel.Enjoy every excel users. Regards Amar -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Happy b'day Noorain Ansari
*Wish you a very happy B'Day Noorain* On Wednesday, 2 January 2013 00:13:59 UTC+5:30, ashish wrote: Happy b'day Noorain Ansari Regards Ashish Koul Sent on my BlackBerry® from Vodafone -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Macro to create attendance Sheet for each Employee
).Value End If End If Next lngLoopDate If varFinal(1, 1) Then On Error Resume Next Worksheets(varName(lngLoopName)).Delete On Error GoTo 0: Err.Clear Worksheets(strSampleFileName).Visible = True Worksheets(strSampleFileName).Copy After:=Sheets(Worksheets.Count) Worksheets(strSampleFileName).Visible = xlVeryHidden Set wksReport = Sheets(Worksheets.Count) With wksReport .Name = varName(lngLoopName) .Range(strReportMonthCell).Value = Format(varFinal(1, 1), ) Attendance Report Format(varFinal(1, 1), ) .Range(strEmpNameCell).Value = Employee Name: .Name .Range(strFinalDataStartCell).Resize(UBound(varFinal), UBound(varFinal, 2)).Value = varFinal .Cells.EntireColumn.AutoFit End With End If Next lngLoopName End With End With On Error Resume Next Worksheets(strTmpSht).Delete On Error GoTo 0: Err.Clear Application.DisplayAlerts = True End If End Sub* * * *Step 3:- Create a shape in master sheet and assign this macro to the shape.* *Step 4:- Make sure the input time format should be always in m/d/ format if in future it change then need to change code also.* Let me know if you need my further assistance. Regards, Lalit Mohan On Monday, 31 December 2012 13:05:42 UTC+5:30, prkhan56 wrote: Hello Lalit Please see the Master Sheet Col A and Col B Also I am pasting one of the previous post here *Where is the out time of the employees.* Please see this sample data of employee A for 1st Nov only. Data for the same employee is also there in my previous sample sheet in Col B against his name. Name A11/1/2012 8:13 *IN TIME* Name A11/1/2012 8:13 Name A11/1/2012 19:52 Name A11/1/2012 19:52 Name A11/1/2012 19:52 Name A11/1/2012 19:52 *OUT TIME* *and if there are 100 emplyees then do you want 100 sheets in the workbook * Yes. Newly created sheet would be as per the number of employees Thanks for your time Regards Rashid On Monday, December 31, 2012 7:07:48 AM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, I didn't find any employe name in the master sheet there is only two column No and Action and please tell me from where i will take in/out time. Didn't understand the data you provided. Please check. Regards, Lalit Mohan On Sunday, 30 December 2012 14:16:09 UTC+5:30, prkhan56 wrote: Hello Experts, Is this possible or not? I am reposting my query once again Regards Rashid Khan On Monday, December 24, 2012 10:58:15 AM UTC+4, prkhan56 wrote: Hello Lalit Thanks for the reply. Please see the sample sheet which I have attached with my first post. The names of the employees are in excel sheet and the time in / time out details are shown against each employee name I want to run the macro to run on Master sheet and create time sheet each employee showing their Time In/ Time Out Is this clear? Thanks Regards Rashid Khan On Monday, December 24, 2012 8:41:35 AM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, Could you please explain when you want to run this process to capture InTime and OutTime of an Employee and how we know who are the employees. Is there any list of employee? Regards, Lalit Mohan On Wednesday, 19 December 2012 16:17:43 UTC+5:30, prkhan56 wrote: Hello All I am using excel 2007 I have data in the Master Sheet for various Employees I want to create separate sheet for each employee viz A, B, C and so on(see sample sheet attached) I got the macro from the Group which create separate sheet for each name (it is in the attached shet) I wish to amend the macro so that it gives the output as shown in sample sheets A, B and C My requirements are 1 It should check the date/ time of each Employee and then put the values (shown in Red Color) against each date Time-In / Time-Out on individual sheets 2 The macro should take the first entry and the last entry for each day for each Name 3 It should do this till the last employee is reached 4 There are some unauthorized entry in Col D with no Names in Col A. These entries should be ignored 5 The macro should put formulas in Col G and H (highlighted in Green Color) 6 Everything in Red Color Font would be done
Re: $$Excel-Macros$$ Re: Loop for changing colour
Hi Black, Did you find the solution is helpful. Regards, Lalit Mohan On Monday, 31 December 2012 10:18:25 UTC+5:30, black panther wrote: Dear Prince, No same color, any color but light color, What i am looking for:- if cell A1 having yellow B1 having blue then for 1500 time it need to change when loop run at that time A1 will be blue and B1 will yellow next A1 will be yellow and B1 will be blue and so on. Thanks Regards, Prajakt Pande +971551388482 ** On Mon, Dec 31, 2012 at 7:12 AM, Prince prince...@gmail.com javascript: wrote: Hi Prajakt, Please explain bit more so that i provide best solution from my end.like 1: do you need same color combination 2: and what do u mean by continuously. Regards Prince On Sunday, December 30, 2012 10:43:35 PM UTC+5:30, black panther wrote: Dear Prince, Good evening, that's right but it need to change continuously like loop Thanks Regards, Prajakt Pande +971551388482 ** On Sun, Dec 30, 2012 at 7:38 PM, Prince prince...@gmail.com wrote: Hi Friend PFA Regards PRINCE On Sunday, December 30, 2012 4:41:42 PM UTC+5:30, black panther wrote: Dear All, In advance Happy New Year. Attached a Excel sheet, in that i need to run a loop for changing cell colour. For example :- A coloumn colour to B coloumn, B to C, C to D, D to E, E to F, F to G, G to H H to A Thanks regards, -- 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 post to this group, send email to excel-...@googlegroups.com. To unsubscribe from this group, send email to excel-macros...@** googlegroups.com. Visit this group at http://groups.google.com/**group/excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en . -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Happy New Year To All My Friends
*Same to you prince and Happy new year to all group members. May all your dreams come true in this year.* Regards, Lalit Mohan On Monday, 31 December 2012 17:26:31 UTC+5:30, Prince wrote: *Happy New Year To All.* * * *May God bless you a very nice new year ahead. * * * * * *Regards* *Prince* -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Macro to create attendance Sheet for each Employee
Hi Rashid, I didn't find any employe name in the master sheet there is only two column No and Action and please tell me from where i will take in/out time. Didn't understand the data you provided. Please check. Regards, Lalit Mohan On Sunday, 30 December 2012 14:16:09 UTC+5:30, prkhan56 wrote: Hello Experts, Is this possible or not? I am reposting my query once again Regards Rashid Khan On Monday, December 24, 2012 10:58:15 AM UTC+4, prkhan56 wrote: Hello Lalit Thanks for the reply. Please see the sample sheet which I have attached with my first post. The names of the employees are in excel sheet and the time in / time out details are shown against each employee name I want to run the macro to run on Master sheet and create time sheet each employee showing their Time In/ Time Out Is this clear? Thanks Regards Rashid Khan On Monday, December 24, 2012 8:41:35 AM UTC+4, Lalit Mohan Pandey wrote: Hi Rashid, Could you please explain when you want to run this process to capture InTime and OutTime of an Employee and how we know who are the employees. Is there any list of employee? Regards, Lalit Mohan On Wednesday, 19 December 2012 16:17:43 UTC+5:30, prkhan56 wrote: Hello All I am using excel 2007 I have data in the Master Sheet for various Employees I want to create separate sheet for each employee viz A, B, C and so on (see sample sheet attached) I got the macro from the Group which create separate sheet for each name (it is in the attached shet) I wish to amend the macro so that it gives the output as shown in sample sheets A, B and C My requirements are 1 It should check the date/ time of each Employee and then put the values (shown in Red Color) against each date Time-In / Time-Out on individual sheets 2 The macro should take the first entry and the last entry for each day for each Name 3 It should do this till the last employee is reached 4 There are some unauthorized entry in Col D with no Names in Col A. These entries should be ignored 5 The macro should put formulas in Col G and H (highlighted in Green Color) 6 Everything in Red Color Font would be done the by the macro…viz the Month, Name, Date, Time In and Time Out Thanks in advance Rashid Khan -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: VBA to Destination of Value only
Hi Charlie, You can copy only value from source to destination using below code also: Sub TestCopyValues() Dim varArray()As Variant varArray = Sheets(DailyList).Range(ItemsList).Value Sheets(ItemsListArchives).Range(D).Resize(UBound(varArray), UBound(varArray, 2)).Value = varArray Erase varArray End Sub Hope this will help you. Regards, Lalit Mohan On Monday, 31 December 2012 08:19:44 UTC+5:30, sswcharlie wrote: Hi Ashish Thanks for code. Still not working. Has message to say 'application defined or object defined error'. Using XL2000 Note that my code without the .value works fine and copies across except it has the error messages. Just need to addthe value only. Thnaks Charlie On Sunday, December 30, 2012 8:45:51 PM UTC+13, sswcharlie wrote: Hi I want to copy the values only to another sheet. Without .value on the end it copies thru with error messages. If I put . value on the end it comes up with message 'copy method of range class failed'. Do not want to use copy/paste special method. Want to use 'Destination as shown. Sheets(DailyList).Range(ItemsList).Copy Destination:=Sheets(ItemsListArchives).Range(D Rows.Count).End(xlUp).Offset(7).value Found other code on the net that just used the .value but one above it does not work. Do I have to make a small change ? or does the .value go in a different position ? Thanks Charlie Harris -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Loop for changing colour
Hi Prajakt, Replace Previous code with this one and assign it to the shape (Press Esc to stop) *Option Explicit* * * *Public Declare Function GetAsyncKeyState Lib user32 (ByVal vKey As Long) As Integer* *Private Declare Sub Sleep Lib kernel32 (ByVal dwMilliseconds As Long)* * * *Function EscapeKey() As Boolean* *EscapeKey = (GetAsyncKeyState(vbKeyEscape) And H8000)* *End Function* * * *Sub Test_LoopColor()* * * *Dim lngColorAs Long* *Const strRange As String = A1:H22* *Const strColSaveAs String = U1* ** *With ThisWorkbook.Worksheets(sheet1)* ** *Application.DisplayAlerts = False* ** *.Range(strRange).Copy* *.Range(strColSave).PasteSpecial* ** *Application.CutCopyMode = False* ** *Do While True* ** *If EscapeKey Then* *Exit Do* *End If* ** *Application.ScreenUpdating = False* *lngColor = .Range(strRange).Resize(, 1).Offset(, .Range(strRange).Columns.Count - 1).Resize(1, 1).Interior.Color* *.Range(strRange).Resize(, 1).Resize(, .Range(strRange).Columns.Count - 1).Copy* *.Range(strRange).Resize(, 1).Offset(, 1).Resize(, .Range(strRange).Columns.Count - 1).PasteSpecial* *.Range(strRange).Resize(, 1).Interior.Color = lngColor* *Application.CutCopyMode = False* *Application.Goto .Range(strRange).Resize(1, 1)* *Application.ScreenUpdating = True* *Sleep 300* *Loop* ** *Application.ScreenUpdating = True* *Application.DisplayAlerts = True* ** *End With* * * *End Sub* Hope it will help you. Regards, Lalit Mohan Pandey +919711867226 On Monday, 31 December 2012 08:42:47 UTC+5:30, Prince wrote: Hi Prajakt, Please explain bit more so that i provide best solution from my end.like 1: do you need same color combination 2: and what do u mean by continuously. Regards Prince On Sunday, December 30, 2012 10:43:35 PM UTC+5:30, black panther wrote: Dear Prince, Good evening, that's right but it need to change continuously like loop Thanks Regards, Prajakt Pande +971551388482 ** On Sun, Dec 30, 2012 at 7:38 PM, Prince prince...@gmail.com wrote: Hi Friend PFA Regards PRINCE On Sunday, December 30, 2012 4:41:42 PM UTC+5:30, black panther wrote: Dear All, In advance Happy New Year. Attached a Excel sheet, in that i need to run a loop for changing cell colour. For example :- A coloumn colour to B coloumn, B to C, C to D, D to E, E to F, F to G, G to H H to A Thanks regards, -- 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 post to this group, send email to excel-...@googlegroups.com. To unsubscribe from this group, send email to excel-macros...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: VBA to Destination of Value only
Dear Charlie, Please share your file with us if it is confidential then please share sample at least for better clearity. Regards, Lalit Mohan On Monday, 31 December 2012 11:46:32 UTC+5:30, sswcharlie wrote: Hi Lalit Have tried the code , same result. Getting the 'application defined or object defined error' message. Can you review for me. A mystery to solve. Thnaks Charlie On Mon, Dec 31, 2012 at 4:11 PM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: Hi Charlie, You can copy only value from source to destination using below code also: Sub TestCopyValues() Dim varArray()As Variant varArray = Sheets(DailyList).Range(ItemsList).Value Sheets(ItemsListArchives).Range(D).Resize(UBound(varArray), UBound(varArray, 2)).Value = varArray Erase varArray End Sub Hope this will help you. Regards, Lalit Mohan On Monday, 31 December 2012 08:19:44 UTC+5:30, sswcharlie wrote: Hi Ashish Thanks for code. Still not working. Has message to say 'application defined or object defined error'. Using XL2000 Note that my code without the .value works fine and copies across except it has the error messages. Just need to addthe value only. Thnaks Charlie On Sunday, December 30, 2012 8:45:51 PM UTC+13, sswcharlie wrote: Hi I want to copy the values only to another sheet. Without .value on the end it copies thru with error messages. If I put . value on the end it comes up with message 'copy method of range class failed'. Do not want to use copy/paste special method. Want to use 'Destination as shown. Sheets(DailyList).Range(**ItemsList).Copy Destination:=Sheets(**ItemsListArchives).Range(D Rows.Count).End(xlUp).Offset(**7).value Found other code on the net that just used the .value but one above it does not work. Do I have to make a small change ? or does the .value go in a different position ? Thanks Charlie Harris -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: How to extract sheet from many excel sheets
Hi Maulik, May be below code help you *Sub Test_ExtractData()* * * *Dim wksSht As Worksheet* *Const strFileSavePath As String = C:\* ** *For Each wksSht In ThisWorkbook.Worksheets* *With Workbooks.Add(1)* *With .Worksheets(1)* *wksSht.Cells.Copy* *.Worksheets(1).Cells.PasteSpecial* *.Worksheets(1).Name = wksSht.Name* *Application.CutCopyMode = False* *End With* *Application.DisplayAlerts = False* *.SaveAs strFileSavePath wksSht.Name .xls, 56* *.Close False* *Application.DisplayAlerts = True* *End With* *Next wksSht* * * *End Sub* * * Regards, Lalit Mohan On Monday, 31 December 2012 13:01:52 UTC+5:30, maulik wrote: Hi team, I am having 1excel workbook which contains 250 excel sheets and now I want to extract those sheets into separate workbook for each sheets. Means I want to create 250 workbooks from.250 sheets request u to can someone provide me the vb code for this. Need it little urgently. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Data collection - Problem
Hi Koneng, Explaing with steps would be more understandable. Regards, Lalit Mohan On Friday, 28 December 2012 09:36:24 UTC+5:30, Bhaity Koneng wrote: Dear All Experts, Gud mrng..!! any Solution for this ? Thanks Regards, Koneng On Thu, Dec 27, 2012 at 2:42 PM, Bhaity Koneng bhaity...@gmail.comjavascript: wrote: Hi Hilary / Prince Sir, In the excel sheet, if you see in sheet2, then NH No, Name of the section...and Car tye. In car type i have :- Car/Jeep/Van Single Entry Car/Jeep/Van Multiple Entry Car/Jeep/Van Reusage of Multiple Entry Car/Jeep/Van Reusages of Monthly Passes Car/Jeep/Van Exempt .. Again we have for LCV, TRUCK/BUS, HCM/EME, .so on Same like above for LCV, Truck/Bus. Now , i want all the column wise into row wise to make easier for pivot table. like in the sheet1. please refer sheet 1. i have done for -Manor- Dahisar. by copy and paste. i am not aware of that how to do. Ok any way if i am unable to explain you all then very frankly tell you all expert that i want the counts of vehicle wise by year or month. i have attached the file and i want pivot table like sheet provided. but it is very difficult for me to to do by one by one. Thanks for the revert. Thanks Regards, Koneng On Thu, Dec 27, 2012 at 1:18 PM, Bhaity Koneng bhaity...@gmail.comjavascript: wrote: Hi experts, it is my first post and here i have attached a excel which i am doing manaully copy and past. and it took me time of 6 hours. i have another 100 to do , i have cmpleted just only 3 and it took me 6 hours. i dont know how to complete that, if i do manually it will take 4-5 months to complete that. Any advice or suggestions how to do this a bit quicker than me. Thank you Gurus and please advice me some more tips to learn and have ideas in excel. Regards, Koneng -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * *
@ amar Nice work with more features. Thanks for sharing this to us. Regards, Lalit Mohan +919711867226 On Wednesday, 26 December 2012 20:09:53 UTC+5:30, (%Allmydreams%) wrote: Hi Guys check out this you may get some more ideas to think in different On Sat, Dec 22, 2012 at 4:38 PM, amar takale amart...@gmail.comjavascript: wrote: ok,that fine no problem. On Sat, Dec 22, 2012 at 2:41 PM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: Hi Amar, I created this file just for the way hilary wants. Now the specifications are changing and for that matter it is not that much dynamic. Regards, Lalit Mohan On Saturday, 22 December 2012 12:32:37 UTC+5:30, amar takale wrote: Dear Lalit, but anyone can see Authorized user name.if anyone got it at least one authorized user name he can see also other user name.I want to hide Other user name or hide user Sheet if possible. Regards Amar On Sat, Dec 22, 2012 at 11:24 AM, Lalit Mohan Pandey mohan.p...@gmail.com wrote: Hi Pravin, You can also change the user access. Just login with Lalit Mohan and add user in Authorized_User sheet. Hope it will helps you. Regards, Lalit Mohan On Saturday, 22 December 2012 09:43:13 UTC+5:30, Pravin Gunjal wrote: Hi Lalit, Can we give different passwords to access this file to authorised users.. Regards Pravin Gunjal. -- Forwarded message -- From: Lalit Mohan Pandey mohan.p...@gmail.com Date: Fri, Dec 21, 2012 at 7:26 PM Subject: Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * * To: excel-...@googlegroups.com Cc: res...@gmail.com Hi Hilary, Please find the revised version of the file. User name is the same as before. Let me know if it meets your expectation. Regards, Lalit Mohan On Friday, 21 December 2012 14:54:12 UTC+5:30, hilary lomotey wrote: Hi Lalit If I don't want only the name of the user to appear once the file opens can you make an adjustment? Ie when the file Ȋ̝̊̅§ opened the user name and time appears on the file name. The time Ȋ̝̊̅§ cool but then every person can see the user name and could log in with that user name so I wish only the name shd not appear as part of the file name. Thanks Sent from my BlackBerry® smartphone from Airtel Ghana -- *From: *Lalit Mohan Pandey mohan.p...@gmail.com *Sender: *excel-...@googlegroups.com *Date: *Fri, 21 Dec 2012 00:06:08 -0800 (PST) *To: *excel-...@googlegroups.com** *ReplyTo: *excel-...@googlegroups.com *Subject: *Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * * [image: Boxbe] https://www.boxbe.com/overview This message is eligible for Automatic Cleanup! (mohan.p...@gmail.com) Add cleanup rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3D%252F8XteJTLQN4LlGLd1T25da4vfaXMulTtmyw5TXWFji6PN68RyfLc3Es%252F1GmXV5KCKDEu8PcJmcoDtiCAeRNfpEreCpw5AXLuP9PZxVMPcv3dAXwndpcRF1jcsapuouqSjnqi99Ii8%252Fgtn0fc7pT20w%253D%253D%26key%3Dau52FGIiXhr%252B6uO38alkrnKGehPfGUF7KxzTxTaCn7g%253Dtc=13173783000_2139480533| More infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=13173783000_2139480533 -- Join official Facebook page of this forum @ https://www.facebook.com/ **discu**ssexcel 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 post to this group, send email to excel-...@googlegroups.com. To unsubscribe from this group, send email to excel-macros...@** googlegroups.**com. Visit this group at http://groups.google.com/**group** /excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en . -- 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
Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * *
Hi amar Nice work . Thanks for sharing. Regards, Lalit Mohan +919711867226 On Wednesday, 26 December 2012 20:09:53 UTC+5:30, (%Allmydreams%) wrote: Hi Guys check out this you may get some more ideas to think in different On Sat, Dec 22, 2012 at 4:38 PM, amar takale amart...@gmail.comjavascript: wrote: ok,that fine no problem. On Sat, Dec 22, 2012 at 2:41 PM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: Hi Amar, I created this file just for the way hilary wants. Now the specifications are changing and for that matter it is not that much dynamic. Regards, Lalit Mohan On Saturday, 22 December 2012 12:32:37 UTC+5:30, amar takale wrote: Dear Lalit, but anyone can see Authorized user name.if anyone got it at least one authorized user name he can see also other user name.I want to hide Other user name or hide user Sheet if possible. Regards Amar On Sat, Dec 22, 2012 at 11:24 AM, Lalit Mohan Pandey mohan.p...@gmail.com wrote: Hi Pravin, You can also change the user access. Just login with Lalit Mohan and add user in Authorized_User sheet. Hope it will helps you. Regards, Lalit Mohan On Saturday, 22 December 2012 09:43:13 UTC+5:30, Pravin Gunjal wrote: Hi Lalit, Can we give different passwords to access this file to authorised users.. Regards Pravin Gunjal. -- Forwarded message -- From: Lalit Mohan Pandey mohan.p...@gmail.com Date: Fri, Dec 21, 2012 at 7:26 PM Subject: Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * * To: excel-...@googlegroups.com Cc: res...@gmail.com Hi Hilary, Please find the revised version of the file. User name is the same as before. Let me know if it meets your expectation. Regards, Lalit Mohan On Friday, 21 December 2012 14:54:12 UTC+5:30, hilary lomotey wrote: Hi Lalit If I don't want only the name of the user to appear once the file opens can you make an adjustment? Ie when the file Ȋ̝̊̅§ opened the user name and time appears on the file name. The time Ȋ̝̊̅§ cool but then every person can see the user name and could log in with that user name so I wish only the name shd not appear as part of the file name. Thanks Sent from my BlackBerry® smartphone from Airtel Ghana -- *From: *Lalit Mohan Pandey mohan.p...@gmail.com *Sender: *excel-...@googlegroups.com *Date: *Fri, 21 Dec 2012 00:06:08 -0800 (PST) *To: *excel-...@googlegroups.com** *ReplyTo: *excel-...@googlegroups.com *Subject: *Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * * [image: Boxbe] https://www.boxbe.com/overview This message is eligible for Automatic Cleanup! (mohan.p...@gmail.com) Add cleanup rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3D%252F8XteJTLQN4LlGLd1T25da4vfaXMulTtmyw5TXWFji6PN68RyfLc3Es%252F1GmXV5KCKDEu8PcJmcoDtiCAeRNfpEreCpw5AXLuP9PZxVMPcv3dAXwndpcRF1jcsapuouqSjnqi99Ii8%252Fgtn0fc7pT20w%253D%253D%26key%3Dau52FGIiXhr%252B6uO38alkrnKGehPfGUF7KxzTxTaCn7g%253Dtc=13173783000_2139480533| More infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=13173783000_2139480533 -- Join official Facebook page of this forum @ https://www.facebook.com/ **discu**ssexcel 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 post to this group, send email to excel-...@googlegroups.com. To unsubscribe from this group, send email to excel-macros...@** googlegroups.**com. Visit this group at http://groups.google.com/**group** /excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en . -- 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
$$Excel-Macros$$ Re: Sumif not working
Hi Rajesh, Apply below formula instead of yours with Ctrl + Shift + Enter =SUM((MONTH($C$16:$C$380)=MONTH($B21))*(D$16:D$380)) and the mistake is the matching range is in date format and the criteria is in Text format. Regards, Lalit Mohan +919711867226 On Wednesday, 26 December 2012 11:24:19 UTC+5:30, kasper wrote: Hi Experts, In the attachment file the sumif formula is not working I don't know why.kindly point the mistakes in it. Regards Rajesh -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Sumif not working
Thanks Ms-Exl-Learner for the suggestion. Regards, Lalit Mohan On Wednesday, 26 December 2012 11:52:11 UTC+5:30, Ms-Exl-Learner wrote: @Lalit, Why not a *Non Array Formula* suggestion? In D2 cell =SUMPRODUCT((TEXT($B$16:$B$380,)=$B2)*(D$16:D$380)) Drag it right Do you think your solution will work in all systems? The answer is *NO*. It *FAIL* in many systems because of different date formatting in *Regional DateTime Setting* :( When you are suggesting alternate solution, then be cautious about the side effects :) On 26-12-2012 11:49 AM, Lalit Mohan Pandey wrote: Hi Rajesh, Apply below formula instead of yours with Ctrl + Shift + Enter =SUM((MONTH($C$16:$C$380)=MONTH($B21))*(D$16:D$380)) and the mistake is the matching range is in date format and the criteria is in Text format. Regards, Lalit Mohan +919711867226 On Wednesday, 26 December 2012 11:24:19 UTC+5:30, kasper wrote: Hi Experts, In the attachment file the sumif formula is not working I don't know why.kindly point the mistakes in it. Regards Rajesh -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Macro to create attendance Sheet for each Employee
Hi Rashid, Could you please explain when you want to run this process to capture InTime and OutTime of an Employee and how we know who are the employees. Is there any list of employee? Regards, Lalit Mohan On Wednesday, 19 December 2012 16:17:43 UTC+5:30, prkhan56 wrote: Hello All I am using excel 2007 I have data in the Master Sheet for various Employees I want to create separate sheet for each employee viz A, B, C and so on (see sample sheet attached) I got the macro from the Group which create separate sheet for each name (it is in the attached shet) I wish to amend the macro so that it gives the output as shown in sample sheets A, B and C My requirements are 1 It should check the date/ time of each Employee and then put the values (shown in Red Color) against each date Time-In / Time-Out on individual sheets 2 The macro should take the first entry and the last entry for each day for each Name 3 It should do this till the last employee is reached 4 There are some unauthorized entry in Col D with no Names in Col A. These entries should be ignored 5 The macro should put formulas in Col G and H (highlighted in Green Color) 6 Everything in Red Color Font would be done the by the macro…viz the Month, Name, Date, Time In and Time Out Thanks in advance Rashid Khan -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Need to convert the fax copy in Excel sheet.
Hi Ganesh, You would need some OCR software to convert image to text. you can read this link i am sure its help you http://www.freewaregenius.com/2011/1...ree-ocr-tools/http://www.freewaregenius.com/2011/11/01/how-to-extract-text-from-images-a-comparison-of-free-ocr-tools/ Regards, Lalit Mohan On Sunday, 23 December 2012 11:16:42 UTC+5:30, GANESH wrote: Hi, Can some please send me the Code for convert the Fax copy to Excel sheet. Thanks and Regards Ganesh Acha -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Reason for delay in Filter
Hi Prashant, Before applying filters please use below code: *Application.Calculation = xlCalculationManual* and when you done with the filter then change it back to its original value *Application.Calculation = xlCalculationAutomatic* Instead of using Selection in your code please use a range like this: *Worksheets(Sheet1).range(A1:D10)..AutoFilter Field:=1, Criteria1:=a* Regards, Lalit Mohan On Saturday, 22 December 2012 21:51:03 UTC+5:30, prashant shinde wrote: Hi Friends, Can somebody please tel me what are the reasons for delay in Autofilter code. We have a macro file where in one of the function we have used Selection.AutoFilter Field:=1, Criteria1:=a This code is executed thousand times and it takes almost 1.75 seconds to execute above line once. so the whole process takes lots of time. Can somebody please tell me how why is it happening. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * *
Hi Amar, I created this file just for the way hilary wants. Now the specifications are changing and for that matter it is not that much dynamic. Regards, Lalit Mohan On Saturday, 22 December 2012 12:32:37 UTC+5:30, amar takale wrote: Dear Lalit, but anyone can see Authorized user name.if anyone got it at least one authorized user name he can see also other user name.I want to hide Other user name or hide user Sheet if possible. Regards Amar On Sat, Dec 22, 2012 at 11:24 AM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: Hi Pravin, You can also change the user access. Just login with Lalit Mohan and add user in Authorized_User sheet. Hope it will helps you. Regards, Lalit Mohan On Saturday, 22 December 2012 09:43:13 UTC+5:30, Pravin Gunjal wrote: Hi Lalit, Can we give different passwords to access this file to authorised users.. Regards Pravin Gunjal. -- Forwarded message -- From: Lalit Mohan Pandey mohan.p...@gmail.com Date: Fri, Dec 21, 2012 at 7:26 PM Subject: Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * * To: excel-...@googlegroups.com Cc: res...@gmail.com Hi Hilary, Please find the revised version of the file. User name is the same as before. Let me know if it meets your expectation. Regards, Lalit Mohan On Friday, 21 December 2012 14:54:12 UTC+5:30, hilary lomotey wrote: Hi Lalit If I don't want only the name of the user to appear once the file opens can you make an adjustment? Ie when the file Ȋ̝̊̅§ opened the user name and time appears on the file name. The time Ȋ̝̊̅§ cool but then every person can see the user name and could log in with that user name so I wish only the name shd not appear as part of the file name. Thanks Sent from my BlackBerry® smartphone from Airtel Ghana -- *From: * Lalit Mohan Pandey mohan.p...@gmail.com *Sender: * excel-...@googlegroups.com *Date: *Fri, 21 Dec 2012 00:06:08 -0800 (PST) *To: *excel-...@googlegroups.com** *ReplyTo: * excel-...@googlegroups.com *Subject: *Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * * [image: Boxbe] https://www.boxbe.com/overview This message is eligible for Automatic Cleanup! (mohan.p...@gmail.com) Add cleanup rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3D%252F8XteJTLQN4LlGLd1T25da4vfaXMulTtmyw5TXWFji6PN68RyfLc3Es%252F1GmXV5KCKDEu8PcJmcoDtiCAeRNfpEreCpw5AXLuP9PZxVMPcv3dAXwndpcRF1jcsapuouqSjnqi99Ii8%252Fgtn0fc7pT20w%253D%253D%26key%3Dau52FGIiXhr%252B6uO38alkrnKGehPfGUF7KxzTxTaCn7g%253Dtc=13173783000_2139480533| More infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=13173783000_2139480533 -- 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 post to this group, send email to excel-...@googlegroups.com. To unsubscribe from this group, send email to excel-macros...@** googlegroups.com. Visit this group at http://groups.google.com/**group/excel-macros?hl=enhttp://groups.google.com/group/excel-macros?hl=en . -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en
Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * *
Hi Hialary, Thanks you so much. and merry christmas to you too. Regards, Lalit Mohan On Friday, 21 December 2012 12:14:02 UTC+5:30, hilary lomotey wrote: Hi lalit This solution is beyond my expectation. Great work thanks a million and a merry christmas On Dec 21, 2012 4:42 AM, amar takale amart...@gmail.com javascript: wrote: [image: Boxbe] https://www.boxbe.com/overview This message is eligible for Automatic Cleanup! (amart...@gmail.com javascript:) Add cleanup rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3D5ZjjuQIap00h%252F7%252F6XNiNXPMdG9H1C0ptdw81tQnsyH81m4qw6bBck3JhdmnvB643jhOlYcq%252FNkwYdLalP2W6EUEteP0MyhObE8fLvsqJTVBVaxJszEfSlbjkULynMBXR7SoCwDeYOD0%253D%26key%3D2lx7I3jeMqFVQMYEc7LVTCn7s3XQMp2n6GI5SZnnG84%253Dtc=13172608676_1882182346| More infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=13172608676_1882182346 Hi Dear, Thanks very much .I got code Authorised user .It is useful me in future On Fri, Dec 21, 2012 at 8:39 AM, Lalit Mohan Pandey mohan.p...@gmail.comjavascript: wrote: Hi Hilary, Sorry for that, User name is Lalit Mohan or a whatever you want. and you can also change the user name in Authorised user sheet. Regards, Lalit Mohan On Friday, 21 December 2012 00:33:55 UTC+5:30, hilary lomotey wrote: Hi Lalit looks Li̶̲̥̅̊K̶̲̥̅̊ε̲̣̣̣̥ it but I don't know the user name to type so I haven't been able to access this. Kindly provide the user name. But it looks Li̶̲̥̅̊K̶̲̥̅̊ε̲̣̣̣̥ exactly what I want. Sent from my BlackBerry® smartphone from Airtel Ghana -- *From: * Lalit Mohan Pandey mohan.p...@gmail.com *Sender: * excel-...@googlegroups.com *Date: *Thu, 20 Dec 2012 07:20:04 -0800 (PST) *To: *excel-...@googlegroups.com** *ReplyTo: * excel-...@googlegroups.com *Cc: *excel-macrosEXCEL-...@**googlegroups.com *Subject: *$$Excel-Macros$$ Re: Vba code to Check authorised users * [image: Boxbe] https://www.boxbe.com/overview This message is eligible for Automatic Cleanup! (mohan.p...@gmail.com) Add cleanup rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3DtF4MQIKNtgo4Q7D0l8cASqNpuYJsw%252Fu83%252FmZqFngi1PBTlxAvaWDQJIjpn4UiKuDQylKoQaMVN7QkSvXQ2l4dBDvdZ1mqb1Rc5kdXjW9U%252FAq6L%252FUCd%252BXKZDXfyaXJ28n9JkaBA5iJgF0gVDzGD6wEw%253D%253D%26key%3D0BCS6OGHPq9HhCGrAhZRQyrbD0cxDtBm17EisDMZFog%253Dtc=13167949549_680277595| More infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=13167949549_680277595 -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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
$$Excel-Macros$$ Re: HTTP Request differences....
Hi Raymond, Hope it might helps you, Put below code in a module and add refrence of Microsoft Internet Controls Option Explicit Declare Function apiShowWindow Lib user32 Alias ShowWindow _ (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long Sub Test() Call CopyWebPage End Sub Private Sub CopyWebPage(Optional IEVisible As Boolean = False) Dim IE As InternetExplorer Dim wksSheetAs Worksheet Dim dtTime As Date Application.DisplayAlerts = False Application.EnableEvents = False If Application.ScreenUpdating Then Application.ScreenUpdating = False Set wksSheet = ThisWorkbook.Worksheets(sheet1) wksSheet.DrawingObjects.Delete wksSheet.Cells.Clear Set IE = New InternetExplorer With IE apiShowWindow .hwnd, 3 .Visible = IEVisible .Navigate http://www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm?cat=N; 'Wait for initial page to load Do While .readyState READYSTATE_COMPLETE Or .Busy: DoEvents: Loop 'Copying website dtTime = Now() Begin: If Now() = DateAdd(s, 3, dtTime) Then GoTo Begin End If .ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT .ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT .ExecWB OLECMDID_CLEARSELECTION, OLECMDEXECOPT_DODEFAULT .Quit End With With wksSheet Application.Goto .Range(A1) .Paste Application.CutCopyMode = False .Cells(301, 1).Resize(100).EntireRow.Delete .Cells(1, 1).Resize(247).EntireRow.Delete DeleteShapes ($1:$2) DeleteShapes ($54:$54), False .Range(A1).CurrentRegion.Font.Size = 8 Application.Goto .Range(A1) End With Set IE = Nothing End Sub Private Sub DeleteShapes(ByVal strRange As String, Optional blnDelOtherShp As Boolean = True) Dim Sh As Shape With Worksheets(Sheet1) For Each Sh In .Shapes If Not Application.Intersect(Sh.TopLeftCell, .Range(strRange)) Is Nothing Then Sh.Delete End If Next Sh For Each Sh In .Shapes If Not Sh.Name Like Picture* Then Sh.Delete End If Next Sh End With End Sub and call Test procedure whereever you need to refresh may be in button click or some where else. Regards, Lalit Mohan On Tuesday, 18 December 2012 19:24:04 UTC+5:30, raymond...@gmail.com wrote: Wrote an HTML request for a client to pull prices from a competitors website sent to Him and while it works on his ‘office’ machines it does not work on his laptop. The get requests returns different html than my laptop does. If I have him copy the URL into his browser web page comes back perfectly… I am thinking some kind of versioning? He is using excel 2010 and I am using 2007 But should that affect the HTTP request (Purely VBA)??? Any help would be greatly appreciated... -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: How to import web page Table in Excel
Hi Dhaval, Hope it might helps you, Put below code in a module and add refrence of Microsoft Internet Controls Option Explicit Declare Function apiShowWindow Lib user32 Alias ShowWindow _ (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long Sub Test() Call CopyWebPage End Sub Private Sub CopyWebPage(Optional IEVisible As Boolean = False) Dim IE As InternetExplorer Dim wksSheetAs Worksheet Dim dtTime As Date Application.DisplayAlerts = False Application.EnableEvents = False If Application.ScreenUpdating Then Application.ScreenUpdating = False Set wksSheet = ThisWorkbook.Worksheets(sheet1) wksSheet.DrawingObjects.Delete wksSheet.Cells.Clear Set IE = New InternetExplorer With IE apiShowWindow .hwnd, 3 .Visible = IEVisible .Navigate http://www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm?cat=N 'Wait for initial page to load Do While .readyState READYSTATE_COMPLETE Or .Busy: DoEvents: Loop 'Copying website dtTime = Now() Begin: If Now() = DateAdd(s, 3, dtTime) Then GoTo Begin End If .ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT .ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT .ExecWB OLECMDID_CLEARSELECTION, OLECMDEXECOPT_DODEFAULT .Quit End With With wksSheet Application.Goto .Range(A1) .Paste Application.CutCopyMode = False .Cells(301, 1).Resize(100).EntireRow.Delete .Cells(1, 1).Resize(247).EntireRow.Delete DeleteShapes ($1:$2) DeleteShapes ($54:$54), False .Range(A1).CurrentRegion.Font.Size = 8 Application.Goto .Range(A1) End With Set IE = Nothing End Sub Private Sub DeleteShapes(ByVal strRange As String, Optional blnDelOtherShp As Boolean = True) Dim Sh As Shape With Worksheets(Sheet1) For Each Sh In .Shapes If Not Application.Intersect(Sh.TopLeftCell, .Range(strRange)) Is Nothing Then Sh.Delete End If Next Sh For Each Sh In .Shapes If Not Sh.Name Like Picture* Then Sh.Delete End If Next Sh End With End Sub and call Test procedure whereever you need to refresh may be in button click or some where else. change sheet name and range aaccordingly. Regards, Lalit Mohan On Friday, 21 December 2012 13:59:59 UTC+5:30, Dhaval Shah wrote: Hello Friends i want to import below mention link table to excel Sheet with auto refresh http://www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm?cat=N actually nseindia update there web site recently . earlier i m easily import ( with auto refresh ) the table also atteach my previous file for u r reference Thanks Dhaval Shah -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * *
Hi Hilary, Please find the revised version of the file. User name is the same as before. Let me know if it meets your expectation. Regards, Lalit Mohan On Friday, 21 December 2012 14:54:12 UTC+5:30, hilary lomotey wrote: Hi Lalit If I don't want only the name of the user to appear once the file opens can you make an adjustment? Ie when the file Ȋ̝̊̅§ opened the user name and time appears on the file name. The time Ȋ̝̊̅§ cool but then every person can see the user name and could log in with that user name so I wish only the name shd not appear as part of the file name. Thanks Sent from my BlackBerry® smartphone from Airtel Ghana -- *From: * Lalit Mohan Pandey mohan.p...@gmail.com javascript: *Sender: * excel-...@googlegroups.com javascript: *Date: *Fri, 21 Dec 2012 00:06:08 -0800 (PST) *To: *excel-...@googlegroups.com javascript: *ReplyTo: * excel-...@googlegroups.com javascript: *Subject: *Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * * [image: Boxbe] https://www.boxbe.com/overview This message is eligible for Automatic Cleanup! (mohan.p...@gmail.com javascript:) Add cleanup rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3D%252F8XteJTLQN4LlGLd1T25da4vfaXMulTtmyw5TXWFji6PN68RyfLc3Es%252F1GmXV5KCKDEu8PcJmcoDtiCAeRNfpEreCpw5AXLuP9PZxVMPcv3dAXwndpcRF1jcsapuouqSjnqi99Ii8%252Fgtn0fc7pT20w%253D%253D%26key%3Dau52FGIiXhr%252B6uO38alkrnKGehPfGUF7KxzTxTaCn7g%253Dtc=13173783000_2139480533| More infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=13173783000_2139480533 -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. UserActivityLog.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * *
Hi Pravin, You can also change the user access. Just login with Lalit Mohan and add user in Authorized_User sheet. Hope it will helps you. Regards, Lalit Mohan On Saturday, 22 December 2012 09:43:13 UTC+5:30, Pravin Gunjal wrote: Hi Lalit, Can we give different passwords to access this file to authorised users.. Regards Pravin Gunjal. -- Forwarded message -- From: Lalit Mohan Pandey mohan.p...@gmail.com javascript: Date: Fri, Dec 21, 2012 at 7:26 PM Subject: Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * * To: excel-...@googlegroups.com javascript: Cc: res...@gmail.com javascript: Hi Hilary, Please find the revised version of the file. User name is the same as before. Let me know if it meets your expectation. Regards, Lalit Mohan On Friday, 21 December 2012 14:54:12 UTC+5:30, hilary lomotey wrote: Hi Lalit If I don't want only the name of the user to appear once the file opens can you make an adjustment? Ie when the file Ȋ̝̊̅§ opened the user name and time appears on the file name. The time Ȋ̝̊̅§ cool but then every person can see the user name and could log in with that user name so I wish only the name shd not appear as part of the file name. Thanks Sent from my BlackBerry® smartphone from Airtel Ghana -- *From: * Lalit Mohan Pandey mohan.p...@gmail.com *Sender: * excel-...@googlegroups.com *Date: *Fri, 21 Dec 2012 00:06:08 -0800 (PST) *To: *excel-...@googlegroups.com** *ReplyTo: * excel-...@googlegroups.com *Subject: *Re: $$Excel-Macros$$ Re: Vba code to Check authorised users * * * [image: Boxbe] https://www.boxbe.com/overview This message is eligible for Automatic Cleanup! (mohan.p...@gmail.com) Add cleanup rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3D%252F8XteJTLQN4LlGLd1T25da4vfaXMulTtmyw5TXWFji6PN68RyfLc3Es%252F1GmXV5KCKDEu8PcJmcoDtiCAeRNfpEreCpw5AXLuP9PZxVMPcv3dAXwndpcRF1jcsapuouqSjnqi99Ii8%252Fgtn0fc7pT20w%253D%253D%26key%3Dau52FGIiXhr%252B6uO38alkrnKGehPfGUF7KxzTxTaCn7g%253Dtc=13173783000_2139480533| More infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=13173783000_2139480533 -- 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 post to this group, send email to excel-...@googlegroups.comjavascript: . To unsubscribe from this group, send email to excel-macros...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
$$Excel-Macros$$ Re: Vba code to Check authorised users
Hi Hilary, Please find the attached file. Hope it meets your expectation. Regards, Lalit mohan On Thursday, 20 December 2012 16:41:58 UTC+5:30, hilary lomotey wrote: Hi experts I want to keep a log of people and time as well as action ie open workbook and close workbk of people who use an excel template . For instance if I have say a list of 5 people authorised people when the workbook opens it shd request for the name of person eg a userform with a name if the name doesnt correspond with the authorized users then workbook shd close at the same time if the right person opens the wkbk then the log shd indicate the time date person and action ie wkbook open or close . Is there a vba code to do this thanks -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. UserActivityLog.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Re: Vba code to Check authorised users *
Hi Hilary, Sorry for that, User name is Lalit Mohan or a whatever you want. and you can also change the user name in Authorised user sheet. Regards, Lalit Mohan On Friday, 21 December 2012 00:33:55 UTC+5:30, hilary lomotey wrote: Hi Lalit looks Li̶̲̥̅̊K̶̲̥̅̊ε̲̣̣̣̥ it but I don't know the user name to type so I haven't been able to access this. Kindly provide the user name. But it looks Li̶̲̥̅̊K̶̲̥̅̊ε̲̣̣̣̥ exactly what I want. Sent from my BlackBerry® smartphone from Airtel Ghana -- *From: * Lalit Mohan Pandey mohan.p...@gmail.com javascript: *Sender: * excel-...@googlegroups.com javascript: *Date: *Thu, 20 Dec 2012 07:20:04 -0800 (PST) *To: *excel-...@googlegroups.com javascript: *ReplyTo: * excel-...@googlegroups.com javascript: *Cc: *excel-macrosexcel-...@googlegroups.com javascript: *Subject: *$$Excel-Macros$$ Re: Vba code to Check authorised users * [image: Boxbe] https://www.boxbe.com/overview This message is eligible for Automatic Cleanup! (mohan.p...@gmail.com javascript:) Add cleanup rulehttps://www.boxbe.com/popup?url=https%3A%2F%2Fwww.boxbe.com%2Fcleanup%3Ftoken%3DtF4MQIKNtgo4Q7D0l8cASqNpuYJsw%252Fu83%252FmZqFngi1PBTlxAvaWDQJIjpn4UiKuDQylKoQaMVN7QkSvXQ2l4dBDvdZ1mqb1Rc5kdXjW9U%252FAq6L%252FUCd%252BXKZDXfyaXJ28n9JkaBA5iJgF0gVDzGD6wEw%253D%253D%26key%3D0BCS6OGHPq9HhCGrAhZRQyrbD0cxDtBm17EisDMZFog%253Dtc=13167949549_680277595| More infohttp://blog.boxbe.com/general/boxbe-automatic-cleanup?tc=13167949549_680277595 -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Macro enabled sheet to be worked with manual protect / unprotect
Hi Pravin, According to your explanation how can we identify, when you want to view the file or when you want to update/change the data. For that you need to trigger an event whenever you want to update/change the data for ex:- Click a button or some shortcut key (whatever is ok for you) So the solution given by prince can be used. if you have any query regarding this, you are most welcome to ask us. :) Regards, Lalit Mohan On Monday, 17 December 2012 17:24:57 UTC+5:30, Pravin Gunjal wrote: Thanks for your reply Lalit. My question is simple :- 1. I have an excel file (which is attached) 2. Which is a macro enabled. 3. Most of the times I can use this file only to view it - During this period I would like to make it protect. 4. But when there are some updataion/changes to be done in this sheet, I must unprotect the sheet to do it. 5. But this macro is not allowing me to do the updations, as immediately this is protecting the sheet when I move from one cell to other. Pl look in to this and do the needful. Regards Pravin Gunjal. -- Forwarded message -- From: Lalit Mohan Pandey mohan.p...@gmail.com javascript: Date: Mon, Dec 17, 2012 at 4:03 PM Subject: Re: $$Excel-Macros$$ Re: Macro enabled sheet to be worked with manual protect / unprotect To: excel-...@googlegroups.com javascript: Dear Pravin, Could you please explain it more when you want to protect or unprotect the sheet. so that we can provide you best solution. Regards, Lalit Mohan On Monday, 17 December 2012 15:51:04 UTC+5:30, Pravin Gunjal wrote: Dear Prince Request for your co-operation on this issue. Regards Pravin Gunjal. -- Forwarded message -- From: Pravin Gunjal isk...@gmail.com Date: Fri, Dec 14, 2012 at 3:55 PM Subject: Fwd: $$Excel-Macros$$ Re: Macro enabled sheet to be worked with manual protect / unprotect To: excel-...@googlegroups.com, Prince Kumar - Excel prince...@gmail.com *Dear Prince* * * *Pl find attached file for the same.* * * *Regards* *Pravin Gunjal.* -- Forwarded message -- From: Prince prince...@gmail.com Date: Thu, Dec 13, 2012 at 3:18 PM Subject: Re: $$Excel-Macros$$ Re: Macro enabled sheet to be worked with manual protect / unprotect To: excel-...@googlegroups.com Hi Pravin, Share your workbook. Regards Prince On Thursday, December 13, 2012 3:13:50 PM UTC+5:30, Pravin Gunjal wrote: Dear Prince, By doing the changes suggested by you, the macro is not working properly. Pl confirm. Regards Pravin. -- Forwarded message -- From: Prince prince...@gmail.com Date: Thu, Dec 13, 2012 at 2:44 PM Subject: Re: $$Excel-Macros$$ Re: Macro enabled sheet to be worked with manual protect / unprotect To: excel-...@googlegroups.com Hi Pravin, You should palce this code on Worksheet_Activate() event and befor udating the sheet protact it.For updating the sheet unprotact it from menu bar. regards Prince On Thursday, December 13, 2012 2:29:57 PM UTC+5:30, Pravin Gunjal wrote: Hi Prince Then please let me know , how can I do the changes. Regards Pravin. -- Forwarded message -- From: Prince prince...@gmail.com Date: Thu, Dec 13, 2012 at 2:09 PM Subject: $$Excel-Macros$$ Re: Macro enabled sheet to be worked with manual protect / unprotect To: excel-...@googlegroups.com Hi Pravin, Whenever you move from one cell to other Worksheet_SelectionChange event is called thats why it is not allowing you to update the sheet. Regards Prince On Thursday, December 13, 2012 1:24:00 PM UTC+5:30, Pravin Gunjal wrote: *Dear Friends,* * * *I am using the following macro in one of my excel files. * * * *Private Sub Worksheet_SelectionChange(ByVal Target As Range)* * * *With ThisWorkbook.Worksheets(Field Emails)* *.Unprotect* *[selRow] = Target.Row* *[selCol] = Target.Column* *.Protect* *End With* *End Sub* * * *I used to work in this file with protect sheet and sometimes I would like to work with unprotect sheet to update the data. * *But the below code is not allowing me to keep file unprotect as it’s automatically protecting the sheet once I move to the another cell.* * * *Therefore, I request you to please inform me some corrections/addition in to this code to enable me to work with or without protection. Protect / unprotect should be happened manually and not automatically.* * * *Thank you,* * * *With regards,** **Pravin Gunjal* -- 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
$$Excel-Macros$$ Re: Plz help urgent
Try this also, =TRIM(REPLACE(A2, FIND(B2,A2),LEN(B2)+1,)) Regards, Lalit Mohan On Monday, 17 December 2012 11:58:24 UTC+5:30, Anil Saxena wrote: Hi All, plz help, I want correct email id and remove the bounce email from email id column. -- *Anil Saxena* MIS Executive - Response *Wizcraft International Entertainment Pvt. Ltd.* Plot No 8, Sector 32 | Urban Estate | Gurgaon - 122001 Tel: + (91) (0124) 480 12 12 Fax: + (91) (0124) 480 12 00 Mobile: + (91) 9971870460** Email: anilsaxe...@gmail.com javascript:** Website: www.wizcraftworld.com -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Re: Macro enabled sheet to be worked with manual protect / unprotect
Dear Pravin, Could you please explain it more when you want to protect or unprotect the sheet. so that we can provide you best solution. Regards, Lalit Mohan On Monday, 17 December 2012 15:51:04 UTC+5:30, Pravin Gunjal wrote: Dear Prince Request for your co-operation on this issue. Regards Pravin Gunjal. -- Forwarded message -- From: Pravin Gunjal isk...@gmail.com javascript: Date: Fri, Dec 14, 2012 at 3:55 PM Subject: Fwd: $$Excel-Macros$$ Re: Macro enabled sheet to be worked with manual protect / unprotect To: excel-...@googlegroups.com javascript:, Prince Kumar - Excel prince...@gmail.com javascript: *Dear Prince* * * *Pl find attached file for the same.* * * *Regards* *Pravin Gunjal.* -- Forwarded message -- From: Prince prince...@gmail.com javascript: Date: Thu, Dec 13, 2012 at 3:18 PM Subject: Re: $$Excel-Macros$$ Re: Macro enabled sheet to be worked with manual protect / unprotect To: excel-...@googlegroups.com javascript: Hi Pravin, Share your workbook. Regards Prince On Thursday, December 13, 2012 3:13:50 PM UTC+5:30, Pravin Gunjal wrote: Dear Prince, By doing the changes suggested by you, the macro is not working properly. Pl confirm. Regards Pravin. -- Forwarded message -- From: Prince prince...@gmail.com Date: Thu, Dec 13, 2012 at 2:44 PM Subject: Re: $$Excel-Macros$$ Re: Macro enabled sheet to be worked with manual protect / unprotect To: excel-...@googlegroups.com Hi Pravin, You should palce this code on Worksheet_Activate() event and befor udating the sheet protact it.For updating the sheet unprotact it from menu bar. regards Prince On Thursday, December 13, 2012 2:29:57 PM UTC+5:30, Pravin Gunjal wrote: Hi Prince Then please let me know , how can I do the changes. Regards Pravin. -- Forwarded message -- From: Prince prince...@gmail.com Date: Thu, Dec 13, 2012 at 2:09 PM Subject: $$Excel-Macros$$ Re: Macro enabled sheet to be worked with manual protect / unprotect To: excel-...@googlegroups.com Hi Pravin, Whenever you move from one cell to other Worksheet_SelectionChange event is called thats why it is not allowing you to update the sheet. Regards Prince On Thursday, December 13, 2012 1:24:00 PM UTC+5:30, Pravin Gunjal wrote: *Dear Friends,* * * *I am using the following macro in one of my excel files. * * * *Private Sub Worksheet_SelectionChange(ByVal Target As Range)* * * *With ThisWorkbook.Worksheets(Field Emails)* *.Unprotect* *[selRow] = Target.Row* *[selCol] = Target.Column* *.Protect* *End With* *End Sub* * * *I used to work in this file with protect sheet and sometimes I would like to work with unprotect sheet to update the data. * *But the below code is not allowing me to keep file unprotect as it’s automatically protecting the sheet once I move to the another cell.* * * *Therefore, I request you to please inform me some corrections/addition in to this code to enable me to work with or without protection. Protect / unprotect should be happened manually and not automatically.* * * *Thank you,* * * *With regards,** **Pravin Gunjal* -- Join official Facebook page of this forum @ https://www.facebook.com/** discu**ssexcel 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 post to this group, send email to excel-...@googlegroups.com. To unsubscribe from this group, send email to excel-macros...@** googlegroups.**com. Visit this group at http://groups.google.com/**group** /excel-macros?hl=en http://groups.google.com/group/excel-macros?hl=en. -- 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,
$$Excel-Macros$$ Re: Help Please
No Possible with the same name On Monday, 17 December 2012 09:29:13 UTC+5:30, Sanjay Kumar Maurya wrote: Dear All, I'm inserting new sheet in a workbook but all sheet have a different name like sheet1, sheet2. but i want, if I'm inserting new sheet then every time they have same name! how it is possible plz reply it urgent. -- ***Thanks Sanjay Kumar Maurya* -- 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
