Re: $$Excel-Macros$$ Macro to sort data in Ascending/Descending order
Why not using available [Sort Ascending / Descending] buttons in Auto Filter ? On 1/26/11, San Pat myitems2...@gmail.com wrote: Hi All, I am working on a excel with data in many columns. I want to add two macro button on each heading to sort data, one for Ascending sort +and second for Descending sort. Is it possible with macro. I have attached the sample sheet. Please advice if it possible. Regards, San -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to make query of cells?
can you explain it in detail also attach the sample worksheet too On Wed, Jan 26, 2011 at 2:53 AM, Hector Ruiz hector.ru...@gmail.com wrote: Please help me to make query of cells (data) in Excel. (Macros). -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Email Macro Based on an Address in a specific Cell
try this see if it helps With OutMail .to = ActiveSheet.Range(b I).Text .CC = ActiveSheet.Range(c I).Text On Wed, Jan 26, 2011 at 1:51 AM, John A. Smith johnasmit...@gmail.comwrote: I use a file with a macro (which this wonderful group collectively contributed to) which when I highlight a range and hit ctrl + s it drops it into a new excel file, opens outlook and addresses it to the addresses in the macro. I would like to let the user put an email address in a cell and when they hit ctrl + s it does the same but goes to that specific email address. I am hoping to be able to go into the current macro and change the to= into the command to get the contents of the specific address cell. Below is the portion of the marco that needs to change: With Dest .SaveAs TempFilePath TempFileName FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .to = .CC = Thank you for your continueing help. John -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ keep one column constant while changes the second
try this see if it helps ActiveCell.FormulaR1C1 = =CORREL($B$2:$B$15,R[-14]C:R[-1]C) On Wed, Jan 26, 2011 at 2:11 AM, Fred jonathanepos...@gmail.com wrote: I am trying to run a simple script the calculates the correlation between column B and the column C, then does the same thing for column B and column D, and then column B and column E, etc.I can't figure out how to keep the first column (i.e. column B) constant, while moving reference to the second column. Line 6 below seems to be the problem. 1 Range(F16).Select 2 ActiveCell.FormulaR1C1 = =CORREL(R[-14]C[-4]:R[-1]C[-4],R[-14]C:R[-1]C) 3 ActiveCell.Offset(0, 1).Select 4 ActiveCell.FormulaR1C1 = =CORREL(R[-14]C[-5]:R[-1]C[-5],R[-14]C:R[-1]C) 5 ActiveCell.Offset(0, 1).Select 6 ActiveCell.FormulaR1C1 = =CORREL(B2:B15,R[-14]C:R[-1]C) Any suggestions?? Thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ keep one column constant while changes the second
So.. your script is simply inserting Excel formulas into the cells? First of all, we need to discuss relative addresses vs Absolute addresses. If you insert the formula: In Cell F16, you inserted the formula: =CORREL(R[-14]C[-4]:R[-1]C[-4],R[-14]C:R[-1]C) In Excel, that resulted in: =CORREL(B2:B15,F2:F15) Now, if you drag the cell to G16, it becomes: =CORREL(C2:C15,G2:G15) That is because these formulas are taken to be in relation to (or relative to) the current cell (or active Cell) So, when you copy it one cell to the right, all cell references are moved one cell to the right. To anchor a column or row, you use the $ symbol. So, changing F16 to: =CORREL($B2:$B15,F2:F15) will anchor the first array to the B column. Dragging it to the right, gives G16 as: =CORREL($B2:$B15,G2:G15) The B column is anchored, but the second array moves with the cell... Now, back to your script. You'll find that if you record a macro and edit F16, you'll get: ActiveCell.FormulaR1C1 = =CORREL(R[-14]C2:R[-1]C2,R[-14]C:R[-1]C) compared to: =CORREL(R[-14]C[-4]:R[-1]C[-4],R[-14]C:R[-1]C) Notice your first C[-4] changed to C2 ?? That is because the [] symbols represent the RELATIVE offset from the active cell. From cell F16, R[-14]C[-4] means from Row 16, move 14 rows up, and column F(6), move 4 columns left. which puts you at cell B2 If you ALWAYS wanted to use the Column B, then change it to C2 (or Column 2) does that make sense? Paul From: Fred jonathanepos...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Tue, January 25, 2011 3:41:46 PM Subject: $$Excel-Macros$$ keep one column constant while changes the second I am trying to run a simple script the calculates the correlation between column B and the column C, then does the same thing for column B and column D, and then column B and column E, etc. I can't figure out how to keep the first column (i.e. column B) constant, while moving reference to the second column. Line 6 below seems to be the problem. 1 Range(F16).Select 2 ActiveCell.FormulaR1C1 = =CORREL(R[-14]C[-4]:R[-1]C[-4],R[-14]C:R[-1]C) 3 ActiveCell.Offset(0, 1).Select 4 ActiveCell.FormulaR1C1 = =CORREL(R[-14]C[-5]:R[-1]C[-5],R[-14]C:R[-1]C) 5 ActiveCell.Offset(0, 1).Select 6 ActiveCell.FormulaR1C1 = =CORREL(B2:B15,R[-14]C:R[-1]C) Any suggestions?? Thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Macro to sort data in Ascending/Descending order
helloo frnds i want to know what is macro and what is the work of it and how we can operate this. thankyou. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ build a database from multiple workbooks
Hello, how can I upload the file, as I see in the notification, this is no longer possible. However, if you can imagine my situation: I have data in columns from A to O and in rows from 1 to 100. The data is in the same rows and columns in all workbooks. Now I need the data to be copied from all this workbooks (and all new ones I create) to a new workbook serving as a database. However all columns allways contain some data, whereas the rows may not. It could be the case that only the 1st row contains any data. I hope this helps a bit. Thank you for the help. Best regards, seba On 17 jan., 18:38, ashish koul koul.ash...@gmail.com wrote: send us the sample workbook On Mon, Jan 17, 2011 at 12:10 AM, Seba sebastjan.hri...@gmail.com wrote: Hello all, I have a question regarding building a database. I have a workbook for each of my projects. I would like to have a macro, which would extract/ copy certain data (always in the same rows and columns) to a new workbook, which would serve as a database. Could anyone please help? Thank you in advance. Best regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ macro for adding comments to cells
Hello, I recorded a macro which adds content from certain range of cells to another range of cells. Each cell serves as a content source for the target cell comment. My problem is this. The source content is added periodically and if I trigger the macro for the second time, when I add source content to another source cell, I get this error: 'Run-time error 1004' Application defined or object defined error I suppose macro should be adapted in a way, that all content is cleared and added again or something like that... Here is the test macro: -- Sub komentar() ' ' komentar Makro ' ' Range(F14).Select Range(F14).AddComment Range(F14).Comment.Visible = True Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select Selection.Copy Range(F14).Comment.Shape.Select True Application.CutCopyMode = False Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select ActiveCell.FormulaR1C1 = asdasda Range(F14).Comment.Shape.Select True Range(F14).Comment.Text Text:=User: Chr(10) asdasda Range(G21).Select End Sub -- Could anyone please help? regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to make query of cells?
Thanks for responding: I have to transport data to ACCESS, because I have consultations there, but I want to query directly from Excel to automate reports with MACROS. Hector Ruiz 2011/1/26 ashish koul koul.ash...@gmail.com can you explain it in detail also attach the sample worksheet too On Wed, Jan 26, 2011 at 2:53 AM, Hector Ruiz hector.ru...@gmail.comwrote: Please help me to make query of cells (data) in Excel. (Macros). -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ keep one column constant while changes the second
makes perfect sense. thanks so much! On Wed, Jan 26, 2011 at 8:06 AM, Paul Schreiner schreiner_p...@att.net wrote: So.. your script is simply inserting Excel formulas into the cells? First of all, we need to discuss relative addresses vs Absolute addresses. If you insert the formula: In Cell F16, you inserted the formula: =CORREL(R[-14]C[-4]:R[-1]C[-4],R[-14]C:R[-1]C) In Excel, that resulted in: =CORREL(B2:B15,F2:F15) Now, if you drag the cell to G16, it becomes: =CORREL(C2:C15,G2:G15) That is because these formulas are taken to be in relation to (or relative to) the current cell (or active Cell) So, when you copy it one cell to the right, all cell references are moved one cell to the right. To anchor a column or row, you use the $ symbol. So, changing F16 to: =CORREL($B2:$B15,F2:F15) will anchor the first array to the B column. Dragging it to the right, gives G16 as: =CORREL($B2:$B15,G2:G15) The B column is anchored, but the second array moves with the cell... Now, back to your script. You'll find that if you record a macro and edit F16, you'll get: ActiveCell.FormulaR1C1 = =CORREL(R[-14]C2:R[-1]C2,R[-14]C:R[-1]C) compared to: =CORREL(R[-14]C[-4]:R[-1]C[-4],R[-14]C:R[-1]C) Notice your first C[-4] changed to C2 ?? That is because the [] symbols represent the RELATIVE offset from the active cell. From cell F16, R[-14]C[-4] means from Row 16, move 14 rows up, and column F(6), move 4 columns left. which puts you at cell B2 If you ALWAYS wanted to use the Column B, then change it to C2 (or Column 2) does that make sense? Paul From: Fred jonathanepos...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Tue, January 25, 2011 3:41:46 PM Subject: $$Excel-Macros$$ keep one column constant while changes the second I am trying to run a simple script the calculates the correlation between column B and the column C, then does the same thing for column B and column D, and then column B and column E, etc. I can't figure out how to keep the first column (i.e. column B) constant, while moving reference to the second column. Line 6 below seems to be the problem. 1 Range(F16).Select 2 ActiveCell.FormulaR1C1 = =CORREL(R[-14]C[-4]:R[-1]C[-4],R[-14]C:R[-1]C) 3 ActiveCell.Offset(0, 1).Select 4 ActiveCell.FormulaR1C1 = =CORREL(R[-14]C[-5]:R[-1]C[-5],R[-14]C:R[-1]C) 5 ActiveCell.Offset(0, 1).Select 6 ActiveCell.FormulaR1C1 = =CORREL(B2:B15,R[-14]C:R[-1]C) Any suggestions?? Thanks -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ build a database from multiple workbooks
try this macro see if it helps On Wed, Jan 26, 2011 at 4:22 PM, Seba sebastjan.hri...@gmail.com wrote: Hello, how can I upload the file, as I see in the notification, this is no longer possible. However, if you can imagine my situation: I have data in columns from A to O and in rows from 1 to 100. The data is in the same rows and columns in all workbooks. Now I need the data to be copied from all this workbooks (and all new ones I create) to a new workbook serving as a database. However all columns allways contain some data, whereas the rows may not. It could be the case that only the 1st row contains any data. I hope this helps a bit. Thank you for the help. Best regards, seba On 17 jan., 18:38, ashish koul koul.ash...@gmail.com wrote: send us the sample workbook On Mon, Jan 17, 2011 at 12:10 AM, Seba sebastjan.hri...@gmail.com wrote: Hello all, I have a question regarding building a database. I have a workbook for each of my projects. I would like to have a macro, which would extract/ copy certain data (always in the same rows and columns) to a new workbook, which would serve as a database. Could anyone please help? Thank you in advance. Best regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel NEW_merge_from_different_workbooks_for_all_sheets.xlsm Description: Binary data
Re: $$Excel-Macros$$ macro for adding comments to cells
look here http://www.contextures.com/xlcomments03.html#Picture regards r 2011/1/26 Seba sebastjan.hri...@gmail.com Hello, I recorded a macro which adds content from certain range of cells to another range of cells. Each cell serves as a content source for the target cell comment. My problem is this. The source content is added periodically and if I trigger the macro for the second time, when I add source content to another source cell, I get this error: 'Run-time error 1004' Application defined or object defined error I suppose macro should be adapted in a way, that all content is cleared and added again or something like that... Here is the test macro: -- Sub komentar() ' ' komentar Makro ' ' Range(F14).Select Range(F14).AddComment Range(F14).Comment.Visible = True Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select Selection.Copy Range(F14).Comment.Shape.Select True Application.CutCopyMode = False Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select ActiveCell.FormulaR1C1 = asdasda Range(F14).Comment.Shape.Select True Range(F14).Comment.Text Text:=User: Chr(10) asdasda Range(G21).Select End Sub -- Could anyone please help? regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ macro for adding comments to cells
can you attach the sample workbook On Wed, Jan 26, 2011 at 8:59 PM, roberto mensa robb@gmail.com wrote: look here http://www.contextures.com/xlcomments03.html#Picture regards r 2011/1/26 Seba sebastjan.hri...@gmail.com Hello, I recorded a macro which adds content from certain range of cells to another range of cells. Each cell serves as a content source for the target cell comment. My problem is this. The source content is added periodically and if I trigger the macro for the second time, when I add source content to another source cell, I get this error: 'Run-time error 1004' Application defined or object defined error I suppose macro should be adapted in a way, that all content is cleared and added again or something like that... Here is the test macro: -- Sub komentar() ' ' komentar Makro ' ' Range(F14).Select Range(F14).AddComment Range(F14).Comment.Visible = True Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select Selection.Copy Range(F14).Comment.Shape.Select True Application.CutCopyMode = False Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select ActiveCell.FormulaR1C1 = asdasda Range(F14).Comment.Shape.Select True Range(F14).Comment.Text Text:=User: Chr(10) asdasda Range(G21).Select End Sub -- Could anyone please help? regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How to make query of cells?
I would suggest looking into the documentation for setting up an external data source. Excel works nicely with Access. In one of my applications, I set up the external source, then, in the macro, have it do a refresh to update the data, then use the macros to generate summary reports from this data. Paul From: Hector Ruiz hector.ru...@gmail.com To: excel-macros@googlegroups.com Sent: Wed, January 26, 2011 9:27:25 AM Subject: Re: $$Excel-Macros$$ How to make query of cells? Thanks for responding: I have to transport data to ACCESS, because I have consultations there, but I want to query directly from Excel to automate reports with MACROS. Hector Ruiz 2011/1/26 ashish koul koul.ash...@gmail.com can you explain it in detail also attach the sample worksheet too On Wed, Jan 26, 2011 at 2:53 AM, Hector Ruiz hector.ru...@gmail.com wrote: Please help me to make query of cells (data) in Excel. (Macros). -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul akoul.blogspot.com akoul.wordpress.com My Linkedin Profile PBefore printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ build a database from multiple workbooks
There are a several ways to accomplish this... Are all of the files in a single folder? Are there other files there? Or...how do you want to identify the files? We can: A)use a sheet to list all of the files. then, loop through the list and process each workbook. B)Place all of the files in one folder, then process each workbook in the folder. The loops required to copy the data is pretty simple. Let me know what approach you'd like to take and I'd be glad to help put together the macro. Paul From: Seba sebastjan.hri...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Wed, January 26, 2011 5:52:59 AM Subject: Re: $$Excel-Macros$$ build a database from multiple workbooks Hello, how can I upload the file, as I see in the notification, this is no longer possible. However, if you can imagine my situation: I have data in columns from A to O and in rows from 1 to 100. The data is in the same rows and columns in all workbooks. Now I need the data to be copied from all this workbooks (and all new ones I create) to a new workbook serving as a database. However all columns allways contain some data, whereas the rows may not. It could be the case that only the 1st row contains any data. I hope this helps a bit. Thank you for the help. Best regards, seba On 17 jan., 18:38, ashish koul koul.ash...@gmail.com wrote: send us the sample workbook On Mon, Jan 17, 2011 at 12:10 AM, Seba sebastjan.hri...@gmail.com wrote: Hello all, I have a question regarding building a database. I have a workbook for each of my projects. I would like to have a macro, which would extract/ copy certain data (always in the same rows and columns) to a new workbook, which would serve as a database. Could anyone please help? Thank you in advance. Best regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ macro for adding comments to cells
try Sub test() Dim rng As Excel.Range Dim v As Excel.Range Set rng = [a1:a10] For Each v In rng write_comment v.Offset(, 1), v Next End Sub Sub write_comment(rngc As Excel.Range, rngt As Excel.Range) If TypeName(rngc.Comment) = Nothing Then rngc.AddComment.Text End If rngc.Comment.Text CStr(rngt.Value) End Sub regards r 2011/1/26 Seba sebastjan.hri...@gmail.com Hello, I recorded a macro which adds content from certain range of cells to another range of cells. Each cell serves as a content source for the target cell comment. My problem is this. The source content is added periodically and if I trigger the macro for the second time, when I add source content to another source cell, I get this error: 'Run-time error 1004' Application defined or object defined error I suppose macro should be adapted in a way, that all content is cleared and added again or something like that... Here is the test macro: -- Sub komentar() ' ' komentar Makro ' ' Range(F14).Select Range(F14).AddComment Range(F14).Comment.Visible = True Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select Selection.Copy Range(F14).Comment.Shape.Select True Application.CutCopyMode = False Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select ActiveCell.FormulaR1C1 = asdasda Range(F14).Comment.Shape.Select True Range(F14).Comment.Text Text:=User: Chr(10) asdasda Range(G21).Select End Sub -- Could anyone please help? regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Email Macro Based on an Address in a specific Cell
Ashish, Thank you for your quick response. I got it to work thank you. Could you explain (b I).Text please as it refers to a location? Thank you for your excellent assistance learning Excel. John On Wed, Jan 26, 2011 at 7:40 AM, ashish koul koul.ash...@gmail.com wrote: try this see if it helps With OutMail .to = ActiveSheet.Range(b I).Text .CC = ActiveSheet.Range(c I).Text On Wed, Jan 26, 2011 at 1:51 AM, John A. Smith johnasmit...@gmail.comwrote: I use a file with a macro (which this wonderful group collectively contributed to) which when I highlight a range and hit ctrl + s it drops it into a new excel file, opens outlook and addresses it to the addresses in the macro. I would like to let the user put an email address in a cell and when they hit ctrl + s it does the same but goes to that specific email address. I am hoping to be able to go into the current macro and change the to= into the command to get the contents of the specific address cell. Below is the portion of the marco that needs to change: With Dest .SaveAs TempFilePath TempFileName FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .to = .CC = Thank you for your continueing help. John -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Question on Pivots
Hi, I have a testing scenario where I have Severity (Critical, High, Medium Low) and I have modules as Insurance, Claims, etc. When I take a pivot, I want to combine all the module information in the body of the pivot showing Severity in Row labels and all the other info under summation of Total of Insurance, Claims, etc. I do not have a total column in my data sheet. Can someone help. Attached is some data related to this. -- V.V.N. Pavan Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Pivot-Question.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Macro to sort data in Ascending/Descending order
Hi Ashish, Thanks for your help. Is there any way I can make it cell specific, assign this macro to particular cell. For example, separate macro for cell A1, B1, C1, D1 etc. I want to put two arrows, up down (one for Ascending sort and second for Descending sort), in each cell. Regards, Sandip On Tue, Jan 25, 2011 at 11:41 PM, ashish koul koul.ash...@gmail.com wrote: Sub sort_ascending() Dim Temp As String Temp = ActiveCell.Address x = Mid(Temp, 2, (InStr(2, Temp, $) - 2)) If ActiveCell.Column = 6 Then Sheets(1).Range(a1:f Range(a1).End(xlDown).Row).Sort key1:=Sheets(1).Range(x 1), order1:=xlAscending, Header:=xlYes End If End Sub Sub sort_des() Dim Temp As String Temp = ActiveCell.Address x = Mid(Temp, 2, (InStr(2, Temp, $) - 2)) If ActiveCell.Column = 6 Then Sheets(1).Range(a1:f Range(a1).End(xlDown).Row).Sort key1:=Sheets(1).Range(x 1), order1:=xlDescending, Header:=xlYes End If End Sub On Wed, Jan 26, 2011 at 7:52 AM, San Pat myitems2...@gmail.com wrote: Hi All, I am working on a excel with data in many columns. I want to add two macro button on each heading to sort data, one for Ascending sort +and second for Descending sort. Is it possible with macro. I have attached the sample sheet. Please advice if it possible. Regards, San -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ how to remove the symbol of drop down list
Hi Rohan, If you are referring to Q18 and down, you have not removed the Data Validation due to which you still see the drop down. Remove the validations and see. Hope this helps. Pavan On Fri, Jan 14, 2011 at 12:14 PM, Rohan Young rohan.j...@gmail.com wrote: Hi, see the attachement for my prob., i made a drop down but after removing the list the symbol of dropdown list still is there, it is by default of any trick to remove, it is not coming in print but visible on screen please help Thanks Regards ROHAN 9818247278, 8860567680 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- V.V.N. Pavan Kumar -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Count Number of Records in worksheet
Hi,, Please tell me how to count the number of total records in a worksheet using the macro or count of non blank cells in a worksheet. -- *Thanks and Regards,* Jitendra Kr. Verma| Sr. Software Engineer Mob: +91.9700695633 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Lookup values in different sheets
Thanks Dave nice idea. Aamir Shahzad On Wed, Jan 26, 2011 at 6:08 AM, Dave Bonallack davebonall...@hotmail.comwrote: Hi, IFERROR can be stacked, just like IF can be. Try this formula in C4, then copy down: =IFERROR(VLOOKUP(B4,$E$3:$F$3,2,0),IFERROR(VLOOKUP(B4,$H$3:$I$3,2,0),VLOOKUP(B4,$K$3:$L$3,2,0))) Regards - Dave -- Date: Tue, 25 Jan 2011 22:57:34 +0500 Subject: $$Excel-Macros$$ Lookup values in different sheets From: aamirshahza...@gmail.com To: excel-macros@googlegroups.com Hi experts, Can I find values in multiple sheets with vlookup function. sheet is attached. iferror function can be use but it can helpful only with 2 sheet, any guidance? Regards, Aamir Shahzad -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Count Number of Records in worksheet
Hi Jitendra, Not sure how you have arranged the data into your worksheet, but try below code:- Sub lastRec() i = Range(a65536).End(xlUp).Row MsgBox i-1 'subtract one from i if you have the headers at top End Sub To count non blank cells in a worksheet, you can use COUNTA function. Please get back in case of doubts. Best Regards, DILIPandey On 1/26/11, Jitendra Verma jitendra.kumarve...@gmail.com wrote: Hi,, Please tell me how to count the number of total records in a worksheet using the macro or count of non blank cells in a worksheet. -- *Thanks and Regards,* Jitendra Kr. Verma| Sr. Software Engineer Mob: +91.9700695633 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Thanks Regards, DILIP KUMAR PANDEY, mvp MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Macro to sort data in Ascending/Descending order
Hi Sandip, Please find the attached file. Feel free to ask further for any query. Regards Sandeep Kumar On Wed, Jan 26, 2011 at 10:41 PM, San Pat myitems2...@gmail.com wrote: Hi Ashish, Thanks for your help. Is there any way I can make it cell specific, assign this macro to particular cell. For example, separate macro for cell A1, B1, C1, D1 etc. I want to put two arrows, up down (one for Ascending sort and second for Descending sort), in each cell. Regards, Sandip On Tue, Jan 25, 2011 at 11:41 PM, ashish koul koul.ash...@gmail.comwrote: Sub sort_ascending() Dim Temp As String Temp = ActiveCell.Address x = Mid(Temp, 2, (InStr(2, Temp, $) - 2)) If ActiveCell.Column = 6 Then Sheets(1).Range(a1:f Range(a1).End(xlDown).Row).Sort key1:=Sheets(1).Range(x 1), order1:=xlAscending, Header:=xlYes End If End Sub Sub sort_des() Dim Temp As String Temp = ActiveCell.Address x = Mid(Temp, 2, (InStr(2, Temp, $) - 2)) If ActiveCell.Column = 6 Then Sheets(1).Range(a1:f Range(a1).End(xlDown).Row).Sort key1:=Sheets(1).Range(x 1), order1:=xlDescending, Header:=xlYes End If End Sub On Wed, Jan 26, 2011 at 7:52 AM, San Pat myitems2...@gmail.com wrote: Hi All, I am working on a excel with data in many columns. I want to add two macro button on each heading to sort data, one for Ascending sort +and second for Descending sort. Is it possible with macro. I have attached the sample sheet. Please advice if it possible. Regards, San -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Sort Data_Aseding_Desending.xlsm Description: application/vnd.ms-excel.sheet.macroenabled.12
Re: $$Excel-Macros$$ Email Macro Based on an Address in a specific Cell
use this one if you email adress for to is in cell b1 and email adress for cc is in cell c1 With OutMail .to = ActiveSheet.Range(b1).Text .CC = ActiveSheet.Range(c1).Text and if you want to make it dynamic Dim I as long I= 1 '( choose row no) With OutMail .to = ActiveSheet.Range(b I).Text .CC = ActiveSheet.Range(c I).Text On Wed, Jan 26, 2011 at 10:21 PM, John A. Smith johnasmit...@gmail.comwrote: Ashish, Thank you for your quick response. I got it to work thank you. Could you explain (b I).Text please as it refers to a location? Thank you for your excellent assistance learning Excel. John On Wed, Jan 26, 2011 at 7:40 AM, ashish koul koul.ash...@gmail.comwrote: try this see if it helps With OutMail .to = ActiveSheet.Range(b I).Text .CC = ActiveSheet.Range(c I).Text On Wed, Jan 26, 2011 at 1:51 AM, John A. Smith johnasmit...@gmail.comwrote: I use a file with a macro (which this wonderful group collectively contributed to) which when I highlight a range and hit ctrl + s it drops it into a new excel file, opens outlook and addresses it to the addresses in the macro. I would like to let the user put an email address in a cell and when they hit ctrl + s it does the same but goes to that specific email address. I am hoping to be able to go into the current macro and change the to= into the command to get the contents of the specific address cell. Below is the portion of the marco that needs to change: With Dest .SaveAs TempFilePath TempFileName FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .to = .CC = Thank you for your continueing help. John -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ build a database from multiple workbooks
Thank you both. I hadn't had the chance the above macro. I will report back how that turns out. To answer your questions Paul, I already have all workbooks in one folder and there aren't any other files there. And any other new workbook I create is also saved in this folder. Workbooks are named for example: year_sequence_number: 2011_001; 2011_002, ... Thank you all for your help. regards, seba On 26 jan., 17:03, Paul Schreiner schreiner_p...@att.net wrote: There are a several ways to accomplish this... Are all of the files in a single folder? Are there other files there? Or...how do you want to identify the files? We can: A)use a sheet to list all of the files. then, loop through the list and process each workbook. B)Place all of the files in one folder, then process each workbook in the folder. The loops required to copy the data is pretty simple. Let me know what approach you'd like to take and I'd be glad to help put together the macro. Paul From: Seba sebastjan.hri...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Wed, January 26, 2011 5:52:59 AM Subject: Re: $$Excel-Macros$$ build a database from multiple workbooks Hello, how can I upload the file, as I see in the notification, this is no longer possible. However, if you can imagine my situation: I have data in columns from A to O and in rows from 1 to 100. The data is in the same rows and columns in all workbooks. Now I need the data to be copied from all this workbooks (and all new ones I create) to a new workbook serving as a database. However all columns allways contain some data, whereas the rows may not. It could be the case that only the 1st row contains any data. I hope this helps a bit. Thank you for the help. Best regards, seba On 17 jan., 18:38, ashish koul koul.ash...@gmail.com wrote: send us the sample workbook On Mon, Jan 17, 2011 at 12:10 AM, Seba sebastjan.hri...@gmail.com wrote: Hello all, I have a question regarding building a database. I have a workbook for each of my projects. I would like to have a macro, which would extract/ copy certain data (always in the same rows and columns) to a new workbook, which would serve as a database. Could anyone please help? Thank you in advance. Best regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below linkhttp://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ macro for adding comments to cells
Hello Roberto, works like a charm:) I just have one additional question. How can I define the target range on a separate sheet. Example: Sheet1 = source range Sheet2 = target range And to make the matter even harder, the target range is transposed, so if source = a1:a10, target range = a1:j1 Thank you very much. This is a lifesavior for me. And I would be very grateful if someone could explain how to attach a sample workbook or any file for that matter. I don't see any option for that. regards, seba On 26 jan., 17:12, roberto mensa robb@gmail.com wrote: try Sub test() Dim rng As Excel.Range Dim v As Excel.Range Set rng = [a1:a10] For Each v In rng write_comment v.Offset(, 1), v Next End Sub Sub write_comment(rngc As Excel.Range, rngt As Excel.Range) If TypeName(rngc.Comment) = Nothing Then rngc.AddComment.Text End If rngc.Comment.Text CStr(rngt.Value) End Sub regards r 2011/1/26 Seba sebastjan.hri...@gmail.com Hello, I recorded a macro which adds content from certain range of cells to another range of cells. Each cell serves as a content source for the target cell comment. My problem is this. The source content is added periodically and if I trigger the macro for the second time, when I add source content to another source cell, I get this error: 'Run-time error 1004' Application defined or object defined error I suppose macro should be adapted in a way, that all content is cleared and added again or something like that... Here is the test macro: -- Sub komentar() ' ' komentar Makro ' ' Range(F14).Select Range(F14).AddComment Range(F14).Comment.Visible = True Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select Selection.Copy Range(F14).Comment.Shape.Select True Application.CutCopyMode = False Range(F14).Comment.Text Text:=User: Chr(10) Range(F20).Select ActiveCell.FormulaR1C1 = asdasda Range(F14).Comment.Shape.Select True Range(F14).Comment.Text Text:=User: Chr(10) asdasda Range(G21).Select End Sub -- Could anyone please help? regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Help Required
Hi Vikram, I clearly did not understand your query, however I have rectified the code. Regards Umesh Dev On Wed, Jan 26, 2011 at 9:32 PM, vikram vikramahuj...@gmail.com wrote: Hi Umesh, Thanks a lot for your help. I need one more help regarding below macro for converting PDF files to excel .Below macro coding is showing error while running this macro as I have to convert around 100 pdf files to excel and for your reference I have enclosed one of the PDF file. Kindly help in this regard. Sub BackToA1() Range(A1).Select End Sub Sub GetPDFnow() Dim varRetVal As Variant, strFullyPathedFileName As String, strDoIt As String 'Add a new worksheet Sheets.Add After:=Sheets(Sheets.Count) 'Name it ActiveSheet.Name = Input01 'Back to A1 Range(A1).Activate 'HERE YOU DEFINE THE FULLY PATHED PDF FILE strFullyPathedFileName = C:\Documents and Settings\ayujain1\Desktop\Excel_Tutorials\Ayush ebooks\14 secret shortcuts of Excel.pdf 'HERE YOU SET UP THE SHELL COMMAND strDoIt = C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe strFullyPathedFileName 'The Shell command varRetVal = Shell(strDoIt, 1) 'Clear CutCopyMode Application.CutCopyMode = False AppActivate varRetVal 'Wait some time Application.Wait Now + TimeValue(00:00:03) ' wait 3 seconds DoEvents 'IN ACROBAT : 'SELECT ALL SendKeys ^a 'COPY SendKeys ^c 'EXIT (Close Exit) SendKeys ^q 'Wait some time Application.Wait Now + TimeValue(00:00:03) ' wait 3 seconds DoEvents 'Paste ActiveSheet.Paste 'Go back to cell A1 Call BackToA1 End Sub *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Umesh Dev *Sent:* Tuesday, January 25, 2011 10:10 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Help Required Hi Vikram, I am enclosing a file. It would ask for file each time you run the macros. Use this as a template and the save as daily file. Is it clear? Regards Umesh Dev 9019107882 On Tue, Jan 25, 2011 at 7:51 AM, vikram vikramahuj...@gmail.com wrote: Hi Umesh, I need one more help regarding macro enabled sheets. As this DAT sheet is uploaded on daily basis and we need to do this activity daily. Kindly advice how to add this macro to daily files so that we can run this macro created by you. Please provide steps to avoid any errors while adding this coding to daily dat file. Regards, Vikram Ahuja * * *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Umesh Dev *Sent:* Sunday, January 23, 2011 7:43 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Help Required Hi Vikram, I hope this would solve your query. Regards Umesh On Sun, Jan 23, 2011 at 11:33 AM, vikram vikramahuj...@gmail.com wrote: Hi, I need regarding enclosed excel sheet for Duplicate audit for vendors. As of now we need to identify different type of invoice as per first three numbers of vendors. For ex: *Code* *Category* 901 Edible Material 902 Packaging Material 903 Engineering 904 Services 905 MEDIA 906 Capex 907 CFA 908 Edible Material 909 Packaging Material 910 CFA 912 INTERCO 913 Others Steps we are following: 1) On first instance we use Left function to get first three numbers of vendor number i.e for 7 numbers. 2) Then we use v look up to get above details on sheet on DAT sheet. Kindly provide any function or VBA coding to get this avoiding above step. In enclosed sheet Column C contains list of all vendor numbers and on the basis of this we need to get first three numbers on Column I and we need to get categorization on Column j. Regards, Vikram Ahuja 08860553902 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to
Re: $$Excel-Macros$$ Macro to sort data in Ascending/Descending order
Sandeep thanks for your help. Sorry guys for not making it clear first time. Let me explain it again. When I click on the specific cell first time, it should arrange the data in ascending or descending order. Similarly, when I click the same cell again, it should arrange the data in reverse (descending or ascending) order (opposite to the first click). It will be great if we can show the up or down arrow with the click. Regards, Sandip On Wed, Jan 26, 2011 at 11:28 AM, Sandeep Kumar Maurya sandymau...@gmail.com wrote: Hi Sandip, Please find the attached file. Feel free to ask further for any query. Regards Sandeep Kumar On Wed, Jan 26, 2011 at 10:41 PM, San Pat myitems2...@gmail.com wrote: Hi Ashish, Thanks for your help. Is there any way I can make it cell specific, assign this macro to particular cell. For example, separate macro for cell A1, B1, C1, D1 etc. I want to put two arrows, up down (one for Ascending sort and second for Descending sort), in each cell. Regards, Sandip On Tue, Jan 25, 2011 at 11:41 PM, ashish koul koul.ash...@gmail.comwrote: Sub sort_ascending() Dim Temp As String Temp = ActiveCell.Address x = Mid(Temp, 2, (InStr(2, Temp, $) - 2)) If ActiveCell.Column = 6 Then Sheets(1).Range(a1:f Range(a1).End(xlDown).Row).Sort key1:=Sheets(1).Range(x 1), order1:=xlAscending, Header:=xlYes End If End Sub Sub sort_des() Dim Temp As String Temp = ActiveCell.Address x = Mid(Temp, 2, (InStr(2, Temp, $) - 2)) If ActiveCell.Column = 6 Then Sheets(1).Range(a1:f Range(a1).End(xlDown).Row).Sort key1:=Sheets(1).Range(x 1), order1:=xlDescending, Header:=xlYes End If End Sub On Wed, Jan 26, 2011 at 7:52 AM, San Pat myitems2...@gmail.com wrote: Hi All, I am working on a excel with data in many columns. I want to add two macro button on each heading to sort data, one for Ascending sort +and second for Descending sort. Is it possible with macro. I have attached the sample sheet. Please advice if it possible. Regards, San -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks
$$Excel-Macros$$ Calculate Difference between two dates
Hi, How do we calculate the difference between two dates in Hours. Dates are like mm/dd/ format only. -- *Thanks and Regards,* Jitendra Kr. Verma| Sr. Software Engineer Mob: +91.9700695633 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Help With UserForms
Hello Ashish Excellent Stuff. One Request. Is it possible to freeze the following to accept only what is in the Drop Down list:? 1. Name of the Waiter (Only Accept what is in the drop down) 2. Order (Only Accept what is in the drop down) Also, The cursor begins with Table Number. Is it possible for the cursor to begin with the Date of Order? If I press the Tab key on the keyboard, the cursor moves around randomly and does not go to the next option. Can this be fixed? The UserForm remains in view after saving the data and goes to the Details Sheet. Can the userform be closed after the data is saved? Also, am looking at the way in which the sorting of dates is done in the Details Sheet. Am not sure what is happening. Can I look at it in more detail and request more help if required? Thanks a lot Ashish Pradhan On 27/01/2011 09:36 AM, ashish koul wrote: use this one On Thu, Jan 27, 2011 at 7:59 AM, ashish koul koul.ash...@gmail.com mailto:koul.ash...@gmail.com wrote: check the attachment see if it helps On Wed, Jan 26, 2011 at 6:50 PM, Ashish Pradhan ashlyprad...@gmail.com mailto:ashlyprad...@gmail.com wrote: Hello Much earlier I had sent a query regarding Userforms in Excel and the group was spontaneous and kind enough to solve my query exactly as I needed the output. I may not have been an active participant to the queries (simply because my knowledge of excel is minuscule in comparison with many of the group members) but I have certainly tried many of the solutions posted by the members. I like to call myself an Excel Buff and occasionally think of various solutions to everyday and practical problems. During a discussion with a friend, I was of the opinion that a billing software can easily be developed in excel. Something similar to the kinds we see in hotels / restaurants. I began to work towards building such a software in Excel using UserForms. Now I am stuck. I have attempted to make a begining. Please see the attached Excel File. (Am using Excel version 2003) The UserForm that I have created has a lot of bugs. Most of it I think is in the naming of the Code and the Code itself. Would someone be kind enough to please take a look at it. The Sheet Main has two hidden columns. Is it possible to Use the Data in these Column A (hidden) as a drop down list in the User Form against Order in the userform? Similarly, for Name of the Waiter in the Userform, is it possible to have a drop down list as in Column C? In Sheet Details, my first Column starts with Date in the format DD/MM/. If there a way to automatically sort the Data in Sheet Details. The primary sorting should be on Date column and then by the Table Column.. At different stages, I have managed to get the userform working, but the moment I add / modify some parts of it, the compilation fails.. Need Any help possible. Thanks Ashish P.S: Assure you that this is not a professional / paid project in any circumstances. This is purely something which I have undertaken simply to satisfy my own curiosity and usage of Excel.. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com http://www.excel-macros.blogspot.com/ 4. Learn VBA Macros at http://www.quickvba.blogspot.com http://www.quickvba.blogspot.com/ 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com http://exceldailytip.blogspot.com/ To post to this group, send email to excel-macros@googlegroups.com mailto:excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* ** *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 PBefore printing, think about the environment. -- *Regards* ** *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 PBefore printing, think about the environment. -- -- Some important links for excel users: 1.
Re: $$Excel-Macros$$ build a database from multiple workbooks
Sub consolidatefromdifferentworkbooks() Application.DisplayAlerts = False 'On Error GoTo abc Dim ask As Workbook Dim ask2 As Workbook Dim ASK3 As Workbook Set ASK3 = ActiveWorkbook Dim i As Long Dim j As Long Dim N, z, r, s, k, d As Long s = 1 k = 1 Dim x As String Dim temp As String Dim sht As Worksheet Set ask2 = ActiveWorkbook Sheets(1).Select Range(A65356).Select Selection.End(xlUp).Select r = ActiveCell.Row Workbooks.Open Filename:=ThisWorkbook.Sheets(1).Range(b2).Value Set ask = ActiveWorkbook For i = 2 To r 'Workbooks(Range(a i).Value).Windows(1).Visible = False ASK3.Activate Sheets(1).Select Workbooks.Open Filename:=Sheets(1).Range(a i).Value Set ask2 = ActiveWorkbook For d = 1 To ask2.Sheets.Count Sheets(d).Activate Sheets(d).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select 'Selection.End(xlToRight).Select ' ' 'temp = ActiveCell.Address 'x = Mid(temp, 2, (InStr(2, temp, $) - 2)) ' ' ' ' Range(A65356).Select 'Selection.End(xlUp).Select N = ActiveCell.Row If N = 2 Then Rows(1: N).Select Selection.Copy 'Sheets.Add After:=Sheets(Sheets.Count) ask.Activate ask.Sheets(1).Activate Sheets(1).Select Range(A1).Select ActiveCell.SpecialCells(xlLastCell).Select z = ActiveCell.Row + 2 Range(A z).Select ActiveSheet.Paste ActiveWorkbook.Save ask2.Activate End If Next d ask2.Activate ask2.Close ask.Activate ask.Sheets(1).Activate ActiveWorkbook.Save Next i 'abc: 'Exit Sub Application.DisplayAlerts = True End Sub On Thu, Jan 27, 2011 at 11:23 AM, Squall squall.l...@gmail.com wrote: Hi guys, Could you please share us the coding especially when helping... it really help to improve those being helped (especially me) to understand the vba macro/coding. Sometimes when I try to open the module/code, it's protected by password :( Nonetheless, thanks for the help. On 1/26/2011 10:33 PM, ashish koul wrote: try this macro see if it helps On Wed, Jan 26, 2011 at 4:22 PM, Seba sebastjan.hri...@gmail.com wrote: Hello, how can I upload the file, as I see in the notification, this is no longer possible. However, if you can imagine my situation: I have data in columns from A to O and in rows from 1 to 100. The data is in the same rows and columns in all workbooks. Now I need the data to be copied from all this workbooks (and all new ones I create) to a new workbook serving as a database. However all columns allways contain some data, whereas the rows may not. It could be the case that only the 1st row contains any data. I hope this helps a bit. Thank you for the help. Best regards, seba On 17 jan., 18:38, ashish koul koul.ash...@gmail.com wrote: send us the sample workbook On Mon, Jan 17, 2011 at 12:10 AM, Seba sebastjan.hri...@gmail.com wrote: Hello all, I have a question regarding building a database. I have a workbook for each of my projects. I would like to have a macro, which would extract/ copy certain data (always in the same rows and columns) to a new workbook, which would serve as a database. Could anyone please help? Thank you in advance. Best regards, seba -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the
Re: $$Excel-Macros$$ Calculate Difference between two dates
=TEXT(A1-B1,[HH]:MM) On Thu, Jan 27, 2011 at 10:48 AM, Jitendra Verma jitendra.kumarve...@gmail.com wrote: Hi, How do we calculate the difference between two dates in Hours. Dates are like mm/dd/ format only. -- *Thanks and Regards,* Jitendra Kr. Verma| Sr. Software Engineer Mob: +91.9700695633 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Calculate Difference between two dates
OR USE =INT((A1-B1)*24) On Thu, Jan 27, 2011 at 11:54 AM, ashish koul koul.ash...@gmail.com wrote: =TEXT(A1-B1,[HH]:MM) On Thu, Jan 27, 2011 at 10:48 AM, Jitendra Verma jitendra.kumarve...@gmail.com wrote: Hi, How do we calculate the difference between two dates in Hours. Dates are like mm/dd/ format only. -- *Thanks and Regards,* Jitendra Kr. Verma| Sr. Software Engineer Mob: +91.9700695633 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com http://akoul.blogspot.com/ *akoul*.wordpress.com http://akoul.wordpress.com/ My Linkedin Profile http://in.linkedin.com/pub/ashish-koul/10/400/830 P Before printing, think about the environment. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
