Thanks Mr. Paul No need to say Sorry. You helped me a lot. This macro is running perfectly now.
Thanks a lot once again. Regards Amit On Tue, Dec 4, 2012 at 6:04 PM, Paul Schreiner <schreiner_p...@att.net>wrote: > Amit, > > Sorry, > > Since I didn't know how large your data set would end up being, > I used a "Dictionary Object" to read the existing records. > then, I would check this list to determine if the record existed. > > Since I made the object "public", it didn't clear out when the macro > finished. > So in the next execution, it retained the previous values. > > (thought you ought to know MY mistake) > > I fixed the macro and "tuned it up" a little. > > hope it serves you well! > > > *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:* Amit Gandhi <silkyro...@gmail.com> > *To:* excel-macros@googlegroups.com > *Sent:* Mon, December 3, 2012 11:55:15 PM > > *Subject:* Re: $$Excel-Macros$$ MACRO FOR LIST CHECK AND ADD ROWS > > Thanks Mr. Paul > > This is what I require. May be I was unable to explain my requirement, but > you rightly understood and give me solution. > Thanks a lot. > > But this macro has some problems. > > 1. If I run this macro 1st time, it add the rows correctly. But when I run > this macro again, it again add those rows and create duplicity of records. > COLUMN A should have unique values always. > > 2. In my example I had copied 1st two values of TTlist in cell A6 & A7, > and this macro has copied down rest of the values from cell A8 onwards > rightly. BUT when I have copied any random values from TTlist in cell A6 & > A7, then macro hasn't checked the values and left 1st two values of TTlist > and copied down rest of the values. > > I have attached file for your ready reference. > > Pls provide solution to this problem as well. > > Thanks in advance. > > Regards > > Amit > > > > On Mon, Dec 3, 2012 at 6:16 PM, Paul Schreiner <schreiner_p...@att.net>wrote: > >> The problem is that you described your problem "backwards". >> >> You SAID that you wanted to compare the list in sheet "TT" >> to the values in the named range "TTlist" >> and copy the values from row 1 if it doesn't exist. >> >> ------------------ >> I "assumed" that you were going to be adding values to sheet "TT" >> and wanted to check to see if they existed in "TTlist". >> If they did NOT, then add the value and copy row 1. >> So, I gave you what you asked for. >> ------------------ >> >> Instead, from your example, you want to compare the values in the >> named range "TTlist" to the values in sheet "TT" >> and add any that do not already exist FROM "TTlist" TO the sheet. >> >> The attached file, macro Add_Records >> should perform this task. >> >> >> >> >> *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:* Amit Gandhi <silkyro...@gmail.com> >> *To:* excel-macros@googlegroups.com >> *Sent:* Sat, December 1, 2012 1:15:34 AM >> >> *Subject:* Re: $$Excel-Macros$$ MACRO FOR LIST CHECK AND ADD ROWS >> >> Hi Paul >> >> This is not working. I am trying to elaborate more step by step what I >> required. >> >> Step 1. Macro will check in sheet *"TT",* column A (A6 onwards) with * >> "TTlist"* for values. >> >> e.g. (a) it will check 1st value of "TTlist" i.e. "DPR/FIBC15544/12" in >> cell A6. >> (b) if this value already exists in A6, then no action and go to >> next cell A7. >> (c) if this value already exists in A7, then no action and go to >> next cell A8. >> This step goes on until BLANK cell in column A. >> >> Step 2. If a value of "TTlist" doesn't exist in column A, then it >> copies Row 1 and paste it at the end (say at row 8 in attached file), then >> it fill-in the value in cell A8, and search for next value. >> >> e.g. (a) 3rd value "DPR/FIBC15509/12" of "TTlist" doesn't exist in column >> A of sheet "TT". >> (b) Macro will copy Row 1 and paste it at Row 8. >> (c) Macro will copy above value of (a) to cell A8. >> >> Then macro will check for 4th value "DPR/FIBC15508/12" of "TTlist" in >> column A of sheet "TT" and so on till complete list has been checked. >> >> After running macro final solution will be as shown in sheet >> "Solution_Reqd". >> >> Hope I make you understand what I need. >> >> Pls help me here. >> >> Regards >> >> Amit >> >> >> On Fri, Nov 30, 2012 at 7:30 PM, Paul Schreiner >> <schreiner_p...@att.net>wrote: >> >>> Your description and sample is somewhat conflicting. >>> >>> You SAID: >>> 1. Macro will check in sheet "TT" column A (A6 onwards) with "TTlist" >>> for values. >>> 2. If a value exists in column A, it ignores and go to next cell. >>> 3. If a value doesn't exist in column A, then it copies Row 1 and paste >>> it at the end (say at row 8 in attached file), then it fill-in the value in >>> cell A8, and search for next value. >>> >>> Yet in your sample, the value in Column A DOES exist, >>> yet you copy line 1 to the indicated row. >>> >>> In the attached file, I did as you requested, not as your sample >>> >>> >>> *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:* Amit Gandhi <silkyro...@gmail.com> >>> *To:* excel-macros@googlegroups.com >>> *Sent:* Fri, November 30, 2012 2:31:51 AM >>> *Subject:* Re: $$Excel-Macros$$ MACRO FOR LIST CHECK AND ADD ROWS >>> >>> Hi Experts >>> >>> I have created a list in "Name Manager" named "TTlist" for values >>> shown in "LIST" sheet of attached file. I used to update this "TTlist" with >>> some other macro. >>> I have another sheet named "TT" in which 1st 5 rows are constant. I sued >>> to fill-in data from 6th row on-wards. >>> >>> I need a macro which will help me doing following. >>> >>> 1. Macro will check in sheet "TT" column A (A6 onwards) with "TTlist" >>> for values. >>> 2. If a value exists in column A, it ignores and go to next cell. >>> 3. If a value doesn't exist in column A, then it copies Row 1 and paste >>> it at the end (say at row 8 in attached file), then it fill-in the value in >>> cell A8, and search for next value. >>> >>> To summarise, i need solution as shown in sheet "SOLUTION_REQD". >>> >>> Pls help me here. >>> >>> Regards >>> >>> Amit >>> >>> >>> On Wed, Nov 28, 2012 at 12:30 PM, Amit Gandhi <silkyro...@gmail.com>wrote: >>> >>>> Hi Experts >>>> >>>> I have created a list in "Name Manager" named "TTlist" for values >>>> shown in "LIST" sheet of attached file. I used to update this "TTlist" with >>>> some other macro. >>>> I have another sheet named "TT" in which 1st 5 rows are constant. I >>>> sued to fill-in data from 6th row on-wards. >>>> >>>> I need a macro which will help me doing following. >>>> >>>> 1. Macro will check in sheet "TT" column A (A6 onwards) with "TTlist" >>>> for values. >>>> 2. If a value exists in column A, it ignores and go to next cell. >>>> 3. If a value doesn't exist in column A, then it copies Row 1 and paste >>>> it at the end (say at row 8 in attached file), then it fill-in the value in >>>> cell A8, and search for next value. >>>> >>>> To summarise, i need solution as shown in sheet "SOLUTION_REQD". >>>> >>>> Pls help me here. >>>> >>>> Regards >>>> >>>> Amit >>>> >>>> -- >>>> 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. >>>> >>>> >>>> >>> >>> >>> >>> -- >>> 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. >>> >>> >>> >>> -- >>> 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. >>> >>> >>> >> >> >> >> -- >> 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. >> >> >> >> -- >> 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. >> >> >> > > > > -- > 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. > > > > -- > 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. > > > -- 'Expecting the world to treat u fairly coz u r a good person is like expecting the lion not to attack u coz u r a vegetarian. Think about it.' Take care Amit -- 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.
