$$Excel-Macros$$ Re: Fwd: about to remove #VALUE! error
Hi Dhananjay, Find the attached excel file with the solution. You can use N( ) to treat any string as 0. Manish Purohit Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world. _ From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Dhananjay Pinjan Sent: 15 August 2009 17:22 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Fwd: about to remove #VALUE! error Dear All, Please ignore the previous mail sent by me because i forgot to attach the excel file. Can anyone provide me the solution as per the requirement in the attachment. Thanks in advance Regards, Dhananjay Pinjan (Mob. No. 9922255533) --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com HELP US GROW !! We reach over 5,200 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~--- query for ITAT.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: insert = in every cell as prefix with nos Blank
Hi Abdul, I think we don't need to use the below code due to which it changes those cells since a non-numeric found and as soon as a non-numeric cell encounters it exit the macro due the following lines of code. Else Exit Sub End If Bakul, Try the below code: Sub Insertequals() Dim cel As Range For Each cel In Selection If IsNumeric(cel.Value) Then cel.Value = = cel.Value End If Next End Sub Manish Purohit Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world. _ From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Abdul Shakeel Sent: 15 August 2009 20:34 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: insert = in every cell as prefix with nos Blank Hi Bakul Try this one Sub Insertequals() Dim cel As Range For Each cel In Selection If IsNumeric(cel.Value) Then cel.Value = = cel.Value Else Exit Sub End If Next End Sub From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Dave Bonallack Sent: Saturday, August 15, 2009 7:00 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: insert = in every cell as prefix with nos Blank Hi Bakul, You'll have to include the formatting lines in the code I sent in order to insert an = in front of numbers and into blank cells. Regards - Dave. _ Date: Fri, 14 Aug 2009 15:02:13 -0700 From: bakulpatel...@yahoo.com Subject: $$Excel-Macros$$ Re: insert = in every cell as prefix with nos Blank To: excel-macros@googlegroups.com HI Abdul, Is it possible to insert '=' as prefix with nos only. If yes, give me code. Thanks, Bakul --- On Fri, 8/14/09, Abdul Shakeel shakeel@gmail.com wrote: From: Abdul Shakeel shakeel@gmail.com Subject: $$Excel-Macros$$ Re: insert = in every cell as prefix with nos Blank To: excel-macros@googlegroups.com Date: Friday, August 14, 2009, 1:42 AM This code just a refined shape of Dave code for multi cell selection, just put the code in any vba module select your desired range where you want to put = sign and press Alt+F8 run insert equal macro. Sub InsertEquals() Dim cel As Range With Selection For Each cel In Selection cel.Value = = cel.Value Next End With End Sub From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Dave Bonallack Sent: Friday, August 14, 2009 7:03 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: insert = in every cell as prefix with nos Blank Hi, I think you'll have to use VBA. Have a look at the attachment. Select the cell you want to change, then press Ctrl+q However. the cell is then treated as text, and cannot be used in calculations. Is this what you need? Regards - Dave. _ From: manoj...@ocimumbio.com To: excel-macros@googlegroups.com Date: Thu, 13 Aug 2009 19:55:41 +0530 Subject: $$Excel-Macros$$ Re: insert = in every cell as prefix with nos Blank suppose i have list and want to insert X' or ='' before each no., is there any way to insert the item(x,=) as prefix by using find Replace method or any other method _ From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Manoj Kukrej Sent: Thursday, August 13, 2009 5:44 PM To: excel-macros@googlegroups.com Cc: vivek.agrawa...@gmail.com; upendrasinghsen...@gmail.com; aind...@gmail.com; dilipan...@gmail.com Subject: $$Excel-Macros$$ Re: insert = in every cell with nos Blank _ From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of vivek agrawal Sent: Thursday, August 13, 2009 5:03 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: insert = in every cell with nos Blank attached is the file... hope this is the solution required Thanks and Regards, Vivek Agrawal Skype ID - vivek.agrawal83 GoogleMoonlight.com - Saving energy-Save Earth On Wed, Aug 12, 2009 at 8:44 PM, Manoj Kukrej manoj...@ocimumbio.com http://us.mc337.mail.yahoo.com/mc/compose?to=manoj...@ocimumbio.com wrote: Hi want to insert = sign before each no. for variety of calculation purpose see attached file Manoj _ From: excel-macros@googlegroups.com http://us.mc337.mail.yahoo.com/mc/compose?to=excel-mac...@googlegroups.com [mailto:excel-macros@googlegroups.com http://us.mc337.mail.yahoo.com/mc/compose?to=excel-mac...@googlegroups.com ] On Behalf Of Aindril De Sent: Wednesday, June 24, 2009 9:15 PM To: excel-macros@googlegroups.com http://us.mc337.mail.yahoo.com/mc/compose?to=excel-mac...@googlegroups.com Subject: $$Excel-Macros$$ Re: replace when large data in cell Sorry forgot to upload the file... Regards, Andy On Wed, Jun 24, 2009 at 8:38 PM, Aindril De aind...@gmail.com http://us.mc337
$$Excel-Macros$$ Re: Help with formatting and subtotal
We can do the subtotal in one cell only. Find the attached excel file for the solutions Manish Purohit Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world. _ From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of vivek agrawal Sent: 15 August 2009 11:28 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Help with formatting and subtotal Hi Attached is the solution to problem.. you just need to hide to column C. Hope this helps Thanks and Regards, Vivek Agrawal Skype ID - vivek.agrawal83 GoogleMoonlight.com - Saving energy-Save Earth On Sat, Aug 15, 2009 at 1:34 AM, Cesar Delanoval cdelano...@gmail.com wrote: I have a simple table of banking transactions. Columns are as follows Column A = Date in MM/DD/YY format Column B = Empty Column C = Unit ID Column D = Empty Column E = Amount (numeric, 2 decimals) Column F = Empty Column G = Check No Column H = Subtotal On this sheet, the transactions are listed and sorted by date. There could be several transactions on a single date or just one transaction on a single date. I am looking for a way to Subtotal the column E (Amount) by date and show that Sub-Total on colum H (see attached sample which was done manually) As an addional formatting I also like to place like from column A to column H whenever the subtotal is calculated to visually show the group of transactions within that subtotal. see sample below DateUnit ID Amount Check NoSubTotal 7/10/2009 5220363.39 1083 7/10/2009 3103222.25 6754 7/10/2009 5118222.25 2508 7/10/2009 3102211.88 1232 7/10/2009 3218188.37 393 7/10/2009 3206211.88 2704 7/10/2009 6211188.37 0 7/10/2009 6221195.00 785 7/10/2009 3220363.39 414 2166.78 7/13/2009 4218188.37 480 7/13/2009 3117270.00 712 7/13/2009 4106267.30 5026725.67 7/14/2009 6227188.37 2206 7/14/2009 6216188.37 798 376.74 7/16/2009 6229211.88 6285211.88 7/17/2009 5110222.25 613 7/17/2009 5229211.88 3287 7/17/2009 6209268.12 1061702.25 The number of transactions varies so it is never the same. if anyone has a question, feel free to call me. Thanks --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com HELP US GROW !! We reach over 5,200 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~--- Sample_Subtotal.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: How to delete characters after a value in a cell?
You can use replace all and in find area put .com* (without quotes) and in replace area put .com. Manish Purohit Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of mkabwilliams Sent: 25 July 2009 08:57 To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ How to delete characters after a value in a cell? I've got a list of URLs in excel that i need to delete everything after the .com in the url. Could someone assist me with how to do this? --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com HELP US GROW !! We reach over 5,200 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: How to retrieve the address of a cell used in a formula
Could you attaché the excel file. Manish Purohit Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world. -Original Message- From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Telenochek Sent: 25 July 2009 01:22 To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ How to retrieve the address of a cell used in a formula Hello everyone, I have a worksheet that calculates system performance on Sheet1 based on electronic components, whose data is located in other Sheets (sheets 2,3...n). I would like to have the user simply select a component from another sheet to place in a system, and have it import the rest of the data automatically. For example, suppose the user wants place an amplifier5 (located in Amplifiers sheet) instead of the resistor3 (located in the Resistors sheet) in my system. I'm wondering if there's a simple way to extract the source address from a formula. For example, suppose the user types the equals sign = and then goes to Amplifiers sheet and selects the name of the amplifier (located in E1 cell, where E2:E20) contain data relevant to this component. Now the formula for cell A1is: = Amplifiers!E1 How can I extract this address, so that I can automatically fill data from Amplifiers!E2:E20. CELL(address, A1) does not work, since it will return A1, not Amplifiers!E1 Do I have to write a macro with an InputBox, or is there an easier way? Assume a non-technical user, we don't want the user to type in =CELL (address, etc..) Thanks --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com HELP US GROW !! We reach over 5,200 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: 1Hardcode a cell value to the code(macro code) |||||||||||||| 2code to save only a particular cell value at the time of closing the workbook
Hi, I think it will work better without using any variable. Try using the below code Sub PasteAsValue() Range(A1).Copy Range(A1).PasteSpecial xlPasteValues End Sub Regards, Manish Purohit Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world. _ From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Dave Bonallack Sent: 15 July 2009 08:21 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: 1Hardcode a cell value to the code(macro code) || 2code to save only a particular cell value at the time of closing the workbook Hi, That could be done by saving A1 to a variable, then clearing the sheet, then re-populate A1. Something like: A = Range(A1).Value ActiveSheet.Cells.ClearContents Range(A1) = A Regards - Dave. _ Date: Tue, 14 Jul 2009 12:28:09 +0530 Subject: $$Excel-Macros$$ 1Hardcode a cell value to the code(macro code) || 2code to save only a particular cell value at the time of closing the workbook From: bhargha...@gmail.com To: excel-macros@googlegroups.com Hello all, 1 I need a code which hardcodes a cell value to the macro 2 Is it possible to only save cell contents.In otherwords, I want the contents of the work book to be deleted except a particular cell say a1. Thanks and Regards Bharghav R /html --~--~-~--~~~---~--~~ -- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com HELP US GROW !! We reach over 5,000 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
