Re: $$Excel-Macros$$ Exl at expert level
Good Collections Maries.. and you also follow excel macro group on regular basis. -- Thanks regards, Noorain Ansari *http://noorainansari.com/* http://excelmacroworld.blogspot.com/ *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Sun, Mar 18, 2012 at 1:30 PM, sandhya jain sanya...@gmail.com wrote: Hi Expert. I want to learn exl at expert level. Help me. Regards, Sandhya -- 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 -- 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
Re: $$Excel-Macros$$ Query (Remove duplicate)
Go to data- remove duplicates Krishnan Sent on my BlackBerry® from Vodafone -Original Message- From: Karan Singh karan1...@gmail.com Sender: excel-macros@googlegroups.com Date: Mon, 19 Mar 2012 13:07:52 To: excel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Query (Remove duplicate) Dear All, I need you assistance, I want to remove duplicate value from column A to B. I'v attached the test file. Kindly help me in this. Kaяan http://www.facebook.com/singhkarann http://twitter.com/#%21/karan1237 https://plus.google.com/43524614789164919/posts -- 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 -- 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
Re: $$Excel-Macros$$ Exl at expert level
Really this link will pay more to each excel user. Excellent collection ! Divaker On Mon, Mar 19, 2012 at 12:32 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Good Collections Maries.. and you also follow excel macro group on regular basis. -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Sun, Mar 18, 2012 at 1:30 PM, sandhya jain sanya...@gmail.com wrote: Hi Expert. I want to learn exl at expert level. Help me. Regards, Sandhya -- 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 -- 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 -- 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
Re: $$Excel-Macros$$ Query (Remove duplicate)
Dear Karan, Please use below code.. Dim i, j As Integer Application.ScreenUpdating = False For i = 1 To Range(A65536).End(xlUp).Row For j = 1 To Range(B65536).End(xlUp).Row If Range(A i).Value = Range(B j).Value Then Range(B j).Delete Shift:=xlUp End If Next j Next i Application.ScreenUpdating = True On Mon, Mar 19, 2012 at 1:07 PM, Karan Singh karan1...@gmail.com wrote: Dear All, I need you assistance, I want to remove duplicate value from column A to B. I'v attached the test file. Kindly help me in this. Kaяan http://www.facebook.com/singhkarann http://twitter.com/#%21/karan1237 https://plus.google.com/43524614789164919/posts -- 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 -- Thanks regards, Noorain Ansari *http://noorainansari.com/* http://excelmacroworld.blogspot.com/ *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.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 Query(Karan).xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
$$Excel-Macros$$ Replace letters with numbers
I am using Excel 2007 I have a list of single letters A to F in column H2 to H26 These letters are are mixed but only one capital letter per cell I want to go to this list and replace each letter by its numerical value. So A becomes 1, B becomes 2, C becomes 3 and so on to the letter F Can you please help with the code. -- 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
Re: $$Excel-Macros$$ Most Helpful Member Feb'12 - Noorain Ansari
My heartly thanks to all of you. On Thu, Mar 15, 2012 at 4:19 AM, Ayush Jain jainayus...@gmail.com wrote: Hello Everyone, Noorain Ansari has been selected as 'Most Helpful Member' for the month of Feb'12 He has posted 156 posts in Feb'12 and helped many people through his expertise. He has been consistent contributor to this excel forum but has acheived this recognition for third time. *About Noorain Ansari :* [image: noorain.jpg] Noorain is a great poet and keep sharing his thoughts on different topics throgh cool poems. Professionally, he is a Excel aficionado and presently working as senior business analyst in One97 Communication Ltd. He is a MS excel - vba expert and keep sharing his thoughts with this forum. You can follow his blogs for Excel knowledge and cool poems : www.excel-macro.blogspot.com www.noorain-ansari.blogspot.com *Noorain, Many thanks for all your great support to group. Keep it up* *Thanks everyone for helping excel enthusiasts voluntarily !! Keep it up !! ** *Keep posting. Regards Ayush Jain Group Manager Microsoft MVP -- 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 -- Thanks regards, Noorain Ansari *http://noorainansari.com/* http://excelmacroworld.blogspot.com/ *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.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 noorain.jpg
Re: $$Excel-Macros$$ Replace letters with numbers
Hi, Try it, =CONCATENATE(IF(LEN(A1)=1,CODE(LEFT(A1,1))-64,),IF(LEN(A1)=2,CODE(MID(A1,2,1))-64,),IF(LEN(A1)=3,CODE(MID(A1,3,1))-64,),IF(LEN(A1)=4,CODE(MID(A1,4,1))-64,),IF(LEN(A1)=5,CODE(MID(A1,5,1))-64,),IF(LEN(A1)=6,CODE(MID(A1,6,1))-64,),IF(LEN(A1)=7,CODE(MID(A1,7,1))-64,),IF(LEN(A1)=8,CODE(MID(A1,8,1))-64,),IF(LEN(A1)=9,CODE(MID(A1,9,1))-64,),IF(LEN(A1)=10,CODE(MID(A1,10,1))-64,),IF(LEN(A1)=11,CODE(MID(A1,11,1))-64,),IF(LEN(A1)=12,CODE(MID(A1,12,1))-64,),IF(LEN(A1)=13,CODE(MID(A1,13,1))-64,),IF(LEN(A1)=14,CODE(MID(A1,14,1))-64,),IF(LEN(A1)=15,CODE(MID(A1,15,1))-64,),IF(LEN(A1)=16,CODE(MID(A1,16,1))-64,),IF(LEN(A1)=17,CODE(MID(A1,17,1))-64,),IF(LEN(A1)=18,CODE(MID(A1,18,1))-64,),IF(LEN(A1)=19,CODE(MID(A1,19,1))-64,),IF(LEN(A1)=20,CODE(MID(A1,20,1))-64,),IF(LEN(A1)=21,CODE(MID(A1,21,1))-64,),IF(LEN(A1)=22,CODE(MID(A1,22,1))-64,),IF(LEN(A1)=23,CODE(MID(A1,23,1))-64,),IF(LEN(A1)=24,CODE(MID(A1,24,1))-64,),IF(LEN(A1)=25,CODE(MID(A1,25,1))-64,),IF(LEN(A1)=26,CODE(MID(A1,26,1))-64,),IF(LEN(A1)=27,CODE(MID(A1,27,1))-64,)) Regards, MARIES. On Mon, Mar 19, 2012 at 12:02 PM, Brian brianfosterbl...@gmail.com wrote: I am using Excel 2007 I have a list of single letters A to F in column H2 to H26 These letters are are mixed but only one capital letter per cell I want to go to this list and replace each letter by its numerical value. So A becomes 1, B becomes 2, C becomes 3 and so on to the letter F Can you please help with the code. -- 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 -- 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
Re: $$Excel-Macros$$ Most Helpful Member Feb'12 - Noorain Ansari
Dear Brother, Heartily congratulation to you. Really feeling proud to be your friend. Keep it up. On Mon, Mar 19, 2012 at 1:45 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: My heartly thanks to all of you. On Thu, Mar 15, 2012 at 4:19 AM, Ayush Jain jainayus...@gmail.comwrote: Hello Everyone, Noorain Ansari has been selected as 'Most Helpful Member' for the month of Feb'12 He has posted 156 posts in Feb'12 and helped many people through his expertise. He has been consistent contributor to this excel forum but has acheived this recognition for third time. *About Noorain Ansari :* [image: noorain.jpg] Noorain is a great poet and keep sharing his thoughts on different topics throgh cool poems. Professionally, he is a Excel aficionado and presently working as senior business analyst in One97 Communication Ltd. He is a MS excel - vba expert and keep sharing his thoughts with this forum. You can follow his blogs for Excel knowledge and cool poems : www.excel-macro.blogspot.com www.noorain-ansari.blogspot.com *Noorain, Many thanks for all your great support to group. Keep it up* *Thanks everyone for helping excel enthusiasts voluntarily !! Keep it up !! ** *Keep posting. Regards Ayush Jain Group Manager Microsoft MVP -- 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 -- Thanks regards, Noorain Ansari *http://noorainansari.com/* http://excelmacroworld.blogspot.com/ *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.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 -- *Kalyan Chattopadhyay* *Executive Sales Coordinator* *R. S. H. Pvt. Ltd.* -- 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 noorain.jpg
Re: $$Excel-Macros$$ Replace letters with numbers
Hi, Also try below code for your quary, Sub numeric() Dim MyCell As Range For Each MyCell In Selection.Cells MyCell.Value = Asc(UCase(MyCell)) - 64 Next End Sub Code On Mon, Mar 19, 2012 at 12:15 PM, Maries talk2mar...@gmail.com wrote: Hi, Try it, =CONCATENATE(IF(LEN(A1)=1,CODE(LEFT(A1,1))-64,),IF(LEN(A1)=2,CODE(MID(A1,2,1))-64,),IF(LEN(A1)=3,CODE(MID(A1,3,1))-64,),IF(LEN(A1)=4,CODE(MID(A1,4,1))-64,),IF(LEN(A1)=5,CODE(MID(A1,5,1))-64,),IF(LEN(A1)=6,CODE(MID(A1,6,1))-64,),IF(LEN(A1)=7,CODE(MID(A1,7,1))-64,),IF(LEN(A1)=8,CODE(MID(A1,8,1))-64,),IF(LEN(A1)=9,CODE(MID(A1,9,1))-64,),IF(LEN(A1)=10,CODE(MID(A1,10,1))-64,),IF(LEN(A1)=11,CODE(MID(A1,11,1))-64,),IF(LEN(A1)=12,CODE(MID(A1,12,1))-64,),IF(LEN(A1)=13,CODE(MID(A1,13,1))-64,),IF(LEN(A1)=14,CODE(MID(A1,14,1))-64,),IF(LEN(A1)=15,CODE(MID(A1,15,1))-64,),IF(LEN(A1)=16,CODE(MID(A1,16,1))-64,),IF(LEN(A1)=17,CODE(MID(A1,17,1))-64,),IF(LEN(A1)=18,CODE(MID(A1,18,1))-64,),IF(LEN(A1)=19,CODE(MID(A1,19,1))-64,),IF(LEN(A1)=20,CODE(MID(A1,20,1))-64,),IF(LEN(A1)=21,CODE(MID(A1,21,1))-64,),IF(LEN(A1)=22,CODE(MID(A1,22,1))-64,),IF(LEN(A1)=23,CODE(MID(A1,23,1))-64,),IF(LEN(A1)=24,CODE(MID(A1,24,1))-64,),IF(LEN(A1)=25,CODE(MID(A1,25,1))-64,),IF(LEN(A1)=26,CODE(MID(A1,26,1))-64,),IF(LEN(A1)=27,CODE(MID(A1,27,1))-64,)) Regards, MARIES. On Mon, Mar 19, 2012 at 12:02 PM, Brian brianfosterbl...@gmail.comwrote: I am using Excel 2007 I have a list of single letters A to F in column H2 to H26 These letters are are mixed but only one capital letter per cell I want to go to this list and replace each letter by its numerical value. So A becomes 1, B becomes 2, C becomes 3 and so on to the letter F Can you please help with the code. -- 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 -- 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
$$Excel-Macros$$ VALUE ERROR PROBLEM
Hello Expert in the attached file, when i select from the drop down list in cell B8, i get a value error in column F (colored yellow). i want a formula that will give me the correct value in column F when the value in column D under periodic withdrawals is blank. kindly note however that if i select annual from the drop down in cell B8, the seems to be no problem, it only happens when the column D (cell d12 downwards) is blank. -- 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 FVOA.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Error while running macro
On Mar 15, 3:48 pm, Chidurala, Shrinivas shrinivas.chidur...@citi.com wrote: Hi I am facing the below error while running below macro. Please can you advise on the same. Thank you for your help ! Sub Convert_HTML_to_Excel() Application.ScreenUpdating = False Dim fldpth As String Dim fld, fil As Object Dim j, a As Long Dim ask, ask2 As Workbook 'fldpth = SelectFolder(Select Folder, ) fldpth = C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2) Set Fso = CreateObject(scripting.filesystemobject) Set fld = Fso.GetFolder(fldpth) For Each fil In fld.Files Set ask2 = Workbooks.Open(fil.path) ask2.Activate Rows(3:5).Select Selection.Insert Shift:=xlDown Range(C1).Value = Page : 1 Range(A1).Select Dim path As String path = (C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2)\ + ActiveSheet.Name) ActiveWorkbook.SaveAs FileName:=path, FileFormat:=51 ask2.Close Next fil Application.ScreenUpdating = True MsgBox Macro Successfully Run, , OK End Sub Regards, Shrinivas Picture (Device Independent Bitmap) 1.jpg 30KViewDownload -- 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
Re: $$Excel-Macros$$ Re: Error while running macro
can u write what error you got. or you can also send screen-shot of error Divaker On Mon, Mar 19, 2012 at 3:30 PM, SHREE chidurala.sh...@gmail.com wrote: On Mar 15, 3:48 pm, Chidurala, Shrinivas shrinivas.chidur...@citi.com wrote: Hi I am facing the below error while running below macro. Please can you advise on the same. Thank you for your help ! Sub Convert_HTML_to_Excel() Application.ScreenUpdating = False Dim fldpth As String Dim fld, fil As Object Dim j, a As Long Dim ask, ask2 As Workbook 'fldpth = SelectFolder(Select Folder, ) fldpth = C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2) Set Fso = CreateObject(scripting.filesystemobject) Set fld = Fso.GetFolder(fldpth) For Each fil In fld.Files Set ask2 = Workbooks.Open(fil.path) ask2.Activate Rows(3:5).Select Selection.Insert Shift:=xlDown Range(C1).Value = Page : 1 Range(A1).Select Dim path As String path = (C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2)\ + ActiveSheet.Name) ActiveWorkbook.SaveAs FileName:=path, FileFormat:=51 ask2.Close Next fil Application.ScreenUpdating = True MsgBox Macro Successfully Run, , OK End Sub Regards, Shrinivas Picture (Device Independent Bitmap) 1.jpg 30KViewDownload -- 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 -- 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
Re: $$Excel-Macros$$ VALUE ERROR PROBLEM
Hi Hilary, Enter the below formula in F13 and drag it down =IF(OR(A13=,D13=),,E13-D13)) HTH, Waheed On Mon, Mar 19, 2012 at 3:24 PM, hilary lomotey resp...@gmail.com wrote: Hello Expert in the attached file, when i select from the drop down list in cell B8, i get a value error in column F (colored yellow). i want a formula that will give me the correct value in column F when the value in column D under periodic withdrawals is blank. kindly note however that if i select annual from the drop down in cell B8, the seems to be no problem, it only happens when the column D (cell d12 downwards) is blank. -- 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 -- 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
$$Excel-Macros$$ Re: Most Helpful Member Feb'12 - Noorain Ansari
Hi Noorain, Congrats On Mar 15, 3:49 am, Ayush Jain jainayus...@gmail.com wrote: Hello Everyone, Noorain Ansari has been selected as 'Most Helpful Member' for the month of Feb'12 He has posted 156 posts in Feb'12 and helped many people through his expertise. He has been consistent contributor to this excel forum but has acheived this recognition for third time. *About Noorain Ansari :* [image: noorain.jpg] Noorain is a great poet and keep sharing his thoughts on different topics throgh cool poems. Professionally, he is a Excel aficionado and presently working as senior business analyst in One97 Communication Ltd. He is a MS excel - vba expert and keep sharing his thoughts with this forum. You can follow his blogs for Excel knowledge and cool poems :www.excel-macro.blogspot.comwww.noorain-ansari.blogspot.com *Noorain, Many thanks for all your great support to group. Keep it up* *Thanks everyone for helping excel enthusiasts voluntarily !! Keep it up !! ** *Keep posting. Regards Ayush Jain Group Manager Microsoft MVP noorain.jpg 2KViewDownload -- 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
Re: $$Excel-Macros$$ VALUE ERROR PROBLEM
Thanks both for your help, On Mon, Mar 19, 2012 at 10:24 AM, Shaik Waheed waheedb...@gmail.com wrote: Hi Hilary, Enter the below formula in F13 and drag it down =IF(OR(A13=,D13=),,E13-D13)) HTH, Waheed On Mon, Mar 19, 2012 at 3:24 PM, hilary lomotey resp...@gmail.com wrote: Hello Expert in the attached file, when i select from the drop down list in cell B8, i get a value error in column F (colored yellow). i want a formula that will give me the correct value in column F when the value in column D under periodic withdrawals is blank. kindly note however that if i select annual from the drop down in cell B8, the seems to be no problem, it only happens when the column D (cell d12 downwards) is blank. -- 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 -- 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 -- 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
$$Excel-Macros$$ last copied item in windows clipboard
Hi Expert, Can anyone give me a hint to findout last and second last copied item. Divaker -- 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
$$Excel-Macros$$ Re: last copied item in windows clipboard
Hello Pandey, If you are using excel 2007 or later , you can use Clipboard CTRL+C pressed twice. On Monday, March 19, 2012 1:53:08 PM UTC+2, Divaker Pandey wrote: Hi Expert, Can anyone give me a hint to findout last and second last copied item. Divaker -- 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
Re: $$Excel-Macros$$ VALUE ERROR PROBLEM
THANKS CHIEF On Mon, Mar 19, 2012 at 12:03 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Hi Hilary, You can also use =IFERROR(IF(A13=,,E13-D13),) or =IF(ISERROR(IF($A14=,,$E14-$D14)), ,IF($A14=,,$E14-$D14)) See attached sheet. On Mon, Mar 19, 2012 at 3:24 PM, hilary lomotey resp...@gmail.com wrote: Hello Expert in the attached file, when i select from the drop down list in cell B8, i get a value error in column F (colored yellow). i want a formula that will give me the correct value in column F when the value in column D under periodic withdrawals is blank. kindly note however that if i select annual from the drop down in cell B8, the seems to be no problem, it only happens when the column D (cell d12 downwards) is blank. -- 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 -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.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 -- 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
Re: $$Excel-Macros$$ Most Helpful Member Feb'12 - Noorain Ansari
Hi Noorain, Congratulations Thanks for helping us always. Regards, Kaushik -- 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
RE: $$Excel-Macros$$ Re: Error while running macro
Divaker, I am facing the below error while running macro. Please can you advise on the same. [cid:685132014@19032012-07C1] Regards, Shrinivas From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Divaker Pandey Sent: Monday, March 19, 2012 3:43 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Re: Error while running macro can u write what error you got. or you can also send screen-shot of error Divaker On Mon, Mar 19, 2012 at 3:30 PM, SHREE chidurala.sh...@gmail.commailto:chidurala.sh...@gmail.com wrote: On Mar 15, 3:48 pm, Chidurala, Shrinivas shrinivas.chidur...@citi.commailto:shrinivas.chidur...@citi.com wrote: Hi I am facing the below error while running below macro. Please can you advise on the same. Thank you for your help ! Sub Convert_HTML_to_Excel() Application.ScreenUpdating = False Dim fldpth As String Dim fld, fil As Object Dim j, a As Long Dim ask, ask2 As Workbook 'fldpth = SelectFolder(Select Folder, ) fldpth = C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2) Set Fso = CreateObject(scripting.filesystemobject) Set fld = Fso.GetFolder(fldpth) For Each fil In fld.Files Set ask2 = Workbooks.Open(fil.path) ask2.Activate Rows(3:5).Select Selection.Insert Shift:=xlDown Range(C1).Value = Page : 1 Range(A1).Select Dim path As String path = (C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2)\ + ActiveSheet.Name) ActiveWorkbook.SaveAs FileName:=path, FileFormat:=51 ask2.Close Next fil Application.ScreenUpdating = True MsgBox Macro Successfully Run, , OK End Sub Regards, Shrinivas Picture (Device Independent Bitmap) 1.jpg 30KViewDownload -- 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.commailto:excel-macros@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 -- 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 inline: Outlook.jpg
Re: $$Excel-Macros$$ Re: Error while running macro
If xl2003, toolsoptionssecurity remove Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Chidurala, Shrinivas Sent: Monday, March 19, 2012 9:21 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Re: Error while running macro Divaker, I am facing the below error while running macro. Please can you advise on the same. Regards, Shrinivas From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Divaker Pandey Sent: Monday, March 19, 2012 3:43 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Re: Error while running macro can u write what error you got. or you can also send screen-shot of error Divaker On Mon, Mar 19, 2012 at 3:30 PM, SHREE chidurala.sh...@gmail.com wrote: On Mar 15, 3:48 pm, Chidurala, Shrinivas shrinivas.chidur...@citi.com wrote: Hi I am facing the below error while running below macro. Please can you advise on the same. Thank you for your help ! Sub Convert_HTML_to_Excel() Application.ScreenUpdating = False Dim fldpth As String Dim fld, fil As Object Dim j, a As Long Dim ask, ask2 As Workbook 'fldpth = SelectFolder(Select Folder, ) fldpth = C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2) Set Fso = CreateObject(scripting.filesystemobject) Set fld = Fso.GetFolder(fldpth) For Each fil In fld.Files Set ask2 = Workbooks.Open(fil.path) ask2.Activate Rows(3:5).Select Selection.Insert Shift:=xlDown Range(C1).Value = Page : 1 Range(A1).Select Dim path As String path = (C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2)\ + ActiveSheet.Name) ActiveWorkbook.SaveAs FileName:=path, FileFormat:=51 ask2.Close Next fil Application.ScreenUpdating = True MsgBox Macro Successfully Run, , OK End Sub Regards, Shrinivas Picture (Device Independent Bitmap) 1.jpg 30KViewDownload -- 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 -- 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 -- 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 -- 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
Re: $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary Report
One possible formula to use vlookup if there is a match. See att =IF(ISNA((MATCH($J4,'Week One'!$A:$A,0))),0,VLOOKUP($J4,'Week One'!$A$2:$C$118,2,0))+IF(ISNA((MATCH($J4,'Week Two'!$A:$A,0))),0,VLOOKUP($J4,'Week Two'!$A$2:$C$118,2,0)) Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: John A. Smith Sent: Monday, March 19, 2012 10:00 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary Report I need help with a formula or macro to consolidate two weekly reports into a summary report. The individual weekly reports have the same column structure and some rows share a common project name. But every project name isn't necessarily on both reports, so matching them up is time consuming. Attached please find an example spreadsheet with two weekly tabs and the desired report. Thank you for your ongoing help. John -- 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 -- 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 Consolidate Two Similiar but Not Exact Reports.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary Report
Don, Thank you for such a quick response. I like the formula and will analyze it until I understand every part of it. The problem with this approach is the projects are actually names and therefore a clean list would have to be manually sorted to eliminate duplicates and then pasted into Column J to begin the lookups. Thank you for your continued help. John On Mon, Mar 19, 2012 at 11:55 AM, dguillett1 dguille...@gmail.com wrote: One possible formula to use vlookup if there is a match. See att =IF(ISNA((MATCH($J4,'Week One'!$A:$A,0))),0,VLOOKUP($J4,'Week One'!$A$2:$C$118,2,0))+IF(ISNA((MATCH($J4,'Week Two'!$A:$A,0))),0,VLOOKUP($J4,'Week Two'!$A$2:$C$118,2,0)) Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* John A. Smith johnasmit...@gmail.com *Sent:* Monday, March 19, 2012 10:00 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary Report I need help with a formula or macro to consolidate two weekly reports into a summary report. The individual weekly reports have the same column structure and some rows share a common project name. But every project name isn't necessarily on both reports, so matching them up is time consuming. Attached please find an example spreadsheet with two weekly tabs and the desired report. Thank you for your ongoing help. John -- 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 -- 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 -- 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
Re: $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary Report
Dear John, See attached sheet..hope it help to you. -- Thanks regards, Noorain Ansari *http://noorainansari.com/* http://excelmacroworld.blogspot.com/ *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Mon, Mar 19, 2012 at 8:30 PM, John A. Smith johnasmit...@gmail.comwrote: I need help with a formula or macro to consolidate two weekly reports into a summary report. The individual weekly reports have the same column structure and some rows share a common project name. But every project name isn't necessarily on both reports, so matching them up is time consuming. Attached please find an example spreadsheet with two weekly tabs and the desired report. Thank you for your ongoing help. John -- 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 -- 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 Consolidate_Two_Similiar_but_Not_Exact_Reports(Solved).xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Re: Error while running macro
Hi Don, Still i am facing the same problem. Regds Shrinivas On Mar 19, 7:35 pm, dguillett1 dguille...@gmail.com wrote: If xl2003, toolsoptionssecurity remove Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Chidurala, Shrinivas Sent: Monday, March 19, 2012 9:21 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Re: Error while running macro Divaker, I am facing the below error while running macro. Please can you advise on the same. Regards, Shrinivas ---- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Divaker Pandey Sent: Monday, March 19, 2012 3:43 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Re: Error while running macro can u write what error you got. or you can also send screen-shot of error Divaker On Mon, Mar 19, 2012 at 3:30 PM, SHREE chidurala.sh...@gmail.com wrote: On Mar 15, 3:48 pm, Chidurala, Shrinivas shrinivas.chidur...@citi.com wrote: Hi I am facing the below error while running below macro. Please can you advise on the same. Thank you for your help ! Sub Convert_HTML_to_Excel() Application.ScreenUpdating = False Dim fldpth As String Dim fld, fil As Object Dim j, a As Long Dim ask, ask2 As Workbook 'fldpth = SelectFolder(Select Folder, ) fldpth = C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2) Set Fso = CreateObject(scripting.filesystemobject) Set fld = Fso.GetFolder(fldpth) For Each fil In fld.Files Set ask2 = Workbooks.Open(fil.path) ask2.Activate Rows(3:5).Select Selection.Insert Shift:=xlDown Range(C1).Value = Page : 1 Range(A1).Select Dim path As String path = (C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2)\ + ActiveSheet.Name) ActiveWorkbook.SaveAs FileName:=path, FileFormat:=51 ask2.Close Next fil Application.ScreenUpdating = True MsgBox Macro Successfully Run, , OK End Sub Regards, Shrinivas Picture (Device Independent Bitmap) 1.jpg 30KViewDownload -- 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 -- 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 -- 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 Outlook.jpg 33KViewDownload -- FORUM RULES (986+ members already
RE: $$Excel-Macros$$ Re: Error while running macro
Hi Noorain Sir, If I write xlExcel7 instead of 51, the macro is running but the some part of disclaimer in the report are missing in output and if I ok in below screen shot then it is running properly. ActiveWorkbook.SaveAs FileName:=path, FileFormat:=51 Please can you advise on the same. Regards, Shrinivas From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: Monday, March 19, 2012 9:51 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Re: Error while running macro Dear Shrinivas, Check below link http://office.microsoft.com/en-us/excel-help/use-office-excel-2010-with-earlier-versions-of-excel-HA010342994.aspx On Mon, Mar 19, 2012 at 7:51 PM, Chidurala, Shrinivas shrinivas.chidur...@citi.commailto:shrinivas.chidur...@citi.com wrote: Divaker, I am facing the below error while running macro. Please can you advise on the same. [cid:541262416@19032012-07C8] Regards, Shrinivas From: excel-macros@googlegroups.commailto:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.commailto:excel-macros@googlegroups.com] On Behalf Of Divaker Pandey Sent: Monday, March 19, 2012 3:43 PM To: excel-macros@googlegroups.commailto:excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Re: Error while running macro can u write what error you got. or you can also send screen-shot of error Divaker On Mon, Mar 19, 2012 at 3:30 PM, SHREE chidurala.sh...@gmail.commailto:chidurala.sh...@gmail.com wrote: On Mar 15, 3:48 pm, Chidurala, Shrinivas shrinivas.chidur...@citi.commailto:shrinivas.chidur...@citi.com wrote: Hi I am facing the below error while running below macro. Please can you advise on the same. Thank you for your help ! Sub Convert_HTML_to_Excel() Application.ScreenUpdating = False Dim fldpth As String Dim fld, fil As Object Dim j, a As Long Dim ask, ask2 As Workbook 'fldpth = SelectFolder(Select Folder, ) fldpth = C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2) Set Fso = CreateObject(scripting.filesystemobject) Set fld = Fso.GetFolder(fldpth) For Each fil In fld.Files Set ask2 = Workbooks.Open(fil.path) ask2.Activate Rows(3:5).Select Selection.Insert Shift:=xlDown Range(C1).Value = Page : 1 Range(A1).Select Dim path As String path = (C:\Documents and Settings\sc52912\Desktop\New Folder\New Folder (2)\ + ActiveSheet.Name) ActiveWorkbook.SaveAs FileName:=path, FileFormat:=51 ask2.Close Next fil Application.ScreenUpdating = True MsgBox Macro Successfully Run, , OK End Sub Regards, Shrinivas Picture (Device Independent Bitmap) 1.jpg 30KViewDownload -- 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.commailto:excel-macros@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.commailto:excel-macros@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.
Re: $$Excel-Macros$$ Exl at expert level
Hi Maries, Nice Collections... *Best Regards,* *Venkat * *Chennai* *My Linked in profile http://in.linkedin.com/pub/venkatesan-c/21/492/a71* On Sun, Mar 18, 2012 at 1:53 PM, Maries talk2mar...@gmail.com wrote: Hi, *Check below Weblinks.* *MVP's* *Web Link* Alan Beban http://home.pacbell.net/bebanhttp://home.pacbell.net/beban Allen Wyatt http://excel.tips.net http://excel.tips.net/ Andrew Engwirda http://www.andrewsexceltips.net/ http://www.andrewsexceltips.net/ Andy Pope http://www.andypope.info/ http://www.andypope.info/ Ashish Koul http://akoul.blogspot.com http://akoul.blogspot.com/ Ashish Mathur http://www.ashishmathur.com http://www.ashishmathur.com/ Ayush Jain http://www.discussexcel.com http://www.discussexcel.com/ Bernd Held http://members.aol.com/Machero http://members.aol.com/Machero Charley Kyd http://www.exceluser.com/ http://www.exceluser.com/ Chip Pearson http://www.cpearson.com http://www.cpearson.com/ Daniel Ferry http://www.excelhero.com/ http://www.excelhero.com/ Daniel Josserand http://dj.joss.free.fr/ http://dj.joss.free.fr/ David McRitchie http://www.mvps.org/dmcritchie/excel/excel.htmhttp://www.mvps.org/dmcritchie/excel/excel.htm Debra Dalgleish http://www.contextures.com/tiptech.htmlhttp://www.contextures.com/tiptech.html Dick Kusleika http://www.dailydoseofexcel.com/http://www.dailydoseofexcel.com/ Dilip Pandey https://mvp.support.microsoft.com/profile/Dilip.Kumar.Pandeyhttps://mvp.support.microsoft.com/profile/Dilip.Kumar.Pandey Ed Ferrero http://www.edferrero.com http://www.edferrero.com/ Fernando Cinquegrani http://www.prodomosua.eu http://www.prodomosua.eu/ Frank Isaacs http://www.vbapro.com http://www.vbapro.com/ Jake Marx http://www.longhead.com/ http://www.longhead.com/ Jan Karel Pieterse http://www.jkp-ads.com http://www.jkp-ads.com/ John Lacher http://www.lacher.com http://www.lacher.com/ John Walkenbach http://spreadsheetpage.com http://spreadsheetpage.com/ Jon Peltier http://www.peltiertech.com/ http://www.peltiertech.com/ Jorge Rodrigues http://exceler.blogspot.com http://exceler.blogspot.com/ Ken Puls http://www.excelguru.ca http://www.excelguru.ca/ Laurent Longre http://xcell05.free.fr http://xcell05.free.fr/ Masaru Kaji http://puremis.net/excel/ http://puremis.net/excel/ Mike Alexander http://www.datapigtechnologies.com http://www.datapigtechnologies.com/ Monika Weber http://www.jumper.ch http://www.jumper.ch/ Nick Hodge http://www.nickhodge.co.uk http://www.nickhodge.co.uk/ Nick Vivian http://ExcelExperts.com http://excelexperts.com/ Orlando Magalhães Filho http://orlando.mvps.org http://orlando.mvps.org/ Patrick Molloy http://www.xl-expert.com http://www.xl-expert.com/ Rob Bovey http://www.appspro.com http://www.appspro.com/ Robert Rosenberg http://www.r-cor.com http://www.r-cor.com/ Rodney Powell http://www.beyondtechnology.com http://www.beyondtechnology.com/ Ron de Bruin http://www.rondebruin.nl http://www.rondebruin.nl/ Somkiat Foongkiat http://www.ExcelExpertTraining.comhttp://www.excelexperttraining.com/ Stephen Bullen http://www.oaltd.co.uk http://www.oaltd.co.uk/ Thomas Ramel http://users.quick-line.ch/ramel/ http://users.quick-line.ch/ramel/ Ture Magnusson http://www.turedata.se http://www.turedata.se/ Tushar Mehta http://www.tushar-mehta.com http://www.tushar-mehta.com/ Zack Barresse http://www.vbaexpress.com http://www.vbaexpress.com/ Regards, MARIES. On Sun, Mar 18, 2012 at 12:00 PM, sandhya jain sanya...@gmail.com wrote: Hi Expert. I want to learn exl at expert level. Help me. Regards, Sandhya -- 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 -- 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)
Re: $$Excel-Macros$$ Query (Remove duplicate)
Hi Karan, Try Advanced filter and select Unique records..And you can Copy and paste it in New Column.. [image: Inline image 1] *Best Regards,* *Venkat * *Chennai* *My Linked in profile http://in.linkedin.com/pub/venkatesan-c/21/492/a71* On Mon, Mar 19, 2012 at 1:07 PM, Karan Singh karan1...@gmail.com wrote: Dear All, I need you assistance, I want to remove duplicate value from column A to B. I'v attached the test file. Kindly help me in this. Kaяan http://www.facebook.com/singhkarann http://twitter.com/#%21/karan1237 https://plus.google.com/43524614789164919/posts -- 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 -- * * * * -- 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 image.png
Re: $$Excel-Macros$$ last copied item in windows clipboard
Hi Divakar, Copy data twice You will get Clipboard..You Can see Last Second Copied Data. [image: Inline image 1] *Best Regards,* *Venkat * *Chennai* *My Linked in profile http://in.linkedin.com/pub/venkatesan-c/21/492/a71* On Mon, Mar 19, 2012 at 5:23 PM, Divaker Pandey divake...@gmail.com wrote: Hi Expert, Can anyone give me a hint to findout last and second last copied item. Divaker -- 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 -- * * * * -- 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 image.png
Re: $$Excel-Macros$$ copy paste in reverse order
Hi Amit, Try Transpose Option on Paste special..if Helps.. *Best Regards,* *Venkat * *Chennai* *My Linked in profile http://in.linkedin.com/pub/venkatesan-c/21/492/a71* On Mon, Mar 19, 2012 at 6:02 PM, Amit Desai (MERU) amit.de...@merucabs.comwrote: Hi, ** ** Is there any formula to copy data from say 2 rows say 1 2 paste them in reverse order. Data of 1st row will move to 2nd of 2nd row to first.. ** ** Best Regards, Amit ** ** -- Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is made that e-mail communications are timely, secure or free from computer virus or other defect. -- 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 -- * * * * -- 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
RE: $$Excel-Macros$$ copy paste in reverse order
See the attached File =INDEX($A$1:$A$20,COUNTA($A$1:$A$20)-ROW()+1,1) Rajan. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Amit Desai (MERU) Sent: Mar/Mon/2012 06:03 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ copy paste in reverse order Hi, Is there any formula to copy data from say 2 rows say 1 2 paste them in reverse order. Data of 1st row will move to 2nd of 2nd row to first.. Best Regards, Amit _ Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is made that e-mail communications are timely, secure or free from computer virus or other defect. -- 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 -- 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 REverse.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ Replace letters with numbers
=CODE(UPPER(A1))-64 A1 contains your letter Rajan From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Maries Sent: Mar/Mon/2012 01:46 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Replace letters with numbers Hi, Try it, =CONCATENATE(IF(LEN(A1)=1,CODE(LEFT(A1,1))-64,),IF(LEN(A1)=2,CODE(MID(A1 ,2,1))-64,),IF(LEN(A1)=3,CODE(MID(A1,3,1))-64,),IF(LEN(A1)=4,CODE(MID( A1,4,1))-64,),IF(LEN(A1)=5,CODE(MID(A1,5,1))-64,),IF(LEN(A1)=6,CODE(MI D(A1,6,1))-64,),IF(LEN(A1)=7,CODE(MID(A1,7,1))-64,),IF(LEN(A1)=8,CODE( MID(A1,8,1))-64,),IF(LEN(A1)=9,CODE(MID(A1,9,1))-64,),IF(LEN(A1)=10,CO DE(MID(A1,10,1))-64,),IF(LEN(A1)=11,CODE(MID(A1,11,1))-64,),IF(LEN(A1) =12,CODE(MID(A1,12,1))-64,),IF(LEN(A1)=13,CODE(MID(A1,13,1))-64,),IF(LE N(A1)=14,CODE(MID(A1,14,1))-64,),IF(LEN(A1)=15,CODE(MID(A1,15,1))-64,) ,IF(LEN(A1)=16,CODE(MID(A1,16,1))-64,),IF(LEN(A1)=17,CODE(MID(A1,17,1))- 64,),IF(LEN(A1)=18,CODE(MID(A1,18,1))-64,),IF(LEN(A1)=19,CODE(MID(A1,1 9,1))-64,),IF(LEN(A1)=20,CODE(MID(A1,20,1))-64,),IF(LEN(A1)=21,CODE(MI D(A1,21,1))-64,),IF(LEN(A1)=22,CODE(MID(A1,22,1))-64,),IF(LEN(A1)=23,C ODE(MID(A1,23,1))-64,),IF(LEN(A1)=24,CODE(MID(A1,24,1))-64,),IF(LEN(A1) =25,CODE(MID(A1,25,1))-64,),IF(LEN(A1)=26,CODE(MID(A1,26,1))-64,),IF(L EN(A1)=27,CODE(MID(A1,27,1))-64,)) Regards, MARIES. On Mon, Mar 19, 2012 at 12:02 PM, Brian brianfosterbl...@gmail.com wrote: I am using Excel 2007 I have a list of single letters A to F in column H2 to H26 These letters are are mixed but only one capital letter per cell I want to go to this list and replace each letter by its numerical value. So A becomes 1, B becomes 2, C becomes 3 and so on to the letter F Can you please help with the code. -- 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 -- 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 -- 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
RE: $$Excel-Macros$$ Replace letters with numbers
=CODE(UPPER(A1))-64 A1 contains your letter Rajan From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Maries Sent: Mar/Mon/2012 01:46 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Replace letters with numbers Hi, Try it, =CONCATENATE(IF(LEN(A1)=1,CODE(LEFT(A1,1))-64,),IF(LEN(A1)=2,CODE(MID(A1 ,2,1))-64,),IF(LEN(A1)=3,CODE(MID(A1,3,1))-64,),IF(LEN(A1)=4,CODE(MID( A1,4,1))-64,),IF(LEN(A1)=5,CODE(MID(A1,5,1))-64,),IF(LEN(A1)=6,CODE(MI D(A1,6,1))-64,),IF(LEN(A1)=7,CODE(MID(A1,7,1))-64,),IF(LEN(A1)=8,CODE( MID(A1,8,1))-64,),IF(LEN(A1)=9,CODE(MID(A1,9,1))-64,),IF(LEN(A1)=10,CO DE(MID(A1,10,1))-64,),IF(LEN(A1)=11,CODE(MID(A1,11,1))-64,),IF(LEN(A1) =12,CODE(MID(A1,12,1))-64,),IF(LEN(A1)=13,CODE(MID(A1,13,1))-64,),IF(LE N(A1)=14,CODE(MID(A1,14,1))-64,),IF(LEN(A1)=15,CODE(MID(A1,15,1))-64,) ,IF(LEN(A1)=16,CODE(MID(A1,16,1))-64,),IF(LEN(A1)=17,CODE(MID(A1,17,1))- 64,),IF(LEN(A1)=18,CODE(MID(A1,18,1))-64,),IF(LEN(A1)=19,CODE(MID(A1,1 9,1))-64,),IF(LEN(A1)=20,CODE(MID(A1,20,1))-64,),IF(LEN(A1)=21,CODE(MI D(A1,21,1))-64,),IF(LEN(A1)=22,CODE(MID(A1,22,1))-64,),IF(LEN(A1)=23,C ODE(MID(A1,23,1))-64,),IF(LEN(A1)=24,CODE(MID(A1,24,1))-64,),IF(LEN(A1) =25,CODE(MID(A1,25,1))-64,),IF(LEN(A1)=26,CODE(MID(A1,26,1))-64,),IF(L EN(A1)=27,CODE(MID(A1,27,1))-64,)) Regards, MARIES. On Mon, Mar 19, 2012 at 12:02 PM, Brian brianfosterbl...@gmail.com wrote: I am using Excel 2007 I have a list of single letters A to F in column H2 to H26 These letters are are mixed but only one capital letter per cell I want to go to this list and replace each letter by its numerical value. So A becomes 1, B becomes 2, C becomes 3 and so on to the letter F Can you please help with the code. -- 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 -- 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 -- 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
Re: $$Excel-Macros$$ Rank Formula required
Hi Pankaj PFA, Try : =RANK(E2,E$2:E$9)+SUMPRODUCT(--(E$2:E$9=E2),--(A2A$2:A$9)) HTH Mahesh On Mon, Mar 19, 2012 at 3:27 AM, dguillett1 dguille...@gmail.com wrote: Shorter. Also enter using CSE (ctrl+shift+enter) =COUNTIF($F$2:$F$9,F2)+**SUM(IF(F2=$F$2:F2,1,0)) Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com -Original Message- From: Secret Shot Sent: Sunday, March 18, 2012 4:02 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Rank Formula required Dear Expert, I want any formula to find Rank in Excel but that rank should be Unique.. if the value are same for ranking its should check second pereority. attach is the file for understanding the problme. Kindly help pls. i have tried with rank formula but it dosent work. -- Pankaj Pandey Bhopal -- 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 -- 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 -- 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 Ranking required_19032012.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary Report
Don, That was what I was hoping for, but when I click the button, it dulpicates some of the entries. Is it me or the macro? Thank you. John On Mon, Mar 19, 2012 at 1:30 PM, dguillett1 dguille...@gmail.com wrote: See attached which works for as many sheets as you may have after the summary sheet. It uses a macro to make a unique list and then get the data for the unique list for each sheet. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* John A. Smith johnasmit...@gmail.com *Sent:* Monday, March 19, 2012 11:09 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary Report Don, Thank you for such a quick response. I like the formula and will analyze it until I understand every part of it. The problem with this approach is the projects are actually names and therefore a clean list would have to be manually sorted to eliminate duplicates and then pasted into Column J to begin the lookups. Thank you for your continued help. John On Mon, Mar 19, 2012 at 11:55 AM, dguillett1 dguille...@gmail.com wrote: One possible formula to use vlookup if there is a match. See att =IF(ISNA((MATCH($J4,'Week One'!$A:$A,0))),0,VLOOKUP($J4,'Week One'!$A$2:$C$118,2,0))+IF(ISNA((MATCH($J4,'Week Two'!$A:$A,0))),0,VLOOKUP($J4,'Week Two'!$A$2:$C$118,2,0)) Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* John A. Smith johnasmit...@gmail.com *Sent:* Monday, March 19, 2012 10:00 AM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Consolidate Two Weekly Reports into a Summary Report I need help with a formula or macro to consolidate two weekly reports into a summary report. The individual weekly reports have the same column structure and some rows share a common project name. But every project name isn't necessarily on both reports, so matching them up is time consuming. Attached please find an example spreadsheet with two weekly tabs and the desired report. Thank you for your ongoing help. John -- 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 -- 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 -- 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 -- 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
RE: $$Excel-Macros$$ last copied item in windows clipboard
Hi Divaker, If you mean the last two things copied within Excel, and you want to identify them from VBA, Take a look at http://www.mrexcel.com/forum/showthread.php?t=167292 Keep in mind that the commandbar containing the Office Clipboard's name varies by Excel version. Here's how I think it goes: In Office 2000 reference the Clipboard commandbar. In Office XP through perhaps I guess Office 2003, reference the Task Pane commandbar. In perhaps Office 2007 through 2010 or 2011 (noted in Office 2010), reference the Office Clipboard commandbar. You might also try using the macro recorder to work with those items and see what you get. I have not tried this, but I saw the tip Each of the MicrosoftR Office XP applications provides a Clipboard toolbar that you can use to store up to 12 items. In addition, you can work with the items stored by the Clipboard toolbar programmatically through the command bar object model. The last item copied to the Clipboard toolbar is the one stored in the Windows Clipboard. at http://msdn.microsoft.com/en-us/library/aa189708%28v=office.10%29.aspx, but without further elaboration. Additional web searches might find you other/better examples. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Divaker Pandey Sent: Monday, March 19, 2012 4:53 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ last copied item in windows clipboard Hi Expert, Can anyone give me a hint to findout last and second last copied item. Divaker -- 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 -- 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
RE: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping
I think I misunderstood your need. I thought you wanted the name to stay
with the data, allowing you to insert columns and have the name still refer
to the same data. This method does that.
If you want the name to stick to the column/range reference without regard
to inserted columns (always column C no matter what, i.e.), and you will
only use the name from VBA, then you could use a string variable:
Dim MyColumnAddress As String, MyColumn As Range
MyColumnAddress = C:C
Set MyColumn = Range(MyColumnAddress) ' After the Set command the range
WILL be effected by inserted columns; so re-set just before use if columns
could have been added/deleting since the last Set command.
If you want to use a defined name, this will always refer to column C:
Names.Add MyColumn,=INDIRECT(C:C)
Hope this helps!
Asa
-Original Message-
From: tangledweb [mailto:domainqu...@gmail.com]
Sent: Monday, March 19, 2012 11:58 AM
To: Asa Rossoff
Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using
column names for looping
thanks, sure wish there was a way to have it be position relative so
could insert new columns without affecting the name on column C but
does not appear to be the case
On Mar 18, 5:19 pm, Asa Rossoff mailto:a...@lovetour.info
a...@lovetour.info wrote:
You can name the entire column as well;
range(C:C).name=mycolumn ' create workbook-level name
then reference it as:
range(mycolumn)
also works as/in a cell formula:
{ =mycolumn }
=match(findthis,mycolumn,0)
evaluate(mycolumn) ' formula eval from vba
[mycolumn] ' formula eval from vba
Asa
-Original Message-
From: mailto:excel-macros@googlegroups.com
excel-macros@googlegroups.com [ mailto:excel-macros@googlegroups.com
mailto:excel-macros@googlegroups.com]
On Behalf Of dguillett1
Sent: Sunday, March 18, 2012 1:26 PM
To: mailto:excel-macros@googlegroups.com excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using
column names for looping
the simplest is
sub nameit
cells(7,4).name=whateveryouwanttonameit
end sub
=whaeveryounamedit
Don Guillett
Microsoft MVP Excel
SalesAid Software
mailto:dguille...@gmail.com mailto:dguille...@gmail.com
mailto:dguille...@gmail.com dguille...@gmail.com
-Original Message-
From: tangledweb
Sent: Sunday, March 18, 2012 5:22 AM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Need help with VBAfor Excel naming and using
column names for looping
I am trying to understand how to name a column of data and be able to
reference the data by that name. Ideally I could set the name
programatically but if it needs to be done through the ribbon command
that is acceptable.
Ideally inserting a new column before the named column will not affect
that column's name.
The simplest example of
=
Sub Macro1()
Dim x as long
Const ExitValue = 4
x = Cells(6, ExitValue).Value
Cells(7, ExitValue) = x
End Sub
===
Functions but for some reason I can not see the value of x in the
immediate window. I just get nothing back if I print x.
But is this really the best wah to do this? It seems inelegant at
best. And certainly if I insert a new column 3 I need to change this
code to
Const ExitValue = 5
--
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
mailto:excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com
mailto:excel-macros@googlegroups.com excel-macros@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
$$Excel-Macros$$ Macro to hide rows, print, unhide rows runs very slow - how to speed up?
I have a macro that will hide unused rows in a spreadsheet, print the active sheet, then unhide the rows. Spreadsheet is from cell A1 to P175. Base info is recorded in various cells on rows 1 to 4. Cells 5 to 16 display output. The main Data input starts in cell A18 to G 167. A18 is a from date, B18 to date, with comments in column P. Because I have 150 lines that could be used, I want to hide unused rows when printing to reduce the pages. Rows 168 to 175 display results and are not hidden. Here is the macro I have. It works but runs very slowly. I'm lookig for ways to speed it up. Option Explicit Sub PrintNonBlankColA() Dim RowCrnt As Integer Dim RowLast As Integer ' Note: This operates on the active worksheet Application.ScreenUpdating = False RowLast = Cells.SpecialCells(xlCellTypeLastCell).Row ' Hide all rows with a used cell and column A empty For RowCrnt = 18 To RowLast If IsEmpty(Cells(RowCrnt, A)) Then Range(RowCrnt : RowCrnt).EntireRow.Hidden = True End If Next Range(A1:Q175).Select ActiveSheet.PageSetup.PrintArea = $A$1:$Q$175 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False Rows(17:168).Select Selection.EntireRow.Hidden = False Range(B2).Select End Sub any suggestions to speed it up appreciated. Mel -- 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
RE: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping
Hi Howard, No worries :) Ask as many questions as needed. Best to send your replies to the list though, so you get the benefit of other replies (if only because I may not have time to reply myself). This also allows other VBA learners can benefit from the conversation. A defined name (either via range.name=mycolumn or names.add) that refers to a range sticks to the same data/cells even when rows and cells are inserted and deleted because Excel updates those name references automatically whenever those events occur. This is just as Excel does the same for references in cell formulae when insert and deletes occur. Usually if you are referring to ranges in VBA you do not use defined names, but just use range variables. Use defined names when the names need to be used from cell formulae, or as one method of retaining those names between sessions (file close, reopen). If you use only a range variable, and column C contains a certain field of data -- say, Birthday -- then you or the user inserts a column before C to add another field -- say, Anniversary... And you use code similar to the following: Public Birthday As Range Sub Workbook_Open() Set Birthday = Range(C:C) 'Column gets inserted here with heading Anniversary MsgBox Birthday.Cells(1,1).Value ' Display the first cell value (column heading) End Sub ...open the file, allowing the above event to execute. Message should say Birthday. ...then close the file... ...reopen...message will say Anniversary. The problem with the above is that although the Range variable will adapt correctly when a column is inserted and still refer to the Birthday column, every time the file is opened it is set explicitly to refer to column C again. A defined name is a simple solution since it is saved between sessions and automatically adapts. The next simplest solution that comes to mind, and is actually even smarter (adapts if user just changes the text of the column heading without inserting or deleting columns) is to use range.find or worksheetfunction.match to find the column with the correct heading, and use that. For example: Set Birthday = Rows(1).Find(Birthday).EntireColumn ...or: Set Birthday = Sheets(MySheet).UsedRange.Rows(1).Find(Birthday, LookIn:=xlValues).EntireColumn ...or: Set Birthday = Columns(WorksheetFunction.Match(Birthday, Range(1:1), 0)) ...or: With Sheets(MySheet).UsedRange Set Birthday = .Columns(WorksheetFunction.Match(Birthday, .Rows(1), 0)) ' Resulting range will be the used part of the Birthday column, e.g. C1:C101 End With The above methods and their variants will always find the column with the given heading, so they are very adaptable and will find the right column after closing/reopening the file or inserting/deleting columns, or changing column headings. Asa -Original Message- From: Domain Admin [mailto:domainqu...@gmail.com] Sent: Monday, March 19, 2012 3:23 PM To: Asa Rossoff Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping You are a very helpful fellow. You were right the first time. I just did not understand that from your answer (I am still reading Excel VBA Programming for Dummies). This expression you sent before range(C:C).name=mycolumn I assumed bound mycolumn name to column C no matter what was in column C and if you inserted a new column before C that became column C then mycolumn would be bound to that. And I do not understand why that is not the case from reading this but I take your word for it. In your new examples much confusion. What is the value of a defined name? Is the other method where you say if only using from VBA equivalent to this Range(C:C).Name = mycolumn and then use mycolumn as the data reference for the cells in column C? Howard (thanks again and I promise this is the last question and not to be your VBA stalker) On Mon, Mar 19, 2012 at 1:48 PM, Asa Rossoff mailto:a...@lovetour.info a...@lovetour.info wrote: I think I misunderstood your need. I thought you wanted the name to stay with the data, allowing you to insert columns and have the name still refer to the same data. This method does that. If you want the name to stick to the column/range reference without regard to inserted columns (always column C no matter what, i.e.), and you will only use the name from VBA, then you could use a string variable: Dim MyColumnAddress As String, MyColumn As Range MyColumnAddress = C:C Set MyColumn = Range(MyColumnAddress) ' After the Set command the range WILL be effected by inserted columns; so re-set just before use if columns could have been added/deleting since the last Set command. If you want to use a defined name, this will always refer to column C: Names.Add MyColumn,=INDIRECT(C:C) Hope this helps! Asa -Original Message- From: tangledweb [
RE: $$Excel-Macros$$ Macro to hide rows, print, unhide rows runs very slow - how to speed up?
Hi Mel, No need to select any ranges. You are not printing based on the selection, so it's irrelevant. Here are a few things you could try: 1. Hide the rows without looping. (Beware though that any method that hides rows will force those rows and their dependencies to be recalculated in Excel 2003+.) i. You could use SpecialCells to find blanks in the first column. For example: ' This procedure operates on the active worksheet Dim HiddenRange As Range ' Dimension a new Range variable Application.EnableEvents = False ' ...and disable events Application.ScreenUpdating = False ' Hide all rows with a used cell and column A empty Set HiddenRange = Range(A18:A167).SpecialCells(xlCellTypeBlanks).EntireRow HiddenRange.Hidden = True ' Set Print Area (A1:P175 instead of A1:Q175 based on the description of your worksheet) ActiveSheet.PageSetup.PrintArea = $A$1:$P$175 ' Print! ActiveSheet.PrintOut ' At End of routine, unhide HiddenRange: HiddenRange.Hidden = False ' ...and re-enable events and screen updating: Application.EnableEvents = True Application.ScreenUpdating = True ii. Another method would be to use AutoFilter instead of SpecialCells/EntireRow/Hidden (though this won't avoid recalculation of those rows and their dependencies). 2. Print the rows without hiding... i. Use Range.PrintOut (There will be a page break between each block of contiguous rows). This can replace your entire procedure (I ignored the used range this time and took your word that the range of interest is always A18:P175) ' This procedure operates on the active worksheet ' Assuming no formulas in column A, determine rows for non-blank A values: Application.Intersect( _ Application.Union( _ Range(1:17), _ Range(A18:A167).SpecialCells(xlCellTypeConstants).EntireRow, _ Range(168:175) _ ), _ Range(A:P) _ ).PrintOut ii. Copy the desired cells to a temporary sheet. (Determine the PrintRange as above; create a temp sheet; copy values and formats to it; print the new, contiguous range, delete the sheet ---for a contiguous printout) Hope this helps. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Mel Sent: Monday, March 19, 2012 2:27 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Macro to hide rows, print, unhide rows runs very slow - how to speed up? I have a macro that will hide unused rows in a spreadsheet, print the active sheet, then unhide the rows. Spreadsheet is from cell A1 to P175. Base info is recorded in various cells on rows 1 to 4. Cells 5 to 16 display output. The main Data input starts in cell A18 to G 167. A18 is a from date, B18 to date, with comments in column P. Because I have 150 lines that could be used, I want to hide unused rows when printing to reduce the pages. Rows 168 to 175 display results and are not hidden. Here is the macro I have. It works but runs very slowly. I'm lookig for ways to speed it up. Option Explicit Sub PrintNonBlankColA() Dim RowCrnt As Integer Dim RowLast As Integer ' Note: This operates on the active worksheet Application.ScreenUpdating = False RowLast = Cells.SpecialCells(xlCellTypeLastCell).Row ' Hide all rows with a used cell and column A empty For RowCrnt = 18 To RowLast If IsEmpty(Cells(RowCrnt, A)) Then Range(RowCrnt : RowCrnt).EntireRow.Hidden = True End If Next Range(A1:Q175).Select ActiveSheet.PageSetup.PrintArea = $A$1:$Q$175 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False Rows(17:168).Select Selection.EntireRow.Hidden = False Range(B2).Select End Sub any suggestions to speed it up appreciated. Mel -- 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 -- 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
Re: $$Excel-Macros$$ copy paste in reverse order
Hi, PFA. =OFFSET($A$1,ROW($A$20)-ROW(),0) Regards, MARIES. On Mon, Mar 19, 2012 at 8:52 PM, Rajan_Verma rajanverma1...@gmail.comwrote: See the attached File ** ** =INDEX($A$1:$A$20,COUNTA($A$1:$A$20)-ROW()+1,1) ** ** Rajan. ** ** *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Amit Desai (MERU) *Sent:* Mar/Mon/2012 06:03 *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ copy paste in reverse order ** ** Hi, ** ** Is there any formula to copy data from say 2 rows say 1 2 paste them in reverse order. Data of 1st row will move to 2nd of 2nd row to first.. ** ** Best Regards, Amit ** ** ** ** -- Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is made that e-mail communications are timely, secure or free from computer virus or other defect. -- 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 -- 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 -- 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 Reverse.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
