Re: $$Excel-Macros$$ Unable to sort the unique records using VBA...
Dear siti Vi, Brillient! Could you please give me some VBA assignments, so that i can try myself and can improve my VBA skill. Thank you once again for your kind help, hope it will continue...! Many Thanks, Sreekanth M Mobile:9916941744 On Tue, Oct 12, 2010 at 8:56 AM, siti Vi wrote: > please test this coding > > Sub Summarize() >' --- >' by: siti Vi / jakarta, 12 Oct 2010 >'--- >Dim dTable As Range, dSummary As Range, NameCol As Range >Dim StrName As String, i As Long, n As Long >Dim SubTot As Double, UniqList '(As Variant) > >Set dTable = Sheets("Data").Cells(1).CurrentRegion >Set dSummary = Sheets("Report").Cells(2, 1) >Set NameCol = dTable.Offset(1, 0).Resize(dTable.Rows.Count - 1, 1) > >'--creating list of unique name in an 1 dimension array >For n = 1 To NameCol.Rows.Count > If InStr(1, StrName, NameCol(n) & "|") = 0 Then > StrName = StrName & NameCol(n) & "|" > End If >Next n >UniqList = Split(StrName, "|") > >'--construct a summary report >For i = 0 To UBound(UniqList) - 1 > SubTot = 0 > For n = 2 To dTable.Rows.Count > If dTable(n, 1) = UniqList(i) Then > SubTot = SubTot + dTable(n, 3) > End If > Next n > dSummary(i + 1, 1) = UniqList(i) > dSummary(i + 1, 2) = SubTot >Next i > End Sub > > and let me know if work > > best regards, > siti > > > > On Mon, Oct 11, 2010 at 7:13 PM, sreekanth m > wrote: > >> Hi All, >> >> Can someone help me to resolve the attached issue, to paste the unique >> records to Report sheet! >> >> I want to have the answer like the below one. But I am unable to do the >> VBA for the same. >> >> Thank in advance...! >> Name Total Marks A 47 B 59 C 136 D 93 X 34 Y 25 Z 71 >> Thanks&Regards, >> >> Sreekanth M >> Mobile:9916941744 >> > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- Thanks&Regards, Sreekanth M Mobile:9916941744 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ vlookup? sumproduct? sumif? argghhh!?
Hi Michael, It would be easy for anyone in the group to respond, if you can send a set of sample data. Regards, Andy On Tue, Oct 12, 2010 at 5:35 AM, MikeMikeMike wrote: > First of all thank you for any effort put in this direction. I am > having problems with a couple excel formulas that I really need to get > right for a client. > > Here is the deal: > > WORKSHEET 1 > Column1 = email address > Column2 = ***This column I want the sum of events on new years but > having problems (Stuck) > > WORKSHEET 2 > Column1 = email address > Column2 = if an event is on new years indicated with a 1 if it is on > new years and blank if it is not. > > I guess where I am confused is I do not know if it is best to do the > summing in a column3 of WORKSHEET 2 and then use a Vlookup to pull > that data into Column2 of WORKSHEET 1. OR if the summing should happen > in the same formula as the vlookup?? > > Any help with this is most appreciated! > > One thousand thank you's! > > -Michael > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ Macro to copy down/right and back up one line
sample data pls On Tue, Oct 12, 2010 at 5:33 AM, DEF wrote: > I have a file that is gl accts and $$ amts with a subtotal at the > bottom. I need to copy down and then over and then come back up off > of the subtotal line. I tried offset and resize but couldn't get it > to do a row instead of a cell. > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- Srinivasan Ethirajalu, +919840544295 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ Unable to sort the unique records using VBA...
Use pivot table. don't use vba for easy work like this. On Mon, Oct 11, 2010 at 5:43 PM, sreekanth m wrote: > Hi All, > > Can someone help me to resolve the attached issue, to paste the unique > records to Report sheet! > > I want to have the answer like the below one. But I am unable to do the VBA > for the same. > > Thank in advance...! > Name Total Marks A 47 B 59 C 136 D 93 X 34 Y 25 Z 71 > Thanks&Regards, > > Sreekanth M > Mobile:9916941744 > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- Srinivasan Ethirajalu, +919840544295 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ Counting Unique Items
if your list of data (some data = duplicated data) are stored in C3:C26 then *The Count of Unique Values *is *=SUM(1/(COUNTIF(C3:C26,C3:C26)))* best regards siti On Tue, Oct 12, 2010 at 7:13 AM, MikeMikeMike wrote: > > I am having a problem with a forumla I am hoping someone out there can > help me with. > > What I am trying to do is use a vlookup to give me the amount of > unique stores attributed to an email address. OR if it is better to > use one of the many SUM options (which one?) to create an additional > column to get the SUM I am looking for on WORKSHEET 2 and then use a > vlookup to pull that data into WORKSHEET 1? > > WORKSHEET 1 > Column1 = email address > Column2 = This is the column I want to show the number of unique > stores that are attributed to the email address (There is a many > to many relationship. Many email addresses can be attributed to many > stores.) > > WORKSHEET 2 - The stores. > Column1 = email address > Column2 = store_name > > Any help is most appreciated. > > Thank you! > > MikeMikeMike -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts ctv_Count of Unique Values.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Password Help Required
Hi, what about a hidden sheet? On Mon, Oct 11, 2010 at 11:23 PM, hemant shah wrote: > Hi All, > > Can you please help me with my query regarding the password change in VBA. > > Thanks in advance. > > Waiting for the resolution on my query. > > Regards, > Hemant Shah > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
$$Excel-Macros$$ How to have an interactive text box ?
Hi I'm trying to have an interactive text box which I could input some words and at the same time refer to a few different data cells in the spreadsheet. I'm not familiar with VBA thus is there any easy way? Many Thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
$$Excel-Macros$$ Macro to copy down/right and back up one line
I have a file that is gl accts and $$ amts with a subtotal at the bottom. I need to copy down and then over and then come back up off of the subtotal line. I tried offset and resize but couldn't get it to do a row instead of a cell. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
$$Excel-Macros$$ vlookup? sumproduct? sumif? argghhh!?
First of all thank you for any effort put in this direction. I am having problems with a couple excel formulas that I really need to get right for a client. Here is the deal: WORKSHEET 1 Column1 = email address Column2 = ***This column I want the sum of events on new years but having problems (Stuck) WORKSHEET 2 Column1 = email address Column2 = if an event is on new years indicated with a 1 if it is on new years and blank if it is not. I guess where I am confused is I do not know if it is best to do the summing in a column3 of WORKSHEET 2 and then use a Vlookup to pull that data into Column2 of WORKSHEET 1. OR if the summing should happen in the same formula as the vlookup?? Any help with this is most appreciated! One thousand thank you's! -Michael -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
$$Excel-Macros$$ Counting Unique Items
I am having a problem with a forumla I am hoping someone out there can help me with. What I am trying to do is use a vlookup to give me the amount of unique stores attributed to an email address. OR if it is better to use one of the many SUM options (which one?) to create an additional column to get the SUM I am looking for on WORKSHEET 2 and then use a vlookup to pull that data into WORKSHEET 1? WORKSHEET 1 Column1 = email address Column2 = This is the column I want to show the number of unique stores that are attributed to the email address (There is a many to many relationship. Many email addresses can be attributed to many stores.) WORKSHEET 2 - The stores. Column1 = email address Column2 = store_name Any help is most appreciated. Thank you! MikeMikeMike -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ CAN ANYONE HELP ME TO SOLVE THE PROBLEM DATE FILTERING
updated one On Mon, Oct 11, 2010 at 10:41 PM, Srinivasan Ethirajalu < srinivasan.ethiraj...@gmail.com> wrote: > refer the attachment > > Srinivasan > > > On Mon, Oct 11, 2010 at 10:21 PM, Rajesh K R > wrote: > >> HI ALL EXPERRTS >> >> IS THERE ANY WAY TO FILTER THE DATA 1/10/10 TO 10/10/10 IN EXCEL, >> >> THANKS IN ADVANCE TO HELP ME. >> >> RAJESH KAINIKKARA >> >> -- >> >> -- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> >> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts >> > > > > -- > Srinivasan Ethirajalu, > +919840544295 > -- Srinivasan Ethirajalu, +919840544295 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts <>
Re: $$Excel-Macros$$ CAN ANYONE HELP ME TO SOLVE THE PROBLEM DATE FILTERING
refer the attachment Srinivasan On Mon, Oct 11, 2010 at 10:21 PM, Rajesh K R wrote: > HI ALL EXPERRTS > > IS THERE ANY WAY TO FILTER THE DATA 1/10/10 TO 10/10/10 IN EXCEL, > > THANKS IN ADVANCE TO HELP ME. > > RAJESH KAINIKKARA > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- Srinivasan Ethirajalu, +919840544295 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts <>
$$Excel-Macros$$ CAN ANYONE HELP ME TO SOLVE THE PROBLEM DATE FILTERING
HI ALL EXPERRTS IS THERE ANY WAY TO FILTER THE DATA 1/10/10 TO 10/10/10 IN EXCEL, THANKS IN ADVANCE TO HELP ME. RAJESH KAINIKKARA -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts DATE FILTERING.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ good evening to all experts
The trick here is to understand that Excel doesn't know anything about "dates" and "time". What it does is take the number of days since 1/1/1900 and INTERPRETS it as days and fraction of days. So.. 10/11/2010 @ 6:37pm is actually 40462 days since 1/1/1900 and .775694 of another day, or 40462.775694 Now, from 10/11/2010 to 11/11/2010 is 31 days. so, taking 10/11/2010 6:37pm + 31 = 11/11/2010 @ 6:37pm (or using a formula like =A2 + 31) to figure out how to do this for ANY month is a bit trickier... Paul - Original Message > From: renuka chari > To: MS EXCEL AND VBA MACROS > Sent: Mon, October 11, 2010 11:20:37 AM > Subject: $$Excel-Macros$$ good evening to all experts > > hi excel experts i have a small problem > > MY DATE AND TIME FORMAT IS "mm/dd/ hh:mm am/pm" > I WANT > TO CHANGE COL: A CHANGED IN TO COL:A" > = > col:a col:a > = > > 10/11/2010 18:37 11/11/2010 18:37 > 10/11/2010 18:30 11/11/2010 18:30 > 10/11/2010 18:40 11/11/2010 18:40 > 10/11/2010 18:50 11/11/2010 18:50 > 10/11/2010 18:23 11/11/2010 18:23 > 10/11/2010 18:10 11/11/2010 18:10 > 10/11/2010 18:09 > 11/11/2010 18:09 > 10/11/2010 18:07 11/11/2010 18:07 > 10/11/2010 18:01 11/11/2010 18:01 > 10/11/2010 17:37 11/11/2010 17:37 > 10/11/2010 18:33 11/11/2010 18:33 > 10/11/2010 17:03 11/11/2010 17:03 > 10/11/2010 21:37 11/11/2010 21:37 > 10/11/2010 19:37 11/11/2010 19:37 > > i want change month only > thanks in advance > > -- >-- >- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : >http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
$$Excel-Macros$$ good evening to all experts
hi excel experts i have a small problem MY DATE AND TIME FORMAT IS "mm/dd/ hh:mm am/pm" I WANT TO CHANGE COL: A CHANGED IN TO COL:A" = col:a col:a = 10/11/2010 18:37 11/11/2010 18:37 10/11/2010 18:3011/11/2010 18:30 10/11/2010 18:4011/11/2010 18:40 10/11/2010 18:5011/11/2010 18:50 10/11/2010 18:2311/11/2010 18:23 10/11/2010 18:1011/11/2010 18:10 10/11/2010 18:09> 11/11/2010 18:09 10/11/2010 18:0711/11/2010 18:07 10/11/2010 18:0111/11/2010 18:01 10/11/2010 17:3711/11/2010 17:37 10/11/2010 18:3311/11/2010 18:33 10/11/2010 17:0311/11/2010 17:03 10/11/2010 21:3711/11/2010 21:37 10/11/2010 19:3711/11/2010 19:37 i want change month only thanks in advance -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ Custom Function with UserForm
How are you calling the function? I copied your function to a module. I changed it to: Function DeviceForm(rng As Range) As String If UCase(rng.Value) = "X" Then MsgBox "Row: " & rng.Row End If End Function so that I didn't have to create a userform. then put a "X" in A2. in B2 I put: =deviceform(A2) them menu then popped up with the row # (2). I moved the X to A4, and the popup displayed (4).. is the rng.Row what you were looking for? Paul - Original Message > From: RemyMaza > To: MS EXCEL AND VBA MACROS > Sent: Mon, October 11, 2010 9:49:14 AM > Subject: $$Excel-Macros$$ Custom Function with UserForm > > I have a simple function that looks at a single cell for it's value. > If it's an X, then i'd like for a form to appear. I'd like to pass > the value rng.row to the form so I can know which row I'm > manipulating. I've failed so far in my attempts to do just that. Any > tips would be awesome. > > Function DeviceForm(rng As Range) As String > If UCase(rng.Value) = "X" Then > frmDevices.Show > End If > End Function > > I'm using a custom form so the user can pick from drop-downs and I'll > update rows on a separate sheet. I'm not sure if I'm headed down the > wrong path, but here's the code for that too. I feel like I'm running > into something rookieish... Can anyone spot it? > > > Private Sub cmbAddDevice_Click() > > ActiveWorkbook.Sheets("TABLES").Range("B2").Value = cmb1.Value > > End Sub > > Private Sub cmdClear_Click() > cmb1.Value = "" > cmb2.Value = "" > cmb3.Value = "" > cmb4.Value = "" > cmb5.Value = "" > cmb6.Value = "" > cmb7.Value = "" > End Sub > > Private Sub UserForm_Initialize() > Dim arrDropDownVals As Variant > > 'Variables for drop-downs > arrDropDownVals = Array("S", "O", "G") > > With Me.cmb1 > .Clear > For i = 0 To UBound(arrDropDownVals) > .AddItem arrDropDownVals(i) > Next i > .ListIndex = 0 > End With > > With Me.cmb2 > .Clear > For i = 0 To UBound(arrDropDownVals) > .AddItem arrDropDownVals(i) > Next i > .ListIndex = 0 > End With > > With Me.cmb3 > .Clear > For i = 0 To UBound(arrDropDownVals) > .AddItem arrDropDownVals(i) > Next i > .ListIndex = 0 > End With > > With Me.cmb4 > .Clear > For i = 0 To UBound(arrDropDownVals) > .AddItem arrDropDownVals(i) > Next i > '.ListIndex = 0 > End With > > With Me.cmb5 > .Clear > For i = 0 To UBound(arrDropDownVals) > .AddItem arrDropDownVals(i) > Next i > '.ListIndex = 0 > End With > > With Me.cmb6 > .Clear > For i = 0 To UBound(arrDropDownVals) > .AddItem arrDropDownVals(i) > Next i > '.ListIndex = 0 > End With > > With Me.cmb7 > .Clear > For i = 0 To UBound(arrDropDownVals) > .AddItem arrDropDownVals(i) > Next i > '.ListIndex = 0 > End With > End Sub > > > Cheers, > Matt > > -- >-- >- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : >http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
$$Excel-Macros$$ Custom Function with UserForm
I have a simple function that looks at a single cell for it's value. If it's an X, then i'd like for a form to appear. I'd like to pass the value rng.row to the form so I can know which row I'm manipulating. I've failed so far in my attempts to do just that. Any tips would be awesome. Function DeviceForm(rng As Range) As String If UCase(rng.Value) = "X" Then frmDevices.Show End If End Function I'm using a custom form so the user can pick from drop-downs and I'll update rows on a separate sheet. I'm not sure if I'm headed down the wrong path, but here's the code for that too. I feel like I'm running into something rookieish... Can anyone spot it? Private Sub cmbAddDevice_Click() ActiveWorkbook.Sheets("TABLES").Range("B2").Value = cmb1.Value End Sub Private Sub cmdClear_Click() cmb1.Value = "" cmb2.Value = "" cmb3.Value = "" cmb4.Value = "" cmb5.Value = "" cmb6.Value = "" cmb7.Value = "" End Sub Private Sub UserForm_Initialize() Dim arrDropDownVals As Variant 'Variables for drop-downs arrDropDownVals = Array("S", "O", "G") With Me.cmb1 .Clear For i = 0 To UBound(arrDropDownVals) .AddItem arrDropDownVals(i) Next i .ListIndex = 0 End With With Me.cmb2 .Clear For i = 0 To UBound(arrDropDownVals) .AddItem arrDropDownVals(i) Next i .ListIndex = 0 End With With Me.cmb3 .Clear For i = 0 To UBound(arrDropDownVals) .AddItem arrDropDownVals(i) Next i .ListIndex = 0 End With With Me.cmb4 .Clear For i = 0 To UBound(arrDropDownVals) .AddItem arrDropDownVals(i) Next i '.ListIndex = 0 End With With Me.cmb5 .Clear For i = 0 To UBound(arrDropDownVals) .AddItem arrDropDownVals(i) Next i '.ListIndex = 0 End With With Me.cmb6 .Clear For i = 0 To UBound(arrDropDownVals) .AddItem arrDropDownVals(i) Next i '.ListIndex = 0 End With With Me.cmb7 .Clear For i = 0 To UBound(arrDropDownVals) .AddItem arrDropDownVals(i) Next i '.ListIndex = 0 End With End Sub Cheers, Matt -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ VLOOKUP function
Dear, Please find herewith VBA vlookup one Value look up in column 1 6 Value look up in column 2 vlookup2(F10, F11, tLookupDemo, 3) Result Option Explicit '' *** '' Purpose : Test VLOOKUP2 function '' Written : 01-Nov-2001 by Andy Wiggins, Byg Software Limited '' Notes: To see the reuslts, ensure the "Immediate" window is open (Ctrl+G) '' Sub Test_Vlookup2() ThisWorkbook.Activate Sheets("Lookup in two columns").Select ''Test two numeric parameters Debug.Print "(1) " & VLOOKUP2(3, 5, Range("tLookupDemo"), 3) ''Test alpha and numeric parameter Debug.Print "(2) " & VLOOKUP2("One", 4, Range("tLookupDemo"), 3) ''Test two valid parameters If IsError(VLOOKUP2(3, 5, Range("tLookupDemo"), 3)) Then Debug.Print "(3) " & "Is error" Else Debug.Print "(3) " & "Okay" End If ''Test with an invalid parameter If IsError(VLOOKUP2(33, 5, Range("tLookupDemo"), 3)) Then Debug.Print "(4) " & "Is error" Else Debug.Print "(4) " & "Okay" End If End Sub '' *** '' Purpose : Lookup function based on two columns / Demonstrate use of Evaluate '' Written : 30-Oct-2001 by Andy Wiggins, Byg Software Limited '' Amended : 09-May-2002 by Andy Wiggins '' Function VLOOKUP2(pVal1, pVal2, pRng As Range, pInd As Integer) ''The lookup values refer to columns 1 and 2 in the range Application.Volatile Dim lStr_Seek As String Dim lStr_Col1 As String Dim lStr_Col2 As String Dim lStr_Col3 As String ''If an error occurs with "Evaluate" it isn't passed to this function's error handler ''This handler will pick up any other errors that may occur On Error GoTo Error_VLOOKUP2 ''The quotes enure strings are treated as such and NOT as range names lStr_Seek = & pVal1 & ":""&""" & pVal2 & lStr_Col1 = pRng.Columns(1).Address lStr_Col2 = pRng.Columns(2).Address lStr_Col3 = pRng.Columns(pInd).Address VLOOKUP2 = Evaluate("index(" & lStr_Col3 & ",match(" & lStr_Seek & "," & lStr_Col1 & "&"":""&" & lStr_Col2 & ",0))") Exit Function Error_VLOOKUP2: VLOOKUP2 = Err End Function On Mon, Oct 11, 2010 at 5:34 PM, Pranav Vashishtha wrote: > Dear Alan > Apply tis formula and your problem will be over. > VlOOKUP Function needs 4 parameters your 1st,2nd,& 4th parameters are > correct but in the 3rd parameter (Column no.) you supplied the column > address that is $E:$E. I have just changed it to column no. 2 which is > second column of your table where you want to look up for data. > > =VLOOKUP($A2,$D$2:$E$9,2,FALSE) > > Hope that helps! > > Warm Regards, > > Pranav > > > On Sun, Oct 10, 2010 at 2:30 AM, Alan wrote: > >> I am using Excel 2007 and having a strange problem with the VLOOKUP >> function. I have never used it before, but I have looked at examples >> on the Internet. However, I cannot figure it out. >> >> I placed the following formula in Cell B2: >> >> =VLOOKUP($A2,$D$2:$E$9,$E:$E,FALSE) >> >> and then copied it to B3 and B4, which respectively show up as: >> >> =VLOOKUP($A3,$D$2:$E$9,$E:$E,FALSE) >> =VLOOKUP($A4,$D$2:$E$9,$E:$E,FALSE) >> >> My lookup table is located at D2:E9, with what I am looking up in >> column D, and the value for the lookup in column E. >> >>I placed the second entry in the table in cells A2, A3, and A4. >> The VLOOKUP functions in cells B2, B3, and B4 show the following >> results: >> >> B2: #VALUE! >> B3: , same as in A3 >> B4: the correct lookup value. >> >> This does not make any sense to me, based on the examples I have >> seen. >> >> What am I doing wrong? Thanks, Alan >> >> >> -- >> >> -- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> >> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts >> > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http:/
Re: $$Excel-Macros$$ VLOOKUP function
Dear Alan Apply tis formula and your problem will be over. VlOOKUP Function needs 4 parameters your 1st,2nd,& 4th parameters are correct but in the 3rd parameter (Column no.) you supplied the column address that is $E:$E. I have just changed it to column no. 2 which is second column of your table where you want to look up for data. =VLOOKUP($A2,$D$2:$E$9,2,FALSE) Hope that helps! Warm Regards, Pranav On Sun, Oct 10, 2010 at 2:30 AM, Alan wrote: > I am using Excel 2007 and having a strange problem with the VLOOKUP > function. I have never used it before, but I have looked at examples > on the Internet. However, I cannot figure it out. > > I placed the following formula in Cell B2: > > =VLOOKUP($A2,$D$2:$E$9,$E:$E,FALSE) > > and then copied it to B3 and B4, which respectively show up as: > > =VLOOKUP($A3,$D$2:$E$9,$E:$E,FALSE) > =VLOOKUP($A4,$D$2:$E$9,$E:$E,FALSE) > > My lookup table is located at D2:E9, with what I am looking up in > column D, and the value for the lookup in column E. > >I placed the second entry in the table in cells A2, A3, and A4. > The VLOOKUP functions in cells B2, B3, and B4 show the following > results: > > B2: #VALUE! > B3: , same as in A3 > B4: the correct lookup value. > > This does not make any sense to me, based on the examples I have > seen. > > What am I doing wrong? Thanks, Alan > > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ VLOOKUP function
Dear Alan Apply tis formula and your problem will be over. VlOOKUP Function needs 4 parameters your 1st,2nd,& 4th parameters are correct but in the 3rd parameter (Column no.) you supplied the column address that is $E:$E. I have just changed it to column no. 2 which is second column of your table where you want to look up for data. =VLOOKUP($A2,$D$2:$E$9,2,FALSE) Hope that helps! Warm Regards, Pranav On Sun, Oct 10, 2010 at 2:30 AM, Alan wrote: > I am using Excel 2007 and having a strange problem with the VLOOKUP > function. I have never used it before, but I have looked at examples > on the Internet. However, I cannot figure it out. > > I placed the following formula in Cell B2: > > =VLOOKUP($A2,$D$2:$E$9,$E:$E,FALSE) > > and then copied it to B3 and B4, which respectively show up as: > > =VLOOKUP($A3,$D$2:$E$9,$E:$E,FALSE) > =VLOOKUP($A4,$D$2:$E$9,$E:$E,FALSE) > > My lookup table is located at D2:E9, with what I am looking up in > column D, and the value for the lookup in column E. > >I placed the second entry in the table in cells A2, A3, and A4. > The VLOOKUP functions in cells B2, B3, and B4 show the following > results: > > B2: #VALUE! > B3: , same as in A3 > B4: the correct lookup value. > > This does not make any sense to me, based on the examples I have > seen. > > What am I doing wrong? Thanks, Alan > > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
RE: $$Excel-Macros$$ playing with several timers with vba excel 2000 ...
Hi Alfred, Please don't apologise for your English. We are quite tolerant here. Have a look at the attached. First click on the 'Subject' buttons. You will notice that clicking any one of them puts a "Yes" in it's Row, and changes the other Rows to "No". Use these buttons to select which subject is the first to be discussed. I have used seconds, not minutes, for test purposes. But you can change back to minutes later. You can enter whatever values you want in Cells B3, B4 and B5, but I suggest you leave them as they are for now. The value in Cell B2 is the entire length of the meeting, and is just the sum of B3, B4 and B5 Click the Start button. and watch what happens. You can click on a different Subject button at any time to change which Subject is being timed, despite the fact the cursor has changed. The bar graph effect is done by Conditional Formatting. Each colour cell represents 1/20 of the subjects allotted time. One small problem - if you want the main timer to stop it before the end of the meeting, select any unused cell and type something (eg: 1) Thanks for the interesting question, and let us know how it goes. Regards - Dave. > Date: Sun, 10 Oct 2010 17:14:40 -0700 > Subject: $$Excel-Macros$$ playing with several timers with vba excel 2000 ... > From: jos...@gmail.com > To: excel-macros@googlegroups.com > > Hi to all ! > first of all, sorry for my so poor english, i'm a poor poor poor > frenchy guy ! > > i'm trying to dev à "meeting time manager" ... > > here is my project : > > in e meeting, you have several subjects, and, of course > each subjects have there own time... > And, of course, one meeting have his global time. > > for exemple > > meeting EXCEL 3 houres. > > and for example > subject 1 : macros : 1 houre > subject 2 : cells : 30 minutes > subject 3 : automation : 1h30 > > well, I write those lines in a sheet > > for each line (meeting EXCEL, subj 1, subj 2 subj 3) i would like > to créate a sort of buton tu start à timer, AND, créate a sort of > progress bar (for each line) with a label, and the width propriety > to show the time progress > > of course, the general time meeting (first line) can't be stopped. > > and, for all the subjects, the "pilot" of the meeting can decide wich > subject will be treated or not and purhaps another order ... > > If a "excel guru" can help me, it will be fantastic for me. > > of course, I must dev on my own that little app, but, if > i can read some examples on : > - using timers > - using progress bar with timers. > - creating labels if a cell is not blank (for exemple if > I put "tatatatat" column A line 6, then I love to know > how to create à buton on line 6 column B, and some Label maned > like the content of line6 colA.) > > > > well. very very very thanks for having read my horrible english > problem, without google translate ! lol > > José from France > > > > > > > > > > > > > > > -- > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts Timer.xls Description: Binary data