$$Excel-Macros$$ Re: Highlight & add data to another sheet - help needed
Hi Experts... please help me on this. On Tuesday, June 7, 2016 at 4:25:32 PM UTC+5:30, SG wrote: > > Hi Masters, > > Need your help again. I need to do some changes in my file. > > First, when the user enters the start & end time with same day with their > name, it would become red highlighted & message popped out to change the > time to void duplicate (row 10 & 13). > Second, when they click the submit button, all data entered in sheet 1 > goes to master sheet in same format & removes the entered data > in sheet 1 but leave the formulas in sheet 1 so that next time when user > enters data, it would update itself & append the data in master sheet > on again submitting it. > > I have attached the sheet for your reference. please help with the solution > Thanks in advance. > -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Highlight & add data to another sheet - help needed
Hi Masters, Need your help again. I need to do some changes in my file. First, when the user enters the start & end time with same day with their name, it would become red highlighted & message popped out to change the time to void duplicate (row 10 & 13). Second, when they click the submit button, all data entered in sheet 1 goes to master sheet in same format & removes the entered data in sheet 1 but leave the formulas in sheet 1 so that next time when user enters data, it would update itself & append the data in master sheet on again submitting it. I have attached the sheet for your reference. please help with the solution Thanks in advance. -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. utilization.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ Re: Presenting Chart..Need help
Thanks for the idea Gowri. I know this trick but this time , i forgot to use. :) Thanks for recalling it. On Monday, June 6, 2016 at 3:58:16 PM UTC+5:30, a.gowri wrote: > > please find the attachment. > > On Mon, Jun 6, 2016 at 3:15 PM, SG <sona...@gmail.com > > wrote: > >> Hi Experts, >> >> Please help. >> >> On Monday, June 6, 2016 at 1:46:58 PM UTC+5:30, SG wrote: >>> >>> Hi Experts, >>> >>> I need your help in presenting the week wise time used in an account. >>> What i need is to prepare a chart in which when i select month & name, >>> the no. of weeks & its values in that particular month would be the >>> source data of chart. >>> >>> For e.g, if i select 6 as month (which is june) & name as "a", the chart >>> would display the time used by "a" in activites of weeks 23,24,25 & 26. >>> >>> one more chart with if i select 6 as month , name as "a" , chart would >>> display time used by "a" account wise in weeks 23,24,25,26. >>> >>> Please help me with the solution. I have attached the data for you >>> reference. >>> >>> Thanks in advance. >>> >> -- >> 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 https://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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Presenting Chart..Need help
Hi Experts, Please help. On Monday, June 6, 2016 at 1:46:58 PM UTC+5:30, SG wrote: > > Hi Experts, > > I need your help in presenting the week wise time used in an account. What > i need is to prepare a chart in which when i select month & name, > the no. of weeks & its values in that particular month would be the source > data of chart. > > For e.g, if i select 6 as month (which is june) & name as "a", the chart > would display the time used by "a" in activites of weeks 23,24,25 & 26. > > one more chart with if i select 6 as month , name as "a" , chart would > display time used by "a" account wise in weeks 23,24,25,26. > > Please help me with the solution. I have attached the data for you > reference. > > Thanks in advance. > -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Presenting Chart..Need help
Hi Experts, I need your help in presenting the week wise time used in an account. What i need is to prepare a chart in which when i select month & name, the no. of weeks & its values in that particular month would be the source data of chart. For e.g, if i select 6 as month (which is june) & name as "a", the chart would display the time used by "a" in activites of weeks 23,24,25 & 26. one more chart with if i select 6 as month , name as "a" , chart would display time used by "a" account wise in weeks 23,24,25,26. Please help me with the solution. I have attached the data for you reference. Thanks in advance. -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. utilization.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ Sampling - Please check
Hi Paul, Added Column "D" as unique id & what needed as result is in "Result" sheet. It would require per month-per slot-per name >> 3 samples. VBA Macro looks a great idea to work with this. PFA for your reference. My apologies & promise no other modification would be required after this. On Monday, February 1, 2016 at 8:46:32 PM UTC+5:30, Paul Schreiner wrote: > > I think I better understand what your data means. > You're wanting a random sample of (4) records for each of the names in the > "Name" column. > and you're wanting the (4) samples to be in each if (4) time ranges. > > The attached file takes care of THAT, > > but your additional requirement, that of what to do if you don't have a > match within the time range > may be more problematic. > > An attempt to create a "flag" to show when a record is selected, > then, when no entry exists, choose from those that were not selected. > Causes a circular reference. > > I think the only way to get past this is to write a VBA macro that loads > the data into an array > and processes the array. > > But before I try something like that: > > Is this the way the data is "presented" to you? > is there any "unique" identifier for the records? > > > *Paul* > - > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John Wesley* > - > > > On Monday, February 1, 2016 8:33 AM, SG <sona...@gmail.com > > wrote: > > > > Paul, apologies for bothering you so much & unable to interpret the > expectation. Please refer sheet 2 in attached file for results per agent. > Also, I have taken duration as example of selecting sample. > > On Monday, February 1, 2016 at 6:37:46 PM UTC+5:30, Paul Schreiner wrote: > > I'm not sure what you are expecting, or how this does not meet your > expectations. > > For January, you have (9) entries in the 0-10 minute range. > So each of your (4) agents will get one of the samples. > > But you only have (3) entries in the 10-20 minute range. > So, the 4th agent does not receive an entry. > > The 20-30 minute range only has ONE entry, so only the first agent > receives an entry. > > in the "over 30" range, there are two entries, so only the first two > agents receive entries. > > That all seems to work. > > You said: "if less than 4, still it would come with number of calls it has" > I have no idea what that means. > does that mean that if there are not sufficient records for that time > period, you want to take an entry from another? > > If you could: > Take the records you've provided for January > and show me what you would EXPECT the (4) agents to show. > (and why?) > > > > *Paul* > -- --- > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John > Wesley*-- > --- > > > On Monday, February 1, 2016 5:34 AM, SG <sona...@gmail.com> wrote: > > > > Hi Paul, > > I really appreciate your help. Apologies for all the confusion. > > I have added some more data in this file. I just need if i select Jan > Month, so who ever agents has 4 calls with duration as per the slab must > come in sample & if less than 4, still it would come with number of calls > it has. It must cover all agent with number of calls they have in that > month. Honestly speaking, You have really done the efforts in this file > but my purpose is not getting solved neither i'm able to do it. :( If you > can again relook this..please check otherwise..really thanks > > Regards > SG > > > > On Friday, January 29, 2016 at 7:25:41 PM UTC+5:30, Paul Schreiner wrote: > > See below: > *Paul* > -- --- > > > On Friday, January 29, 2016 3:19 AM, SG <sona...@gmail.com> wrote: > > > > Hi Paul, I got the sheet. Thankyou so much for all your effort in this > sampling. I'm really thankful. > > Only one thing i observed is that, in raw data , there are 4 entries of > Rohit in Jan but in Random Sampling section, only two are coming as sample > for Rohit whereas it should be 4calls,per person, per month with available > slots as per the slab. PFA for you. > *I don't think that
Re: $$Excel-Macros$$ Sampling - Please check
Hi Paul, I really appreciate your help. Apologies for all the confusion. I have added some more data in this file. I just need if i select Jan Month, so who ever agents has 4 calls with duration as per the slab must come in sample & if less than 4, still it would come with number of calls it has. It must cover all agent with number of calls they have in that month. Honestly speaking, You have really done the efforts in this file but my purpose is not getting solved neither i'm able to do it. :( If you can again relook this..please check otherwise..really thanks Regards SG On Friday, January 29, 2016 at 7:25:41 PM UTC+5:30, Paul Schreiner wrote: > > See below: > *Paul* > - > > > On Friday, January 29, 2016 3:19 AM, SG <sona...@gmail.com > > wrote: > > > > Hi Paul, I got the sheet. Thankyou so much for all your effort in this > sampling. I'm really thankful. > > Only one thing i observed is that, in raw data , there are 4 entries of > Rohit in Jan but in Random Sampling section, only two are coming as sample > for Rohit whereas it should be 4calls,per person, per month with available > slots as per the slab. PFA for you. > *I don't think that is a reasonable expectation.* > *In January, rohit may appear 4 times, but it only appears TWICE in the > 0-10 minute category, ONCE in the 10-20 minute and ONCE in the 20-30 > minute.* > > *In the sample data, there are (5) records in the 0-10 minute category for > January.* > *With only (4) people, one of the (5) records will not be selected.* > *When the data is sorted by the Rand" column, there is a "random" chance > that only one of the rohit records will be selected.* > > > Also, Rand function don't have any impact in selection of samples. Am i > right? > Please check if this can be solved as this is the condition for sampling. > *In order for the RAND() function to affect the sampling, you must sort > the data.* > *When you sort it by the Rand column, the "index" number gets recalculated > based on the "position".* > *so, each time you sort the data, you'll get a different set of selections > for the (4) people.* > > *Attached you will find a version of the file with a "sort" button.* > > > On Friday, January 29, 2016 at 11:58:04 AM UTC+5:30, SG wrote: > > No Paul, I didn't get it. Can you please share it again. > > On Thursday, January 28, 2016 at 6:36:10 PM UTC+5:30, Paul Schreiner wrote: > > Yeesss.. and responded with an update. > Did you not receive it? > > *Paul* > -- --- > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John > Wesley*-- > --- > > > On Thursday, January 28, 2016 12:43 AM, SG <sona...@gmail.com> wrote: > > > > Paul, have you checked it? > > On Wednesday, January 27, 2016 at 6:45:26 PM UTC+5:30, SG wrote: > > Thanks for your quick response Paul. The line ""In case, any slot not > available, it should pick so on" means that in case any slot is not > available, it should skip it & choose the call from next slab. Your trick > is helping me very much. One more modification i need is per person > sampling. That means, per person, 4 calls/month for all 4 slabs. > Please try one more time. > > On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner > wrote: > > Here's a possible solution. > Basically, you need to determine which "set" the record falls into. > I used: > IF(AND(G2>0,G2<=TIME(0,10,0)), 1, > IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2, > IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3, > IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0 > this says that if the Duration value is >0 and <= 10 minutes, then it > belongs in set #1. > >10 and <= 20 is set 2, > >20 and <= 30 is set 3 > >30 and <= 60 is set 4 (you could make it >30, but it "looks cleaner" to > keep them all the same!) > > IF(AND(G2>0,G2<=TIME(0,10,0)), 1, > IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2, > IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3, > IF(G2>TIME(0,30,0),4,0 > doesn't look quite so "nice" in my opinion! (just a LITTLE OCD) > > Next, I wanted to use the month in determining the set, so I prefixed the > formula with: > =MONTH(F2) &"."& > > giving me: > =MONTH(F2) &"."& > IF(AND(G2&g
Re: $$Excel-Macros$$ Sampling - Please check
Paul, apologies for bothering you so much & unable to interpret the expectation. Please refer sheet 2 in attached file for results per agent. Also, I have taken duration as example of selecting sample. On Monday, February 1, 2016 at 6:37:46 PM UTC+5:30, Paul Schreiner wrote: > > I'm not sure what you are expecting, or how this does not meet your > expectations. > > For January, you have (9) entries in the 0-10 minute range. > So each of your (4) agents will get one of the samples. > > But you only have (3) entries in the 10-20 minute range. > So, the 4th agent does not receive an entry. > > The 20-30 minute range only has ONE entry, so only the first agent > receives an entry. > > in the "over 30" range, there are two entries, so only the first two > agents receive entries. > > That all seems to work. > > You said: "if less than 4, still it would come with number of calls it has" > I have no idea what that means. > does that mean that if there are not sufficient records for that time > period, you want to take an entry from another? > > If you could: > Take the records you've provided for January > and show me what you would EXPECT the (4) agents to show. > (and why?) > > > > *Paul* > - > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John Wesley* > - > > > On Monday, February 1, 2016 5:34 AM, SG <sona...@gmail.com > > wrote: > > > > Hi Paul, > > I really appreciate your help. Apologies for all the confusion. > > I have added some more data in this file. I just need if i select Jan > Month, so who ever agents has 4 calls with duration as per the slab must > come in sample & if less than 4, still it would come with number of calls > it has. It must cover all agent with number of calls they have in that > month. Honestly speaking, You have really done the efforts in this file > but my purpose is not getting solved neither i'm able to do it. :( If you > can again relook this..please check otherwise..really thanks > > Regards > SG > > > > On Friday, January 29, 2016 at 7:25:41 PM UTC+5:30, Paul Schreiner wrote: > > See below: > *Paul* > -- --- > > > On Friday, January 29, 2016 3:19 AM, SG <sona...@gmail.com> wrote: > > > > Hi Paul, I got the sheet. Thankyou so much for all your effort in this > sampling. I'm really thankful. > > Only one thing i observed is that, in raw data , there are 4 entries of > Rohit in Jan but in Random Sampling section, only two are coming as sample > for Rohit whereas it should be 4calls,per person, per month with available > slots as per the slab. PFA for you. > *I don't think that is a reasonable expectation.* > *In January, rohit may appear 4 times, but it only appears TWICE in the > 0-10 minute category, ONCE in the 10-20 minute and ONCE in the 20-30 > minute.* > > *In the sample data, there are (5) records in the 0-10 minute category for > January.* > *With only (4) people, one of the (5) records will not be selected.* > *When the data is sorted by the Rand" column, there is a "random" chance > that only one of the rohit records will be selected.* > > > Also, Rand function don't have any impact in selection of samples. Am i > right? > Please check if this can be solved as this is the condition for sampling. > *In order for the RAND() function to affect the sampling, you must sort > the data.* > *When you sort it by the Rand column, the "index" number gets recalculated > based on the "position".* > *so, each time you sort the data, you'll get a different set of selections > for the (4) people.* > > *Attached you will find a version of the file with a "sort" button.* > > > On Friday, January 29, 2016 at 11:58:04 AM UTC+5:30, SG wrote: > > No Paul, I didn't get it. Can you please share it again. > > On Thursday, January 28, 2016 at 6:36:10 PM UTC+5:30, Paul Schreiner wrote: > > Yeesss.. and responded with an update. > Did you not receive it? > > *Paul* > -- --- > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John > Wesley*-- > --- > > > On Thursday, January 28, 2016 12:43 AM,
Re: $$Excel-Macros$$ Sampling - Please check
Hi Paul, I got the sheet. Thankyou so much for all your effort in this sampling. I'm really thankful. Only one thing i observed is that, in raw data , there are 4 entries of Rohit in Jan but in Random Sampling section, only two are coming as sample for Rohit whereas it should be 4calls,per person, per month with available slots as per the slab. PFA for you. Also, Rand function don't have any impact in selection of samples. Am i right? Please check if this can be solved as this is the condition for sampling. On Friday, January 29, 2016 at 11:58:04 AM UTC+5:30, SG wrote: > > No Paul, I didn't get it. Can you please share it again. > > On Thursday, January 28, 2016 at 6:36:10 PM UTC+5:30, Paul Schreiner wrote: >> >> Yeesss.. and responded with an update. >> Did you not receive it? >> >> *Paul* >> - >> >> >> >> >> >> >> >> *“Do all the good you can,By all the means you can,In all the ways you >> can,In all the places you can,At all the times you can,To all the people >> you can,As long as ever you can.” - John Wesley* >> - >> >> >> On Thursday, January 28, 2016 12:43 AM, SG <sona...@gmail.com> wrote: >> >> >> >> Paul, have you checked it? >> >> On Wednesday, January 27, 2016 at 6:45:26 PM UTC+5:30, SG wrote: >> >> Thanks for your quick response Paul. The line ""In case, any slot not >> available, it should pick so on" means that in case any slot is not >> available, it should skip it & choose the call from next slab. Your trick >> is helping me very much. One more modification i need is per person >> sampling. That means, per person, 4 calls/month for all 4 slabs. >> Please try one more time. >> >> On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner >> wrote: >> >> Here's a possible solution. >> Basically, you need to determine which "set" the record falls into. >> I used: >> IF(AND(G2>0,G2<=TIME(0,10,0)), 1, >> IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2, >> IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3, >> IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0 >> this says that if the Duration value is >0 and <= 10 minutes, then it >> belongs in set #1. >> >10 and <= 20 is set 2, >> >20 and <= 30 is set 3 >> >30 and <= 60 is set 4 (you could make it >30, but it "looks cleaner" to >> keep them all the same!) >> >> IF(AND(G2>0,G2<=TIME(0,10,0)), 1, >> IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2, >> IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3, >> IF(G2>TIME(0,30,0),4,0 >> doesn't look quite so "nice" in my opinion! (just a LITTLE OCD) >> >> Next, I wanted to use the month in determining the set, so I prefixed the >> formula with: >> =MONTH(F2) &"."& >> >> giving me: >> =MONTH(F2) &"."& >> IF(AND(G2>0,G2<=TIME(0,10,0)), 1, >> IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2, >> IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3, >> IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0 >> >> I added two columns (A and B). >> Put this in A2 and copied it down the list. >> >> Next, in column B I used =RAND() and copied IT down the list. >> (technically, this could have been put at the end) >> >> Next, sort these records by the RAND column. >> >> Now, for the "selection", I created a table and put 1.1, 1.2, 1.3, 1.4 >> etc. in the first column. >> I then used vLookup to find the first record in the data that matches >> this "set". >> Note: since I used =month() & "." & to come up with the set names, this >> is TEXT, not a number. >> So, in the vlookup, you must make sure the "set" you're looking for is >> TEXT. >> That is: when you enter the set numbers 1.1, 1.2, 1.3, 1.4 etc. (in >> column "L" in my case) >> Excel enters them as numeric. >> so in the VLookup(), I converted them to text with TEXT(). >> Like: =VLOOKUP(TEXT($L2,"0.0"),$A:$ G,3,FALSE) >> >> Now, in your data, there were no records for February that had a duration >> of 20-30 minutes. >> So no set # 2.3 >> >> I'm not sure what you want to do in this case. >> In your explanation, you said: >> "In case, any slot not available, it should pick so on." >> >> I have no idea
Re: $$Excel-Macros$$ Sampling - Please check
No Paul, I didn't get it. Can you please share it again. On Thursday, January 28, 2016 at 6:36:10 PM UTC+5:30, Paul Schreiner wrote: > > Yeesss.. and responded with an update. > Did you not receive it? > > *Paul* > - > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John Wesley* > ----- > > > On Thursday, January 28, 2016 12:43 AM, SG <sona...@gmail.com > > wrote: > > > > Paul, have you checked it? > > On Wednesday, January 27, 2016 at 6:45:26 PM UTC+5:30, SG wrote: > > Thanks for your quick response Paul. The line ""In case, any slot not > available, it should pick so on" means that in case any slot is not > available, it should skip it & choose the call from next slab. Your trick > is helping me very much. One more modification i need is per person > sampling. That means, per person, 4 calls/month for all 4 slabs. > Please try one more time. > > On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner > wrote: > > Here's a possible solution. > Basically, you need to determine which "set" the record falls into. > I used: > IF(AND(G2>0,G2<=TIME(0,10,0)), 1, > IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2, > IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3, > IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0 > this says that if the Duration value is >0 and <= 10 minutes, then it > belongs in set #1. > >10 and <= 20 is set 2, > >20 and <= 30 is set 3 > >30 and <= 60 is set 4 (you could make it >30, but it "looks cleaner" to > keep them all the same!) > > IF(AND(G2>0,G2<=TIME(0,10,0)), 1, > IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2, > IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3, > IF(G2>TIME(0,30,0),4,0 > doesn't look quite so "nice" in my opinion! (just a LITTLE OCD) > > Next, I wanted to use the month in determining the set, so I prefixed the > formula with: > =MONTH(F2) &"."& > > giving me: > =MONTH(F2) &"."& > IF(AND(G2>0,G2<=TIME(0,10,0)), 1, > IF(AND(G2>TIME(0,10,0),G2<= TIME(0,20,0)),2, > IF(AND(G2>TIME(0,20,0),G2<= TIME(0,30,0)),3, > IF(AND(G2>TIME(0,30,0),G2<= TIME(0,60,0)),4,0 > > I added two columns (A and B). > Put this in A2 and copied it down the list. > > Next, in column B I used =RAND() and copied IT down the list. > (technically, this could have been put at the end) > > Next, sort these records by the RAND column. > > Now, for the "selection", I created a table and put 1.1, 1.2, 1.3, 1.4 > etc. in the first column. > I then used vLookup to find the first record in the data that matches this > "set". > Note: since I used =month() & "." & to come up with the set names, this is > TEXT, not a number. > So, in the vlookup, you must make sure the "set" you're looking for is > TEXT. > That is: when you enter the set numbers 1.1, 1.2, 1.3, 1.4 etc. (in column > "L" in my case) > Excel enters them as numeric. > so in the VLookup(), I converted them to text with TEXT(). > Like: =VLOOKUP(TEXT($L2,"0.0"),$A:$ G,3,FALSE) > > Now, in your data, there were no records for February that had a duration > of 20-30 minutes. > So no set # 2.3 > > I'm not sure what you want to do in this case. > In your explanation, you said: > "In case, any slot not available, it should pick so on." > > I have no idea what you mean by "it should pick so on". > > > *Paul* > -- --- > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John > Wesley*-- > --- > > > On Wednesday, January 27, 2016 7:25 AM, SG <sona...@gmail.com> wrote: > > > > > > Hi Experts > > I need your help in choosing the samples for quality check. The only idea > in my mind is the Random function but this wouldn’t suffice as there are > 2-3 conditions on basis of which sampling should be done. > It should be done for 4 calls per month per type (column A for type). > Different Duration slabs like out of 4 calls for each type, > One call with Duration of 1 to 10 minutes > One call with Du
Re: $$Excel-Macros$$ Sampling - Please check
Paul, have you checked it? On Wednesday, January 27, 2016 at 6:45:26 PM UTC+5:30, SG wrote: > > Thanks for your quick response Paul. The line ""In case, any slot not > available, it should pick so on" means that in case any slot is not > available, it should skip it & choose the call from next slab. Your trick > is helping me very much. One more modification i need is per person > sampling. That means, per person, 4 calls/month for all 4 slabs. > Please try one more time. > > On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner > wrote: >> >> Here's a possible solution. >> Basically, you need to determine which "set" the record falls into. >> I used: >> IF(AND(G2>0,G2<=TIME(0,10,0)),1, >> IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2, >> IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3, >> IF(AND(G2>TIME(0,30,0),G2<=TIME(0,60,0)),4,0 >> this says that if the Duration value is >0 and <= 10 minutes, then it >> belongs in set #1. >> >10 and <= 20 is set 2, >> >20 and <= 30 is set 3 >> >30 and <= 60 is set 4 (you could make it >30, but it "looks cleaner" to >> keep them all the same!) >> >> IF(AND(G2>0,G2<=TIME(0,10,0)),1, >> IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2, >> IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3, >> IF(G2>TIME(0,30,0),4,0 >> doesn't look quite so "nice" in my opinion! (just a LITTLE OCD) >> >> Next, I wanted to use the month in determining the set, so I prefixed the >> formula with: >> =MONTH(F2) &"."& >> >> giving me: >> =MONTH(F2) &"."& >> IF(AND(G2>0,G2<=TIME(0,10,0)),1, >> IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2, >> IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3, >> IF(AND(G2>TIME(0,30,0),G2<=TIME(0,60,0)),4,0 >> >> I added two columns (A and B). >> Put this in A2 and copied it down the list. >> >> Next, in column B I used =RAND() and copied IT down the list. >> (technically, this could have been put at the end) >> >> Next, sort these records by the RAND column. >> >> Now, for the "selection", I created a table and put 1.1, 1.2, 1.3, 1.4 >> etc. in the first column. >> I then used vLookup to find the first record in the data that matches >> this "set". >> Note: since I used =month() & "." & to come up with the set names, this >> is TEXT, not a number. >> So, in the vlookup, you must make sure the "set" you're looking for is >> TEXT. >> That is: when you enter the set numbers 1.1, 1.2, 1.3, 1.4 etc. (in >> column "L" in my case) >> Excel enters them as numeric. >> so in the VLookup(), I converted them to text with TEXT(). >> Like: =VLOOKUP(TEXT($L2,"0.0"),$A:$G,3,FALSE) >> >> Now, in your data, there were no records for February that had a duration >> of 20-30 minutes. >> So no set # 2.3 >> >> I'm not sure what you want to do in this case. >> In your explanation, you said: >> "In case, any slot not available, it should pick so on." >> >> I have no idea what you mean by "it should pick so on". >> >> >> *Paul* >> - >> >> >> >> >> >> >> >> *“Do all the good you can,By all the means you can,In all the ways you >> can,In all the places you can,At all the times you can,To all the people >> you can,As long as ever you can.” - John Wesley* >> - >> >> >> On Wednesday, January 27, 2016 7:25 AM, SG <sona...@gmail.com> wrote: >> >> >> >> >> >> Hi Experts >> >> I need your help in choosing the samples for quality check. The only idea >> in my mind is the Random function but this wouldn’t suffice as there are >> 2-3 conditions on basis of which sampling should be done. >> It should be done for 4 calls per month per type (column A for type). >> Different Duration slabs like out of 4 calls for each type, >> One call with Duration of 1 to 10 minutes >> One call with Duration of 10 to 20 minutes >> One call with Duration of 20 to 30 minutes >> One call with Duration of 30 to 40 minutes >> In case, any slot not available, it should pick so on. >> I really know it’s very tricky but need your help. PFA data for your >> reference. >> Thanks in advance >> -- &
$$Excel-Macros$$ Sampling - Please check
Hi Experts I need your help in choosing the samples for quality check. The only idea in my mind is the Random function but this wouldn’t suffice as there are 2-3 conditions on basis of which sampling should be done. It should be done for 4 calls per month per type (column A for type). Different Duration slabs like out of 4 calls for each type, One call with Duration of 1 to 10 minutes One call with Duration of 10 to 20 minutes One call with Duration of 20 to 30 minutes One call with Duration of 30 to 40 minutes In case, any slot not available, it should pick so on. I really know it’s very tricky but need your help. PFA data for your reference. Thanks in advance -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. sample.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ Sampling - Please check
Thanks for your quick response Paul. The line ""In case, any slot not available, it should pick so on" means that in case any slot is not available, it should skip it & choose the call from next slab. Your trick is helping me very much. One more modification i need is per person sampling. That means, per person, 4 calls/month for all 4 slabs. Please try one more time. On Wednesday, January 27, 2016 at 6:33:38 PM UTC+5:30, Paul Schreiner wrote: > > Here's a possible solution. > Basically, you need to determine which "set" the record falls into. > I used: > IF(AND(G2>0,G2<=TIME(0,10,0)),1, > IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2, > IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3, > IF(AND(G2>TIME(0,30,0),G2<=TIME(0,60,0)),4,0 > this says that if the Duration value is >0 and <= 10 minutes, then it > belongs in set #1. > >10 and <= 20 is set 2, > >20 and <= 30 is set 3 > >30 and <= 60 is set 4 (you could make it >30, but it "looks cleaner" to > keep them all the same!) > > IF(AND(G2>0,G2<=TIME(0,10,0)),1, > IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2, > IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3, > IF(G2>TIME(0,30,0),4,0 > doesn't look quite so "nice" in my opinion! (just a LITTLE OCD) > > Next, I wanted to use the month in determining the set, so I prefixed the > formula with: > =MONTH(F2) &"."& > > giving me: > =MONTH(F2) &"."& > IF(AND(G2>0,G2<=TIME(0,10,0)),1, > IF(AND(G2>TIME(0,10,0),G2<=TIME(0,20,0)),2, > IF(AND(G2>TIME(0,20,0),G2<=TIME(0,30,0)),3, > IF(AND(G2>TIME(0,30,0),G2<=TIME(0,60,0)),4,0 > > I added two columns (A and B). > Put this in A2 and copied it down the list. > > Next, in column B I used =RAND() and copied IT down the list. > (technically, this could have been put at the end) > > Next, sort these records by the RAND column. > > Now, for the "selection", I created a table and put 1.1, 1.2, 1.3, 1.4 > etc. in the first column. > I then used vLookup to find the first record in the data that matches this > "set". > Note: since I used =month() & "." & to come up with the set names, this is > TEXT, not a number. > So, in the vlookup, you must make sure the "set" you're looking for is > TEXT. > That is: when you enter the set numbers 1.1, 1.2, 1.3, 1.4 etc. (in column > "L" in my case) > Excel enters them as numeric. > so in the VLookup(), I converted them to text with TEXT(). > Like: =VLOOKUP(TEXT($L2,"0.0"),$A:$G,3,FALSE) > > Now, in your data, there were no records for February that had a duration > of 20-30 minutes. > So no set # 2.3 > > I'm not sure what you want to do in this case. > In your explanation, you said: > "In case, any slot not available, it should pick so on." > > I have no idea what you mean by "it should pick so on". > > > *Paul* > - > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John Wesley* > - > > > On Wednesday, January 27, 2016 7:25 AM, SG <sona...@gmail.com > > wrote: > > > > > > Hi Experts > > I need your help in choosing the samples for quality check. The only idea > in my mind is the Random function but this wouldn’t suffice as there are > 2-3 conditions on basis of which sampling should be done. > It should be done for 4 calls per month per type (column A for type). > Different Duration slabs like out of 4 calls for each type, > One call with Duration of 1 to 10 minutes > One call with Duration of 10 to 20 minutes > One call with Duration of 20 to 30 minutes > One call with Duration of 30 to 40 minutes > In case, any slot not available, it should pick so on. > I really know it’s very tricky but need your help. PFA data for your > reference. > Thanks in advance > -- > 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 regardi
$$Excel-Macros$$ Re: Need Help in calculation of score
Hi Mandeep, Sorry for the confusion. Final score is 100 if all are Yes. I need the formula in Colum "I" for Score.(Distribution of Score in case of "NA" should be calculated here.) Data in K:P are the values for "Yes" & "No" of data in B:H. PFA updated sheet. The difference in headers are typo errors. Regards, SG On Thursday, January 7, 2016 at 1:11:40 PM UTC+5:30, Mandeep Baluja wrote: > > Hi SG, > > Things I would like to know !!! > 1) What is required You need formula in column "I" or You need to > distribute data in "K3:P5" > 2) Why headers(%) B:H and k:P are different? are they being used in > formulas for calculation. > 3) What is final Score ? > > Can we have the result set below your question with result required > manually for better understanding. > > Regards, > Mandeep baluja > > > > > > -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Book1.xlsx Description: MS-Excel 2007 spreadsheet
$$Excel-Macros$$ Re: Need Help in calculation of score
Hi Mandeep, Please refer to the comment in attached sheet. I hope it would get clear now. :) On Thursday, January 7, 2016 at 2:06:11 PM UTC+5:30, Mandeep Baluja wrote: > > Hey SG, > > Correct me if I am wrong !! > 1)If Parameter M(Fatal) is No, then total score would be 0 Formula should > be =IF(H3="No",0 > > 2)If any parameter is "No", then its score should get deducted from final > score formula should be IF(COUNTIF($B$3:$H$3,"No")>0,100-SUM(K3:P3) > Now on combining these two formulas > is =IF(H3="No",0,IF(COUNTIF($B$3:$H$3,"No")>0,100-SUM(K3:P3) > > Confusion !! > 3) if any of parameter is NA then its score must be distributed in rest > of the parameters.(Not to M(Fatal) parameter) > *Score must be distributed in rest of parameters ??? Does it mean > formula should do distribution on which cells ? because k3:p5 is already > filled with your formula.Can you put the output manually below your data.* > > *Regards,* > *Mandeep baluja * > > > -- 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 https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Book1 - Copy.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ Create user wise feedback sheet
Vabz sir, thanku so much really you helped me alot...Hats off:) On Tuesday, August 26, 2014 11:46:12 PM UTC+5:30, Vabz wrote: place all sheets to be updated in one folder, run macro select that folder, data will get consolidated in this book. Cheers!! On Tue, Aug 26, 2014 at 4:49 PM, SG sona...@gmail.com javascript: wrote: gr88...then will you please help me on this. On Tuesday, August 26, 2014 4:27:32 PM UTC+5:30, Vabz wrote: yes On Tue, Aug 26, 2014 at 3:36 PM, SG sona...@gmail.com wrote: yes sir..absolutely right.. is it feasible na? On Tuesday, August 26, 2014 2:59:02 PM UTC+5:30, Vabz wrote: Ok. Do you want to first merge 4 weekly scorecard in to 1 and then ctrate employee wise feedback sheet. 2nd part is ready now you want macro for first part. Am I correct. On Aug 26, 2014 2:38 PM, SG sona...@gmail.com wrote: Vabz, This would be used for monthly feedback. For monthly, i have to consolidate those 4 scorecard files which are prepared on weekly baisi then i prepare the feedback for each. This requires the consolidation of scores of those 4 files. I hope i'm able to clear all doubts. On Tuesday, August 26, 2014 1:51:29 PM UTC+5:30, Vabz wrote: How do you want data in feed back sheet, do you want to add for 2 period or in separate sheet? On Tue, Aug 26, 2014 at 1:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Any update on this. Can it be consolidated in any other way. please,can anybody else also look in to this. On Monday, August 25, 2014 7:50:13 PM UTC+5:30, SG wrote: some of the criterias are to consolidate the scores of users for region UK location b. The header yellow highlighted column(formulas) wouldn't change in consolidate scorecard. sheet. It would sum the values of column F,G ,(J,K,L) (N,O,P) from scorecard files. Also for column R - TAT , if all are hit, then it would be hit but it would be miss even if one of them is miss. I have attached two scorecards one consolidate file in which summary would come but this would gonna work for atleast 4 scorecard files which would kept in a folder. On Monday, August 25, 2014 7:25:49 PM UTC+5:30, Vabz wrote: Hi File which you shared contains data for one week only. Cheers!! On Mon, Aug 25, 2014 at 6:49 PM, SG sona...@gmail.com wrote: Hi Vabzzz.. I need one more help in this. As you have seen the scorecard file, that should be summary of four weeks thehn the feedback would be prepared. Can you please also help me in consolidation of those four files in to one file. It would be a great help. On Monday, August 25, 2014 3:37:52 PM UTC+5:30, SG wrote: Thanku so much vabz for clarification... On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote: Old module is for creating files in wrokbook insted in feedback sheet. I have also created Macro for updating only 1 sheet, wherein you needed to keep open scorecard feedback book. After that doble click on name of person whose record you wanted to update, post double clicking on name a userform will appear in which you will be asked to select excel book in which employee sheet is situated post selecting wb name (feedback sheet in your case) data will get updated. Cheers!! On Aug 21, 2014 5:24 PM, SG sona...@gmail.com wrote: Vabz sirhats offthnku so much...really gr88 now i have some questions so that i can clear my doubt. what you have created a code name create old feedback what is the use of Option Explicit Public MyFile As String Public Stopped As Boolean On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote: FA Cheers!! On Thu, Aug 21, 2014 at 1:43 PM, SG sona...@gmail.com wrote: Hi Vabz Great.. :) Thanku so much..please only one change is needed in total external errors internal errors(row 9 10 of feedback sheet), the sum of errors should come not the score. Please can you change it. Thanks alot ... On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote: hi dont use double click, it is for only single sheet update. You have to just run a macro, check this file, click blue button.. Make sure both files are open. Cheers!! On Wed, Aug 20, 2014 at 8:20 PM, SG sona...@gmail.com wrote: Hi Vabz, i'm really not getting it. how to work. on double click on name, it asks please select one of file. How hsould i start how would it create userwise seperate sheets. Please help out. On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote: ok, chek this!! Cheers!! On Wed, Aug 20, 2014 at 6:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Thanks so much for the help but it's not solving the purpose. User wise seperate sheets are not made in feedback workbook also the values of errors for rahul are incorrect. Can you please check it. Also i didn't get the logic behind clicking the name (col d) part. On Wednesday, August 20, 2014 2
Re: $$Excel-Macros$$ Create user wise feedback sheet
Hi Vabz, Any update on this. Can it be consolidated in any other way. please,can anybody else also look in to this. On Monday, August 25, 2014 7:50:13 PM UTC+5:30, SG wrote: some of the criterias are to consolidate the scores of users for region UK location b. The header yellow highlighted column(formulas) wouldn't change in consolidate scorecard. sheet. It would sum the values of column F,G ,(J,K,L) (N,O,P) from scorecard files. Also for column R - TAT , if all are hit, then it would be hit but it would be miss even if one of them is miss. I have attached two scorecards one consolidate file in which summary would come but this would gonna work for atleast 4 scorecard files which would kept in a folder. On Monday, August 25, 2014 7:25:49 PM UTC+5:30, Vabz wrote: Hi File which you shared contains data for one week only. Cheers!! On Mon, Aug 25, 2014 at 6:49 PM, SG sona...@gmail.com wrote: Hi Vabzzz.. I need one more help in this. As you have seen the scorecard file, that should be summary of four weeks thehn the feedback would be prepared. Can you please also help me in consolidation of those four files in to one file. It would be a great help. On Monday, August 25, 2014 3:37:52 PM UTC+5:30, SG wrote: Thanku so much vabz for clarification... On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote: Old module is for creating files in wrokbook insted in feedback sheet. I have also created Macro for updating only 1 sheet, wherein you needed to keep open scorecard feedback book. After that doble click on name of person whose record you wanted to update, post double clicking on name a userform will appear in which you will be asked to select excel book in which employee sheet is situated post selecting wb name (feedback sheet in your case) data will get updated. Cheers!! On Aug 21, 2014 5:24 PM, SG sona...@gmail.com wrote: Vabz sirhats offthnku so much...really gr88 now i have some questions so that i can clear my doubt. what you have created a code name create old feedback what is the use of Option Explicit Public MyFile As String Public Stopped As Boolean On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote: FA Cheers!! On Thu, Aug 21, 2014 at 1:43 PM, SG sona...@gmail.com wrote: Hi Vabz Great.. :) Thanku so much..please only one change is needed in total external errors internal errors(row 9 10 of feedback sheet), the sum of errors should come not the score. Please can you change it. Thanks alot ... On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote: hi dont use double click, it is for only single sheet update. You have to just run a macro, check this file, click blue button.. Make sure both files are open. Cheers!! On Wed, Aug 20, 2014 at 8:20 PM, SG sona...@gmail.com wrote: Hi Vabz, i'm really not getting it. how to work. on double click on name, it asks please select one of file. How hsould i start how would it create userwise seperate sheets. Please help out. On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote: ok, chek this!! Cheers!! On Wed, Aug 20, 2014 at 6:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Thanks so much for the help but it's not solving the purpose. User wise seperate sheets are not made in feedback workbook also the values of errors for rahul are incorrect. Can you please check it. Also i didn't get the logic behind clicking the name (col d) part. On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote: FA macro. Also if you want to update single sheet then keep both feedback sheet scorecard sheet open together then in scorecard book double click on name (col D) and u will get prompted to select sheet to update with existing values. Cheers!! On Tue, Aug 19, 2014 at 7:39 PM, SG sona...@gmail.com wrote: Hi Experts, I need your help in preparation of a feedback sheet through macro. My requirement is to create name wise feedback worksheet in a workbook which contains their data from scorecard sheet. one of the criteria is to create the name wise sheet for only region uk location b users. I have attached feedback sheet in which Column C would remain fixed for all user sheet also mentioned the columns names of Scorecard sheet from which data would get extracted. Scorecard sheet is also attached. I'm really confused how to begin with it. Please help me in preparing it. Thanks in advance. -- 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
Re: $$Excel-Macros$$ Create user wise feedback sheet
Vabz, This would be used for monthly feedback. For monthly, i have to consolidate those 4 scorecard files which are prepared on weekly baisi then i prepare the feedback for each. This requires the consolidation of scores of those 4 files. I hope i'm able to clear all doubts. On Tuesday, August 26, 2014 1:51:29 PM UTC+5:30, Vabz wrote: How do you want data in feed back sheet, do you want to add for 2 period or in separate sheet? On Tue, Aug 26, 2014 at 1:11 PM, SG sona...@gmail.com javascript: wrote: Hi Vabz, Any update on this. Can it be consolidated in any other way. please,can anybody else also look in to this. On Monday, August 25, 2014 7:50:13 PM UTC+5:30, SG wrote: some of the criterias are to consolidate the scores of users for region UK location b. The header yellow highlighted column(formulas) wouldn't change in consolidate scorecard. sheet. It would sum the values of column F,G ,(J,K,L) (N,O,P) from scorecard files. Also for column R - TAT , if all are hit, then it would be hit but it would be miss even if one of them is miss. I have attached two scorecards one consolidate file in which summary would come but this would gonna work for atleast 4 scorecard files which would kept in a folder. On Monday, August 25, 2014 7:25:49 PM UTC+5:30, Vabz wrote: Hi File which you shared contains data for one week only. Cheers!! On Mon, Aug 25, 2014 at 6:49 PM, SG sona...@gmail.com wrote: Hi Vabzzz.. I need one more help in this. As you have seen the scorecard file, that should be summary of four weeks thehn the feedback would be prepared. Can you please also help me in consolidation of those four files in to one file. It would be a great help. On Monday, August 25, 2014 3:37:52 PM UTC+5:30, SG wrote: Thanku so much vabz for clarification... On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote: Old module is for creating files in wrokbook insted in feedback sheet. I have also created Macro for updating only 1 sheet, wherein you needed to keep open scorecard feedback book. After that doble click on name of person whose record you wanted to update, post double clicking on name a userform will appear in which you will be asked to select excel book in which employee sheet is situated post selecting wb name (feedback sheet in your case) data will get updated. Cheers!! On Aug 21, 2014 5:24 PM, SG sona...@gmail.com wrote: Vabz sirhats offthnku so much...really gr88 now i have some questions so that i can clear my doubt. what you have created a code name create old feedback what is the use of Option Explicit Public MyFile As String Public Stopped As Boolean On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote: FA Cheers!! On Thu, Aug 21, 2014 at 1:43 PM, SG sona...@gmail.com wrote: Hi Vabz Great.. :) Thanku so much..please only one change is needed in total external errors internal errors(row 9 10 of feedback sheet), the sum of errors should come not the score. Please can you change it. Thanks alot ... On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote: hi dont use double click, it is for only single sheet update. You have to just run a macro, check this file, click blue button.. Make sure both files are open. Cheers!! On Wed, Aug 20, 2014 at 8:20 PM, SG sona...@gmail.com wrote: Hi Vabz, i'm really not getting it. how to work. on double click on name, it asks please select one of file. How hsould i start how would it create userwise seperate sheets. Please help out. On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote: ok, chek this!! Cheers!! On Wed, Aug 20, 2014 at 6:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Thanks so much for the help but it's not solving the purpose. User wise seperate sheets are not made in feedback workbook also the values of errors for rahul are incorrect. Can you please check it. Also i didn't get the logic behind clicking the name (col d) part. On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote: FA macro. Also if you want to update single sheet then keep both feedback sheet scorecard sheet open together then in scorecard book double click on name (col D) and u will get prompted to select sheet to update with existing values. Cheers!! On Tue, Aug 19, 2014 at 7:39 PM, SG sona...@gmail.com wrote: Hi Experts, I need your help in preparation of a feedback sheet through macro. My requirement is to create name wise feedback worksheet in a workbook which contains their data from scorecard sheet. one of the criteria is to create the name wise sheet for only region uk location b users. I have attached feedback sheet in which Column C would remain fixed for all user sheet also mentioned the columns names of Scorecard sheet from which data would get extracted. Scorecard sheet is also attached
Re: $$Excel-Macros$$ Create user wise feedback sheet
yes sir..absolutely right.. is it feasible na? On Tuesday, August 26, 2014 2:59:02 PM UTC+5:30, Vabz wrote: Ok. Do you want to first merge 4 weekly scorecard in to 1 and then ctrate employee wise feedback sheet. 2nd part is ready now you want macro for first part. Am I correct. On Aug 26, 2014 2:38 PM, SG sona...@gmail.com javascript: wrote: Vabz, This would be used for monthly feedback. For monthly, i have to consolidate those 4 scorecard files which are prepared on weekly baisi then i prepare the feedback for each. This requires the consolidation of scores of those 4 files. I hope i'm able to clear all doubts. On Tuesday, August 26, 2014 1:51:29 PM UTC+5:30, Vabz wrote: How do you want data in feed back sheet, do you want to add for 2 period or in separate sheet? On Tue, Aug 26, 2014 at 1:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Any update on this. Can it be consolidated in any other way. please,can anybody else also look in to this. On Monday, August 25, 2014 7:50:13 PM UTC+5:30, SG wrote: some of the criterias are to consolidate the scores of users for region UK location b. The header yellow highlighted column(formulas) wouldn't change in consolidate scorecard. sheet. It would sum the values of column F,G ,(J,K,L) (N,O,P) from scorecard files. Also for column R - TAT , if all are hit, then it would be hit but it would be miss even if one of them is miss. I have attached two scorecards one consolidate file in which summary would come but this would gonna work for atleast 4 scorecard files which would kept in a folder. On Monday, August 25, 2014 7:25:49 PM UTC+5:30, Vabz wrote: Hi File which you shared contains data for one week only. Cheers!! On Mon, Aug 25, 2014 at 6:49 PM, SG sona...@gmail.com wrote: Hi Vabzzz.. I need one more help in this. As you have seen the scorecard file, that should be summary of four weeks thehn the feedback would be prepared. Can you please also help me in consolidation of those four files in to one file. It would be a great help. On Monday, August 25, 2014 3:37:52 PM UTC+5:30, SG wrote: Thanku so much vabz for clarification... On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote: Old module is for creating files in wrokbook insted in feedback sheet. I have also created Macro for updating only 1 sheet, wherein you needed to keep open scorecard feedback book. After that doble click on name of person whose record you wanted to update, post double clicking on name a userform will appear in which you will be asked to select excel book in which employee sheet is situated post selecting wb name (feedback sheet in your case) data will get updated. Cheers!! On Aug 21, 2014 5:24 PM, SG sona...@gmail.com wrote: Vabz sirhats offthnku so much...really gr88 now i have some questions so that i can clear my doubt. what you have created a code name create old feedback what is the use of Option Explicit Public MyFile As String Public Stopped As Boolean On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote: FA Cheers!! On Thu, Aug 21, 2014 at 1:43 PM, SG sona...@gmail.com wrote: Hi Vabz Great.. :) Thanku so much..please only one change is needed in total external errors internal errors(row 9 10 of feedback sheet), the sum of errors should come not the score. Please can you change it. Thanks alot ... On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote: hi dont use double click, it is for only single sheet update. You have to just run a macro, check this file, click blue button.. Make sure both files are open. Cheers!! On Wed, Aug 20, 2014 at 8:20 PM, SG sona...@gmail.com wrote: Hi Vabz, i'm really not getting it. how to work. on double click on name, it asks please select one of file. How hsould i start how would it create userwise seperate sheets. Please help out. On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote: ok, chek this!! Cheers!! On Wed, Aug 20, 2014 at 6:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Thanks so much for the help but it's not solving the purpose. User wise seperate sheets are not made in feedback workbook also the values of errors for rahul are incorrect. Can you please check it. Also i didn't get the logic behind clicking the name (col d) part. On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote: FA macro. Also if you want to update single sheet then keep both feedback sheet scorecard sheet open together then in scorecard book double click on name (col D) and u will get prompted to select sheet to update with existing values. Cheers!! On Tue, Aug 19, 2014 at 7:39 PM, SG sona...@gmail.com wrote: Hi Experts, I need your help in preparation of a feedback sheet through macro. My requirement is to create name wise feedback worksheet in a workbook which
Re: $$Excel-Macros$$ Create user wise feedback sheet
gr88...then will you please help me on this. On Tuesday, August 26, 2014 4:27:32 PM UTC+5:30, Vabz wrote: yes On Tue, Aug 26, 2014 at 3:36 PM, SG sona...@gmail.com javascript: wrote: yes sir..absolutely right.. is it feasible na? On Tuesday, August 26, 2014 2:59:02 PM UTC+5:30, Vabz wrote: Ok. Do you want to first merge 4 weekly scorecard in to 1 and then ctrate employee wise feedback sheet. 2nd part is ready now you want macro for first part. Am I correct. On Aug 26, 2014 2:38 PM, SG sona...@gmail.com wrote: Vabz, This would be used for monthly feedback. For monthly, i have to consolidate those 4 scorecard files which are prepared on weekly baisi then i prepare the feedback for each. This requires the consolidation of scores of those 4 files. I hope i'm able to clear all doubts. On Tuesday, August 26, 2014 1:51:29 PM UTC+5:30, Vabz wrote: How do you want data in feed back sheet, do you want to add for 2 period or in separate sheet? On Tue, Aug 26, 2014 at 1:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Any update on this. Can it be consolidated in any other way. please,can anybody else also look in to this. On Monday, August 25, 2014 7:50:13 PM UTC+5:30, SG wrote: some of the criterias are to consolidate the scores of users for region UK location b. The header yellow highlighted column(formulas) wouldn't change in consolidate scorecard. sheet. It would sum the values of column F,G ,(J,K,L) (N,O,P) from scorecard files. Also for column R - TAT , if all are hit, then it would be hit but it would be miss even if one of them is miss. I have attached two scorecards one consolidate file in which summary would come but this would gonna work for atleast 4 scorecard files which would kept in a folder. On Monday, August 25, 2014 7:25:49 PM UTC+5:30, Vabz wrote: Hi File which you shared contains data for one week only. Cheers!! On Mon, Aug 25, 2014 at 6:49 PM, SG sona...@gmail.com wrote: Hi Vabzzz.. I need one more help in this. As you have seen the scorecard file, that should be summary of four weeks thehn the feedback would be prepared. Can you please also help me in consolidation of those four files in to one file. It would be a great help. On Monday, August 25, 2014 3:37:52 PM UTC+5:30, SG wrote: Thanku so much vabz for clarification... On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote: Old module is for creating files in wrokbook insted in feedback sheet. I have also created Macro for updating only 1 sheet, wherein you needed to keep open scorecard feedback book. After that doble click on name of person whose record you wanted to update, post double clicking on name a userform will appear in which you will be asked to select excel book in which employee sheet is situated post selecting wb name (feedback sheet in your case) data will get updated. Cheers!! On Aug 21, 2014 5:24 PM, SG sona...@gmail.com wrote: Vabz sirhats offthnku so much...really gr88 now i have some questions so that i can clear my doubt. what you have created a code name create old feedback what is the use of Option Explicit Public MyFile As String Public Stopped As Boolean On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote: FA Cheers!! On Thu, Aug 21, 2014 at 1:43 PM, SG sona...@gmail.com wrote: Hi Vabz Great.. :) Thanku so much..please only one change is needed in total external errors internal errors(row 9 10 of feedback sheet), the sum of errors should come not the score. Please can you change it. Thanks alot ... On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote: hi dont use double click, it is for only single sheet update. You have to just run a macro, check this file, click blue button.. Make sure both files are open. Cheers!! On Wed, Aug 20, 2014 at 8:20 PM, SG sona...@gmail.com wrote: Hi Vabz, i'm really not getting it. how to work. on double click on name, it asks please select one of file. How hsould i start how would it create userwise seperate sheets. Please help out. On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote: ok, chek this!! Cheers!! On Wed, Aug 20, 2014 at 6:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Thanks so much for the help but it's not solving the purpose. User wise seperate sheets are not made in feedback workbook also the values of errors for rahul are incorrect. Can you please check it. Also i didn't get the logic behind clicking the name (col d) part. On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote: FA macro. Also if you want to update single sheet then keep both feedback sheet scorecard sheet open together then in scorecard book double click on name (col D) and u will get prompted to select sheet to update with existing values. Cheers!! On Tue, Aug 19, 2014 at 7:39 PM, SG sona
Re: $$Excel-Macros$$ Create user wise feedback sheet
Thanku so much vabz for clarification... On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote: Old module is for creating files in wrokbook insted in feedback sheet. I have also created Macro for updating only 1 sheet, wherein you needed to keep open scorecard feedback book. After that doble click on name of person whose record you wanted to update, post double clicking on name a userform will appear in which you will be asked to select excel book in which employee sheet is situated post selecting wb name (feedback sheet in your case) data will get updated. Cheers!! On Aug 21, 2014 5:24 PM, SG sona...@gmail.com javascript: wrote: Vabz sirhats offthnku so much...really gr88 now i have some questions so that i can clear my doubt. what you have created a code name create old feedback what is the use of Option Explicit Public MyFile As String Public Stopped As Boolean On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote: FA Cheers!! On Thu, Aug 21, 2014 at 1:43 PM, SG sona...@gmail.com wrote: Hi Vabz Great.. :) Thanku so much..please only one change is needed in total external errors internal errors(row 9 10 of feedback sheet), the sum of errors should come not the score. Please can you change it. Thanks alot ... On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote: hi dont use double click, it is for only single sheet update. You have to just run a macro, check this file, click blue button.. Make sure both files are open. Cheers!! On Wed, Aug 20, 2014 at 8:20 PM, SG sona...@gmail.com wrote: Hi Vabz, i'm really not getting it. how to work. on double click on name, it asks please select one of file. How hsould i start how would it create userwise seperate sheets. Please help out. On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote: ok, chek this!! Cheers!! On Wed, Aug 20, 2014 at 6:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Thanks so much for the help but it's not solving the purpose. User wise seperate sheets are not made in feedback workbook also the values of errors for rahul are incorrect. Can you please check it. Also i didn't get the logic behind clicking the name (col d) part. On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote: FA macro. Also if you want to update single sheet then keep both feedback sheet scorecard sheet open together then in scorecard book double click on name (col D) and u will get prompted to select sheet to update with existing values. Cheers!! On Tue, Aug 19, 2014 at 7:39 PM, SG sona...@gmail.com wrote: Hi Experts, I need your help in preparation of a feedback sheet through macro. My requirement is to create name wise feedback worksheet in a workbook which contains their data from scorecard sheet. one of the criteria is to create the name wise sheet for only region uk location b users. I have attached feedback sheet in which Column C would remain fixed for all user sheet also mentioned the columns names of Scorecard sheet from which data would get extracted. Scorecard sheet is also attached. I'm really confused how to begin with it. Please help me in preparing it. Thanks in advance. -- 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
Re: $$Excel-Macros$$ Create user wise feedback sheet
Hi Vabzzz.. I need one more help in this. As you have seen the scorecard file, that should be summary of four weeks thehn the feedback would be prepared. Can you please also help me in consolidation of those four files in to one file. It would be a great help. On Monday, August 25, 2014 3:37:52 PM UTC+5:30, SG wrote: Thanku so much vabz for clarification... On Friday, August 22, 2014 12:11:04 PM UTC+5:30, Vabz wrote: Old module is for creating files in wrokbook insted in feedback sheet. I have also created Macro for updating only 1 sheet, wherein you needed to keep open scorecard feedback book. After that doble click on name of person whose record you wanted to update, post double clicking on name a userform will appear in which you will be asked to select excel book in which employee sheet is situated post selecting wb name (feedback sheet in your case) data will get updated. Cheers!! On Aug 21, 2014 5:24 PM, SG sona...@gmail.com wrote: Vabz sirhats offthnku so much...really gr88 now i have some questions so that i can clear my doubt. what you have created a code name create old feedback what is the use of Option Explicit Public MyFile As String Public Stopped As Boolean On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote: FA Cheers!! On Thu, Aug 21, 2014 at 1:43 PM, SG sona...@gmail.com wrote: Hi Vabz Great.. :) Thanku so much..please only one change is needed in total external errors internal errors(row 9 10 of feedback sheet), the sum of errors should come not the score. Please can you change it. Thanks alot ... On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote: hi dont use double click, it is for only single sheet update. You have to just run a macro, check this file, click blue button.. Make sure both files are open. Cheers!! On Wed, Aug 20, 2014 at 8:20 PM, SG sona...@gmail.com wrote: Hi Vabz, i'm really not getting it. how to work. on double click on name, it asks please select one of file. How hsould i start how would it create userwise seperate sheets. Please help out. On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote: ok, chek this!! Cheers!! On Wed, Aug 20, 2014 at 6:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Thanks so much for the help but it's not solving the purpose. User wise seperate sheets are not made in feedback workbook also the values of errors for rahul are incorrect. Can you please check it. Also i didn't get the logic behind clicking the name (col d) part. On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote: FA macro. Also if you want to update single sheet then keep both feedback sheet scorecard sheet open together then in scorecard book double click on name (col D) and u will get prompted to select sheet to update with existing values. Cheers!! On Tue, Aug 19, 2014 at 7:39 PM, SG sona...@gmail.com wrote: Hi Experts, I need your help in preparation of a feedback sheet through macro. My requirement is to create name wise feedback worksheet in a workbook which contains their data from scorecard sheet. one of the criteria is to create the name wise sheet for only region uk location b users. I have attached feedback sheet in which Column C would remain fixed for all user sheet also mentioned the columns names of Scorecard sheet from which data would get extracted. Scorecard sheet is also attached. I'm really confused how to begin with it. Please help me in preparing it. Thanks in advance. -- 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
Re: $$Excel-Macros$$ Create user wise feedback sheet
Hi Vabz Great.. :) Thanku so much..please only one change is needed in total external errors internal errors(row 9 10 of feedback sheet), the sum of errors should come not the score. Please can you change it. Thanks alot ... On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote: hi dont use double click, it is for only single sheet update. You have to just run a macro, check this file, click blue button.. Make sure both files are open. Cheers!! On Wed, Aug 20, 2014 at 8:20 PM, SG sona...@gmail.com javascript: wrote: Hi Vabz, i'm really not getting it. how to work. on double click on name, it asks please select one of file. How hsould i start how would it create userwise seperate sheets. Please help out. On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote: ok, chek this!! Cheers!! On Wed, Aug 20, 2014 at 6:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Thanks so much for the help but it's not solving the purpose. User wise seperate sheets are not made in feedback workbook also the values of errors for rahul are incorrect. Can you please check it. Also i didn't get the logic behind clicking the name (col d) part. On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote: FA macro. Also if you want to update single sheet then keep both feedback sheet scorecard sheet open together then in scorecard book double click on name (col D) and u will get prompted to select sheet to update with existing values. Cheers!! On Tue, Aug 19, 2014 at 7:39 PM, SG sona...@gmail.com wrote: Hi Experts, I need your help in preparation of a feedback sheet through macro. My requirement is to create name wise feedback worksheet in a workbook which contains their data from scorecard sheet. one of the criteria is to create the name wise sheet for only region uk location b users. I have attached feedback sheet in which Column C would remain fixed for all user sheet also mentioned the columns names of Scorecard sheet from which data would get extracted. Scorecard sheet is also attached. I'm really confused how to begin with it. Please help me in preparing it. Thanks in advance. -- 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. 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
Re: $$Excel-Macros$$ Create user wise feedback sheet
Vabz sirhats offthnku so much...really gr88 now i have some questions so that i can clear my doubt. what you have created a code name create old feedback what is the use of Option Explicit Public MyFile As String Public Stopped As Boolean On Thursday, August 21, 2014 2:53:50 PM UTC+5:30, Vabz wrote: FA Cheers!! On Thu, Aug 21, 2014 at 1:43 PM, SG sona...@gmail.com javascript: wrote: Hi Vabz Great.. :) Thanku so much..please only one change is needed in total external errors internal errors(row 9 10 of feedback sheet), the sum of errors should come not the score. Please can you change it. Thanks alot ... On Wednesday, August 20, 2014 8:30:40 PM UTC+5:30, Vabz wrote: hi dont use double click, it is for only single sheet update. You have to just run a macro, check this file, click blue button.. Make sure both files are open. Cheers!! On Wed, Aug 20, 2014 at 8:20 PM, SG sona...@gmail.com wrote: Hi Vabz, i'm really not getting it. how to work. on double click on name, it asks please select one of file. How hsould i start how would it create userwise seperate sheets. Please help out. On Wednesday, August 20, 2014 8:04:44 PM UTC+5:30, Vabz wrote: ok, chek this!! Cheers!! On Wed, Aug 20, 2014 at 6:11 PM, SG sona...@gmail.com wrote: Hi Vabz, Thanks so much for the help but it's not solving the purpose. User wise seperate sheets are not made in feedback workbook also the values of errors for rahul are incorrect. Can you please check it. Also i didn't get the logic behind clicking the name (col d) part. On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote: FA macro. Also if you want to update single sheet then keep both feedback sheet scorecard sheet open together then in scorecard book double click on name (col D) and u will get prompted to select sheet to update with existing values. Cheers!! On Tue, Aug 19, 2014 at 7:39 PM, SG sona...@gmail.com wrote: Hi Experts, I need your help in preparation of a feedback sheet through macro. My requirement is to create name wise feedback worksheet in a workbook which contains their data from scorecard sheet. one of the criteria is to create the name wise sheet for only region uk location b users. I have attached feedback sheet in which Column C would remain fixed for all user sheet also mentioned the columns names of Scorecard sheet from which data would get extracted. Scorecard sheet is also attached. I'm really confused how to begin with it. Please help me in preparing it. Thanks in advance. -- 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. To post to this group, send email to excel
Re: $$Excel-Macros$$ Create user wise feedback sheet
Hi Vabz, Through macro because this would be prepared monthly. for this, it would open 4 scorecard files of a month,consolidate their scores then prepare the feedback. On Wednesday, August 20, 2014 1:07:07 PM UTC+5:30, Vabz wrote: hi how do you want to achieve result thru formula or macro?? Cheers!! On Tue, Aug 19, 2014 at 7:39 PM, SG sona...@gmail.com javascript: wrote: Hi Experts, I need your help in preparation of a feedback sheet through macro. My requirement is to create name wise feedback worksheet in a workbook which contains their data from scorecard sheet. one of the criteria is to create the name wise sheet for only region uk location b users. I have attached feedback sheet in which Column C would remain fixed for all user sheet also mentioned the columns names of Scorecard sheet from which data would get extracted. Scorecard sheet is also attached. I'm really confused how to begin with it. Please help me in preparing it. Thanks in advance. -- 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.
Re: $$Excel-Macros$$ Create user wise feedback sheet
Hi Vabz, Thanks so much for the help but it's not solving the purpose. User wise seperate sheets are not made in feedback workbook also the values of errors for rahul are incorrect. Can you please check it. Also i didn't get the logic behind clicking the name (col d) part. On Wednesday, August 20, 2014 2:34:36 PM UTC+5:30, Vabz wrote: FA macro. Also if you want to update single sheet then keep both feedback sheet scorecard sheet open together then in scorecard book double click on name (col D) and u will get prompted to select sheet to update with existing values. Cheers!! On Tue, Aug 19, 2014 at 7:39 PM, SG sona...@gmail.com javascript: wrote: Hi Experts, I need your help in preparation of a feedback sheet through macro. My requirement is to create name wise feedback worksheet in a workbook which contains their data from scorecard sheet. one of the criteria is to create the name wise sheet for only region uk location b users. I have attached feedback sheet in which Column C would remain fixed for all user sheet also mentioned the columns names of Scorecard sheet from which data would get extracted. Scorecard sheet is also attached. I'm really confused how to begin with it. Please help me in preparing it. Thanks in advance. -- 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$$ Create user wise feedback sheet
Hi Experts, I need your help in preparation of a feedback sheet through macro. My requirement is to create name wise feedback worksheet in a workbook which contains their data from scorecard sheet. one of the criteria is to create the name wise sheet for only region uk location b users. I have attached feedback sheet in which Column C would remain fixed for all user sheet also mentioned the columns names of Scorecard sheet from which data would get extracted. Scorecard sheet is also attached. I'm really confused how to begin with it. Please help me in preparing it. Thanks in advance. -- 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. feedback.xlsx Description: MS-Excel 2007 spreadsheet Scorecard.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ Summarise data based on multiple combination of criterias
Thankyou so much Vabz On Wednesday, June 18, 2014 8:50:04 PM UTC+5:30, Vabz wrote: Chek this,,, On Wed, Jun 18, 2014 at 7:08 PM, SG sona...@gmail.com javascript: wrote: yeah got it...one more thing..in reports, we can't say customer to select * for All because it's not user friendly. What to do in that case?? On Wednesday, June 18, 2014 6:53:29 PM UTC+5:30, Vabz wrote: when you say ALL, for excel / computer it is * (asterik), remember old days when DOS were used where command was Dir *.* Cheers!! On Wed, Jun 18, 2014 at 6:48 PM, SG sona...@gmail.com wrote: Amazing Vabz...thanksss..Can you please explain the idea behind this??? On Wednesday, June 18, 2014 6:30:28 PM UTC+5:30, Vabz wrote: Hi In cell C2 put * (only asterik) instead of ALL. Cheers!! On Wed, Jun 18, 2014 at 6:10 PM, SG sona...@gmail.com wrote: Hi Experts, I'm looking for a solution to summarise the data based on multiple criterias in drop downs. My data has 3 dropdowns.I have tried it using advance filter but it fails when all criterias selected as All. The problem starts when any of the dropdown is selected as All .I'm unable to find the correct solution. Please help me with a solution.PFA sheet for your reference. Thanks in advance -- 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. 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
Re: $$Excel-Macros$$ Summarise data based on multiple combination of criterias
Amazing Vabz...thanksss..Can you please explain the idea behind this??? On Wednesday, June 18, 2014 6:30:28 PM UTC+5:30, Vabz wrote: Hi In cell C2 put * (only asterik) instead of ALL. Cheers!! On Wed, Jun 18, 2014 at 6:10 PM, SG sona...@gmail.com javascript: wrote: Hi Experts, I'm looking for a solution to summarise the data based on multiple criterias in drop downs. My data has 3 dropdowns.I have tried it using advance filter but it fails when all criterias selected as All. The problem starts when any of the dropdown is selected as All .I'm unable to find the correct solution. Please help me with a solution.PFA sheet for your reference. Thanks in advance -- 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.
Re: $$Excel-Macros$$ Summarise data based on multiple combination of criterias
yeah got it...one more thing..in reports, we can't say customer to select * for All because it's not user friendly. What to do in that case?? On Wednesday, June 18, 2014 6:53:29 PM UTC+5:30, Vabz wrote: when you say ALL, for excel / computer it is * (asterik), remember old days when DOS were used where command was Dir *.* Cheers!! On Wed, Jun 18, 2014 at 6:48 PM, SG sona...@gmail.com javascript: wrote: Amazing Vabz...thanksss..Can you please explain the idea behind this??? On Wednesday, June 18, 2014 6:30:28 PM UTC+5:30, Vabz wrote: Hi In cell C2 put * (only asterik) instead of ALL. Cheers!! On Wed, Jun 18, 2014 at 6:10 PM, SG sona...@gmail.com wrote: Hi Experts, I'm looking for a solution to summarise the data based on multiple criterias in drop downs. My data has 3 dropdowns.I have tried it using advance filter but it fails when all criterias selected as All. The problem starts when any of the dropdown is selected as All .I'm unable to find the correct solution. Please help me with a solution.PFA sheet for your reference. Thanks in advance -- 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 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$$ Sort Dynamic range - Help needed
Hi Experts, I need a solution to sort the data on basis of location highest marks. For this, i have created a dynamic named range to call it by macro then sort. My problem comes when the range also selects the blank formulas in sheet which shouldn't be selected. Only visible data should selected in that range. Please help me with the solution. Thanks in advance. -- 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. Dynamic Range.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Sort Dynamic range - Help needed
Hi vabz, Thanks for the reply but this is not solving my purpose.The data is not sorted on location highest marks i don't want to paste the visible data anywhere else. The sorting done only at that location i.e from colun A to D. On Thursday, April 17, 2014 2:02:46 PM UTC+5:30, Vabz wrote: PFA Thx On Thu, Apr 17, 2014 at 1:28 PM, SG sona...@gmail.com javascript:wrote: Hi Experts, I need a solution to sort the data on basis of location highest marks. For this, i have created a dynamic named range to call it by macro then sort. My problem comes when the range also selects the blank formulas in sheet which shouldn't be selected. Only visible data should selected in that range. Please help me with the solution. Thanks in advance. -- 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. 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.
Re: $$Excel-Macros$$ Values for Radio Buttons
Hi Abhi, Thanku so much for the clarification. On Monday, February 17, 2014 7:41:40 PM UTC+5:30, Abhi wrote: It's from the same form control toolbox you have picked the option button from. Upon hovering the mouse pointer over the control, it says Group Box (Form Control). You pick the group box first, draw it to the size on sheet, then you put option buttons in there. After you have put all the buttons, just right click on any of them (without selecting the group box) and choose the reference cell where the value would be placed. On Mon, Feb 17, 2014 at 7:23 PM, SG sona...@gmail.com javascript:wrote: okeyzzThanku so much for explanation. One more thing, i'm unable to find this group box in toolbox. Is this is the Microsoft Form 2.0 frame?? if yes, then how you have grouped these radio buttons in this box. Because on dragging this, we have to use frame object for option button in which linked cell is not available. On Monday, February 17, 2014 6:51:55 PM UTC+5:30, Abhi wrote: Not exactly! For Option Buttons to act individually, you need to keep them in a group. The box you are seeing around the 5 buttons is a Frame (came from same toolbox) and when the option buttons are inside a frame, they are grouped and work individually from others. Since there were 3 questions, there are 3 groups. For each question you might add from here on now, you would need to copy the buttons as well as frame and link them to another reference cell. Let me know if you need any further help with this. Regards, Abhishek On Mon, Feb 17, 2014 at 6:42 PM, SG sona...@gmail.com wrote: O yes. Thanku so much. I guess the idea behind its working is that you have unfreezed the linked cell in properties. Am i right sir?? On Monday, February 17, 2014 6:23:08 PM UTC+5:30, Abhi wrote: Something like this? On Mon, Feb 17, 2014 at 4:58 PM, SG sona...@gmail.com wrote: Hi experts Please help me with your effective solution. I’m working on a training template in which individual scores would be analyzed scores for all trainees comes in summary sheet. For this, in each individual sheet I have used radio buttons. What I need is for a parameter, if I click under 1, it generate value 1, under 2, it generate value 2 so on. The problems come when I click the first button of second point it generates the value 6. It should generate 1 so that I can lookup the unique value for each point in summary sheet to get the scores collated. I have attached the same for your reference. Please help me on this ASAP. -- 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/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
$$Excel-Macros$$ Values for Radio Buttons
Hi experts Please help me with your effective solution. I’m working on a training template in which individual scores would be analyzed scores for all trainees comes in summary sheet. For this, in each individual sheet I have used radio buttons. What I need is for a parameter, if I click under 1, it generate value 1, under 2, it generate value 2 so on. The problems come when I click the first button of second point it generates the value 6. It should generate 1 so that I can lookup the unique value for each point in summary sheet to get the scores collated. I have attached the same for your reference. Please help me on this ASAP. -- 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. Training .xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Values for Radio Buttons
O yes. Thanku so much. I guess the idea behind its working is that you have unfreezed the linked cell in properties. Am i right sir?? On Monday, February 17, 2014 6:23:08 PM UTC+5:30, Abhi wrote: Something like this? On Mon, Feb 17, 2014 at 4:58 PM, SG sona...@gmail.com javascript:wrote: Hi experts Please help me with your effective solution. I’m working on a training template in which individual scores would be analyzed scores for all trainees comes in summary sheet. For this, in each individual sheet I have used radio buttons. What I need is for a parameter, if I click under 1, it generate value 1, under 2, it generate value 2 so on. The problems come when I click the first button of second point it generates the value 6. It should generate 1 so that I can lookup the unique value for each point in summary sheet to get the scores collated. I have attached the same for your reference. Please help me on this ASAP. -- 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. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Values for Radio Buttons
okeyzzThanku so much for explanation. One more thing, i'm unable to find this group box in toolbox. Is this is the Microsoft Form 2.0 frame?? if yes, then how you have grouped these radio buttons in this box. Because on dragging this, we have to use frame object for option button in which linked cell is not available. On Monday, February 17, 2014 6:51:55 PM UTC+5:30, Abhi wrote: Not exactly! For Option Buttons to act individually, you need to keep them in a group. The box you are seeing around the 5 buttons is a Frame (came from same toolbox) and when the option buttons are inside a frame, they are grouped and work individually from others. Since there were 3 questions, there are 3 groups. For each question you might add from here on now, you would need to copy the buttons as well as frame and link them to another reference cell. Let me know if you need any further help with this. Regards, Abhishek On Mon, Feb 17, 2014 at 6:42 PM, SG sona...@gmail.com javascript:wrote: O yes. Thanku so much. I guess the idea behind its working is that you have unfreezed the linked cell in properties. Am i right sir?? On Monday, February 17, 2014 6:23:08 PM UTC+5:30, Abhi wrote: Something like this? On Mon, Feb 17, 2014 at 4:58 PM, SG sona...@gmail.com wrote: Hi experts Please help me with your effective solution. I’m working on a training template in which individual scores would be analyzed scores for all trainees comes in summary sheet. For this, in each individual sheet I have used radio buttons. What I need is for a parameter, if I click under 1, it generate value 1, under 2, it generate value 2 so on. The problems come when I click the first button of second point it generates the value 6. It should generate 1 so that I can lookup the unique value for each point in summary sheet to get the scores collated. I have attached the same for your reference. Please help me on this ASAP. -- 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/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Macro needed to add worksheet
Hi Experts please help On Wednesday, September 11, 2013 7:55:14 PM UTC+5:30, SG wrote: hi Ravi, This code is throwing error subscript out of range. Please suggest. On Wednesday, September 11, 2013 5:44:17 PM UTC+5:30, Ravi Kumar wrote: No dear, Point 1) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) Point 2) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) Above code is like in single line you can say. When we use “_” it will continue our current line with next new line. You can see above number 1 and 2 point both are same * * *Warm Regards,* *Ravi Kumar.* *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *SG *Sent:* Wednesday, September 11, 2013 5:33 PM *To:* excel-...@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet Hi Ravi, i think the below code is incomplete.please correct me if i'm wrong. Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ On Tuesday, September 10, 2013 2:54:19 PM UTC+5:30, Ravi Kumar wrote: Try this and change you folder name accordingly … Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False primewb = ActiveWorkbook.Name Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\Admin\Desktop\Some VBA Tricks) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) Sheets.Add after:=Sheets(Sheets.Count) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub * * *Warm Regards,* *Ravi Kumar.* *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *SG *Sent:* Tuesday, September 10, 2013 2:36 PM *To:* excel-...@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub see if it helps On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you
Re: $$Excel-Macros$$ Macro needed to add worksheet
Hi Ravi, i think the below code is incomplete.please correct me if i'm wrong. Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ On Tuesday, September 10, 2013 2:54:19 PM UTC+5:30, Ravi Kumar wrote: Try this and change you folder name accordingly … Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False primewb = ActiveWorkbook.Name Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\Admin\Desktop\Some VBA Tricks) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) Sheets.Add after:=Sheets(Sheets.Count) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub * * *Warm Regards,* *Ravi Kumar.* *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *SG *Sent:* Tuesday, September 10, 2013 2:36 PM *To:* excel-...@googlegroups.com javascript: *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub see if it helps On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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/groups/opt_out. -- *Regards* * * *Ashish Koul* *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
Re: $$Excel-Macros$$ Macro needed to add worksheet
hi Ashish, it's running perfectly without any error but it's not adding the sheet in workbook. On Tuesday, September 10, 2013 7:54:02 PM UTC+5:30, ashish wrote: is it giving you any error On Tue, Sep 10, 2013 at 2:54 PM, Ravi Kumar excelle...@gmail.comjavascript: wrote: Try this and change you folder name accordingly … ** ** ** ** Sub copy_sheet() ** ** Application.ScreenUpdating = False Application.DisplayAlerts = False ** ** ** ** primewb = ActiveWorkbook.Name ** ** Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook ** ** ** ** Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\Admin\Desktop\Some VBA Tricks) ' folder having files ** ** ** ** For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) Sheets.Add after:=Sheets(Sheets.Count) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) wkb.Save wkb.Close Set wkb = Nothing Next ** ** Application.ScreenUpdating = True Application.DisplayAlerts = True ** ** ** ** End Sub ** ** ** ** * * *Warm Regards,* *Ravi Kumar.* ** ** *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *SG *Sent:* Tuesday, September 10, 2013 2:36 PM *To:* excel-...@googlegroups.com javascript: *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet ** ** Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: ** ** Sub copy_sheet() ** ** Application.ScreenUpdating = False Application.DisplayAlerts = False ** ** ** ** Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook ** ** Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files ** ** For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next ** ** Application.ScreenUpdating = True Application.DisplayAlerts = True ** ** ** ** ** ** End Sub ** ** ** ** see if it helps ** ** ** ** On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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/groups/opt_out. ** ** -- *Regards* * * *Ashish Koul* ** ** ** ** *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ ** ** ** ** P
Re: $$Excel-Macros$$ Macro needed to add worksheet
hi Ravi, This code is throwing error subscript out of range. Please suggest. On Wednesday, September 11, 2013 5:44:17 PM UTC+5:30, Ravi Kumar wrote: No dear, Point 1) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) Point 2) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) Above code is like in single line you can say. When we use “_” it will continue our current line with next new line. You can see above number 1 and 2 point both are same * * *Warm Regards,* *Ravi Kumar.* *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *SG *Sent:* Wednesday, September 11, 2013 5:33 PM *To:* excel-...@googlegroups.com javascript: *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet Hi Ravi, i think the below code is incomplete.please correct me if i'm wrong. Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ On Tuesday, September 10, 2013 2:54:19 PM UTC+5:30, Ravi Kumar wrote: Try this and change you folder name accordingly … Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False primewb = ActiveWorkbook.Name Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\Admin\Desktop\Some VBA Tricks) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) Sheets.Add after:=Sheets(Sheets.Count) Workbooks(primewb).Sheets(sample).Range(A4:c22).Copy Destination:=Workbooks(Mid(fil.Name, 1, _ WorksheetFunction.Find(., fil.Name) - 1)).Sheets(Sheets.Count).Range(A1) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub * * *Warm Regards,* *Ravi Kumar.* *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *SG *Sent:* Tuesday, September 10, 2013 2:36 PM *To:* excel-...@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Macro needed to add worksheet Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub see if it helps On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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
Re: $$Excel-Macros$$ Macro needed to add worksheet
Thanks for the help but it's not adding my sheet in rest of the workbooks. i have attached that single sheet added the macro in it.Please suggest Where i'm going wrong. On Monday, September 9, 2013 8:16:20 PM UTC+5:30, ashish wrote: Sub copy_sheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim fld As Object, fil As Object, fso As Object Dim wkb As Workbook Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(C:\Users\admin\Desktop\test) ' folder having files For Each fil In fld.Files Set wkb = Workbooks.Open(fil.Path) ThisWorkbook.Sheets(sample).Copy After:=wkb.Sheets(wkb.Sheets.Count) wkb.Save wkb.Close Set wkb = Nothing Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub see if it helps On Mon, Sep 9, 2013 at 7:45 PM, SG sona...@gmail.com javascript:wrote: Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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. For more options, visit https://groups.google.com/groups/opt_out. -- *Regards* * * *Ashish Koul* *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. Book1.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
$$Excel-Macros$$ Macro needed to add worksheet
Hi Experts, Once again I neeed your help.I have one excel worksheet which is common for all workbooks. However, that worksheet is not added in the workbooks. I have atleast 50 excel workbooks in a folder in which i have to add that single worksheet manually.The name of all worbooks are same only the week number is different in filenames like report wk-1,report wk-2 so on.Please help me with a macro which would add that single sheet in all workbooks of that folder one by one. Thanks in advance -- 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 Problem...plzzz help
Thanks for the reply bt it doesn't solve my purpose.Let me try one more time to explain. What i want is that when i select domain, then, only name accuracy in that domain will display on report sheet.. then ,if i select any project in that domain, then it's correponding name accuracy will display on report sheet. then if i select any ques in that project,then it's correponding name accuracy will display on report sheet. It needs 3 criteria based filters on base data which will retrieve corresponding name accuracy display them on report sheet. please sir, help me with a solution. On Saturday, July 6, 2013 10:30:04 AM UTC+5:30, Vabz wrote: Hi PFA, You have to ensure criteria range is in same sheet in which you apply filter. you also need to extend your range to accommodate rest of data.. Thaks On Fri, Jul 5, 2013 at 5:25 PM, SG sona...@gmail.com javascript:wrote: hi Experts, I'm in a great problem as i'm beginner in macros.I have created a template in which the data would be extracted on basis on 3 criterias.However, by using macro, i'm unable to extract it.For criterias, i have created drop-down list by using Data validation name manager. Now first problem is if i add some data in base data then it is not updated in Drop downs second problem is based on criteria, data is not extracted.I don't know where i'm lagging behind.please help me with the solution. Thanks in advance. -- 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. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ Macro Problem...plzzz help
hi Experts, I'm in a great problem as i'm beginner in macros.I have created a template in which the data would be extracted on basis on 3 criterias.However, by using macro, i'm unable to extract it.For criterias, i have created drop-down list by using Data validation name manager. Now first problem is if i add some data in base data then it is not updated in Drop downs second problem is based on criteria, data is not extracted.I don't know where i'm lagging behind.please help me with the solution. Thanks in advance. -- 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. rough.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
$$Excel-Macros$$ Re: Macro Problem...plzzz help
plz experts..spare some time on my problem. looking forward for your help. On Friday, July 5, 2013 5:25:27 PM UTC+5:30, SG wrote: hi Experts, I'm in a great problem as i'm beginner in macros.I have created a template in which the data would be extracted on basis on 3 criterias.However, by using macro, i'm unable to extract it.For criterias, i have created drop-down list by using Data validation name manager. Now first problem is if i add some data in base data then it is not updated in Drop downs second problem is based on criteria, data is not extracted.I don't know where i'm lagging behind.please help me with the solution. Thanks in advance. -- 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$$ Extract Data on basis of different Criterias
hi Ashish, thanks for the help.please help me in result as i want only name accuracy to show. On Wednesday, July 3, 2013 8:49:33 PM UTC+5:30, ashish wrote: try the attachment . if ur data is in lakhs best is u use access Regards Ashish On Wed, Jul 3, 2013 at 8:42 PM, SG sona...@gmail.com javascript:wrote: hi Ashish, The data is in lakhs advance filter would allow me to filter only one criteria.I have atleast 4 filters in this data. plz experts help me with a macro. On Wednesday, July 3, 2013 8:29:18 PM UTC+5:30, ashish wrote: u can do it using advance filter On Wed, Jul 3, 2013 at 7:55 PM, SG sona...@gmail.com wrote: thanx for the response. PFA example.What i want is to filter the data as oer the Form sheet extract name it's accuracy paste in the data section.the data is so huge that's why i want it by macro. On Wednesday, July 3, 2013 7:41:24 PM UTC+5:30, Vabz wrote: HI Pl share sample file. Thanks On Wed, Jul 3, 2013 at 7:30 PM, SG sona...@gmail.com wrote: Hi Experts, I want to create a report in which data would be extracted on basis of atleast 4 filters from base data sheet.please help me with a macro example. Thanks in advance. -- 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/**discu**ssexcelhttps://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros...@**googlegroups.**com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/**group**/excel-macroshttp://groups.google.com/group/excel-macros . For more options, visit https://groups.google.com/**grou**ps/opt_outhttps://groups.google.com/groups/opt_out . -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/**discussexcelhttps://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros...@**googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/**group/excel-macroshttp://groups.google.com/group/excel-macros . For more options, visit https://groups.google.com/**groups/opt_outhttps://groups.google.com/groups/opt_out . -- *Regards* * * *Ashish Koul* *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting
$$Excel-Macros$$ Extract Data on basis of different Criterias
Hi Experts, I want to create a report in which data would be extracted on basis of atleast 4 filters from base data sheet.please help me with a macro example. Thanks in advance. -- 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$$ Extract Data on basis of different Criterias
thanx for the response. PFA example.What i want is to filter the data as oer the Form sheet extract name it's accuracy paste in the data section.the data is so huge that's why i want it by macro. On Wednesday, July 3, 2013 7:41:24 PM UTC+5:30, Vabz wrote: HI Pl share sample file. Thanks On Wed, Jul 3, 2013 at 7:30 PM, SG sona...@gmail.com javascript:wrote: Hi Experts, I want to create a report in which data would be extracted on basis of atleast 4 filters from base data sheet.please help me with a macro example. Thanks in advance. -- 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. For more options, visit https://groups.google.com/groups/opt_out. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out. example.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Extract Data on basis of different Criterias
hi Ashish, The data is in lakhs advance filter would allow me to filter only one criteria.I have atleast 4 filters in this data. plz experts help me with a macro. On Wednesday, July 3, 2013 8:29:18 PM UTC+5:30, ashish wrote: u can do it using advance filter On Wed, Jul 3, 2013 at 7:55 PM, SG sona...@gmail.com javascript:wrote: thanx for the response. PFA example.What i want is to filter the data as oer the Form sheet extract name it's accuracy paste in the data section.the data is so huge that's why i want it by macro. On Wednesday, July 3, 2013 7:41:24 PM UTC+5:30, Vabz wrote: HI Pl share sample file. Thanks On Wed, Jul 3, 2013 at 7:30 PM, SG sona...@gmail.com wrote: Hi Experts, I want to create a report in which data would be extracted on basis of atleast 4 filters from base data sheet.please help me with a macro example. Thanks in advance. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/**discussexcelhttps://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros...@**googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/**group/excel-macroshttp://groups.google.com/group/excel-macros . For more options, visit https://groups.google.com/**groups/opt_outhttps://groups.google.com/groups/opt_out . -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, 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. For more options, visit https://groups.google.com/groups/opt_out. -- *Regards* * * *Ashish Koul* *Visit* *My Excel Blog http://www.excelvbamacros.com/* Like Us on Facebookhttp://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897 Join Us on Facebook http://www.facebook.com/groups/163491717053198/ P Before printing, think about the environment. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email
Re: $$Excel-Macros$$ Consolidating Multiple files
hi Paul, Sorry for the inconvenience.PFA file names of excel file are 1,2,3. Please suggest. On Friday, December 14, 2012 9:03:11 PM UTC+5:30, Paul Schreiner wrote: I don't think we can work that way... You're using someone ELSE's macro with YOUR files and folders and you've admitted that you've edited the macro a little bit... and you want us to suggest how to fix it without seeing the macro, your files and folders, and what edit's you've done? Well.. I GUESS I could suggest one thing: please give us more information. Perhaps attach a file. At the LEAST, attach the macro and tell us what your file names and folder names are... *Paul* - *“Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley *- -- *From:* SG sona...@gmail.com javascript: *To:* excel-...@googlegroups.com javascript: *Sent:* Fri, December 14, 2012 10:11:52 AM *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files thanks but the macro is not entering in to the loop. I have edited the macro lil bit at my end.I have removed the part which is for saving the complied file i think it's not going to effect anyway. please suggest.. On Friday, December 14, 2012 8:31:22 PM UTC+5:30, Rajan_Verma wrote: *Ok,* * * *Open Visula Basic Editor* *Goto the Tool- Reference –Select **Microsoft scripting runtime*** * * * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com [mailto:excel-...@ googlegroups.com] *On Behalf Of *SG *Sent:* 14 December 2012 8:26 *To:* excel-...@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files may be i'm silly with the question, but really not getting it that you have replied. On Friday, December 14, 2012 8:16:28 PM UTC+5:30, Rajan_Verma wrote: *Ah..* * * *You can add reference “Microsoft scripting runtime”* * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com [mailto:excel-...@ googlegroups.com] *On Behalf Of *SG *Sent:* 14 December 2012 7:54 *To:* excel-...@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files hi Rajan, Thanks for the reply.I have taken the macro from 2nd link. However on running the macro, the code in 4th line 'Dim Fso As New Scripting.FileSystemObject' gives an complie error 'user-defined type not defined'. please help on this. On Friday, December 14, 2012 7:28:17 PM UTC+5:30, Rajan_Verma wrote: *See if it helps* * * *1)** **http://excelpoweruser. wordpress.com/2012/06/07/ compile-worksheets/http://excelpoweruser.wordpress.com/2012/06/07/compile-worksheets/ * *2)** **http://excelpoweruser. wordpress.com/2011/08/12/ compiling-workbooks-2/http://excelpoweruser.wordpress.com/2011/08/12/compiling-workbooks-2/ * *3)** **http://excelpoweruser. wordpress.com/2011/07/06/ compiling-workbooks/http://excelpoweruser.wordpress.com/2011/07/06/compiling-workbooks/ * * * * * * * * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com [mailto:excel-...@ googlegroups.com] *On Behalf Of *SG *Sent:* 14 December 2012 7:19 *To:* excel-...@googlegroups.com *Subject:* $$Excel-Macros$$ Consolidating Multiple files Hi Experts, I need your help in one of my problem.I want to consolidate the multiple excel files of a folder with same name numbered as 1,2,3 in to a one excel file.I have tried but unable to create a macro on it.There is only one criteria in this consolidation is that on column D of every sheet is a date.What i want is to extract the data on basis of a particular week number let's say we are in week 50 i want the data of week-49.Currently i'm doing this manually.I apply the weeknum formula in column where data gets end let's say that's column M then extract the data. Please help me on this with a macro thanks in advance. -- 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
Re: $$Excel-Macros$$ Consolidating Multiple files
experts please help me with this.i have also attached the file . On Monday, December 17, 2012 1:59:39 PM UTC+5:30, SG wrote: hi Paul, Sorry for the inconvenience.PFA file names of excel file are 1,2,3. Please suggest. On Friday, December 14, 2012 9:03:11 PM UTC+5:30, Paul Schreiner wrote: I don't think we can work that way... You're using someone ELSE's macro with YOUR files and folders and you've admitted that you've edited the macro a little bit... and you want us to suggest how to fix it without seeing the macro, your files and folders, and what edit's you've done? Well.. I GUESS I could suggest one thing: please give us more information. Perhaps attach a file. At the LEAST, attach the macro and tell us what your file names and folder names are... *Paul* - *“Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley *- -- *From:* SG sona...@gmail.com *To:* excel-...@googlegroups.com *Sent:* Fri, December 14, 2012 10:11:52 AM *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files thanks but the macro is not entering in to the loop. I have edited the macro lil bit at my end.I have removed the part which is for saving the complied file i think it's not going to effect anyway. please suggest.. On Friday, December 14, 2012 8:31:22 PM UTC+5:30, Rajan_Verma wrote: *Ok,* * * *Open Visula Basic Editor* *Goto the Tool- Reference –Select **Microsoft scripting runtime*** * * * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com [mailto:excel-...@ googlegroups.com] *On Behalf Of *SG *Sent:* 14 December 2012 8:26 *To:* excel-...@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files may be i'm silly with the question, but really not getting it that you have replied. On Friday, December 14, 2012 8:16:28 PM UTC+5:30, Rajan_Verma wrote: *Ah..* * * *You can add reference “Microsoft scripting runtime”* * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com [mailto:excel-...@ googlegroups.com] *On Behalf Of *SG *Sent:* 14 December 2012 7:54 *To:* excel-...@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files hi Rajan, Thanks for the reply.I have taken the macro from 2nd link. However on running the macro, the code in 4th line 'Dim Fso As New Scripting.FileSystemObject' gives an complie error 'user-defined type not defined'. please help on this. On Friday, December 14, 2012 7:28:17 PM UTC+5:30, Rajan_Verma wrote: *See if it helps* * * *1)** **http://excelpoweruser. wordpress.com/2012/06/07/ compile-worksheets/http://excelpoweruser.wordpress.com/2012/06/07/compile-worksheets/ * *2)** **http://excelpoweruser. wordpress.com/2011/08/12/ compiling-workbooks-2/http://excelpoweruser.wordpress.com/2011/08/12/compiling-workbooks-2/ * *3)** **http://excelpoweruser. wordpress.com/2011/07/06/ compiling-workbooks/http://excelpoweruser.wordpress.com/2011/07/06/compiling-workbooks/ * * * * * * * * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com [mailto:excel-...@ googlegroups.com] *On Behalf Of *SG *Sent:* 14 December 2012 7:19 *To:* excel-...@googlegroups.com *Subject:* $$Excel-Macros$$ Consolidating Multiple files Hi Experts, I need your help in one of my problem.I want to consolidate the multiple excel files of a folder with same name numbered as 1,2,3 in to a one excel file.I have tried but unable to create a macro on it.There is only one criteria in this consolidation is that on column D of every sheet is a date.What i want is to extract the data on basis of a particular week number let's say we are in week 50 i want the data of week-49.Currently i'm doing this manually.I apply the weeknum formula in column where data gets end let's say that's column M then extract the data. Please help me on this with a macro thanks in advance. -- 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
$$Excel-Macros$$ Consolidating Multiple files
Hi Experts, I need your help in one of my problem.I want to consolidate the multiple excel files of a folder with same name numbered as 1,2,3 in to a one excel file.I have tried but unable to create a macro on it.There is only one criteria in this consolidation is that on column D of every sheet is a date.What i want is to extract the data on basis of a particular week number let's say we are in week 50 i want the data of week-49.Currently i'm doing this manually.I apply the weeknum formula in column where data gets end let's say that's column M then extract the data. Please help me on this with a macro thanks in advance. -- 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$$ Consolidating Multiple files
hi Rajan, Thanks for the reply.I have taken the macro from 2nd link. However on running the macro, the code in 4th line 'Dim Fso As New Scripting.FileSystemObject' gives an complie error 'user-defined type not defined'. please help on this. On Friday, December 14, 2012 7:28:17 PM UTC+5:30, Rajan_Verma wrote: *See if it helps* * * *1) ** http://excelpoweruser.wordpress.com/2012/06/07/compile-worksheets/* *2) ** http://excelpoweruser.wordpress.com/2011/08/12/compiling-workbooks-2/* *3) ** http://excelpoweruser.wordpress.com/2011/07/06/compiling-workbooks/* * * * * * * * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *SG *Sent:* 14 December 2012 7:19 *To:* excel-...@googlegroups.com javascript: *Subject:* $$Excel-Macros$$ Consolidating Multiple files Hi Experts, I need your help in one of my problem.I want to consolidate the multiple excel files of a folder with same name numbered as 1,2,3 in to a one excel file.I have tried but unable to create a macro on it.There is only one criteria in this consolidation is that on column D of every sheet is a date.What i want is to extract the data on basis of a particular week number let's say we are in week 50 i want the data of week-49.Currently i'm doing this manually.I apply the weeknum formula in column where data gets end let's say that's column M then extract the data. Please help me on this with a macro thanks in advance. -- 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$$ Consolidating Multiple files
may be i'm silly with the question, but really not getting it that you have replied. On Friday, December 14, 2012 8:16:28 PM UTC+5:30, Rajan_Verma wrote: *Ah..* * * *You can add reference “Microsoft scripting runtime”* * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *SG *Sent:* 14 December 2012 7:54 *To:* excel-...@googlegroups.com javascript: *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files hi Rajan, Thanks for the reply.I have taken the macro from 2nd link. However on running the macro, the code in 4th line 'Dim Fso As New Scripting.FileSystemObject' gives an complie error 'user-defined type not defined'. please help on this. On Friday, December 14, 2012 7:28:17 PM UTC+5:30, Rajan_Verma wrote: *See if it helps* * * *1)** ** http://excelpoweruser.wordpress.com/2012/06/07/compile-worksheets/* *2)** ** http://excelpoweruser.wordpress.com/2011/08/12/compiling-workbooks-2/* *3)** ** http://excelpoweruser.wordpress.com/2011/07/06/compiling-workbooks/* * * * * * * * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *SG *Sent:* 14 December 2012 7:19 *To:* excel-...@googlegroups.com *Subject:* $$Excel-Macros$$ Consolidating Multiple files Hi Experts, I need your help in one of my problem.I want to consolidate the multiple excel files of a folder with same name numbered as 1,2,3 in to a one excel file.I have tried but unable to create a macro on it.There is only one criteria in this consolidation is that on column D of every sheet is a date.What i want is to extract the data on basis of a particular week number let's say we are in week 50 i want the data of week-49.Currently i'm doing this manually.I apply the weeknum formula in column where data gets end let's say that's column M then extract the data. Please help me on this with a macro thanks in advance. -- 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-...@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
Re: $$Excel-Macros$$ Consolidating Multiple files
thanks but the macro is not entering in to the loop. I have edited the macro lil bit at my end.I have removed the part which is for saving the complied file i think it's not going to effect anyway. please suggest.. On Friday, December 14, 2012 8:31:22 PM UTC+5:30, Rajan_Verma wrote: *Ok,* * * *Open Visula Basic Editor* *Goto the Tool- Reference –Select **Microsoft scripting runtime*** * * * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com javascript: [mailto: excel-...@googlegroups.com javascript:] *On Behalf Of *SG *Sent:* 14 December 2012 8:26 *To:* excel-...@googlegroups.com javascript: *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files may be i'm silly with the question, but really not getting it that you have replied. On Friday, December 14, 2012 8:16:28 PM UTC+5:30, Rajan_Verma wrote: *Ah..* * * *You can add reference “Microsoft scripting runtime”* * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *SG *Sent:* 14 December 2012 7:54 *To:* excel-...@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Consolidating Multiple files hi Rajan, Thanks for the reply.I have taken the macro from 2nd link. However on running the macro, the code in 4th line 'Dim Fso As New Scripting.FileSystemObject' gives an complie error 'user-defined type not defined'. please help on this. On Friday, December 14, 2012 7:28:17 PM UTC+5:30, Rajan_Verma wrote: *See if it helps* * * *1)** ** http://excelpoweruser.wordpress.com/2012/06/07/compile-worksheets/* *2)** ** http://excelpoweruser.wordpress.com/2011/08/12/compiling-workbooks-2/* *3)** ** http://excelpoweruser.wordpress.com/2011/07/06/compiling-workbooks/* * * * * * * * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* * * *From:* excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] *On Behalf Of *SG *Sent:* 14 December 2012 7:19 *To:* excel-...@googlegroups.com *Subject:* $$Excel-Macros$$ Consolidating Multiple files Hi Experts, I need your help in one of my problem.I want to consolidate the multiple excel files of a folder with same name numbered as 1,2,3 in to a one excel file.I have tried but unable to create a macro on it.There is only one criteria in this consolidation is that on column D of every sheet is a date.What i want is to extract the data on basis of a particular week number let's say we are in week 50 i want the data of week-49.Currently i'm doing this manually.I apply the weeknum formula in column where data gets end let's say that's column M then extract the data. Please help me on this with a macro thanks in advance. -- 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-...@googlegroups.com. To unsubscribe from this group, send email to excel-macros...@googlegroups.com. Visit this group at http://groups.google.com/group/excel
Re: $$Excel-Macros$$ Re: Calculated Field not Working
any update plz... plz help me to understand this. On Monday, September 24, 2012 9:06:59 PM UTC+5:30, SG wrote: hi Vabz, Still i'm not able to find the difference.Though i have also used the same formula in calculated field in my sheet. Plz let me know...so that i would be able to help it in other sheets. On Thursday, September 20, 2012 6:59:33 PM UTC+5:30, Vabz wrote: Hi, Just use formula insert field button to define formula.. thats it. There was error in formula given by me was working well in chetan's file. Rgds//Vabs On Thursday, September 20, 2012 6:54:45 PM UTC+5:30, SG wrote: hi Chetan/vabz thanks for the help. I got the solutions.plz explain what you have done to get the ans. i don't know that whose file is this which gave me ans. plz explain. On Wednesday, September 19, 2012 11:42:14 PM UTC+5:30, Chethan wrote: check..if these help. check and let me know Regards CK On Wed, Sep 19, 2012 at 2:45 PM, SG sona...@gmail.com wrote: Experts, plz help mei need solution.plz look in to this On Monday, September 17, 2012 8:20:53 PM UTC+5:30, SG wrote: Hi Experts, I'm again in to a problem whic is silly but still i can't make it.I calculate the scores for individuals seperately but this time i want to make all under one table.For this, i have created a pivot table for Calculating the Score, I have used Calculated field which is not giving me the right ans.I'm attaching the sheet.Plz look in to this.I have highlighted the column for which i nedd your help. thanks in advance -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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. -- Chethan -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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.
Re: $$Excel-Macros$$ Re: Calculated Field not Working
hi Vabz, Still i'm not able to find the difference.Though i have also used the same formula in calculated field in my sheet. Plz let me know...so that i would be able to help it in other sheets. On Thursday, September 20, 2012 6:59:33 PM UTC+5:30, Vabz wrote: Hi, Just use formula insert field button to define formula.. thats it. There was error in formula given by me was working well in chetan's file. Rgds//Vabs On Thursday, September 20, 2012 6:54:45 PM UTC+5:30, SG wrote: hi Chetan/vabz thanks for the help. I got the solutions.plz explain what you have done to get the ans. i don't know that whose file is this which gave me ans. plz explain. On Wednesday, September 19, 2012 11:42:14 PM UTC+5:30, Chethan wrote: check..if these help. check and let me know Regards CK On Wed, Sep 19, 2012 at 2:45 PM, SG sona...@gmail.com wrote: Experts, plz help mei need solution.plz look in to this On Monday, September 17, 2012 8:20:53 PM UTC+5:30, SG wrote: Hi Experts, I'm again in to a problem whic is silly but still i can't make it.I calculate the scores for individuals seperately but this time i want to make all under one table.For this, i have created a pivot table for Calculating the Score, I have used Calculated field which is not giving me the right ans.I'm attaching the sheet.Plz look in to this.I have highlighted the column for which i nedd your help. thanks in advance -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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. -- Chethan -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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.
Re: $$Excel-Macros$$ Re: Calculated Field not Working
hi Chetan/vabz thanks for the help. I got the solutions.plz explain what you have done to get the ans. i don't know that whose file is this which gave me ans. plz explain. On Wednesday, September 19, 2012 11:42:14 PM UTC+5:30, Chethan wrote: check..if these help. check and let me know Regards CK On Wed, Sep 19, 2012 at 2:45 PM, SG sona...@gmail.com javascript:wrote: Experts, plz help mei need solution.plz look in to this On Monday, September 17, 2012 8:20:53 PM UTC+5:30, SG wrote: Hi Experts, I'm again in to a problem whic is silly but still i can't make it.I calculate the scores for individuals seperately but this time i want to make all under one table.For this, i have created a pivot table for Calculating the Score, I have used Calculated field which is not giving me the right ans.I'm attaching the sheet.Plz look in to this.I have highlighted the column for which i nedd your help. thanks in advance -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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:. -- Chethan -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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.
$$Excel-Macros$$ Re: Calculated Field not Working
Experts, plz help mei need solution.plz look in to this On Monday, September 17, 2012 8:20:53 PM UTC+5:30, SG wrote: Hi Experts, I'm again in to a problem whic is silly but still i can't make it.I calculate the scores for individuals seperately but this time i want to make all under one table.For this, i have created a pivot table for Calculating the Score, I have used Calculated field which is not giving me the right ans.I'm attaching the sheet.Plz look in to this.I have highlighted the column for which i nedd your help. thanks in advance -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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.
$$Excel-Macros$$ Calculated Field not Working
Hi Experts, I'm again in to a problem whic is silly but still i can't make it.I calculate the scores for individuals seperately but this time i want to make all under one table.For this, i have created a pivot table for Calculating the Score, I have used Calculated field which is not giving me the right ans.I'm attaching the sheet.Plz look in to this.I have highlighted the column for which i nedd your help. thanks in advance -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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. Book1.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Error Cleaning
hi pascal, Thanks for explaining.It really helped me.I'm a beginner in macros so i used to follow all experts. On Tuesday, July 24, 2012 5:48:08 PM UTC+5:30, bpascal123 wrote: This forum is a great place to learn, contributors I closely follow; Noorain, Asa, Don... On Monday, July 23, 2012 5:25:31 PM UTC+1, bpascal123 wrote: My Vba coding style is much inspired from Kris who from what I know, posts here and on excelfox.com. If you can read his code, you should learn many tricks. If rCol 1 Then v = Array(r) .Rows(i + 1 : i + rCol - 1).Insert .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 1), .Cells(i + rCol - 1, 7)) .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _ v = Array(r) could actually be changed to v = r or v = r.value v is an array that stores each code used by user column. Using an array was the only option since later the code calls a copy transpose method but as a range (opposite to array), copy transpose can't be used on the same range as for instance H8 would keep the first value, H9 the second. Using a range copy method, I would have had to insert an additional row to transpose the data and then delete the row 8, I hope you can follow me .Rows(i + 1 : i + rCol - 1).Insert rCol is the variable that stores the number of code used by user, as i'm using an array, i can keep the value in the first row and add just a new row (still for H8 example) .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 1), .Cells(i + rCol - 1, 7)) This is to copy the data from the initial line to the row i'm adding...rCol-1 same as line above rCol-1 .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _ Application.WorksheetFunction.Transpose(v) This took me some time to figure out, without this transpose function, I would have add to another loop... transpose just re-arrange data from row to column or the other way around, here v is the array that stores the code used by user values for one project or one row. If rCol lastc Then lastc = rCol This is for cleaning purpose, lastc stores the latest column used so to delete from H column to that column only instead of using a range H to columns.count clear contents method that could mess with your worksheet formatting or else --- As these line are part of a loop, you can see this code in action by setting a break point (a big red dot on the left margin of a line of code) using the Vba IDE at the entry of the for loop, click Run until the break point you have inserted is highlighted in yellow, then minimize your Vba IDE window so you can see your worksheet and the data as well as some line of the Vba IDE (horizontal tile window like), then press once F8, look the position of the code advancing, and look for any changes on the data such as inserting rows, copy paste transpose... You can also check the value of variables or object using the watch or debug window. The watch window is more straightforward since you just need to right click a variable like v or an object like r and select add watch, you will then be able to monitor values stored in an object or variable. I think if you already have some knowledge of Vba, you can use these Vba tools as I'm trying to present you. Pascal Baro On Monday, July 23, 2012 2:03:26 PM UTC+1, SG wrote: hi Pascal Thanku so muchit really helped meappreciate your help. even it increased my knowledge in VB... plz explain below part of code... thanks again If rCol 1 Then v = Array(r) .Rows(i + 1 : i + rCol - 1).Insert .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 1), .Cells(i + rCol - 1, 7)) .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _ -- -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
$$Excel-Macros$$ Re: Error Cleaning
hi Pascal Thanku so muchit really helped meappreciate your help. even it increased my knowledge in VB... plz explain below part of code... thanks again If rCol 1 Then v = Array(r) .Rows(i + 1 : i + rCol - 1).Insert .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 1), .Cells(i + rCol - 1, 7)) .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _ On Saturday, July 21, 2012 9:05:09 PM UTC+5:30, bpascal123 wrote: This line should allow you to run this macro from the workbook where the data is ... if the file is not xlsx (it can then be xls, xlsm, xlsb ) Set wbIn = ThisWorkbook (the line...worksheet add... that was commented out i forgot to delete wouldn't work anyway-i just found it easier to run the code directly on the splitted data sheet) and one more change in the complete code below (with method was missing in what i sent previously): --- Sub SGTest() Dim wbIn As Workbook Dim wsIn As Worksheet Dim wsInOut As Worksheet Dim v() As Variant Dim r As Range Dim rCol As Integer Dim i As Long, j As Long Dim lastr As Long, lastc As Long Set wbIn = ThisWorkbook Set wsInOut = wbIn.Worksheets(sorted data) Set wsIn = wbIn.Worksheets(splitted data) With Application .Calculation = xlCalculationManual .DisplayAlerts = False .ScreenUpdating = False End With With wsIn lastc = 0 .Rows(1).Copy wsInOut.Rows(1) lastr = .Cells(Rows.Count, 8).End(xlUp).Row For i = lastr To 2 Step -1 Set r = .Range(.Cells(i, 8), .Cells(i, .Columns.Count).End(xlToLeft)) rCol = r.Columns.Count If rCol 1 Then v = Array(r) .Rows(i + 1 : i + rCol - 1).Insert .Range(.Cells(i, 1), .Cells(i, 7)).Copy .Range(.Cells(i + 1, 1), .Cells(i + rCol - 1, 7)) .Range(.Cells(i, 8), .Cells(i + rCol - 1, 8)).Value = _ Application.WorksheetFunction.Transpose(v) If rCol lastc Then lastc = rCol End If Next i lastr = .Cells(Rows.Count, 8).End(xlUp).Row .Range(wsIn.Cells(1, 9), .Cells(lastr, lastc + 9)).ClearContents End With With Application .Calculation = xlCalculationAutomatic .DisplayAlerts = True .ScreenUpdating = True End With Set r = Nothing Set wbIn = Nothing Set wsIn = Nothing Set wsInOut = Nothing End Sub --- Pascal On Friday, July 20, 2012 2:34:04 PM UTC+1, SG wrote: hi pascal, i didn't understand this part of code...what should i write if i run this macro in new workbook Set wbIn = Workbooks(Book1 - Copy.xlsm) 'Set wsInOut = wbIn.Worksheets.Add: wsInOut.Name = sorted data ok2 Set wsInOut = wbIn.Worksheets(sorted data) Set wsIn = wbIn.Worksheets(splitted data) -- -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
$$Excel-Macros$$ Re: Error Cleaning
hi pascal, i didn't understand this part of code...what should i write if i run this macro in new workbook Set wbIn = Workbooks(Book1 - Copy.xlsm) 'Set wsInOut = wbIn.Worksheets.Add: wsInOut.Name = sorted data ok2 Set wsInOut = wbIn.Worksheets(sorted data) Set wsIn = wbIn.Worksheets(splitted data) On Friday, July 20, 2012 2:37:37 AM UTC+5:30, bpascal123 wrote: Hi SG, This code below should help you. You can run it from the vba ide in the attached file. I'm not as comfortable as Don is with vba objects and methods so maybe Don's code is better for this. I don't run if it takes time for many rows, ask again i like to search on the web trick to improve coding syntax. Hope this answers what you need, Pascal Baro Sub SGTest() Dim wbIn As Workbook Dim wsIn As Worksheet Dim wsInOut As Worksheet Dim v() As Variant Dim r As Range Dim rCol As Integer Dim i As Long, j As Long Dim lastr As Long, lastc As Long Set wbIn = Workbooks(Book1 - Copy.xlsm) 'Set wsInOut = wbIn.Worksheets.Add: wsInOut.Name = sorted data ok2 Set wsInOut = wbIn.Worksheets(sorted data) Set wsIn = wbIn.Worksheets(splitted data) With Application .Calculation = xlCalculationManual .DisplayAlerts = False .ScreenUpdating = False End With lastc = 0 wsIn.Rows(1).Copy wsInOut.Rows(1) lastr = wsIn.Cells(Rows.Count, 8).End(xlUp).Row For i = lastr To 2 Step -1 Set r = wsIn.Range(wsIn.Cells(i, 8), wsIn.Cells(i, wsIn.Columns.Count).End(xlToLeft)) rCol = r.Columns.Count If rCol 1 Then v = Array(r) wsIn.Rows(i + 1 : i + rCol - 1).Insert wsIn.Range(wsIn.Cells(i, 1), wsIn.Cells(i, 7)).Copy _ wsIn.Range(wsIn.Cells(i + 1, 1), wsIn.Cells(i + rCol - 1, 7)) wsIn.Range(wsIn.Cells(i, 8), wsIn.Cells(i + rCol - 1, 8)).Value = _ Application.WorksheetFunction.Transpose(v) If rCol lastc Then lastc = rCol End If Next i lastr = wsIn.Cells(Rows.Count, 8).End(xlUp).Row wsIn.Range(wsIn.Cells(1, 9), wsIn.Cells(lastr, lastc + 9)).ClearContents With Application .Calculation = xlCalculationAutomatic .DisplayAlerts = True .ScreenUpdating = True End With Set r = Nothing Set wbIn = Nothing Set wsIn = Nothing Set wsInOut = Nothing End Sub -- -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
$$Excel-Macros$$ Re: Error Cleaning
Hi Experts, Please find attached sheet for cleaning. What i want is this after splitting , each error gets a unique row like i manually done in the third sheet.Problem is that the no. of columns can increase Code used by user will always be in the end,so, macro will copy the complete row from code used by user to Region Column then overwrite the splitted value in the Code used by Coder value same would be deleted from splitted column. If u need more explanation, please refer document in the my first post for this problem.I'm looking forward to the help. Thanks On Tuesday, July 17, 2012 8:32:12 PM UTC+5:30, bpascal123 wrote: It's difficult to understand and help as you've posted different version of your data, at first there you start with a certain number of columns and then you end up with different column like : Study Name Coder(SC) 1.Coder /Qer name for scorecards 2.Type Of Errors Final for Scorecard I also try to understand Don's code but honestly it makes me more confused. Try to post the exact thing that you want, that is the data as it comes and the data as you exactly want it -- -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com Book1.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Error Cleaning
Hi Pascal, Thanks for the suggestion but after cleaning the data, we have to perform other things in excel.Ialso don't have knowledge of Access,I'll try it. On Tuesday, July 17, 2012 1:03:29 PM UTC+5:30, bpascal123 wrote: Hi SG, If you have so many rows and need to have data sorted in a table way, Access might be a better solution than Excel. I understand you can have one and only Project number, one and only one person Name for a project and one to many error codes. I don't have strong Access skills but if you have thousand of rows for projects and any multiple for these for error codes Access should perform better than Excel. Pascal Baro bpascal...@gmail.com On Monday, July 16, 2012 9:42:48 AM UTC+1, SG wrote: Sorry for Replying late.Yes, i choose delimit choose comma for it.Problem is this that the data is in thousandsr rows manually it takes so much time.i have attached a file i hope it clears the doubts brfore cleaning the data after cleaning the data. On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote: Hi Experts, I need your help again.We extract the data from database each user got some errors on different projects.I need to clean the error data to get the count of errors.For this,i do text to column on error column.Then, in each column after error column, i take the value one by one copy paste the data below the original data.It's too time consuming as data may expand up to many columns each column has irregular no. of errors description.I have tried to explain best need a macro to make unique row of erros of all users.I have attached the excel file a document in which i have tried to explain.Looking forward for your help. Thanks in advance. -- -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Re: Error Cleaning
Hi Don, My question is that after running the macro why data from column A to C is repeating then the data from column A to E comes consolidated below it which actually i want.Sorry for asking so much questions.But till the time it's not clear to meI'm not able to implement it. On Monday, July 16, 2012 9:03:57 PM UTC+5:30, Don Guillett wrote: You might try expanding column C to find out that that is NOT true Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com *From:* SG sonal...@gmail.com *Sent:* Monday, July 16, 2012 10:26 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning Yes, if you see..from row 12 to row 40, the data is blank..can it be possible after running the macro...it would consolidated without such blanks in the data?? On Monday, July 16, 2012 8:31:55 PM UTC+5:30, Don Guillett wrote: Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com *From:* SG sonal...@gmail.com *Sent:* Monday, July 16, 2012 9:30 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning Hi Don, It's working perfectly.but one more problem is this i have shown only 3 columns in my sample file but in real ...i have atleast 23 columns last column is the Error column on which this macro will work...now this macro is consolidating the data at the end.Please have a look.For instance, i have increased the column no to 5. On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote: I wrote it to continue what you had already done. The TTC could be incorporated. Option explicit forces use of dim Look in vba help index for resize Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com *From:* SG sonal...@gmail.com *Sent:* Monday, July 16, 2012 8:49 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning Hi Don, Thanks for the help. I haven't run the macro.I'm a begineer to the macro.Can you please explain the option Explicit use of resize would i run this macro after text to column splitting? uillett wrote: Should do it Option Explicit Sub getlists() Dim lr As Long Dim i As Long Dim dr As Long Dim btc As Range lr = Cells(Rows.Count, 1).End(xlUp).Row - 1 Set btc = Cells(2, 1).Resize(lr, 2) For i = 2 To lr + 1 dr = Cells(Rows.Count, 1).End(xlUp).Row + 1 btc.Copy Cells(dr, 1) Cells(2, i + 1).Resize(lr).Copy Cells(dr, c) 'MsgBox i Next i Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub ‘’ Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com *From:* SG sonal...@gmail.com *Sent:* Monday, July 16, 2012 3:42 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Re: Error Cleaning Sorry for Replying late.Yes, i choose delimit choose comma for it.Problem is this that the data is in thousandsr rows manually it takes so much time.i have attached a file i hope it clears the doubts brfore cleaning the data after cleaning the data. On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote: Hi Experts, I need your help again.We extract the data from database each user got some errors on different projects.I need to clean the error data to get the count of errors.For this,i do text to column on error column.Then, in each column after error column, i take the value one by one copy paste the data below the original data.It's too time consuming as data may expand up to many columns each column has irregular no. of errors description.I have tried to explain best need a macro to make unique row of erros of all users.I have attached the excel file a document in which i have tried to explain.Looking forward for your help. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED
$$Excel-Macros$$ Re: Error Cleaning
Sorry for Replying late.Yes, i choose delimit choose comma for it.Problem is this that the data is in thousandsr rows manually it takes so much time.i have attached a file i hope it clears the doubts brfore cleaning the data after cleaning the data. On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote: Hi Experts, I need your help again.We extract the data from database each user got some errors on different projects.I need to clean the error data to get the count of errors.For this,i do text to column on error column.Then, in each column after error column, i take the value one by one copy paste the data below the original data.It's too time consuming as data may expand up to many columns each column has irregular no. of errors description.I have tried to explain best need a macro to make unique row of erros of all users.I have attached the excel file a document in which i have tried to explain.Looking forward for your help. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com Book4.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Re: Error Cleaning
Hi Don, It's working perfectly.but one more problem is this i have shown only 3 columns in my sample file but in real ...i have atleast 23 columns last column is the Error column on which this macro will work...now this macro is consolidating the data at the end.Please have a look.For instance, i have increased the column no to 5. On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote: I wrote it to continue what you had already done. The TTC could be incorporated. Option explicit forces use of dim Look in vba help index for resize Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com *From:* SG sonal...@gmail.com *Sent:* Monday, July 16, 2012 8:49 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning Hi Don, Thanks for the help. I haven't run the macro.I'm a begineer to the macro.Can you please explain the option Explicit use of resize would i run this macro after text to column splitting? uillett wrote: Should do it Option Explicit Sub getlists() Dim lr As Long Dim i As Long Dim dr As Long Dim btc As Range lr = Cells(Rows.Count, 1).End(xlUp).Row - 1 Set btc = Cells(2, 1).Resize(lr, 2) For i = 2 To lr + 1 dr = Cells(Rows.Count, 1).End(xlUp).Row + 1 btc.Copy Cells(dr, 1) Cells(2, i + 1).Resize(lr).Copy Cells(dr, c) 'MsgBox i Next i Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub ‘’ Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com *From:* SG sonal...@gmail.com *Sent:* Monday, July 16, 2012 3:42 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Re: Error Cleaning Sorry for Replying late.Yes, i choose delimit choose comma for it.Problem is this that the data is in thousandsr rows manually it takes so much time.i have attached a file i hope it clears the doubts brfore cleaning the data after cleaning the data. On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote: Hi Experts, I need your help again.We extract the data from database each user got some errors on different projects.I need to clean the error data to get the count of errors.For this,i do text to column on error column.Then, in each column after error column, i take the value one by one copy paste the data below the original data.It's too time consuming as data may expand up to many columns each column has irregular no. of errors description.I have tried to explain best need a macro to make unique row of erros of all users.I have attached the excel file a document in which i have tried to explain.Looking forward for your help. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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
Re: $$Excel-Macros$$ Re: Error Cleaning
Yes, if you see..from row 12 to row 40, the data is blank..can it be possible after running the macro...it would consolidated without such blanks in the data?? On Monday, July 16, 2012 8:31:55 PM UTC+5:30, Don Guillett wrote: Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com *From:* SG sonal...@gmail.com *Sent:* Monday, July 16, 2012 9:30 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning Hi Don, It's working perfectly.but one more problem is this i have shown only 3 columns in my sample file but in real ...i have atleast 23 columns last column is the Error column on which this macro will work...now this macro is consolidating the data at the end.Please have a look.For instance, i have increased the column no to 5. On Monday, July 16, 2012 7:32:46 PM UTC+5:30, Don Guillett wrote: I wrote it to continue what you had already done. The TTC could be incorporated. Option explicit forces use of dim Look in vba help index for resize Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com *From:* SG sonal...@gmail.com *Sent:* Monday, July 16, 2012 8:49 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Re: Error Cleaning Hi Don, Thanks for the help. I haven't run the macro.I'm a begineer to the macro.Can you please explain the option Explicit use of resize would i run this macro after text to column splitting? uillett wrote: Should do it Option Explicit Sub getlists() Dim lr As Long Dim i As Long Dim dr As Long Dim btc As Range lr = Cells(Rows.Count, 1).End(xlUp).Row - 1 Set btc = Cells(2, 1).Resize(lr, 2) For i = 2 To lr + 1 dr = Cells(Rows.Count, 1).End(xlUp).Row + 1 btc.Copy Cells(dr, 1) Cells(2, i + 1).Resize(lr).Copy Cells(dr, c) 'MsgBox i Next i Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub ‘’ Don Guillett Microsoft Excel Developer SalesAid Software dguille...@gmail.com *From:* SG sonal...@gmail.com *Sent:* Monday, July 16, 2012 3:42 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Re: Error Cleaning Sorry for Replying late.Yes, i choose delimit choose comma for it.Problem is this that the data is in thousandsr rows manually it takes so much time.i have attached a file i hope it clears the doubts brfore cleaning the data after cleaning the data. On Friday, July 13, 2012 5:33:35 PM UTC+5:30, SG wrote: Hi Experts, I need your help again.We extract the data from database each user got some errors on different projects.I need to clean the error data to get the count of errors.For this,i do text to column on error column.Then, in each column after error column, i take the value one by one copy paste the data below the original data.It's too time consuming as data may expand up to many columns each column has irregular no. of errors description.I have tried to explain best need a macro to make unique row of erros of all users.I have attached the excel file a document in which i have tried to explain.Looking forward for your help. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss
$$Excel-Macros$$ Heavy Files on Share drive
Hi Experts, I'm in crunch situation.Let me try to explain.We have some Excel files named-1,2 3 saved on network share of pune they are really very heavy like 40 MB each. What i have to do is to fetch the data of current week for eg. week-27.I have analysed them the trend of data is irregular i.e you have the Current week data in all the files.Each files takes so much of time to get open.Then, i put weeknum function in the file copy the data at my end.Please suggest any solution(if any macro) if it can be resolved. Thanks SG -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Heavy Files on Share drive
hi Noorain, thanks alot.I haven't tried this.Before trying, i would like to ask some queries.I'm not too good in macros...will this macro reduce the size of files placed at share drive or should i open all files on share drive then run this macro at my end??? Please clear my doubts on it. Thanks On Thursday, July 5, 2012 6:46:25 PM UTC+5:30, NOORAIN ANSARI wrote: Dear SG. Please try it.. Option Explicit Sub SHRINK_EXCEL_FILE_SIZE() Dim WSheet As Worksheet Dim CSheet As String 'New Worksheet Dim OSheet As String 'Old WorkSheet Dim Col As Long Dim ECol As Long 'Last Column Dim lRow As Long Dim BRow As Long 'Last Row Dim Pic As Object For Each WSheet In Worksheets WSheet.Activate 'Put the sheets in a variable to make it easy to go back and forth CSheet = WSheet.Name 'Rename the sheet to its name with _Delete at the end OSheet = CSheet _Delete WSheet.Name = OSheet 'Add a new sheet and call it the original sheets name Sheets.Add ActiveSheet.Name = CSheet Sheets(OSheet).Activate 'Find the bottom cell of data on each column and find the further row For Col = 1 To Columns.Count 'Find the actual last bottom row If Cells(Rows.Count, Col).End(xlUp).Row BRow Then BRow = Cells(Rows.Count, Col).End(xlUp).Row End If Next 'Find the end cell of data on each row that has data and find the furthest one For lRow = 1 To BRow 'Find the actual last right column If Cells(lRow, Columns.Count).End(xlToLeft). Column ECol Then ECol = Cells(lRow, Columns.Count).End(xlToLeft).Column End If Next 'Copy the REAL set of data Range(Cells(1, 1), Cells(BRow, ECol)).Copy Sheets(CSheet).Activate 'Paste Every Thing Range(A1).PasteSpecial xlPasteAll 'Paste Column Widths Range(A1).PasteSpecial xlPasteColumnWidths Sheets(OSheet).Activate For Each Pic In ActiveSheet.Pictures Pic.Copy Sheets(CSheet).Paste Sheets(CSheet).Pictures(Pic.Index).Top = Pic.Top Sheets(CSheet).Pictures(Pic.Index).Left = Pic.Left Next Pic Sheets(CSheet).Activate 'Reset the variable for the next sheet BRow = 0 ECol = 0 Next WSheet ' Since, Excel will automatically replace the sheet references for you on your formulas, ' the below part puts them back. ' This is done with a simple replace, replacing _Delete with nothing For Each WSheet In Worksheets WSheet.Activate Cells.Replace _Delete, Next WSheet 'Roll through the sheets and delete the original fat sheets For Each WSheet In Worksheets If Not Len(Replace(WSheet.Name, _Delete, )) = Len(WSheet.Name) Then Application.DisplayAlerts = False WSheet.Delete Application.DisplayAlerts = True End If Next End Sub http://www.excelitems.com/2010/11/shrink-reduce-excel-file-size.html -- Thanks regards, Noorain Ansari www.noorainansari.com www.excelmacroworld.blogspot.com On Thu, Jul 5, 2012 at 6:09 PM, SG sonal...@gmail.com wrote: Hi Experts, I'm in crunch situation.Let me try to explain.We have some Excel files named-1,2 3 saved on network share of pune they are really very heavy like 40 MB each. What i have to do is to fetch the data of current week for eg. week-27.I have analysed them the trend of data is irregular i.e you have the Current week data in all the files.Each files takes so much of time to get open.Then, i put weeknum function in the file copy the data at my end.Please suggest any solution(if any macro) if it can be resolved. Thanks SG -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members
Re: $$Excel-Macros$$ Text Cleaning
Hi Asa, your code really works exact for me.Thanks for the help but can u please explain why you have done this Character As String * 1. On Monday, June 4, 2012 9:14:07 PM UTC+5:30, Asa R. wrote: I re-read your query and I see you only wanted alphabet characters. I had this include numbers as well… for just alphabetical characters A-Z and a-z, here is the correction: Function CleanText(Text As String) As String Dim NewText As String, Character As String * 1, Position As Long For Position = 1 To Len(Text) Character = Mid(Text, Position, 1) If Character Like [A-z] Then NewText = NewText Character End If Next Position CleanText = NewText End Function By the way, welcome to the group! Asa *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Asa Rossoff *Sent:* Monday, June 04, 2012 8:38 AM *To:* excel-macros@googlegroups.com *Subject:* RE: $$Excel-Macros$$ Text Cleaning Hi Sonal, Another function for you: Function CleanText(Text As String) As String Dim NewText As String, Character As String * 1, Position As Long For Position = 1 To Len(Text) Character = Mid(Text, Position, 1) If Character Like [A-z] Or Character Like # Then NewText = NewText Character End If Next Position CleanText = NewText End Function You can use this as a UDF in a worksheet formula or call it from other macros. Asa *From:* excel-macros@googlegroups.com [ mailto:excel-macros@googlegroups.com excel-macros@googlegroups.com] *On Behalf Of *sonal gupta *Sent:* Monday, June 04, 2012 8:15 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Text Cleaning Hi, This is my first post in this group.I have learnt many things from this group.I'm finding difficulty in cleaning the range in which each cell has string like rahul;6e3-.I need a macro which will clean the special characters,spaces numbers leave the alphabets only. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to
Re: $$Excel-Macros$$ Text Cleaning
Hi david. that e was bymistake.I want to clean special characters spaces it's done by Asa's code. thanks for your concern. On Tuesday, June 5, 2012 6:11:41 AM UTC+5:30, David Grugeon wrote: Hi Sonal I hope you realise that if you apply any of the suggested solutions to your string rahul;6e3- you will get rahule not rahul. I hope this is what you want. If not we might need to see a greater selection of strings to see if there is a pattern (like - always cut them off at the first non-letter). Regards David Grugeon On 5 June 2012 01:28, sonal gupta sonal...@gmail.com wrote: Thanks ashish.I'll try it. On Monday, June 4, 2012 8:51:06 PM UTC+5:30, ashish wrote: Sub text_clean() Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Dim cell As Range Dim str As String, text1 As String For Each cell In Selection str = text1 = str = cell.Text For i = 1 To Len(str) If (Asc(Mid(str, i, 1)) 64 And Asc(Mid(str, i, 1)) 90) Or (Asc(Mid(str, i, 1)) 96 And Asc(Mid(str, i, 1)) 123) Then text1 = text1 Mid(str, i, 1) End If Next i cell.Value = text1 Next Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic End Sub On Mon, Jun 4, 2012 at 8:44 PM, sonal gupta sonal...@gmail.com wrote: Hi, This is my first post in this group.I have learnt many things from this group.I'm finding difficulty in cleaning the range in which each cell has string like rahul;6e3-.I need a macro which will clean the special characters,spaces numbers leave the alphabets only. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --**--** --** To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscribe@** googlegroups.com excel-macros%2bunsubscr...@googlegroups.com -- *Regards* * * *Ashish Koul* *http://www.excelvbamacros.com/* *http://www.accessvbamacros.com/* http://www.accessvbamacros.com/ P Before printing, think about the environment. -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- David Grugeon -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Text Cleaning
Rajan...Thanks for the help but still your code is returning numbers . On Monday, June 4, 2012 9:11:22 PM UTC+5:30, Rajan_Verma wrote: Ah , *Cod* is just a integer type *variable* , that will hold the *ACSII Code* or each character in string, * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *sonal *Sent:* 04 June 2012 9:04 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Text Cleaning again thanks but i know how to run a macro what i was asking is the explanation for COD that ypu have used in your code. On Monday, June 4, 2012 8:59:23 PM UTC+5:30, Rajan_Verma wrote: Ok.. Press ALT+F11 ALT + I M Paste this code there Use Function Worksheet Like : = RemoveSpecialCharacters(YourString) * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *sonal gupta *Sent:* 04 June 2012 8:58 *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Text Cleaning Thanks rajan, but i'm new to macros..can you please explain the use of COD in this. On Monday, June 4, 2012 8:51:52 PM UTC+5:30, Rajan_Verma wrote: Try this : Public Function RemoveSpecialCharacters(Shname As String) As String Dim Cod As Integer Dim ShN As String For i = 1 To Len(Shname) Cod = Asc(Mid(Shname, i, 1)) If (Cod 47 And Cod 58) Or (Cod 64 And Cod 91) Or (Cod 96 And Cod 123) Then ShN = ShN Mid(Shname, i, 1) End If Next RemoveSpecialCharacters = ShN End Function * * *Regards* *Rajan verma* *+91 7838100659 [IM-Gtalk]* *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *sonal gupta *Sent:* 04 June 2012 8:45 *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Text Cleaning Hi, This is my first post in this group.I have learnt many things from this group.I'm finding difficulty in cleaning the range in which each cell has string like rahul;6e3-.I need a macro which will clean the special characters,spaces numbers leave the alphabets only. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post