$$Excel-Macros$$ Re: Sorting a column
Hi, Just formatting a cell as number after the cell has received data, does not change the format of the data. If all your data is numeric, then follow these steps to actually change your data to numbers: 1. Select Range A71:A1000 2. Format as number. 3. Enter 1 into an unused cell, then right-click that cell, and select Copy. 4. Select Range A71:A1000, then right-click and select Paste Special. 5. Select Multiply from the Operation options. 6. Click OK. Now run your macro again, and it should sort as you want. If your data is a mix of numbers and letters, eg 1234ABCD, then the operation is more complex, but still possible. Regards - Dave. Date: Thu, 23 Oct 2008 02:36:27 -0700 Subject: $$Excel-Macros$$ Sorting a column From: [EMAIL PROTECTED] To: excel-macros@googlegroups.com Hi All I'm trying to code a worksheet so that when I press a button, it sorts the column in numerical order. I have been using the code Private Sub commandbutton12_click() Range(A71:U1000).Select Selection.Sort Key1:=Range(A71), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range(A2).Select Call CommandButton1_Click End Sub However, whether the cells are formatted as numbers, general or text, the numbers sort in the following manner 1 10 2 20 etc rather than 1 2 10 20etc How can I sort them as per the second manner please? Many thanks in advance _ --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Sorting a column
You're welcome. Date: Fri, 24 Oct 2008 02:47:33 -0700 Subject: $$Excel-Macros$$ Re: Sorting a column From: [EMAIL PROTECTED] To: excel-macros@googlegroups.com Thanks Dave Has worked a charm! I have formatted the cells that data shall be placed into (via macro) in advance, so should not be a problem in the future. Thank you kindly once again! On Oct 23, 5:28 pm, Dave Bonallack [EMAIL PROTECTED] wrote: Hi, Just formatting a cell as number after the cell has received data, does not change the format of the data. If all your data is numeric, then follow these steps to actually change your data to numbers: 1. Select Range A71:A1000 2. Format as number. 3. Enter 1 into an unused cell, then right-click that cell, and select Copy. 4. Select Range A71:A1000, then right-click and select Paste Special. 5. Select Multiply from the Operation options. 6. Click OK. Now run your macro again, and it should sort as you want. If your data is a mix of numbers and letters, eg 1234ABCD, then the operation is more complex, but still possible. Regards - Dave. Date: Thu, 23 Oct 2008 02:36:27 -0700 Subject: $$Excel-Macros$$ Sorting a column From: [EMAIL PROTECTED] To: excel-macros@googlegroups.com Hi All I'm trying to code a worksheet so that when I press a button, it sorts the column in numerical order. I have been using the code Private Sub commandbutton12_click() Range(A71:U1000).Select Selection.Sort Key1:=Range(A71), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range(A2).Select Call CommandButton1_Click End Sub However, whether the cells are formatted as numbers, general or text, the numbers sort in the following manner 1 10 2 20 etc rather than 1 2 10 20etc How can I sort them as per the second manner please? Many thanks in advance _- Hide quoted text - - Show quoted text - _ --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: counting values
Hi Dean, You can probably use SUMPRODUCT, but if you could attach your spreadsheet, or a sample thereof, it'd be easier. Regards - Dave. Date: Mon, 17 Nov 2008 18:14:14 -0800 Subject: $$Excel-Macros$$ counting values From: [EMAIL PROTECTED] To: excel-macros@googlegroups.com I have a list of building data. Within that data, there are 15 unique towns. I want to know how many times a building of a certain age group occurs in each town. There are 13 age groups. For example: Los Angeles - 1930 = 34,000 Santa Barbara - 1910 = 670 I have tried COUNTIF and other formulas, as well as advanced filtering, and I have not been able to find the right thing to help me. Can anyone offer any assistance? Thanks in advance, Dean _ Time for change? Find your ideal job with SEEK. http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Eseek%2Ecom%2Eau%2F%3Ftracking%3Dsk%3Atl%3Ask%3Anine%3A0%3Ahottag%3Achange_t=757263783_r=SEEK_tagline_m=EXT --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Validating a column of numbers against another worksheet
Hi Rob, Yeah, sorry about that. Countif is pretty slow when there's lots of data. By the way, a macro will be slower, but has the distinct advantage that you can run it only when convenient. Just looking at your code, I see that you are using the Cells thingy. When using Cells, you cant refer to column by their letters, like you can when you use Range only. I think the following line is incorrect for 2 reasons: Set rangeICD9 = Sheets(ICD9Codes).Range(Cells(2, B), Cells(7000, B).End(1)) You have used B for the column ref, but you need to use the actual column number. Also, I think the last bracket is in the wrong place. I think the line should look like this: Set rangeICD9 = Sheets(ICD9Codes).Range(Cells(2, 2), Cells(7000, 2)).End(1) Notice also that the double quotes are also not used. Go through your code, replacing all alphabet column refs with numbers in any place where you have used the Cells thingy. The line with W in it also has a missplaced bracket, same as above. Regards - Dave. Date: Mon, 5 Jan 2009 17:24:35 -0800 Subject: $$Excel-Macros$$ Re: Validating a column of numbers against another worksheet From: robbuonoc...@infocore.us To: excel-macros@googlegroups.com Dave: Thanks for your response. I tried this solution but ran into performance issues. There are 10 columns that hold values and sometimes hundreds of rows of data. There are also approx. 20,000 valid ICD9 codes that this is validating against. Moving around the spreadsheet caused serious delays. I have a list of Diagnosis codes entered into 5 columns on a worksheet. The second worksheet, named ICD9 Codes, lists all the possible ICD9 Codes that are valid. I want to create a macro that you could activate at the top of a column and it would validate each number against the list in the second worksheet. If the value was valid, it would set the color of the cell to green, if it was not valid, it would set the color to red. Here is the code I have so far. Sub ValidateICD9() ' ' ValidateICD9 Macro ' Keyboard Shortcut: Ctrl+z ' Dim iRow As Long Dim rangeICD9 As Range Dim rCell As Range ' Select Source Data Worksheet Set rangeICD9 = Sheets(ICD9Codes).Range(Cells(2, B), Cells (7000, B).End(1)) Sheets(ICD9).Select For Each rCell In Range(Cells(2, W), Cells(Rows.Count, W).End (xlUp)) If rangeICD9.Find(what:=rCell, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then rCell.Font.ColorIndex = 3 Else rCell.Font.ColorIndex = 3 End If Next End Sub When I run this, I get a message that Subscript is out of range. Right now its set for only column W. I don't know how to set this to the active column. Any help would be appreciated. Thanks. Rob Buonocore _ Holiday cheer from Messenger. Download free emoticons today!http://livelife.ninemsn.com.au/article.aspx?id=669758- Hide quoted text - - Show quoted text - _ Messenger's gift to you! Download free emoticons today! http://livelife.ninemsn.com.au/article.aspx?id=669758 --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com If you find any spam message in the group, please send an email to Ayush @ jainayus...@gmail.com -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Need an option to select any text file when importing into a worksheet
Hi Steve, Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:=Text Files, *.txt) If myFileName = False Then Exit Sub 'user hits cancel With ActiveSheet With .QueryTables.Add(Connection:=TEXT; myFileName, Destination:=.Range(AA2)) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlFixedWidth .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileFixedColumnWidths = Array(20, 88, 60, 6, 2, 60, 30, 12, 1, 20, 4, 9, 6, 12, 10, _ 10, 12) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub Any help would be greatly appreciated. Thanks, Steve _ Net yourself a bargain. Find great deals on eBay. http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Frover%2Eebay%2Ecom%2Frover%2F1%2F705%2D10129%2D5668%2D323%2F4%3Fid%3D10_t=763807330_r=hotmailTAGLINES_m=EXT --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com If you find any spam message in the group, please send an email to Ayush @ jainayus...@gmail.com -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Help-Excel files formula
Hi, Please check attached for your solution. Regards - Dave. Date: Tue, 3 Feb 2009 17:39:40 +0300Subject: $$Excel-Macros$$ Help-Excel files formulaFrom: rabinthapa2...@gmail.comto: excel-macros@googlegroups.com Please help me on attach files.-- Regards,Rabindra Thapa¨`·.·´¨) Always `·.¸(¨`·.·´¨) Keep (¨`·.·´¨)¸.·´ Smiling! `·.¸.·´ (¨`·.·´¨) (¨`·.·´¨) `·.¸(¨`·.·´¨)¸.·´ `·.¸.·«*:·. .·:* *:·. *«*:·. .·:* *:·._ It's simple! Sell your car for just $50 http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com If you find any spam message in the group, please send an email to Ayush @ jainayus...@gmail.com -~--~~~~--~~--~--~--- Book1(1).xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: How to avoid NA# or remove NA# when there is no value found by vlookup.
Hi, If you have xl2007, you can use the new built-in function: IFERR. Dave. Date: Wed, 25 Feb 2009 09:16:37 -0800 Subject: $$Excel-Macros$$ How to avoid NA# or remove NA# when there is no value found by vlookup. From: prabhat.shrivasta...@gmail.com To: excel-macros@googlegroups.com CC: prabhat.shrivast...@hdfcbank.com Dear All, Pls help me out to avoid NA# error while there is no value found by Vlookup. let suppose if there is in a cloumn i did not found any value in define range than result would come as NA#. Now i want get the result other than NA# or result define by me. Thanks and Regrds Prabhat Shrivastava HDFC Bank _ Get rid of those unwanted christmas presents! Get what you want at ebay. http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Frover%2Eebay%2Ecom%2Frover%2F1%2F705%2D10129%2D5668%2D323%2F4%3Fid%3D10_t=763807330_r=hotmailTAGLINES_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: complete checkbox automatically
Hi Wilz I think this would be easier done with conditional formatting. Would it be acceptable if the cell colour in Column 3 were red when the conditions weren't met? Let's say your data is in Columns A, B and C, starting in Row 2. In the first row of Column 3, put the following into the Formula is line of the conditional formatting: =OR(A2=,B2=) Then set the format you want - perhaps red fill, Then copy down as far as your data goes. Hope this helps. Regards - Dave Date: Fri, 27 Feb 2009 05:21:31 -0800 Subject: $$Excel-Macros$$ complete checkbox automatically From: barthwil...@gmail.com To: excel-macros@googlegroups.com Newbie question: I have a worksheet with three columns and multiple rows When column 1 and column 2 have entries (i.e., the cells are no longer blank), I want a checkbox in column 3 to be automatically checked In this manner, I can tell which rows need my attention Is there anyway to do this with VBA? thanks in advance, wilz _ Want to marry your mail? Combine your email accounts here! http://livelife.ninemsn.com.au/article.aspx?id=633386 --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Find next available row
Hi, This is the code I use for this. Suppose you are looking for the next available row in column A A = 1 Do Until Cells(A, 1) = A = A + 1 Loop When this section finishes, the next available cell: Cells(A, 1) If your data is contiguous, you can use: A = WorksheetFunction.CountA(A:A) + 1 Next available cell: Cells(A, 1) Regards - Dave. _ Looking for a place to manage all your online stuff? Explore the new Windows Live . http://www.microsoft.com/australia/windows/windowslive/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: COLOR CELLS BY % PERCENTAGE 33% Color one cell 33% , another cell 33%, another cell 34%
Hi EU777, As far as I know, this sort of formatting is only available in XL2007. Regards - Dave Date: Thu, 12 Mar 2009 04:51:00 -0700 Subject: $$Excel-Macros$$ COLOR CELLS BY % PERCENTAGE 33% Color one cell 33% , another cell 33%, another cell 34% From: lax_...@comcast.net To: excel-macros@googlegroups.com HI basically i'd like to only show coloring in part of a cell so for example cell A1 is grey only 33% of the cell is grey I'd like to color one cell grey 33% I have several cells to color, different percentages... once i see one macro i can create the other percentages... Is that possible with a macro? appreciate all your help Excel 2003 preferably Thanks, ExcelUser777 _ View photos of singles in your area. Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fchannel%2Findex%2Easpx%3Ftrackingid%3D1046247_t=773166080_r=Hotmail_Endtext_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Data Validation Problem with Copy/Paste
Hi xmux, This is the main weakness of data validation. The only way around it is to check the entry using a VBA Change event code. Regards - Dave. _ View photos of singles in your area. Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fchannel%2Findex%2Easpx%3Ftrackingid%3D1046247_t=773166080_r=Hotmail_Endtext_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: User input as an argument in duplicate deletion macro
Hi, You need to use an input box, not a message box. Instead of: MsgBox (Select Source sheet against which data will be checked) Use: Ans = Inputbox(Select Source sheet against which data will be checked) The Ans variable will contain whatever the user inputs. Remember that when asking for a column, you probably need a number, not a letter. Somehow you will have to pursuade the operator to enter 1 for Col A, or 2 for col B etc. Regards - Dave. Date: Mon, 23 Mar 2009 14:18:08 -0700 Subject: $$Excel-Macros$$ User input as an argument in duplicate deletion macro From: firstcasua...@googlemail.com To: excel-macros@googlegroups.com Hi, I'm putting together a macro that will allow a user to select two separate workbooks and compare one column in each workbook to find any matches across them. Where there is a match it will copy the row to a third sheet and delete it from the original sheet, before moving on to the next value. I have an input box asking the user which column number should be searched but I don't know how to make the macro use their response in the logic test here - If x.Value = Sheets(1).Cells(iCtr, 1).Value Then. Could anyone give me some pointers on that please? Dim iListCount As Integer Dim iCtr As Integer Dim LoopRange As Range Dim HoldRng As Range Dim MatchRow As Range Dim SourceColumn As Integer Dim SourceSheet As Range Dim TargetSheet As Range MsgBox (Select Source sheet against which data will be checked) FileName = Application.GetOpenFilename Workbooks.Open FileName If FileName = Then End MsgBox (Select Target sheet from which duplicates will be deleted) Set FileName = SourceSheet FileName2 = Application.GetOpenFilename Workbooks.Open FileName2 If FileName2 = Then End Set FileName2 = TargetSheet FileNum = FreeFile() ' Get count of records to search through (list that will be deleted). iListCount = Sheets(1).UsedRange.Rows.Count SourceColumn = InputBox(Enter the number of the matching column from Left To Right, e.g 1 for A, 2 for B, Column Selection) Application.ScreenUpdating = False ' Loop through the master list. For Each x In Sheets(1).UsedRange ' Loop through all records in the second list. For iCtr = 1 To iListCount ' Do comparison of next record. ' To specify a different column, change 1 to the column number. If x.Value = Sheets(1).Cells(iCtr, 1).Value Then ' If match is true then delete row. Sheets(1).Cells(iCtr, 1).EntireRow.Delete xlShiftUp ' Increment counter to account for deleted row. iCtr = iCtr + 1 End If Next iCtr Next Application.ScreenUpdating = True MsgBox All duplicates removed _ View photos of singles in your area. Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fchannel%2Findex%2Easpx%3Ftrackingid%3D1046247_t=773166080_r=Hotmail_Endtext_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ RE: $$Excel-Macros$$
Hi Joe, The reason you get the flood of emails not solely pertaining to yourself is so that perhaps you can help others with their problems, and not simply receive solutions to your own. This is how the group works - everyone pitching in. Regards - Dave. From: j...@it4u.co.uk To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Date: Mon, 23 Mar 2009 14:29:35 + Hi chaps, Can anyone please tell me how I disable the flood of emails I get from Excel Googlegroups other than those pertinent to any questions I post myself ?? Thank you Joe _ View photos of singles in your area. Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fchannel%2Findex%2Easpx%3Ftrackingid%3D1046247_t=773166080_r=Hotmail_Endtext_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Create Formula to Total Certain Cells
Hi, If your 2 columns are A and B, try this: =SUMPRODUCT(--(LEFT(A2:A20,3)=Tot)*(B2:B20)) Change the ranges to suit your data. Regards - Dave. _ Find out what’s new with your friends Download the new Windows Live Messenger http://download.live.com/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Autorun a macro on save
Hi Hamster, If you go to the Workbook window of the VBA, where your Workbook Open macros live, set the left drop-down to Workbook, then in the right drop-down, select: BeforeSave. You will se a blank sub like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub Then just put your code in. Hope this helps. Regards - Dave. _ The new Windows Live Messenger has landed. Download it here. http://download.live.com/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: UserForm objects/controls collections?
Hi, I'm not sure if this would work with forms, but when I put multiple text boxes on a worksheet, I can change them all with the following code: ActiveSheet.Shapes.Range(Array(Text Box 1, Text Box 2, Text Box 3)).Select Selection.ShapeRange.Height = 84.75 Selection.ShapeRange.Width = 283.5 The above is courtesy of the macro recorder, slightly edited. Perhaps this can be adapted for use with forms. Regards - Dave. Are you sure there are absolutely no absolutes? _ The new Windows Live Messenger has landed. Download it here. http://download.live.com/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Concatenate Macro
Hi Steve, Try this code. It assumes data is in Columns A and B, as stated, and that the data starts in Row 2. Sub Steve() Application.ScreenUpdating = False A = 2 B = 2 C = 2 For i = 1 To 100 Do Until Cells(A + 1, 1) Cells(B, 2) = WorksheetFunction.trim(Cells(B, 2) Cells(A + 1, 2)) Range(Cells(A + 1, 1), Cells(A + 1, 2)).Delete Shift:=xlUp C = C + 1 If C - A 200 Then Exit Sub Loop A = A + 1 B = A Next i Application.ScreenUpdating = True End Sub Hi, I could really use some help with creating a macro. I have two columns of data. The first column provides me with a list of Part Numbers and the second column Provides the Description of the Part Numbers. There are multiple rows for the Descriptions because that's the way they come into my spreadsheet. Looks something like the following: (Column A)(Column B) Part Number Description 100-100 Box with Cover and 4 slotted sides 100-211 Bubble Wrap, ESD sensitive 100-344 Shipping crate with pallet. I need the information in column B to be concatenated onto one line, preferably in the same row as the Part Numbers. Any help would be much appreciated. Thanks, Steve _ Looking to change your car this year? Find car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Dcount or Countif?
Hi Steve, I need an equation that will count the number of records with a name of Jack and a state of Michigan and filter that count to a range. I will drop the count for each range in a different cell so the equation would be a static age bracket. Sorry, but I can't understand the part I have highlighted. Regards - Dave. I have a spreadsheet with 5 columns and need to count the number of responses between a range based on the criteria of two cells. For instance the range is A1:E16 Columns A = Name B = Address C = City D = State E = Age Criteria A1 = Jack D2 = Michigan Count Brackets: Age = 20-29, 30-39, 40-49, 50-59 I need an equation that will count the number of records with a name of Jack and a state of Michigan and filter that count to a range. I will drop the count for each range in a different cell so the equation would be a static age bracket. Can anymore make any suggestions? _ Need a new place to rent, share or buy? Let ninemsn property search for you. http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Macros in Outlook
Hi Mudassar, Thankyou for posting the email workbook. Could you please tell us the VBA password so we can see how it works? Regards - Dave. From: johnplaye...@gmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Macros in Outlook Date: Wed, 8 Apr 2009 11:05:08 +0500 Please find the attached file. Maybe helpful for you. I didn't remember who share. But its better one I have seen ever. Regards, Mudassar Ramzan - Original Message - From: shashank bhosle To: excel-macros@googlegroups.com Sent: Tuesday, April 07, 2009 11:24 PM Subject: $$Excel-Macros$$ Macros in Outlook Hi I have to send a same mail frequently to all the employee.so i need to some macros where as i run the macro,the content to automatically apear in new mail Thanks and Regards Shashank Bhosle Life is all about Uncertaity Add more friends to your messenger and enjoy! Invite them now./A _ View photos of singles in your area Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fchannel%2Findex%2Easpx%3Ftrackingid%3D1046247_t=773166080_r=Hotmail_Endtext_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Macros in Outlook
Thanks. Date: Wed, 8 Apr 2009 18:42:25 -0700 From: alokeshwar.tiw...@yahoo.com Subject: $$Excel-Macros$$ Re: Macros in Outlook To: excel-macros@googlegroups.com There is no password.. see attached _ There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Thursday, 9 April, 2009 7:07:51 AM Subject: $$Excel-Macros$$ Re: Macros in Outlook Hi Mudassar, Thankyou for posting the email workbook. Could you please tell us the VBA password so we can see how it works? Regards - Dave. From: johnplaye...@gmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Macros in Outlook Date: Wed, 8 Apr 2009 11:05:08 +0500 Please find the attached file. Maybe helpful for you. I didn't remember who share. But its better one I have seen ever. Regards, Mudassar Ramzan - Original Message - From: shashank bhosle To: excel-macros@googlegroups.com Sent: Tuesday, April 07, 2009 11:24 PM Subject: $$Excel-Macros$$ Macros in Outlook Hi I have to send a same mail frequently to all the employee.so i need to some macros where as i run the macro,the content to automatically apear in new mail Thanks and Regards Shashank Bhosle Life is all about Uncertaity Add more friends to your messenger and enjoy! Invite them now./A Add more friends to your messenger and enjoy! Invite them now. _ Looking for a fresh way to share photos? Get the new Windows Live Messenger. http://download.live.com/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Macros in Outlook
Hi Roopesh, Thanks a lot. Regards - Dave. From: roopesh.ka...@gmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Macros in Outlook Date: Thu, 9 Apr 2009 08:00:32 +0530 Dave, Below is the code of the macro in the file that Mudassar. I am attaching another file that I use for bulk mailing, I have a requirement where in on a given day I need to send over 1000 emails and that database of mine is growing each day, I cannot afford to sit and press “Yes” on the security warning so I have altered the code to suit my need and it works perfect. Pls read the code carefully as there are some fine points that you need to read properly. Regards, Roopesh Kapur Sub send(i As Integer) Set myOlApp = CreateObject(Outlook.Application) Set mail = myOlApp.CreateItem(olMailItem) Set attach = mail.Attachments Worksheets(2).Select mail.To = Cells(i, 1) mail.CC = Cells(i, 2) mail.BCC = Cells(i, 3) mail.Subject = Cells(i, 4) mail.Body = Cells(i, 5) If Cells(i, 6) Then attach.Add Cells(i, 6) End If mail.send End Sub Sub email() Dim count As Integer Dim i As Integer Dim ok As Boolean Dim errcount As Integer Dim message As String Worksheets(1).Select count = Cells(11, 8) Worksheets(2).Select ok = True errcount = 0 For i = 2 To count + 1 If Cells(i, 1) = And Cells(i, 2) = And Cells(i, 3) = Then ok = False errcount = errcount + 1 End If Next i If ok = True Then For i = 2 To count + 1 send (i) Next i Else message = MsgBox(You should have atleast one address in TO,CC or BCC field ( errcount errors detected.), vbOKOnly) = vbOK End If End Sub From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Dave Bonallack Sent: Thursday, April 09, 2009 7:33 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Macros in Outlook Thanks. Date: Wed, 8 Apr 2009 18:42:25 -0700 From: alokeshwar.tiw...@yahoo.com Subject: $$Excel-Macros$$ Re: Macros in Outlook To: excel-macros@googlegroups.com There is no password.. see attached _ There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Thursday, 9 April, 2009 7:07:51 AM Subject: $$Excel-Macros$$ Re: Macros in Outlook Hi Mudassar, Thankyou for posting the email workbook. Could you please tell us the VBA password so we can see how it works? Regards - Dave. From: johnplaye...@gmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Macros in Outlook Date: Wed, 8 Apr 2009 11:05:08 +0500 Please find the attached file. Maybe helpful for you. I didn't remember who share. But its better one I have seen ever. Regards, Mudassar Ramzan - Original Message - From: shashank bhosle To: excel-macros@googlegroups.com Sent: Tuesday, April 07, 2009 11:24 PM Subject: $$Excel-Macros$$ Macros in Outlook Hi I have to send a same mail frequently to all the employee.so i need to some macros where as i run the macro,the content to automatically apear in new mail Thanks and Regards Shashank Bhosle Life is all about Uncertaity Add more friends to your messenger and enjoy! Invite them now./A Add more friends to your messenger and enjoy! Invite them now. /html _ View photos of singles in your area Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fchannel%2Findex%2Easpx%3Ftrackingid%3D1046247_t=773166080_r=Hotmail_Endtext_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: color code excel cell if a cell has a formula
Hi, A quick way to reveal which cells have formulas: Ctrl+` That's Ctrl plus the key above the Tab key. Regards - Dave. Date: Mon, 13 Apr 2009 11:15:32 -0700 Subject: $$Excel-Macros$$ color code excel cell if a cell has a formula From: ned...@yahoo.com To: excel-macros@googlegroups.com could anyone tell me how to color code an excel cell if a cell has a formula. I have a long worksheet with lot of data and it it hard to tell which data is a formula and which is just value entered by users. Thanks _ Need a new place to rent, share or buy? Let ninemsn property search for you. http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: color code excel cell if a cell has a formula
Hi Shay, Thanks for that. Always learning. Regards - Dave. Date: Tue, 14 Apr 2009 10:30:53 +0530 Subject: $$Excel-Macros$$ Re: color code excel cell if a cell has a formula From: shahinarafi...@gmail.com To: excel-macros@googlegroups.com Dave, You can also select cont+G, click on special tab and again click on formula's tab, all cells whic contain formula's will get highlighted, you can now select the fill color option and colour the cells with your favourite colour. Regards, Shay On Tue, Apr 14, 2009 at 10:24 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi, A quick way to reveal which cells have formulas: Ctrl+` That's Ctrl plus the key above the Tab key. Regards - Dave. Date: Mon, 13 Apr 2009 11:15:32 -0700 Subject: $$Excel-Macros$$ color code excel cell if a cell has a formula From: ned...@yahoo.com To: excel-macros@googlegroups.com could anyone tell me how to color code an excel cell if a cell has a formula. I have a long worksheet with lot of data and it it hard to tell which data is a formula and which is just value entered by users. Thanks -- Shay A Thinker tends to use reason and logic A feeler tends to use values and subjective Judgement. Be a Thinker in Life. _ Looking for a fresh way to share photos? Get the new Windows Live Messenger. http://download.live.com/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Check if selected area contains some merged cells.
Hi ajxecc, Try this code: Sub mergedetect() Dim c As Range For Each c In Range(A1:A10) If c.MergeCells = True Then MsgBox c.Address is merged with another cell. Next c End Sub Change the Range ref to suit your needs. You can change the code after the Then keyword, to do anything you want. Regards - Dave. Date: Tue, 14 Apr 2009 13:40:57 -0700 Subject: $$Excel-Macros$$ Check if selected area contains some merged cells. From: ajx...@gmail.com To: excel-macros@googlegroups.com Hello I'm trying to write a small excel vba script that returns TRUE if in a selected area of cells there are any merged cells. Sounds simple, but I haven't found the solutions yet... :-( Is there a poperty that I can check for merged cells??? Or any other trick that helps Any hint is greatly appreciated.. ;-) thanks in advance. ajxecc _ View photos of singles in your area Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fchannel%2Findex%2Easpx%3Ftrackingid%3D1046247_t=773166080_r=Hotmail_Endtext_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Color Conditional Formatting
Hi Surya, As long as your data is in the format provided in your sample file, the solution in the attached will be ok. Regards - Dave. Date: Wed, 15 Apr 2009 12:59:11 +0530 Subject: $$Excel-Macros$$ Color Conditional Formatting From: suryaprasad...@gmail.com To: excel-macros@googlegroups.com Hi Guys, Can any one help me on the conditional formatting . Cheers Surya _ The new Windows Live Messenger has landed. Download it here. http://download.live.com/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- Sample(1).xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Need Help with autofill please !!!
Hi goog, Attach a sample workbook with 20 or so lines of data, with manually entered results of what you want. We should be able to help you. Regards - Dave. Date: Mon, 20 Apr 2009 17:35:46 -0700 Subject: $$Excel-Macros$$ Need Help with autofill please !!! From: amadugu...@gmail.com To: excel-macros@googlegroups.com Hi all, I am new to excel vba, but need help with a project i have that is due tomorrow. This is my question ... i have a spreadsheet with 10 columns like this.. Id col1 col2 col3 . col6 col7 col8 col9 col10 1 a a a 2 b b b 3 c c c val1 val2 val3 val4 val5 4 d a b 5 e a f val2 val3 val6 val2 val3 what i need is the cols 6 - col10 populated in rows 1, 2 with values from row 3 which is the first record which has data and cols 6 - col10 in row 4 copied from row 5 and so on the data needs to be populated from the first row which is not empty and autofill and so on. the spreadsheet is about 4 rows, so i cant do it manually without errors or in time i was wondering if there was a way to write some code that would do the same thing? Thanks in Advance !!! _ Need a new place to rent, share or buy? Let ninemsn property search for you. http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Need Help with Macro, Insert Rows based on another cell's value
Hi Dustin, Paste the following code into a module. Sub InsertMissing() Do Until ActiveCell.Value = A = ActiveCell.Value B = ActiveCell.Offset(1, 0).Value If B - A 1 Then ActiveCell.Offset(1, 0).EntireRow.Select Selection.Insert Shift:=xlDown ActiveCell.Value = ActiveCell.Offset(-1, 0) + 1 Else: ActiveCell.Offset(1, 0).Select End If Loop End Sub Before running, save your workbook in case something unexpected happens. You have to select the first numbered cell in your list, then run the code. Hope this helps. Regards - Dave. Date: Mon, 20 Apr 2009 07:57:06 -0700 Subject: $$Excel-Macros$$ Need Help with Macro, Insert Rows based on another cell's value From: dustin.ho...@gmail.com To: excel-macros@googlegroups.com Hello! I am new to this group, and new to VBA in general, so I would appreciate any insight / explanations into this =) Here is the situation: There will be raw data in the form of a spreadsheet which is in chronological order, though not in steps of 1. So for instance, Column A could be numbered 1,2,3,4,5,15,16,45 ...etc. There will also be corresponding data in columns following it. The following is an example of what it could look like (Columns A and B, though there will be many more columns) 100 this is 100 101 this is 101 102 this is 102 103 this is 103 104 this is 104 110 this is 110 ... What I need this macro to do, is to insert a row where the order is not in steps of 1, and then copy the appropriate number into that new row. Thus: 100 this is 100 101 this is 101 102 this is 102 103 this is 103 104 this is 104 105 106 107 108 109 110 this is 110 ... I would super appreciate help with this! Thanks!! Dustin _ Need a new place to rent, share or buy? Let ninemsn property search for you. http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Macro Help
Hi Dustin, You have to use the 'Cells' thingy instaed of the 'Range' thingie. Then you can use variables. But you have to use Column numbers, not Column letters, and you have to put the Row first, then the Column, separated by a comma. Example: YourRow=5 'Row YourCol=3'Column Cells(YourRow, YourCol) is the same as Range(C5) If you want a multi-cell range, the syntax is: Range(Cells(YourRow, YourCol), Cells(MyRow, MyCol)) If MyRow=20 and MyCol=10, the above would be the same as: Range(C5:J20) You can also use just numbers if you want: Cells(3, 2) is the same as Range(B3) Hope this helps. Regards - Dave. Date: Thu, 23 Apr 2009 10:54:34 -0700 Subject: $$Excel-Macros$$ Macro Help From: dustin.ho...@gmail.com To: excel-macros@googlegroups.com Okay guys, I can't get this one. I've been trying for hours to make this work and I am missing something small. Here's the deal: This is part of a larger set of macros I am writing. This snippet shows that I am looking for the next available row and then copying from a worksheet called RAW and then pasting that range into the next available row on the ExecServices worksheet. Worksheets(RAW).Range(A4701:CE4900).Copy _ Destination:=Worksheets(ExecServices).Range(A iNextRow) Note that iNextRow is a variable for the next available row. the above code works great. What I need, is to NOT have static cell references like ***Range(A4701:CE4900). I want to be able to specify those ranges with variables. So what I want will (I think) look something like this: Worksheets(RAW).Range(A4701:CE4900).Copy _ Destination:=Worksheets(ExecServices).Range(A iNextRow:CE iNextRow) but this returns an error any way I try to do it. I love this group and I have a lot of faith in you guys already =) Help is very much appreciated!! also, as a side note... is there a way to be notified when your discussion post is replied to? Dustin _ Looking for a fresh way to share photos? Get the new Windows Live Messenger. http://download.live.com/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: **ADD/Multiply/Subtract/Divide two Numbers**
Hi Paresh, The syntax is failry simple for what you want to do, but perhaps it would be easier done with a worksheet function. However, if you want do it by macro, the below is an example: Sub DoMath Answer1 = 4 + 5 Answer2 = 4 * 5 Answer3 = 4 - 5 Answer4 = 4 / 5 End Sub 'Answer1' , 'Answer2' etc, are called variables, which means they can represent anything you want. In our case, they represent the answers to the 4 maths equations. Regards - Dave. Date: Sat, 25 Apr 2009 10:29:41 +0530 Subject: $$Excel-Macros$$ **ADD/Multiply/Subtract/Divide two Numbers** From: paresh.g.2...@gmail.com To: excel-macros@googlegroups.com Dear Aayush, I am a beginer of Macros and please tell me how to add/subtract/multiply/divide two numbers in macros. Regards Paresh Gugale _ Looking for a fresh way to share photos? Get the new Windows Live Messenger. http://download.live.com/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Macro Problem
Hi, The following will select the last data column, Row 1: Cells(1, Columns.Count).End(xlToLeft).Select The following assigns the last data column number to the variable 'A' A = Cells(1, Columns.Count).End(xlToLeft).Column The following will select the last data row, Col A: Cells(Rows.Count, 1).End(xlUp).Select The following assigns the last data row number to the variable 'A' A = Cells(Rows.Count, 1).End(xlUp).Row Hope this helps. Date: Tue, 28 Apr 2009 10:27:16 -0700 Subject: $$Excel-Macros$$ Macro Problem From: phanikumar...@gmail.com To: excel-macros@googlegroups.com I Have a Macro which contains some Costing Related Report... Now i got a problem with the macro that for the next month that the data may vary and the Macro doesn't work as the last column Rows Differ ...So if any one can help me to automatically select the Last column and Last row and there by apply the Conditions specified in the Code... I will be Thankful if any one can help me For reference i have uploaded my File in this Following Link: Points to be Noted: 1) Remove the cr and Replace with - 2) Sum up all the Quarter(3months) and Keep the Formula without Paste Special 3) Subtotal the Data and insert the Serial no. 4) Color the SubTotal with Brown and Grand Total with Blue http://www.easy-share.com/1904815745/Email.xls Sub Macro1() ' ' Macro1 Macro ' Macro recorded 28/04/2009 by Phani kumar ' ' Range(A1:F559).Sort Key1:=Range(A2), Order1:=xlAscending, Key2:=Range _ (B2), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal Selection.AutoFilter Range(C1).Select Selection.AutoFilter Field:=3, Criteria1:==*cr*, Operator:=xlAnd Range(C1).Select Selection.End(xlToRight).Select Selection.End(xlToRight).Select Selection.End(xlToLeft).Select Range(AK1).Select ActiveCell.FormulaR1C1 = -1 Range(AK1).Select Selection.Copy Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Range(C64).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:=cr, Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Range(C1).Select Application.CutCopyMode = False Selection.AutoFilter Selection.AutoFilter Range(D1).Select Selection.AutoFilter Field:=4, Criteria1:==*cr*, Operator:=xlAnd Range(D154).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:=cr, Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range(D1).Select Selection.End(xlToRight).Select Selection.End(xlToRight).Select Selection.Copy Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Range(D154).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range(D1).Select Selection.AutoFilter Selection.AutoFilter Range(E1).Select Selection.AutoFilter Field:=5, Criteria1:==*cr*, Operator:=xlAnd Range(E221).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Replace What:=cr, Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.End(xlUp).Select Selection.End(xlToRight).Select Selection.End(xlToRight).Select Selection.Copy Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Range(E221).Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Range(E1).Select Selection.AutoFilter Range(F1).Select Selection.AutoFilter Range(F1).Select Selection.AutoFilter Columns(C:C).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Replace What:=dr, Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range(F1).Select Selection.AutoFilter Range(F1).Select Selection.AutoFilter Field:=6, Criteria1:==*cr*, Operator:=xlAnd Range(F64).Select Selection.Replace What:=cr, Replacement:=, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Range(F1).Select Selection.End(xlToRight).Select Selection.Copy Selection.End(xlToLeft).Select Range(F64).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False,
$$Excel-Macros$$ Re: Help with creating a unique set of numbers
Hi Grunta, This can't be done with worksheet formulas, as they will be re-calculated everytime something changes, as you have discovered. You will need to use the RND function in VBA to put a value in a cell. Since this is done only once, the value won't change when the sheet re-calcs. Regards - Dave. Date: Tue, 28 Apr 2009 16:21:28 -0700 Subject: $$Excel-Macros$$ Help with creating a unique set of numbers From: grun...@gmail.com To: excel-macros@googlegroups.com Hello I would like a solution regard to excel formulas / VBA scripting. The purpose is to assist in generatating unique PIN numbers for users on our phone system.. The numbers generated need to be a number between 1000 ... The basic formula I have used (=RANDBETWEEN(1000,)) does it initially, but if the formula gets extended or a row gets deleted the numbers are regenerated The numbers need to remain how they are at the initial time of running the formula.. Any ideas? – I guess there needs to be some fault tolerances involved, but for the life of me I would know what they are Kind Regards, Grant _ The new Windows Live Messenger has landed. Download it here. http://download.live.com/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: !!URGENT!! Formula Required Please HELP
Hi Ahmed An IF statement has the following: Firstly, the condition. Secondly, the result if the condition is met. Thirdly, the result if the condition is not met. Each of the above is separated by commas, so it looks like this: IF(Condition, Result if met, Result if not met) In your case, the condition is that C8 does not equal C7 In XL, we use '' to mean 'does not equal' So, in the formula: IF(C8C7,1,0) you see the 3 sections separated by commas, as described above. Firstly, the condition: C8C7 (C8 does not equal C7) Secondly, the result if the condition is met: 1 Thirdly, the result if the condition is not met: 0 Hope this helps. Regard - Dave. Date: Wed, 29 Apr 2009 23:24:02 +0100 Subject: $$Excel-Macros$$ Re: !!URGENT!! Formula Required Please HELP From: rogergov...@googlemail.com To: excel-macros@googlegroups.com Hi The formula is saying If the value in cell C8 is not equal to the value in cell C7, then return the value of 1, otherwise, return the value of 0. Regards Roger Govier Microsoft Excel MVP 2009/4/27 Ahmedhonest ahmedhon...@gmail.com Dear Fabio Lemos, I appreciate your solution to the Query but may i know the logic of this formula how it works plzzz =IF(C8C7,1,0) - Explain this plz Regards Ahmed On Mon, Apr 27, 2009 at 4:31 PM, Fabio Lemos flnle...@gmail.com wrote: Hi, You can use: =IF(C8C7,1,0) regards! 2009/4/27 Mayank Patel mkpat...@gmail.com Dear Friends Please suggest the formula. sample file is attached. Thanks to all in Advance Regards Mayank Patel ITC LTD 9822978041 9422749110 -- Fabio L Lemos email: flnle...@gmail.com -- Ahmed Bawazir -- Roger Govier rogergov...@gmail.com _ Looking for a fresh way to share photos? Get the new Windows Live Messenger. http://download.live.com/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Part Numbers
Hi Dan, You could use VLOOKUP. The syntax is VLOOKUP(Lookup_Value,Lookup_Table,Lookup_Column,Type) The Lookup_Value is the reference of your drop-down cell - eg B2 The Lookup_Table is as below, but needs to be in 2 columns - eg Y2:Z14 Lookup_Cloumn would be 2 Type would be 0 Hope this helps. Regards - Dave. Date: Thu, 30 Apr 2009 15:20:50 -0700 Subject: $$Excel-Macros$$ Part Numbers From: dbovin...@ovonic.com To: excel-macros@googlegroups.com Greetings, I am new to this list. If this question was recently asked, please point me to it and forgive me for asking it so soon. I want to make an Excel table to do a task. I want a drop down (I know how to use Data valadation with a list) where a user will pick a word in the drop down, and in another cell, an alpha-numeric part number will be the result. I have 20 or so part numbers, so a simple IF formula will not work, or if I could use it, it would very hard to make them over and over, and hard to edit. Here is sample: Drop down: Result: BHSCS AES18 SHCS AES14 FHSCS AES16 HHCS AEB02 Shoulder Soc. Cap Screw AES20 Soc. Cap Set Screw AES32 SAE Flat Washer AEW24 Std. Lock Washer AEW07 HI Collar Lock Washer AEW10 Hex Nut AEN04 Hex Jam Nut AEN05 Dowel AEP05 Roll Pin AEP23 Can this be done in Excel without a macro, API, VBA? I have been reading the help file for hours. Thanks for any help you may give me. Dan Rochester Hills, Michigan _ Looking to change your car this year? Find car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: VBA code to search a column for a string
Good. Glad to help. Date: Wed, 6 May 2009 05:22:51 -0700 Subject: $$Excel-Macros$$ Re: VBA code to search a column for a string From: dsrmccl...@gmail.com To: excel-macros@googlegroups.com Dave, Thanks. With a little bit of tweaking that's just what I needed! Doug On May 5, 10:12 pm, Dave Bonallack davebonall...@hotmail.com wrote: Hi, try this code somewhere in your sub: With Range(A1:A1000) D = Seed-BOD Set C = .Find(D, LookIn:=xlFormulas) If Not C Is Nothing Then MsgBox Sample is already here. End With If you are already using C and/or D as variables, you'll need to change them. You may need to change the range in the first line. Regards - Dave. Date: Tue, 5 May 2009 05:03:49 -0700 Subject: $$Excel-Macros$$ VBA code to search a column for a string From: dsrmccl...@gmail.com To: excel-macros@googlegroups.com Seems easy enough but I'm new at VBA so I can't seem to get it right. I have a sub that adds a row to my worksheet but this particular row needs to be unique. So all I want to do is call a function that will search the first column of the active worksheet for the string Seed- BOD. If it finds the string, it gives a message Sample is already here. If it doesn't find the string then the sub will continue. I don't want it to go to where the string is. I just want to confirm whether it exists or not. Thanks. _ Looking for a fresh way to share your photos? Check out the new Windows Live Messengerhttp://windowslive.ninemsn.com.au/article.aspx?id=792335- Hide quoted text - - Show quoted text - _ View photos of singles in your area Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fsearch%2Fsearch%2Easpx%3Fexec%3Dgo%26tp%3Dq%26gc%3D2%26tr%3D1%26lage%3D18%26uage%3D55%26cl%3D14%26sl%3D0%26dist%3D50%26po%3D1%26do%3D2%26trackingid%3D1046138%26r2s%3D1_t=773166090_r=Hotmail_Endtext_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Condtional Formatting - Query
Hi, If your 'Given Cell' is A1: Select the row you want to format In the Conditional Format window, select 'Formula is' In the formula thingy, put: =$A$1=Friday Set the formatting you want. Click ok. Hope this helps. Regards - Dave. Date: Sun, 10 May 2009 19:43:56 -0700 Subject: $$Excel-Macros$$ Condtional Formatting - Query From: kishore.maro...@gmail.com To: excel-macros@googlegroups.com Hello, I want to use conditional formatting for the below issue: eg: If say given cell of a row has value Friday then i want that entire row to be in Blue in color. Is the above function possble using conditiona formatting or mix of standard excel features, wihout using macros Thanks balla _ Want to stay on top of your life online? Find out how with Windows Live! http://windowslive.ninemsn.com.au/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Fwd: Help required
Hi Sharma, First you have to format all the necessary cells as 'Text', otherwise the 0 is lost. Then try putting this into data validation (Custom, Formula) of A3: =AND(ISNUMBER(VALUE(A3)),LEN(A3)=10,LEFT(A3,1)=0) Copy validation to necessary cells. Hope this helps. Regards - Dave Date: Wed, 13 May 2009 16:50:11 +0530 Subject: $$Excel-Macros$$ Re: Fwd: Help required From: amrahs...@gmail.com To: harmeet.hew...@gmail.com CC: excel-macros@googlegroups.com Hi Harmeet, Please find the attached sample workbook that contains a simple query for my project. Thanx in advance. Regards, Sharma On Wed, May 6, 2009 at 10:58 PM, Harmeet Singh harmeet.hew...@gmail.com wrote: Hi, Please see attached file. On Wed, May 6, 2009 at 5:53 PM, amrahs k amrahs...@gmail.com wrote: Hi Team, I need your help it is very urgent! Thanks, Sharma -- Forwarded message -- From: amrahs k amrahs...@gmail.com Date: Wed, May 6, 2009 at 5:33 PM Subject: Help required To: gyanjai...@gmail.com Hi Jain, I have attached the sample spreadsheet that i was working upon for my project. The requirement would be- Count the data that has been entered in each cell under coulumn D6:D17, F6:F17, H6:H17 and so on. (every alternate column) I want the macro to evaluate the total count of each alternate coulumn though I have few conditions. condition 1: count all the data entered in each cell (text or numeric value) condition 2: do not count the data NA It would be helpful if the macro populated the result in a message box- Ex: The Total Count is 13 (The attached document has 17 data in the range D6:D17, what i want to exclude the value NA and produce the result as 13). similarly for every alternate column. I believe you could do this since am not familiar with VBA macro. Thanx in advance. Regards, Sharma -- Thanks Regards, Harmeet Singh _ Looking for a fresh way to share your photos? Check out the new Windows Live Messenger http://windowslive.ninemsn.com.au/article.aspx?id=792335 --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: formula in macros
Hi Habeeb, Have a look at the attached, and see if it does what you need. Regards - Dave. From: habeebc...@hotmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ formula in macros Date: Fri, 15 May 2009 04:34:26 + Hi Guys, I currently written a code were in total will be populated by adding various sub totals. But I am not able to put in the formula for adding for future reference. I am not able to think a logic for that. I have explained my requirement in the attachement. Please help me Thank you Habeeb MSN Battles We pitch one stalwart against the other and give you the power. Who will you vote for? Share photos while you chat with Windows Live Messenger. _ Looking to move somewhere new this winter? Let ninemsn property help http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- subtotal.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Need a macro to automatically merge cells in the worksheet/workbook
Hi, If you don't need any merged cells in your sheet, try putting these lines at the beginning of your present code: Cells.Select With Selection .MergeCells = False End With Range(A1).Select Regards - Dave. Date: Thu, 21 May 2009 23:11:30 +0530 From: praveen.khu...@gmail.com To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Need a macro to automatically merge cells in the worksheet/workbook Hi all How's everyone doing? I am novice to macro, I need a help from you guys. I hope it should be simple. Present Scenario: I have a macro which remove non-breaking space from all the cells at once in the worksheet. (If anyone need, I can share the code). Usefulness of the Macro: When you copy data from the internet and paste in an excel worksheet, it contains lot of spaces before and after, and when you want to perform some calculation it doesn't give desired result. This macro removes spaces before and after so that your calculation (life) becomes easy. Problem: This macro stops working beyond the point where the it finds a merged cell. To overcome this, I need to select all the cells of the worksheet and then Go to Format Cells and click uncheck merge cells (as shown in image). This is tedious repetitive job since I have about 30 worksheets. Requirement: A macro which automatically merges all the cells of the worksheet (if possible-entire workbook). Warm Regards Praveen Khunte _ Looking to move somewhere new this winter? Let ninemsn property help http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- attachment: 2009-05-21_224641.png
$$Excel-Macros$$ Re: Formula needed
Hi Venkat, Sounds like a job for SUMPRODUCT. We could probably do the needful if you attached a sample worksheet. Regards - Dave. Date: Wed, 27 May 2009 09:19:07 +0530 Subject: $$Excel-Macros$$ Formula needed From: sudhakar...@gmail.com To: excel-macros@googlegroups.com Dear Friends, I need a formula for my work. There will be 2 Excel sheets. In First Sheet there will be 2 Columns In First column there will be code and in 2nd column there will be value and 'n' number of rows will be there. And the code may be repeated at several times in different rows. And in second sheet also, there will be 2 columns In first column there will be code. Now what i need is, against each item code in second sheet, the sum of values in first sheet representing corresponding code. Can any one do the needful. Regards, Venkat _ Looking to change your car this year? Find car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: $$Excel-Macros$$
You're welcome. Date: Wed, 27 May 2009 11:11:24 +0530 Subject: $$Excel-Macros$$ Re: $$Excel-Macros$$ From: atul.kesa...@gmail.com To: excel-macros@googlegroups.com Hello Dave, You are a rock Star...thanks a ton!!! The macro is awesome.this is what i wanted. Thanks and Regards Atul Kesaria On Wed, May 27, 2009 at 10:10 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi Atul, Have a look at the attached workbook, and see if it does what you want. I've added a button to run the macro from the sheet, but you may want to run it some other way. I've left your formulas as you had them. Regards - Dave. Date: Tue, 26 May 2009 18:33:57 +0530 Subject: $$Excel-Macros$$ Re: $$Excel-Macros$$ From: atul.kesa...@gmail.com To: excel-macros@googlegroups.com Hello Dave, Thanks for the below formulas. The reason I am asking for the macro is that we have more then 25 sheet in a file and more then 200 line items in each sheet, macro would help to reduce the file size and minimize the changes of error. I have attached a file in which i have tried to write a macro. Two things that i am unable to add. 1 )The macro should able to apply the formula till the last line 2) It should copy and paste special value all the formula applied. Thanking you once again in advance. Thanks and Regards Atul Kesaria On Tue, May 26, 2009 at 2:47 PM, Dave Bonallack davebonall...@hotmail.com wrote: Hi Atul, Just as a by-the-way, those formulas are much longer than needed. There are lots of redundant parts. The following would do the same thing: =IF(AND(B20,B2=1,0),1%,IF(b2=2,3%,IF(b2=3,5%,IF(b2=4,7.5%,IF(b2=5,8%,IF(b2=6,10%,0)) =IF(AND(b20,b2=1,0),11%,IF(b2=2,13%,IF(b2=3,15%,IF(b2=4,17.5%,IF(b2=5,18%,IF(b2=6,20%,0)) =IF(AND(b20,b2=1,0),21%,IF(b2=2,23%,IF(b2=3,25%,IF(b2=4,27.5%,IF(b2=5,28%,IF(b2=6,20%,0)) A multiple IF statement stops when it finds the first true statement, so you don't need to define the lower level of each range. Also, this sort of thing is done better using a table with VLOOKUP. You can change the values more easily, and the formulas are much simpler. As to a macro, if no one else answers, I could provide you with that. Regards - Dave. Check out the new Windows Live Messenger Looking for a fresh way to share your photos? _ Looking to move somewhere new this winter? Let ninemsn property help http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: $$Excel-Macros$$
Hi Atul, Sorry about that. Try the modified attached. Regards - Dave. Date: Wed, 27 May 2009 14:08:38 +0530 Subject: $$Excel-Macros$$ Re: $$Excel-Macros$$ From: atul.kesa...@gmail.com To: excel-macros@googlegroups.com Hello Dave, One small issue in the macro, the cell number b2 in the formula remain the same across all the row when we run the macro, however it should changes as the row changes. Please help. Thanks and Regards Atul Kesaria On Wed, May 27, 2009 at 11:11 AM, Atul Kesaria atul.kesa...@gmail.com wrote: Hello Dave, You are a rock Star...thanks a ton!!! The macro is awesome.this is what i wanted. Thanks and Regards Atul Kesaria On Wed, May 27, 2009 at 10:10 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi Atul, Have a look at the attached workbook, and see if it does what you want. I've added a button to run the macro from the sheet, but you may want to run it some other way. I've left your formulas as you had them. Regards - Dave. Date: Tue, 26 May 2009 18:33:57 +0530 Subject: $$Excel-Macros$$ Re: $$Excel-Macros$$ From: atul.kesa...@gmail.com To: excel-macros@googlegroups.com Hello Dave, Thanks for the below formulas. The reason I am asking for the macro is that we have more then 25 sheet in a file and more then 200 line items in each sheet, macro would help to reduce the file size and minimize the changes of error. I have attached a file in which i have tried to write a macro. Two things that i am unable to add. 1 )The macro should able to apply the formula till the last line 2) It should copy and paste special value all the formula applied. Thanking you once again in advance. Thanks and Regards Atul Kesaria On Tue, May 26, 2009 at 2:47 PM, Dave Bonallack davebonall...@hotmail.com wrote: Hi Atul, Just as a by-the-way, those formulas are much longer than needed. There are lots of redundant parts. The following would do the same thing: =IF(AND(B20,B2=1,0),1%,IF(b2=2,3%,IF(b2=3,5%,IF(b2=4,7.5%,IF(b2=5,8%,IF(b2=6,10%,0)) =IF(AND(b20,b2=1,0),11%,IF(b2=2,13%,IF(b2=3,15%,IF(b2=4,17.5%,IF(b2=5,18%,IF(b2=6,20%,0)) =IF(AND(b20,b2=1,0),21%,IF(b2=2,23%,IF(b2=3,25%,IF(b2=4,27.5%,IF(b2=5,28%,IF(b2=6,20%,0)) A multiple IF statement stops when it finds the first true statement, so you don't need to define the lower level of each range. Also, this sort of thing is done better using a table with VLOOKUP. You can change the values more easily, and the formulas are much simpler. As to a macro, if no one else answers, I could provide you with that. Regards - Dave. Check out the new Windows Live Messenger Looking for a fresh way to share your photos? _ Want to stay on top of your life online? Find out how with Windows Live! http://windowslive.ninemsn.com.au/ --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- SAMPLE(1).xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Trouble with find
Hi Paul, I think the op means that the text NO SALES can be in any cell somewhere on the sheet - sheet name unknown, and not a sheet called NO SALES. Hence his desire to look thru every cell on every sheet. (Cells.Select - Selection.Find) And that's a good tip about counting backwards. I've come across that myself when working down thru a bunch of rows, deleting some according to specific criteria. I found a solution, but not as elegant as just working up from the bottom. Regards - Dave. Date: Thu, 28 May 2009 04:43:38 -0700 From: schreiner_p...@att.net Subject: $$Excel-Macros$$ Re: Trouble with find To: excel-macros@googlegroups.com you're off to a good start. However, the .Find function looks for things in the selected CELL. What you really want is to see if the sheet NAME is No Sales. Try:'== Sub test() for i = 1 to Sheets.Count if (ucase(Sheets(i).Name) = NO SALES) then Sheets(i).Delete Exit For end If Next I End Sub '== Notice the Exit For.. well, there can be only one sheet called No Sales, so once it's gone, there no use in continue looking! Now, what if you expect sheets that CONTAIN the name No Sales (like No Sales 1, No Sales 2) then I'd use: '== Sub test() for i = Sheets.Count to 1 step -1 if (instr(1,ucase(Sheets(i).Name),NO SALES) 0) then Sheets(i).Delete end If Next I End Sub '== Notice that I used Sheets.Count to 1 instead of 1 to Sheets.Count. That is because: Let's say that there are 10 sheets. The third sheet is called No Sales If you loop from 1 to sheets.count, then when you get to I = 3, sheet 3 is deleted, sheet 4 then becomes sheet3, (sheet 5 becomes 4, etc) so the loop would then SKIP checking what was sheet 4. Then, when it got to i=10, it would ERROR, because there are not 10 sheets! by counting down, it eliminates this problem. hope this helps. Paul From: sjsean sjsean95...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Wednesday, May 27, 2009 8:00:27 PM Subject: $$Excel-Macros$$ Trouble with find I have a set of worksheets where the data provider always puts a sheet with No Sales. I am trying to write a macro that will remove this sheet (and others if they fit the above criteria). Sub test() For i = 1 To Sheets.Count Sheets(i).Select Cells.Select Dim r As Range Set r = Selection.Find(What:=No Sales, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).Activate If Not r Is Nothing Then Sheets(i).Delete Next End Sub _ Looking to move somewhere new this winter? Let ninemsn property help http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: A Macro needed for the task beow
Hi Dave, Great name, by the way. Have a look at the attached workbook and see if I have understood you properly... Regards - Dave. Date: Wed, 27 May 2009 15:28:41 -0700 Subject: $$Excel-Macros$$ A Macro needed for the task beow From: davidstev...@gmail.com To: excel-macros@googlegroups.com Hi, I have a spreadsheet that contains a company code in cell A3 to A150. This company code uniquely identifies a company. These codes ones assigned are not transferred to another company. What I want to do is when a user clicks in a cell with the company code I want to reference its company name. This way the user is not running around looking up company names that are stored an another excel file . The company codes are as follows: 0010- 0010- 0013- 0015-0100 0041-WOKS So if a user clicks on cell a1 containing 0010- I want to return Big Bass Outfitters and if they click in the cell contusing 0010- I want to display Canada Water and so on and so forth I was thinking about a macro some how adding automatic comments by looking up the company codes against the company name and inserting comments. But I am not sure if its a good solution and if it can be done. All suggestions are welcome. Please reply here or reply to may email, thanks, Dave _ Looking to change your car this year? Find car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- Book1.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: cells change triger macro problem
Hi Vincent, Try disabling the calculate thingy Application.Calculation = xlManual Look for change in $A$1 Application.Calculation = xlAutomatic Regards - Dave. Date: Fri, 29 May 2009 06:33:36 -0700 Subject: $$Excel-Macros$$ cells change triger macro problem From: vincent2...@gmail.com To: excel-macros@googlegroups.com Hi Dear all, Now comes to the tricky part :) It is easy to use worksheet_change event to triger a macro when only one cell in the sheet changes at a time. we can use private worksheet_change(byval target as range ) If Target.address=$a$1 then application.enableEvents=false 'your functional code application.enableEvents=true end if end sub However, my sheet has 500 cells updating every second When i run the sheet, it can't work as it s supposed to . Since my sheet has so many changes in 1 second, it simply has no time to do the if check. Even I put Application.enableEvents=false there to disable events. I only need to monitor one cell change. Do you guess have any ideas ? Something like register a handler on a certain cell,cell_change instead of worksheet_change? It is easy to do in Jave or C# language. thansk Vincent _ Looking to change your car this year? Find car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: parse Excel file
Hi George, Can't quite understand your request. column F down untill we reach two empty cells in columns F and J But your sample data doesn't have 2 empty cells in both F and J. Perhaps you could attach a sample workbook with a bit more data - say 20 rows. Regards - Dave. Date: Fri, 29 May 2009 05:07:32 -0700 Subject: $$Excel-Macros$$ parse Excel file From: use...@ccx-grads.org To: excel-macros@googlegroups.com Dear group members, I have got .xls file where two columns (F and J) must be parsed. Columns look like this: F J aaa bbb (empty) ccc (empty) ddd (empty) (empty) fff ggg (empty) rrr (empty) vvv (empty) (empty) So, I need to go through these columns and copy data to other sheet: column J - as is column F - all but empty cells; copy and paste the same data from column F down untill we reach two empty cells in columns F and J; after that we copy and paste the next not empty data from column F. I have to parse 23000 rows. Help me please, I'm new to VBA. _ Looking to change your car this year? Find car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Copy one column to another except any cells containing zero
Hi TAlgo, Have a look at the attached. I've included 2 different macros because I wasn't sure of what you really need. Hope this points you in the right direction. Regards - Dave Guys, trying to create a macro which will copy column A to column B. This is simple but only exception I m looking is macro should not copy any value = 0 I m trying this but no luck so far..any help will be greatly appreciated.. Sub test_again() If cell(a:a) 0 Then Sheets(sheet7).Range(a:a).Copy Sheets(sheet7).Range(b:b) End If End Sub _ Looking to move somewhere new this winter? Let ninemsn property help http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- Book1.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: read saved file name and put a digit in a column range ????
Hi Robin, I have tried to understand your needs, but cannot. 1. a macro which will read my file name This we can do. 2. i want that my macto add a column D and reads my saved file name and put a digit 25 in that D coulmn in range d1 to d last (till column A, B, C, carries data) ?? This I don't understand. If you provide a sample workbook, with specific instructions in that workbook, we may be able to help. Regards - Dave Date: Fri, 29 May 2009 22:27:48 -0700 Subject: $$Excel-Macros$$ read saved file name and put a digit in a column range From: mail2rbhand...@gmail.com To: excel-macros@googlegroups.com hi friends, i am looking for a macro which will read my file name (saved file where i will run macro) and print a code in given range of a column, for an example if my file name is test (saved in desktop) is carring some data in column A, B, and C, i want that my macto add a column D and reads my saved file name and put a digit 25 in that D coulmn in range d1 to d last (till column A, B, C, carries data) ?? please help me out with this, i am having 256 such files and i need to do it manually every day, which is very fustrating... please give some idea of this macro code atlest, Thanks, Robin _ View photos of singles in your area Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fsearch%2Fsearch%2Easpx%3Fexec%3Dgo%26tp%3Dq%26gc%3D2%26tr%3D1%26lage%3D18%26uage%3D55%26cl%3D14%26sl%3D0%26dist%3D50%26po%3D1%26do%3D2%26trackingid%3D1046138%26r2s%3D1_t=773166090_r=Hotmail_Endtext_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Trouble with find
Hi sjsean, Try the attached workbook. Put the words No Sales (without the quotes) into any cell on one of the sheets. Note that sheet name and then run the macro. That sheet should be deleted. Hope this helps. Regards - Dave. From: sjsean sjsean95...@gmail.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Wednesday, May 27, 2009 8:00:27 PM Subject: $$Excel-Macros$$ Trouble with find I have a set of worksheets where the data provider always puts a sheet with No Sales. I am trying to write a macro that will remove this sheet (and others if they fit the above criteria). Sub test() For i = 1 To Sheets.Count Sheets(i).Select Cells.Select Dim r As Range Set r = Selection.Find(What:=No Sales, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True).Activate If Not r Is Nothing Then Sheets(i).Delete Next End Sub _ Looking to move somewhere new this winter? Let ninemsn property helphttp://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Eco... _ Looking to change your car this year? Find car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- Book2.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: read saved file name and put a digit in a column range ????
Hi Robin, You can import the file name into any cell with the following formula: =MID(CELL(Filename),SEARCH([,CELL(Filename),1)+1,LEN(CELL(Filename))-1-SEARCH(],CELL(Filename),1)) I'm sure that there's a shorter single-formula method, but I can't think of it just now. Waiting for your sample workbook. Regards - Dave. This Date: Sun, 31 May 2009 02:15:53 -0700 Subject: $$Excel-Macros$$ Re: read saved file name and put a digit in a column range From: mail2rbhand...@gmail.com To: excel-macros@googlegroups.com HI so can u tell me how will u import file name in tha same sheet ... i am doing +cell(filename) but this is giving whole path but i just need file name...?? On May 31, 12:18 pm, robin_suv mail2rbhand...@gmail.com wrote: hi Dave, thanks for ur replay, well yes its very confusing, i have a file name AXN which is having some data inside with column A, B, C, now there is another file (or a sheet2 of AXN) with two column A, B which says channel name and channel code and data in it is AXN and 234 and other such channel name and its code like sony 123 in A B column (sheet2) , now i do not have any column in AXN file which says channel name, but i want channal code in column D of it with refrence of sheet2 which carry channel name and its code, i am not able to use Vlookup in this case due to no such column in AXN which says channel name, i have 347 such files ... and i am doing it manualy to put codes in col D of AXN and other files SENDING U the sample file Thanks, Robin On May 30, 6:37 pm, Dave Bonallack davebonall...@hotmail.com wrote: Hi Robin, I have tried to understand your needs, but cannot. 1. a macro which will read my file name This we can do. 2. i want that my macto add a column D and reads my saved file name and put a digit 25 in that D coulmn in range d1 to d last (till column A, B, C, carries data) ?? This I don't understand. If you provide a sample workbook, with specific instructions in that workbook, we may be able to help. Regards - Dave Date: Fri, 29 May 2009 22:27:48 -0700 Subject: $$Excel-Macros$$ read saved file name and put a digit in a column range From: mail2rbhand...@gmail.com To: excel-macros@googlegroups.com hi friends, i am looking for a macro which will read my file name (saved file where i will run macro) and print a code in given range of a column, for an example if my file name is test (saved in desktop) is carring some data in column A, B, and C, i want that my macto add a column D and reads my saved file name and put a digit 25 in that D coulmn in range d1 to d last (till column A, B, C, carries data) ?? please help me out with this, i am having 256 such files and i need to do it manually every day, which is very fustrating... please give some idea of this macro code atlest, Thanks, Robin _ View photos of singles in your area Click Herehttp://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Eco...Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - _ View photos of singles in your area Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fsearch%2Fsearch%2Easpx%3Fexec%3Dgo%26tp%3Dq%26gc%3D2%26tr%3D1%26lage%3D18%26uage%3D55%26cl%3D14%26sl%3D0%26dist%3D50%26po%3D1%26do%3D2%26trackingid%3D1046138%26r2s%3D1_t=773166090_r=Hotmail_Endtext_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: MS Excell 2007 problem
Hi, I assume you are referring to VBA. VBA can't select a cell or range on a sheet which isn't active. You have to activate the sheet, then select the range: Sheets(A).Activate Range(A1).Select However, most of the time you don't have to select the cell or range you want to work on. For example, the following 2 codes snippets both turn A1 on sheet A, red: Sheets(A).Activate Range(A1).Select Selection.Interior.ColorIndex = 3 Sheets(A).Range(A1).Interior.ColorIndex = 3 It's also quicker not to select the cell you want to work on. This is quite different to the way the macro recorder works. Hope this helps. Regards - Dave. Hello I'm having a problem with selecting a cell from a different Sheet. Say I'm in Sheet B and I want to select a cell ( or range of cells) from Sheet A. All I would need to do in click on Sheet A and select that intended cell(s). However, Excel doesn't change the name of the Sheet automatically upon selection. I would have manually go in and change it myself. Is anyone else having this problem? Thank you. _ Looking to move somewhere new this winter? Let ninemsn property help http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Help required
Hi, Take a look at the attached. Regards - Dave. Date: Wed, 3 Jun 2009 10:43:07 +0530 Subject: $$Excel-Macros$$ Help required From: sachina...@gmail.com To: excel-macros@googlegroups.com Dear All, Please help me on analysis of different senarion. details question and sheet attached. Thanks Sachin Shukla _ View photos of singles in your area Click Here http://dating.ninemsn.com.au/search/search.aspx?exec=gotp=qgc=2tr=1lage=18uage=55cl=14sl=0dist=50po=1do=2trackingid=1046138r2s=1_t=773166090_r=WLM_EndText --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- Book3(1).xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: find next empty cell address
Hi, Try this: A = Cells(Rows.Count, 1).End(xlUp).Row + 1 This will give you the first blank row number Regards - Dave. Date: Thu, 4 Jun 2009 18:40:46 +0530 Subject: $$Excel-Macros$$ find next empty cell address From: grli...@gmail.com To: excel-macros@googlegroups.com Dear All my data started in colomn A cell no 6 upto a50(Cell a1 to a5 is empty), using Vba I need to find the next empty cells address Could help me to find the next emty cell address through Vba in perticular coloumn Regards Grlinks www.grlinksindia.com _ Get the latest news, goss and sport Make ninemsn your homepage! http://windowslive.ninemsn.com.au/article.aspx?id=813730 --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Need Help!!!!!!!!!!!!
Hi Mahesh, One way is to select all cells in the column: (I have assumed Col A, starting Row 2) Select conditional format, formula is, then enter: =COUNTIF($A$2:$A$1000, A2)1 Set the format you want - say red bold text. Hit ok All duplicates will be red bold. Change the range and starting cell to suit your data. The attached shows this method in practice. As you add new values to Col A, they will be red bold if they are not unique. Hope this helps Regards - Dave. Date: Fri, 5 Jun 2009 09:38:23 +0530 Subject: $$Excel-Macros$$ Need Help From: mahender.bi...@gmail.com To: excel-macros@googlegroups.com Dear All, I want to know the how i can find the repeat or duplicate value in same column or row.(without Advance filter) -- With Love, Mahesh Bisht _ Get the latest news, goss and sport Make ninemsn your homepage! http://windowslive.ninemsn.com.au/article.aspx?id=813730 --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- Dups.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Phone Number Filtering
Hi Lion123, Have a look at the attached. I've used random numbers to test it. Details on the sheet. Hope this is sort of what you want. Anyway, really interesting. Regards - Dave. I am trying to sort special numbers in a long list of telephone numbers (7 digits). For example: a. Highlight yellow those numbers which have 3 consecutive digits (eg 2281555). b. Highlightt blue those numbers which have two double numbers (eg 4431122). c. Highlight green those numbers which are in a certain sequence (eg 2281234). I tried to use Left, Right and Mid formulas but the game becomes very complicated and hard to troubleshoot. Any better solution? _ Looking for a new car this winter? Let us help with car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- Interseting.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Phone Number Filtering
Hi Fabio, That's a very interesting approach to the problem, and a very good formula. Regards - Dave. From: flnle...@gmail.com Date: Fri, 5 Jun 2009 09:29:46 -0300 Subject: $$Excel-Macros$$ Re: Phone Number Filtering To: excel-macros@googlegroups.com see atached file... I've created a name for the formula on column B and used it in the conditional format condition. 2009/6/5 Lion123 asadnave...@gmail.com I am trying to sort special numbers in a long list of telephone numbers (7 digits). For example: a. Highlight yellow those numbers which have 3 consecutive digits (eg 2281555). b. Highlightt blue those numbers which have two double numbers (eg 4431122). c. Highlight green those numbers which are in a certain sequence (eg 2281234). I tried to use Left, Right and Mid formulas but the game becomes very complicated and hard to troubleshoot. Any better solution? -- Fabio Lemos e-mail: flnle...@gmail.com _ POP access for Hotmail is here! Click here to find out more http://windowslive.ninemsn.com.au/article.aspx?id=802246 --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: remove module
Hi Alokeswar, Your attachment has the VBA protected with a password. Regards - Dave. Date: Fri, 5 Jun 2009 18:37:58 -0700 From: alokeshwar.tiw...@yahoo.com Subject: $$Excel-Macros$$ Re: remove module To: excel-macros@googlegroups.com You can use SendKeys to protect and unprotect VBA project. See attached. I had used this to create a macro to delete all modules. _ There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. From: Chris Spicer chris.spi...@technicana.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Friday, 5 June, 2009 5:07:56 PM Subject: $$Excel-Macros$$ Re: remove module Hi Mike, I normally like to answer people's questions directly, rather than offer suggestions, but I'm not sure whether what you're trying to achieve is possible. Perhaps an alternative would be to move your proprietary code from module 2 into a separate xla add-in? That way module 1 can still make calls to your code, but you don't need to distribute the add-in. The project containing module 1 can be unprotected, but the add-in containing module 2 can be protected. If splitting your code like this is acceptable, the only other thing I can see you having to watch out for is a dangling reference to the xla addin... you should be able to remove that reference programmatically when it comes time to distribute your workbook. I hope this helps, Chris Spicer www.Technicana.com On Jun 4, 8:08 pm, mikeallen7 mikealle...@gmail.com wrote: I have 2 vba modules that are both within a protected project. I wish to remove one module via code from the other module. I have this in module1: ThisWorkbook.VBProject.vbcomponents.Remove ThisWorkbook.VBProject.vbcomponents(module2) It works fine IF I have the project unprotected, but the problem arises when the project is protected. I get: Run-time error '50289': Can't perform operation since the project is protected. Someone suggested once to go to Tools--Macros--Security--Trusted Publishers--Trust access to Visual Basic project (click box). I have this done, but still does not work when protected. I must have project protected because others will be using this file and I do not want them to have access to the sensitive code in 'module2'. The users will need 'module2' initially to run program, then they will be converting the program into an output-only file w/ no 'module2'. Thanks Explore and discover exciting holidays and getaways with Yahoo! India Travel Click here! _ View photos of singles in your area Click Here http://dating.ninemsn.com.au/search/search.aspx?exec=gotp=qgc=2tr=1lage=18uage=55cl=14sl=0dist=50po=1do=2trackingid=1046138r2s=1_t=773166090_r=WLM_EndText --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Tips
Hi Group, Tip. The following line of code line returns the first blank cell at the end of a series of data, ignoring any blank cells within that series. In this case, the series is in Col A. I use it a lot when copying stuff onto the bottom of existing data. This is not new, but might be useful for those still learning VBA - which I guess is all of us really... A = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Address If you just need to know the Row number, use A = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row By the way, I think this 'submit your tip' idea is excellent. Regards - Dave. _ View photos of singles in your area Click Here http://dating.ninemsn.com.au/search/search.aspx?exec=gotp=qgc=2tr=1lage=18uage=55cl=14sl=0dist=50po=1do=2trackingid=1046138r2s=1_t=773166090_r=WLM_EndText --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: remove module - password is password
Hi Alokeswar, Thanks for sharing that. Regards - Dave. Date: Sat, 6 Jun 2009 02:55:50 -0700 From: alokeshwar.tiw...@yahoo.com Subject: $$Excel-Macros$$ Re: remove module - password is password To: excel-macros@googlegroups.com I apologize, please use following password: password Regards, Alokeshwar _ There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. From: Alokeshwar Tiwary alokeshwar.tiw...@yahoo.com To: excel-macros@googlegroups.com Sent: Saturday, 6 June, 2009 7:07:58 AM Subject: $$Excel-Macros$$ Re: remove module You can use SendKeys to protect and unprotect VBA project. See attached. I had used this to create a macro to delete all modules. _ There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we know we don't know. But there are also unknown unknowns. There are things we don't know we don't know. From: Chris Spicer chris.spi...@technicana.com To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Friday, 5 June, 2009 5:07:56 PM Subject: $$Excel-Macros$$ Re: remove module Hi Mike, I normally like to answer people's questions directly, rather than offer suggestions, but I'm not sure whether what you're trying to achieve is possible. Perhaps an alternative would be to move your proprietary code from module 2 into a separate xla add-in? That way module 1 can still make calls to your code, but you don't need to distribute the add-in. The project containing module 1 can be unprotected, but the add-in containing module 2 can be protected. If splitting your code like this is acceptable, the only other thing I can see you having to watch out for is a dangling reference to the xla addin... you should be able to remove that reference programmatically when it comes time to distribute your workbook. I hope this helps, Chris Spicer www.Technicana.com On Jun 4, 8:08 pm, mikeallen7 mikealle...@gmail.com wrote: I have 2 vba modules that are both within a protected project. I wish to remove one module via code from the other module. I have this in module1: ThisWorkbook.VBProject.vbcomponents.Remove ThisWorkbook.VBProject.vbcomponents(module2) It works fine IF I have the project unprotected, but the problem arises when the project is protected. I get: Run-time error '50289': Can't perform operation since the project is protected. Someone suggested once to go to Tools--Macros--Security--Trusted Publishers--Trust access to Visual Basic project (click box). I have this done, but still does not work when protected. I must have project protected because others will be using this file and I do not want them to have access to the sensitive code in 'module2'. The users will need 'module2' initially to run program, then they will be converting the program into an output-only file w/ no 'module2'. Thanks Explore and discover exciting holidays and getaways with Yahoo! India Travel Click here! Own a website.Get an unlimited package.Pay next to nothing.* Click here!. _ View photos of singles in your area Click Here http://dating.ninemsn.com.au/search/search.aspx?exec=gotp=qgc=2tr=1lage=18uage=55cl=14sl=0dist=50po=1do=2trackingid=1046138r2s=1_t=773166090_r=WLM_EndText --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Tips
Hi Yu, Thanks. I guess we could use the Range thingy to do the same thing. This allows us to input the Column using its alphabetic notation. The following 2 lines do the same thing, finding the next cell in Column AA B = Cells(Rows.Count, 27).End(xlUp).Offset(1, 0).Address B = Range(AA Rows.Count).End(xlUp).Offset(1, 0).Address Regards - Dave. Date: Sat, 6 Jun 2009 10:25:44 +0200 Subject: $$Excel-Macros$$ Re: Tips From: vincent2...@gmail.com To: excel-macros@googlegroups.com Hi Dave, Thanks for your share. This is one of lines I used a lot as well. The only drawback of this is that you need to give it the number of Column. Most of times, it is more easier to give the Column Name,like A instead of 1 when it reaches beyond AA 'cos you need to count the number manually. My solution is I write a few lines to convert string name of Column to number. How do you guys do it? or you use another way to find blank cells? Cheers Yu On Sat, Jun 6, 2009 at 10:12 AM, Dave Bonallack davebonall...@hotmail.com wrote: Hi Group, Tip. The following line of code line returns the first blank cell at the end of a series of data, ignoring any blank cells within that series. In this case, the series is in Col A. I use it a lot when copying stuff onto the bottom of existing data. This is not new, but might be useful for those still learning VBA - which I guess is all of us really... A = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Address If you just need to know the Row number, use A = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row By the way, I think this 'submit your tip' idea is excellent. Regards - Dave. Click Here View photos of singles in your area _ Looking for a new car this winter? Let us help with car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: find method does not work in filtered (hidden) ranges
Hi, If the FIND thingy doesn't work with filter-hidden rows, try just looping through each cell in the range you want: Something like: For each c in Range(A2:A1000) If c.value = Your criteria here Then Msgbox Bing! It's here! Next c Replace the Message Box with whatever code you need. Hope this is of some help. Regards - Dave. Date: Mon, 8 Jun 2009 08:47:12 -0400 Subject: $$Excel-Macros$$ Re: find method does not work in filtered (hidden) ranges From: youcanthavemyemailaddr...@gmail.com To: excel-macros@googlegroups.com I know it has to be done with a macro. I'm just not sure how (and if it is possible) to use the find method and search in rows hidden by a filter. If you have an idea let me know. Thank you. On Sat, Jun 6, 2009 at 1:11 AM, Nishant Jainnishantjai...@gmail.com wrote: this is possible if you want to use macro... i hope you can develop one, but if not let me know... On Jun 6, 2:29 am, Anon Ymous youcanthavemyemailaddr...@gmail.com wrote: Hello. First post here (and first post to a Google group). I have been a long time user of the Ozgrid forum, but I am against the idea of paying to post. Hopefully I'll find some nice and helpful people here. :) Ok, so the find method works on visible and hidden rows depending if you use LookIn:=xlValues or xlFormulas; however, it does not work on hidden rows caused by a filter. I have a range that is filtered, and I want to find a value in a column that may or may not be the filter criteria (and thus hidden). I do not want to show all the data, do the find, then reapply all the filters again. Ideas? _ Looking for a new car this winter? Let us help with car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: BEST EXCEL TIP WEEK # 24 Submit TIP and Win PRIZE
Hi Harmeet, I think this is a very good tip. Regards - Dave. Date: Tue, 9 Jun 2009 00:29:31 +0530 Subject: $$Excel-Macros$$ Re: BEST EXCEL TIP WEEK # 24 Submit TIP and Win PRIZE From: harmeet.hew...@gmail.com To: excel-macros@googlegroups.com HI All, Name : Harmeet Subject : Hidden text in Formula Tip : =2018+1056-4*120+N(My Salary + Bonus - 4 weekly loan repayments) BY using N() formula You can enter hidden text in your formula. Try it. Regards, Harmeet On Fri, Jun 5, 2009 at 9:11 PM, Ayush jainayus...@gmail.com wrote: Hello Everyone, Please submit your tip in this post for Week # 24. I am looking for your overwhelming response in this competition.You just need to submit your name along with the tip and subject. The tip can be shortcut, trick, hidden secret, etc. Last Submission Date-13th June 2009. Winner Declaration Date -14th June 2009. Example : Name : Ayush Jain Subject : the Great 'F4' Tip : The 'F4' is easily the most useful shortcut key in Excel. Basically it repeats your last command. So say you changed the color of a font in a cell. Now click in a new cell and hit 'F4' and it will change the font in the new cell to the same color.Wonderful, right? Well what's great about this is that it works for nearly everything else in Excel too.Highlight a row, right- click and choose 'Delete'. Now highlight another row and hit 'F4'. It deletes that row.Whatever you did last, Excel will attempt to repeat the same command until you do something else. This works great for things that require you to go to the menus or right-click and make choices or click buttons that bring up more dialogs. It can be huge time saver once you get used to using it. As the help says, it doesn't work in every situation but most times 'F4' works great. SO, WHAT ARE YOU WAITING FOR ? JUST SUBMIT A TIP WIN PRIZES. Invite your friends too for participation in the contest. The more participation means more learning. Thanks again, Ayush Jain -- Thanks Regards, Harmeet Singh _ Looking for a new car this winter? Let us help with car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: getting the value from a cell that has a forumla in it
Hi, Include this line in the code: A = Range(D21).Value * Range(D20).Value The variable 'A' will store the number you're looking for. Regards - Dave. Date: Mon, 15 Jun 2009 14:35:29 -0700 Subject: $$Excel-Macros$$ getting the value from a cell that has a forumla in it From: stv.nels...@gmail.com To: excel-macros@googlegroups.com I'm sorry if this is a really simple question or if the answer could be found in the archives, I could not find it. I have a workseet with a simple that has a simple forumula =D21*D20 in VBA, I need to get the value of that cell. Thanks for your help, I hope it is something very simple, sorry again if I missed something obvious. _ Looking for a place to rent, share or buy this winter? Find your next place with Ninemsn property http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Best Excel Tip Award of Week # 24 goes to HARMEET SINGH
Good choice Ayush. I also thought it was very good. It's the Worksheet-Function equivalent of putting comments after an apostrophe in VBA. Regards - Dave. Date: Wed, 17 Jun 2009 07:48:06 -0700 Subject: $$Excel-Macros$$ Best Excel Tip Award of Week # 24 goes to HARMEET SINGH From: jainayus...@gmail.com To: excel-macros@googlegroups.com CC: harmeet.hew...@gmail.com Hello Everyone, I am happy to inform you that the award of best excel Tip of Week # 24 goes to Harmeet Singh. The tip submitted by Harmeet is as follows : Subject : Hidden text in Formula Tip : =2018+1056-4*120+N(My Salary + Bonus - 4 weekly loan repayments) BY using *N() *formula You can enter hidden text in your formula. It was very different, useful and unique tip among all the tips by the participants. I encourage you all to submit such great tips which will help all members in big way and let you win the prize. CONGRATULATIONS HARMEET !! You are now nominated for monthly award your name is published on home page of this group. CHEERS !!! The competition of week # 25 is already started you can submit your tip in the thread mentioned below http://groups.google.com/group/excel-macros/browse_thread/thread/8f60d8d55837f5c9# Please participate and win wonderful prizes. KEEP POSTING Thanks and best regards, Ayush Jain _ View photos of singles in your area Click Here http://dating.ninemsn.com.au/search/search.aspx?exec=gotp=qgc=2tr=1lage=18uage=55cl=14sl=0dist=50po=1do=2trackingid=1046138r2s=1_t=773166090_r=WLM_EndText --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Vlookup
Hi Ruchi, Not sure I understand all your needs. Take a look at the attached and tell me if I'm on the right track Regards - Dave. Date: Wed, 17 Jun 2009 22:38:51 +0530 Subject: $$Excel-Macros$$ Re: Vlookup From: ruchigab...@gmail.com To: excel-macros@googlegroups.com Hi actually i send 1 query in the group but i hvnt get ne reply yet plz help me otherwise i will b in trouble my probs is i hv file in which i hv mention th eintervals acc. to that i want to get the frequency and avg and also i hv to make chart which showz the counts frquency as well as avg but again in this chart i want to show 1 line at 2:30 which is cut of time so plz revert me back thank u regards ruchi On Wed, Jun 17, 2009 at 10:11 PM, god is gr8 i love uruchigab...@gmail.com wrote: Hi oh this is gr8 thank u regards ruchi On Wed, Jun 17, 2009 at 5:17 PM, Dilip Pandeydilipan...@gmail.com wrote: Hi Ruchi, This query is related to the linking of the files onto a excel spreadsheet. For example, you have some week - wise spreadsheets in your c drive (e.g. C:\Ruchi\work). The files may have the names like (week4.xls, week5.xls, week6.xls) and so on. Now what you want to do, is to link up certain range or a cell from each of these files to a master spreadsheet. (MasterSales.xls). And, In Master file, you want to pick up M50 of each week's file, So the query is to have all the files linked to master file by changing some cell reference or some formulas in master file SO THAT when you change the week number in master file, respective week's file get linked up and result is obtained in the master spreadsheet. Thanks, Dilipandey -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 110062 On Tue, Jun 16, 2009 at 11:08 PM, god is gr8 i love u ruchigab...@gmail.com wrote: Hi Dilip can u plz help me to understand this query? plz elaborate it thanks regards ruchi On Tue, Jun 16, 2009 at 10:51 PM, Paul Schreinerschreiner_p...@att.net wrote: Well... the short answer is Yes and No. First, what does this have to do with the title Vlookup?? Your question is WAY too vague. There can BE no files called c:week25, c:week26. they would have to be at a MINIMUM C:\week25.xls, etc. Also... are they all really in the root of C:\ ??? then... you say you want to create a link.. do you really mean a hyperlink? or something else? If you mean a hyperlink, what do you want to assign it to? the number you enter? assuming lots of stuff you haven't shared, I created this change event: Private Sub Worksheet_Change(ByVal Target As Range) Dim fso, fname, WKno, BasePath If Target.Count 1 Then Exit Sub If Target.Column = 1 Then If (Target.Value ) Then BasePath = C:\temp\ WKno = Target.Value If (Len(WKno) 2) Then WKno = 0 WKno Set fso = CreateObject(Scripting.FileSystemObject) fname = BasePath week WKno .xls If (fso.fileexists(fname)) Then 'MsgBox file Exists Chr(13) fname Application.EnableEvents = False ActiveSheet.Hyperlinks.Add Anchor:=Range(Target.Address), Address:=fname Application.EnableEvents = True End If End If End If End Sub It checks to see if the number you entered exists in the C:\temp\ folder and the file will be called week ## .xls. Then creates a hyperlink to the file. hopefully, this will get you moving in the right direction. Paul From: Jack j...@jackcwood.co.uk To: MS EXCEL AND VBA MACROS excel-macros@googlegroups.com Sent: Monday, June 15, 2009 2:14:02 PM Subject: $$Excel-Macros$$ Vlookup Hi I need to create a link to other workbooks on c: called week 25 week 26 etc based on a number in a cell ie ='c:week' A4 - A4 being the week number to add to file path. Can this be done ? _ POP access for Hotmail is here! Click here to find out more http://windowslive.ninemsn.com.au/article.aspx?id=802246 --~--~-~--~~~---~--~~ - 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
$$Excel-Macros$$ Re: Error- Can not execute code in break mode
Hi, This just means that the code has paused for some reason (perhaps because you asked it to, or perhaps it has a mind of its own). You just need to click the Reset button on the VBA toolbar. This will allow other code to run. Basically, you can only run one code at a time. When a code is in break mode (paused) XL still considers that code to be running. Regards - Dave. Date: Mon, 22 Jun 2009 21:55:54 +0530 Subject: $$Excel-Macros$$ Error- Can not execute code in break mode From: mahes...@gmail.com To: excel-macros@googlegroups.com Dear All while running vb script i m getting Can not execute code in break mode error, Can any one explain the cause of this error _ View photos of singles in your area Click Here http://dating.ninemsn.com.au/search/search.aspx?exec=gotp=qgc=2tr=1lage=18uage=55cl=14sl=0dist=50po=1do=2trackingid=1046138r2s=1_t=773166090_r=WLM_EndText --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Plz. Help: Convert complex figures into higher or lower 00 Figures
Hi Dilip, When I open your workbook (with XL2000), it functions as you say it should. However, when I type in '=+MROUND(A3,100)' to any cell, I get a #NAME? error. I don't see 'MROUND' in the function list, or the use of the '+' Can you tell me what's happening please? Regards - Dave. Date: Wed, 24 Jun 2009 13:23:51 +0530 Subject: $$Excel-Macros$$ Re: Plz. Help: Convert complex figures into higher or lower 00 Figures From: dilipan...@gmail.com To: excel-macros@googlegroups.com Hi ddp, It is not as big as you have presented. Try the below formula and you will have the required result. =+MROUND(A3,100) 'where A3 is the reference I have tested this on a workbook and same is attached herewith for your reference. Thanks, Dilipandey -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 110062 On Tue, Jun 23, 2009 at 11:07 PM, ddp dharmendrakumar...@gmail.com wrote: Hi members, Hope you all are doing well. Please help me with a solution which, I am sure, will benefit others too. Problem:It is very difficult to remember tones of figures Solution: It is easier to remember nearest lower/higher figures Formula/function required: If last two digits50, convert the entire figure to higher 00 level and if last two digits50, convert the entire figure to lower 00 level Sales Qty Formula/function required, which will lead to 2723 2700 2584 2600 2358 2400 147 100 91547232 91547200 I use Office 2007 at home and 2003 in office I am well versed with excel but not much with VBA Thanking you in advance. Regards, Dharmendra _ POP access for Hotmail is here! Click here to find out more http://windowslive.ninemsn.com.au/article.aspx?id=802246 --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Parse through a data range
Hi Levi, We need to know how this data range looks. Any chance of attaching a sample workbook? Regards - Dave. Date: Wed, 24 Jun 2009 07:12:03 -0700 Subject: $$Excel-Macros$$ Parse through a data range From: rlsmalls...@gmail.com To: excel-macros@googlegroups.com I am wanting to parse through a data range one row at a time. Looking for pairs of numbers. Then display the results of the most common pairs in a cell on the Excel spreadsheet. Can anyone help with a task like this? Levi _ Get the latest news, goss and sport Make ninemsn your homepage! http://windowslive.ninemsn.com.au/article.aspx?id=813730 --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: how to extract a comment to a cell
Hi Dharmendra, Have a look at the attachment, click the button and see if it does what you want. Have a look at the code. It's really just 1 line. I've added the second line just to undo the auto Word Wrap feature. Regards - Dave. Date: Wed, 24 Jun 2009 23:43:02 +0530 Subject: $$Excel-Macros$$ how to extract a comment to a cell From: dharmendrakumar...@gmail.com To: excel-macros@googlegroups.com Hi members, Greetings! Please help: how to extract a comment to a cell (inside the cell, not as comment); say comment of A1 to B1 (inside the cell, not as comment). Please go through the attached file for clarification. Thanks in advance. Regards, Dharmendra _ Get the latest news, goss and sport Make ninemsn your homepage! http://windowslive.ninemsn.com.au/article.aspx?id=813730 --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- Extract comment in cell-1.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: how to extract a comment to a cell
You're welcome! Date: Thu, 25 Jun 2009 10:32:44 -0700 Subject: $$Excel-Macros$$ Re: how to extract a comment to a cell From: dharmendrakumar...@gmail.com To: excel-macros@googlegroups.com Thanks a ton Dave..It's working fine.. Regards, Dharmendra On Jun 25, 8:21 pm, Dave Bonallack davebonall...@hotmail.com wrote: Hi Dharmendra, Have a look at the attachment, click the button and see if it does what you want. Have a look at the code. It's really just 1 line. I've added the second line just to undo the auto Word Wrap feature. Regards - Dave. Date: Wed, 24 Jun 2009 23:43:02 +0530 Subject: $$Excel-Macros$$ how to extract a comment to a cell From: dharmendrakumar...@gmail.com To: excel-macros@googlegroups.com Hi members, Greetings! Please help: how to extract a comment to a cell (inside the cell, not as comment); say comment of A1 to B1 (inside the cell, not as comment). Please go through the attached file for clarification. Thanks in advance. Regards, Dharmendra _ Get the latest news, goss and sport Make ninemsn your homepage!http://windowslive.ninemsn.com.au/article.aspx?id=813730 Extract comment in cell-1.xls 43KViewDownload _ Looking for a place to rent, share or buy this winter? Find your next place with Ninemsn property http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: BEST EXCEL TIPWEEK # 27Submit your TIP here only
I concur Regards - Dave. Date: Wed, 1 Jul 2009 19:58:20 +0530 Subject: $$Excel-Macros$$ Re: BEST EXCEL TIPWEEK # 27Submit your TIP here only From: vishvesh.chau...@gmail.com To: excel-macros@googlegroups.com Really good bakul. I'm using excel since started using computers but its new to me. Thanks. On Wed, Jul 1, 2009 at 4:57 PM, Bakul Patel bakulpatel...@yahoo.com wrote: Name: Bakul Patel Tip: Dictionary in excel If you want to use dictionary in excel, just press ALT key click on cell which has the word. you will get the meaning of the word. Cheers, Bakul --- On Tue, 6/30/09, Ramesh Katta kr.sap...@gmail.com wrote: From: Ramesh Katta kr.sap...@gmail.com Subject: $$Excel-Macros$$ Re: BEST EXCEL TIPWEEK # 27Submit your TIP here only To: excel-macros@googlegroups.com Date: Tuesday, June 30, 2009, 11:58 PM Hi Dear Members, Name: Ramesh Katta Tip: Create chart by pressing F11 function key. We can create a chart quickly without having to use the chart button on the toolbar by pressing the function key F11 whilst inside a range of data. I have attached a workbook for reference just press F11 on data range to get chart. Thanks Regards Ramesh Katta On Tue, Jun 30, 2009 at 6:31 PM, Aindril Deaind...@gmail.com wrote: Name: Aindril De (Andy) Tip: Create Validation List from Data in Different Sheet A lot of times, when creating a validation list, we might want to get the data from another sheet in the workbook. But by default, Excel does not allow the validation data to be captured from another worksheet. To work around this limitation, we have to do the following steps: 1) Select the range in another sheet that we want to appear in our validation list. 2) Define a name for the selected range. For e.g. Cont 3) Select the area in the other worksheet where you want the validation list to appear. 4) Choose the validation type as list. 5) In the source, if you try to switch to the other sheet, you will notice Excel is not allowing to do so. 6) Type =Range Name i.e the range name that we have just defined. NOTE: donot write the name as it is without the = symbol, otherwise the list will not appear, instead only the name entered will appear 7) Fill the other Validation tabs if required. 8) Click OK. You will notice that the range that you wanted will appear in the list. I have also attached a worksheet to desctibe this example. Regards, Andy On Tue, Jun 30, 2009 at 11:32 PM, Sastry brb.sas...@gmail.com wrote: Name: Sastry B Tip: How to use a combo box. (Excel attached for clarity) This must be very useful for all those who are in MNCs and have to show their financial/economic nos. in more than one currency. This comes extremely handy. I have written the core tip in this attachment. You can put more controls and also can put one sheet for parameters where you can change the key nos. regards, Sastry 2009/6/30 Ayush Jain jain_ayus...@yahoo.com Dear Members, Thanks for your great response in the Week 26 competition. It was again great learning for the members of the group. Let us begin week # 27 quiz with the more spirit. This is last chance for you to be eligible for June month competition.The winner of week # 26 will be declared soon. Keep watching this Space.. Last Date of Submission of tips : 5th July 2009. PLEASE NOTE THAT THE TIPS SUBMITTED IN THIS POST WILL ONLY BE CONSIDERED FOR PRIZES. DO NOT START NEW THREADS OR POST FOR SUBMISSION OF TIPS. You need to submit your name, Subject the details of tips and tricks. DO let me know if you need any clarifications. Let me begin by submitting the first tip for this week competition. --- Name : Ayush Jain Subject : Skip through your worksheets with a shortcut Detail : When I'm working on many programs at once (yes, of course I'm a multitasker — what did you expect?), I use the keyboard shortcut ALT +TAB to move between open programs. (If you haven't tried this, do so now — it's a great Microsoft Windows® trick.) However, this doesn't work when you're moving between worksheets in the same workbook. Don't despair; there are a couple of neat little shortcuts that handle the job quite nicely. To move one worksheet to the right Press CTRL+PAGE DOWN. To move one worksheet to the left Press CTRL+PAGE UP. Now you can skip through those worksheets with lightning speed and amaze your friends and coworkers (if they're the type to be amazed by that sort of thing, of course). Who is next - _ View photos of singles in your area Click Here
$$Excel-Macros$$ Re: Help on copying formula.....
Hi Ashutosh, Absolute refrencing won't help you here. If you have control of the spread-sheet, and you can easily re-structure it, change the vertical list to a horizontal one. If you can't do that, select the refrence list, then copy. find an unused pert of the sheet. Click in a cell, then rightclick, Paste Special. in the Paste Special dialog box, tick the transpose option (near the bottom). you can use the new list as a reference for the copy-across you want to do. Hope this helps. Regards - Dave. Date: Thu, 2 Jul 2009 09:46:31 +0530 Subject: $$Excel-Macros$$ Help on copying formula. From: ca.ashut...@gmail.com To: excel-macros@googlegroups.com Hi All, Genrally in spreadheet, if you copy a formule in other rows, formula is changed according to row number. e.g. we have a cell with formula =A1 and now we copy this formula across columns to the right. Naturally, this results in the Formula reference changing to =B1, =C1, =D1 etc. However, this is not the result I want. I want that if I copy this formula across columns to the right, it should show as =A2, =A3, =A4 and so on. Can anyone help in this. Many Thanks, Ashutosh _ Get the latest news, goss and sport Make ninemsn your homepage! http://windowslive.ninemsn.com.au/article.aspx?id=813730 --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Help on copying formula.....
Hi all, There is another way to do this using the INDIRECT function. Have a look at the attached sheet. Select B2, then copy across. Column A will appear in Row 1 You may have to change the formula slightly to suit your data. Regards - Dave. Date: Thu, 2 Jul 2009 12:24:24 +0530 From: sanjaysoni2...@yahoo.co.in Subject: $$Excel-Macros$$ Re: Help on copying formula. To: ca.ashut...@gmail.com CC: excel-macros@googlegroups.com Dear Ashutosh, I understood you problem it can be solved by making the coloum fixed by dollar sign and then copy that formula. For eg :- =A1 fix the coloum by =$A1. Best Regards, Sanjay --- On Thu, 2/7/09, Dave Bonallack davebonall...@hotmail.com wrote: From: Dave Bonallack davebonall...@hotmail.com Subject: $$Excel-Macros$$ Re: Help on copying formula. To: excel-macros@googlegroups.com excel-macros@googlegroups.com Date: Thursday, 2 July, 2009, 12:13 PM Hi Ashutosh, Absolute refrencing won't help you here. If you have control of the spread-sheet, and you can easily re-structure it, change the vertical list to a horizontal one. If you can't do that, select the refrence list, then copy. find an unused pert of the sheet. Click in a cell, then rightclick, Paste Special. in the Paste Special dialog box, tick the transpose option (near the bottom). you can use the new list as a reference for the copy-across you want to do. Hope this helps.. Regards - Dave. Date: Thu, 2 Jul 2009 09:46:31 +0530 Subject: $$Excel-Macros$$ Help on copying formula. From: ca.ashut...@gmail.com To: excel-macros@googlegroups.com Hi All, Genrally in spreadheet, if you copy a formule in other rows, formula is changed according to row number. e.g. we have a cell with formula =A1 and now we copy this formula across columns to the right. Naturally, this results in the Formula reference changing to =B1, =C1, =D1 etc. However, this is not the result I want. I want that if I copy this formula across columns to the right, it should show as =A2, =A3, =A4 and so on.Can anyone help in this. Many Thanks, Ashutosh Yahoo! recommends that you upgrade to the new and safer Internet Explorer 8 _ Looking for a new car this winter? Let us help with car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- Indirect.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: help required
Hi Shivashankar.C.Katageri. Have a look at the attached. Select B1 then copy across. Regards - Dave. Date: Thu, 2 Jul 2009 13:04:10 +0530 Subject: $$Excel-Macros$$ help required From: shankarkatag...@gmail.com To: excel-macros@googlegroups.com Dear all, Any one can help w.r.t copy from subjects of column cells to row cells,by just dragging or in no time, please for reference see attached file, -- Thanks Regards Shivashankar.C.Katageri. _ Looking for a new car this winter? Let us help with car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~--- New Microsoft Excel Worksheet (2).xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: A question about conditional formatting
Hi Doug, All formatting can be done through VBA. But if you want the formatting applied to each cell as you click on it, then you need an event macro, which needs to reside in the code window of that sheet. So each day when you create a new sheet, you'd have to paste in the VBA code as well. My guess is that the way you are doing it now, is best. But let me know if there's a pressing reason to use VBA instead. Regards - Dave Date: Thu, 2 Jul 2009 03:55:36 -0700 Subject: $$Excel-Macros$$ Re: A question about conditional formatting From: dsrmccl...@gmail.com To: excel-macros@googlegroups.com Dave, That's exactly how I'm doing it now. I'm just wondering if each cell could call a sub that would do the formatting on the fly and not hard code it with each sheet. If there would be any advantage if it could be done. On Jul 1, 10:29 pm, Dave Bonallack davebonall...@hotmail.com wrote: Hi Doug, I think I would keep a sheet in the workbook called 'Template' which contains all the formatting you want. Then each day, make a copy of that sheet, and rename the copy as needed. Don't ever put data into your Template sheet. Just use it to create new sheets. Regards - Dave. Date: Wed, 1 Jul 2009 08:55:34 -0700 Subject: $$Excel-Macros$$ A question about conditional formatting From: dsrmccl...@gmail.com To: excel-macros@googlegroups.com Just a quick question. I'm writing a workbook in which a new worksheet will be added every day. Each worksheet contains a number of cells with the same conditional formatting applied. Is there any advantage to applying the formatting through VBA code as opposed to applying it through the Format menu? I'm thinking about how this workbook will grow in size as new sheets are added. Thank you. _ Looking for a place to rent, share or buy this winter? Find your next place with Ninemsn propertyhttp://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Eco...- Hide quoted text - - Show quoted text - _ View photos of singles in your area Click Here http://dating.ninemsn.com.au/search/search.aspx?exec=gotp=qgc=2tr=1lage=18uage=55cl=14sl=0dist=50po=1do=2trackingid=1046138r2s=1_t=773166090_r=WLM_EndText --~--~-~--~~~---~--~~ -- 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 -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Simple VBA Data validation query
Hi Steve, Have a look at the attached. Regards - Dave. Date: Fri, 3 Jul 2009 02:12:06 -0700 Subject: $$Excel-Macros$$ Simple VBA Data validation query From: stevedha...@googlemail.com To: excel-macros@googlegroups.com Hi, Hopoing someone will be kind enough to help me with this simple query. I have a spreadhseeet, and want to write some vba code which says if the value of cell a2 is greater than the value of cell g3, then display a message saying Value cant be higher than effort Best Steve _ POP access for Hotmail is here! Click here to find out more http://windowslive.ninemsn.com.au/article.aspx?id=802246 --~--~-~--~~~---~--~~ -- 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 -~--~~~~--~~--~--~--- example.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: calendar pop-up
Hi Dilip, Could you please tell me how you did the calendar thing? Your sample workbook gives no explanations, and I can't figure it out by the code. Regards - Dave. Date: Sat, 4 Jul 2009 00:22:25 +0530 Subject: $$Excel-Macros$$ Re: calendar pop-up From: dilipan...@gmail.com To: excel-macros@googlegroups.com Hi Lynn, Calender control has been explained in the attached workbook. Note:- Date will be inserted in the selected cell, which can be modified. check up the code and you will understand the logic, It is quite simple. Thanks, -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 110062 On Fri, Jul 3, 2009 at 9:35 PM, Lynn moley_c...@yahoo.com.au wrote: How can I have a calendar pop-up box for me to make date selection when I click on a cell? br _ POP access for Hotmail is here! Click here to find out more http://windowslive.ninemsn.com.au/article.aspx?id=802246 --~--~-~--~~~---~--~~ -- 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 -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: FIND COMMAND IN EXCEL VBA
Hi Jessie, If you attach a sample workbook, we will be able to help you better. I use this method a lot, but I need to know how your data is laid out. Regards - Dave. Date: Sat, 4 Jul 2009 04:57:42 -0700 Subject: $$Excel-Macros$$ FIND COMMAND IN EXCEL VBA From: jayachitra.dreamzunlimi...@gmail.com To: excel-macros@googlegroups.com Hi Guys, Am new to the forum and learning basics of Excel Vba. I now need to find a list of values from sheet2 and delete it from sheet1. Tried using the Find command in the following way Cells.Find(What:=Sheet2.Cells(R2, C2), After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.EntireRow.Delete But this deletes the row from the sheet2. I need to delete the row from sheet1. All that now i want to know is how do i differentiate between i.e through return value of the find function. 1. The search string is found 2. Search string is not found Plz help me out. This problem is really bugging me... _ POP access for Hotmail is here! Click here to find out more http://windowslive.ninemsn.com.au/article.aspx?id=802246 --~--~-~--~~~---~--~~ -- 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 -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: V look up error 1004
Hi, Assuming accno is a named range, try one of the following: WorksheetFunction.VLookup((accno), Range(q4:w570), 5, False) or WorksheetFunction.VLookup(Range(accno), Range(q4:w570), 5, False) or WorksheetFunction.VLookup(Range(accno), Range(q4:w570), 5, False) Regards - Dave. Date: Sun, 5 Jul 2009 16:30:09 +0530 Subject: $$Excel-Macros$$ V look up error 1004 From: sba...@gmail.com To: excel-macros@googlegroups.com Can some body in the group tell me why error occurs for V look up function Private Sub ComboBox3_Change() 'accno = ComboBox3.Value Workbooks.Open Filename:=h:\credit\Limits of rs 25 lacs and over Worksheets(sheet1).Activate ccode = WorksheetFunction.VLookup((accno), Range(q4:w570), 5, False) Error on this line End Sub _ Get the latest news, goss and sport Make ninemsn your homepage! http://windowslive.ninemsn.com.au/article.aspx?id=813730 --~--~-~--~~~---~--~~ -- 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 -~--~~~~--~~--~--~---
$$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 _ Looking for a new car this winter? Let us help with car news, reviews and more http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT --~--~-~--~~~---~--~~ -- 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 -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: how to get the week no in a function
Hi, Try the following: =WEEKNUM(TODAY()) Regards - Dave. Date: Tue, 14 Jul 2009 19:57:18 -0700 Subject: $$Excel-Macros$$ how to get the week no in a function From: subbu1...@gmail.com To: excel-macros@googlegroups.com hi can you pls tell me the function to get the week no of a particular date in a cell entry? For example if the date is 12/1/2009, i need the week no in 2009 for this date . thanks _ POP access for Hotmail is here! Click here to find out more http://windowslive.ninemsn.com.au/article.aspx?id=802246 --~--~-~--~~~---~--~~ -- 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 -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Value all the Selection
Hi, Try putting the following code in a module, then Run: Sub ChangeToValue() A = 2 Do Until Cells(A, 1) = Cells(A, 1) = Val(Cells(A, 1)) A = A + 1 Loop End Sub Regards - Dave Date: Thu, 16 Jul 2009 23:17:46 -0700 Subject: $$Excel-Macros$$ Value all the Selection From: deepakrawa...@gmail.com To: excel-macros@googlegroups.com Hi Dear friends I have a little querryi have attached a file and i want to use value function by macro there are some data which are not value and i want to make them value with the help of value function by macro. i am facing problem that when m making object of worksheetfunction then there is not any function of value there. Pls help!! regards, Deepak Rawat _ Looking for a place to rent, share or buy this winter? Find your next place with Ninemsn property http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ -- 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 -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: DateStamp Macro Debug Help
Hi, I pasted your code into my XL2000. I don't get any error when inserting rows. Dave. Date: Fri, 17 Jul 2009 12:36:21 -0700 Subject: $$Excel-Macros$$ DateStamp Macro Debug Help From: pogs...@gmail.com To: excel-macros@googlegroups.com Hey all, I found a very simple macro to track any changes made to any cell in a row by datestamping the end of the row. The issue comes in whenever i try to insert a row into the sheet, i get an error: Runtime Error 1004, Application-defined or Object- defined error and im not sure how to fix this. Here is a sample of my code: Public Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then Application.EnableEvents = False Target.Offset.Offset(0, 13) = Now() Application.EnableEvents = True End If End Sub Could anyone please point me in the right direction, how can i keep this functionality and still be able to insert new rows into my sheet? Thanks in advance! Best Regards, Pogster _ Get the latest news, goss and sport Make ninemsn your homepage! http://windowslive.ninemsn.com.au/article.aspx?id=813730 --~--~-~--~~~---~--~~ -- 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 -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: need help!!!!!
Hi Dev, Your macro is more complex than necessary. You don't have to unhide sheets, or select ranges to work on them. The following 2 lines of code should do what you need. Sub DoWhatDevNeeds() Sheets(Dump).Cells.ClearContents Sheets(Formula).Columns(A:B).ClearContents End Sub Hope this helps. Regards - Dave. Date: Tue, 21 Jul 2009 11:21:06 +0530 Subject: $$Excel-Macros$$ need help! From: b.debapr...@gmail.com To: excel-macros@googlegroups.com Hi Guys, I need a help on attached file. The test.xls file contain 3 sheets. Rep, Dump1, Formula.But when you open the file you will find only Rep because other 2 are hidden. Now i want to write a macro which will do the following.step1 unhide Dump1 sheet delete all the content step2 hide Dump1 sheet and go back to Rep sheet step3unhide Formula sheet and delete the content from A B column step4 hide the Formula sheet and go back to Rep sheet the problem is, the macro which I have written that also deleting the content of Rep sheet means the 1st sheet, which I dont want. I want dont want any modification on Rep sheet. Guys Pleasee help me with this... -- ***sAy ChEeSe*** (¨`·.·´¨) `·.¸.·´ '' DeV ''.. _ View photos of singles in your area Click Here http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fdating%2Eninemsn%2Ecom%2Eau%2Fsearch%2Fsearch%2Easpx%3Fexec%3Dgo%26tp%3Dq%26gc%3D2%26tr%3D1%26lage%3D18%26uage%3D55%26cl%3D14%26sl%3D0%26dist%3D50%26po%3D1%26do%3D2%26trackingid%3D1046138%26r2s%3D1_t=773166090_r=Hotmail_Endtext_m=EXT --~--~-~--~~~---~--~~ -- 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 -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Segregation of data on a button click in excel
Hi, If Pinky's data is just like you say, have a look at the attached. You can copy down to accommodate more data. However, if the sheet has a lot of data, ie thousands of lines, the attached worksheet function solution may take too long, and a VBA solution would be better. Regards - Dave. From: jainayus...@gmail.com Date: Mon, 27 Jul 2009 23:22:18 +0530 Subject: $$Excel-Macros$$ Re: Segregation of data on a button click in excel To: excel-macros@googlegroups.com CC: pinky.sang...@gmail.com Hello All, Please find attached input and desired output of Pinky. Thanks for your help, Ayush Jain _ Need a new model in your life? Sell your car fast. http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F866383_t=758314219_r=carpoint_tagline_m=EXT --~--~-~--~~~---~--~~ -- 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 -~--~~~~--~~--~--~--- text(1).xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Segregation of data on a button click in excel
Hi, I don't think this can be done with worksheet functions. You'll have to use VBA. Something like the following: Range(A1) = Range(I3) vbNewLine Range(J3) vbNewLine Range(K3) This puts 3 lines of data into A1. If you want to put lots of lines of data into one cell, we could use a loop: A = 9 Do Until A = 26 Range(A1) = Range(A1) + Cells(3, A) vbNewLine A = A + 1 Loop This puts data from cells I3 to Z3 into cell A1, each on a new line. Hope this helps Regards - Dave Date: Mon, 27 Jul 2009 22:14:12 -0700 From: shannur...@yahoo.com Subject: $$Excel-Macros$$ Re: Segregation of data on a button click in excel To: excel-macros@googlegroups.com Hello Dave, Incase if I have to assemble all the different cell values like i3, j3,toz3 all these cell data to be assembled in one particular cell how to go about it and all this data should appear one after the other and in one whole cell. --- On Tue, 7/28/09, Dave Bonallack davebonall...@hotmail.com wrote: From: Dave Bonallack davebonall...@hotmail.com Subject: $$Excel-Macros$$ Re: Segregation of data on a button click in excel To: excel-macros@googlegroups.com excel-macros@googlegroups.com Date: Tuesday, July 28, 2009, 10:23 AM Hi, If Pinky's data is just like you say, have a look at the attached. You can copy down to accommodate more data. However, if the sheet has a lot of data, ie thousands of lines, the attached worksheet function solution may take too long, and a VBA solution would be better. Regards - Dave. From: jainayus...@gmail.com Date: Mon, 27 Jul 2009 23:22:18 +0530 Subject: $$Excel-Macros$$ Re: Segregation of data on a button click in excel To: excel-macros@googlegroups.com CC: pinky.sang...@gmail.com Hello All, Please find attached input and desired output of Pinky. Thanks for your help, Ayush Jain _ What goes online, stays online Check the daily blob for the latest on what's happening around the web http://windowslive.ninemsn.com.au/blog.aspx --~--~-~--~~~---~--~~ -- 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: Find a Value From Other Worksheet Without Activating?
Hi, I have also come across this problem. I have not found a way to search a sheet that is not selected. The work-around I use is as follows: Application.ScreenUpdating = False A = ActiveSheet.Name Your code here, including the Sheet.Select line Sheets(A).Select Application.ScreenUpdating = True The above code gives at least the visual impression that the searched sheet is not being activated. If you have event macros that fire when you activate or deactivate a sheet, you may want to include: Application.EnablEvents = False after the first line, and Application.EnablEvents = True before the last line. Regards - Dave Date: Thu, 30 Jul 2009 16:16:19 -0700 Subject: $$Excel-Macros$$ Find a Value From Other Worksheet Without Activating? From: jhawk...@locutius.com To: excel-macros@googlegroups.com Hi, The following code snippit works fine: Dim iMyValue As Integer Worksheets(Config).Activate iMyValue = Cells(Cells.Find(What:=FindMe, After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False).Row, 7).Value However, if I don't activate, but give the other worksheet as the starting point, it fails unless it happens to be running from the worksheet being searched: Dim iMyValue As Integer iMyValue = Cells(Cells.Find(What:=FindMe, After:=Worksheets (Config).Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= False, SearchFormat:=False).Row, 7).Value Is it possible to find a value on another worksheet without activating it? I have a worksheet called config. Basically I am trying to look up parameters from the config worksheet based on a combo box choice on a different worksheet. Once the user makes a selection, I try to find the row in the config worksheet that has the parameters for the user's suggestion, then update any data as needed. But I don't want to go switching worksheets around while doing this. Thanks in advance for any help. _ What goes online, stays online Check the daily blob for the latest on what's happening around the web http://windowslive.ninemsn.com.au/blog.aspx --~--~-~--~~~---~--~~ -- 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: Data Validation to only allow 7 digit numbers in a entire column
Hi Wes, Try formatting your data as text, then use the following data validation: =AND(LEN(A1)=7,ISERROR(VALUE(A1))=FALSE) Regards - Dave. Date: Mon, 3 Aug 2009 18:11:25 -0700 Subject: $$Excel-Macros$$ Data Validation to only allow 7 digit numbers in a entire column From: wesley.d.gi...@gmail.com To: excel-macros@googlegroups.com I've been trying to get some cell validation on all cells within column A without any luck. What I want is column A to only accept 7 digit numbers but can't seem to get it working right. All values would have to be 7 in length. All of those 7 digits in a single cell must be all numbers. A cell could have a value of 007, because it is 7 digits and it is 7 in length. The closest I can get this to working is by formatting the cells to a custom format with a value of 000. And putting a data validation in place on these cells that has a formula of: =LEN($A$1:$A$65000)=7 but this formula has problems cause it doesn't seem to think a cell value of 003 is length 7, guess it thinks it's a length of 1 because it's converting to number and losing the preceding zeros... I would greatly appreciate any help or direction, Thanks in advance for any help you can provide. _ Looking for a place to rent, share or buy this winter? Find your next place with Ninemsn property http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ -- 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: Formula-Replacing Minus Symbol
Hi, Try this formula in B2, and copy down. =LEFT(A2,LEN(A2)-1)*-1 You will have to format the cell to Number, 2 decimal places. Regards - Dave. Date: Tue, 4 Aug 2009 14:38:34 +0530 Subject: $$Excel-Macros$$ Formula-Replacing Minus Symbol From: puttamada...@gmail.com To: excel-macros@googlegroups.com Hi All, Can any one tell me the formula to get the value as in Required answer as against Original Value. Original Value Required answer 125756.95- -125756.95 75845.25- -75845.25 123.38- -123.38 10.15- -10.15 158946- -158946.00 856456.25- -856456.25 Regards- Putta Madaiah- Bangalore _ Looking for a place to rent, share or buy this winter? Find your next place with Ninemsn property http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline_t=774152450_r=Domain_tagline_m=EXT --~--~-~--~~~---~--~~ -- 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: Simple question about Format macro
Hi, Highlight the entire column - lets suppose it's column A. Then put the following into the Formula is part in the Conditional Format dialog box: =LEN(A1)10 Set the desired format. Regards - Dave. Date: Sat, 8 Aug 2009 08:12:55 -0700 Subject: $$Excel-Macros$$ Simple question about Format macro From: cdelano...@gmail.com To: excel-macros@googlegroups.com I am trying to do a simple macro to look at a column or line (most of the time a column) and evaluate the length on the data on each cell and determine if it is 10 digits. If the value of the data in the cell is different than 10 digits (more or less). I want that cell to change color. it is like a conditional formatting, but I can't figure out the right language to accomplish this. I would appreciate your help with this. Thanks a whole million !!! _ Use Windows Live Messenger from your Hotmail inbox Web IM has arrived! http://windowslive.ninemsn.com.au/article.aspx?id=823454 --~--~-~--~~~---~--~~ -- 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: Calculate Range of Days
Hi, Paste this into D2, then copy down. =IF(B2-A260,Above 60 days,IF(B2-A230,31-60 days,0-15 days)) Regards - Dave. Date: Sun, 9 Aug 2009 15:02:05 +0530 Subject: $$Excel-Macros$$ Calculate Range of Days From: mahes...@gmail.com To: excel-macros@googlegroups.com Dear All i want to calculate range of days like 0-15 days 16-30 days, i do it by filter, anyone knows the formula, please find attach sample sheet. _ Use Windows Live Messenger from your Hotmail inbox Web IM has arrived! http://windowslive.ninemsn.com.au/article.aspx?id=823454 --~--~-~--~~~---~--~~ -- 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: 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 wrote: Hi want to insert = sign before each no. for variety of calculation purpose see attached file Manoj From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Aindril De Sent: Wednesday, June 24, 2009 9:15 PM To: excel-macros@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 wrote: Dear Sandeep, Please find attached the updated file. It shd not be an issue. Just say select all... then Ctrl + H to open the Find Replace box... In Find type In Replace type \ Then press replace all. Regards, Andy On Wed, Jun 24, 2009 at 6:46 PM, Sandeep sandymau...@gmail.com wrote: Hi Everybody, Please help me. I have a file with a lot of inverted commas in cells which should be \ and many cells have large data due to which 'find and replace' command not working and saying too long formula. How can I replace this. Please help me. Thanks You How can I replace This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions that are unlawful. This e-mail may contain viruses. Ocimum Biosolutions has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions that are unlawful. This e-mail may contain viruses. Ocimum Biosolutions has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions that are unlawful. This e-mail may contain viruses. Ocimum Biosolutions has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. _ Need a new model in your life? Sell your car fast. http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F866383_t=758314219_r=carpoint_tagline_m=EXT --~--~-~--~~~---~--~~ -- Some
$$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 wrote: Hi want to insert = sign before each no. for variety of calculation purpose see attached file Manoj From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Aindril De Sent: Wednesday, June 24, 2009 9:15 PM To: excel-macros@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 wrote: Dear Sandeep, Please find attached the updated file. It shd not be an issue. Just say select all... then Ctrl + H to open the Find Replace box... In Find type In Replace type \ Then press replace all. Regards, Andy On Wed, Jun 24, 2009 at 6:46 PM, Sandeep sandymau...@gmail.com wrote: Hi Everybody, Please help me. I have a file with a lot of inverted commas in cells which should be \ and many cells have large data due to which 'find and replace' command not working and saying too long formula. How can I replace this. Please help me. Thanks You How can I replace This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions that are unlawful. This e-mail may contain viruses. Ocimum Biosolutions has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions that are unlawful. This e-mail may contain viruses. Ocimum Biosolutions has taken every reasonable
$$Excel-Macros$$ Re: Fwd: about to remove #VALUE! error
Hi, Have a look at the attached. I've used SUMIF. But I don't know why you don't want to use SUM. It works just as well, and is simpler. They are the results in red. Regards - Dave Date: Sat, 15 Aug 2009 17:21:35 +0530 Subject: $$Excel-Macros$$ Fwd: about to remove #VALUE! error From: dppin...@gmail.com To: excel-macros@googlegroups.com 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) _ Use Windows Live Messenger from your Hotmail inbox Web IM has arrived! http://windowslive.ninemsn.com.au/article.aspx?id=823454 --~--~-~--~~~---~--~~ -- 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: Combine multiple ranges using R1C1
Hi Epi, One way is to use the Union thingy Union(Range(Cells(1, 1), Cells(2, 2)), Range(Cells(3, 3), Cells(4, 4))).Name = RangeName Regards - Dave Date: Wed, 26 Aug 2009 18:14:05 -0700 Subject: $$Excel-Macros$$ Combine multiple ranges using R1C1 From: epi_cen...@hotmail.com To: excel-macros@googlegroups.com Hello group, after spending hours on the web searching for an answer i resort to you with my noob-question: How do I combine separated ranges of a worksheet like Range(A1:B2, C3:D4).Name = RangeName using R1C1 references as in (pseudocode) Range(Cells(1,1):Cells(2,2), Cells(3,3):Cells(4,4)).Name = RangeName Your knowledge and help is appreciated very much! Cheers, Epi _ What goes online, stays online Check the daily blob for the latest on what's happening around the web http://windowslive.ninemsn.com.au/blog.aspx --~--~-~--~~~---~--~~ -- 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: Hidden sheets
Hi Tomy, I think the problem is that you have the macro 'Selecting' the range which is xlveryhidden. Such a range can't be seleceted, but XL doesn't have to select a cell to work on it. Try the following code lines instead of what you have. Sub Move_Data() ' ' Move_Data Macro ' Macro recorded 8/26/2009 by Tommy ' ' Keyboard Shortcut: Ctrl+m ' Selection.Copy Sheets(Sheet3).Range(A1).PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Range(B1).Select End Sub Regards - Dave. Date: Wed, 26 Aug 2009 17:00:03 -0700 Subject: $$Excel-Macros$$ Hidden sheets From: jntwh...@verizon.net To: excel-macros@googlegroups.com I have not been successful in move data from one sheet to another when the destination sheet (sheet3 in this case) has been hidden using the following: Sheets ( “sheet3” ) . Visible = x1VeryHidden When I unhide sheet3 the following Macro works fine. Sub Move_Data() ' ' Move_Data Macro ' Macro recorded 8/26/2009 by Tommy ' ' Keyboard Shortcut: Ctrl+m ' Selection.Copy Sheets(Sheet3).Select Range(A1).Select ActiveSheet.Paste Sheets(Sheet2).Select Application.CutCopyMode = False Range(B1).Select End Sub The error comes up on line 2 having to do with the selection of sheet3 I’m lead to believe that a sheet hidden in this way is not write- protected, thus information transfers in or out can be done. Can someone help me on this? Tomy _ What goes online, stays online Check the daily blob for the latest on what's happening around the web http://windowslive.ninemsn.com.au/blog.aspx --~--~-~--~~~---~--~~ -- 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: Too many arguments for this function help with nested IF statements
Hi Mark, Your description is very detailed, but I still get lost in it. Perhaps you could attach the file. Fudge the data if it's sensitive. Regards - Dave. Date: Mon, 7 Sep 2009 06:37:32 -0700 Subject: $$Excel-Macros$$ Too many arguments for this function help with nested IF statements From: marka...@gmail.com To: excel-macros@googlegroups.com Good morning! Would love your help with this issue. I'm try to create a formula that will evaluate a number based on whether it fits into one of five buckets and then apply the appropriate formula to calculate the resulting credit value I'm seeking. N = The number being evaluated (referred to in my formula below as final!IJ3). I have three sheets in the same Workbook: Formula sheet is where this formula resides Final sheet is where the N data is pulled from Background sheet is where all of the calculations to derive the needed information are determined. On to the buckets Bucket 1: N is zero or empty cell = Bucket 2: N is greater than zero but less than baseline (baseline = background!IJ$348) = base credit (base credit = background!IJ$346) Bucket 3: N is greater than or equal to baseline but less than the average (average = background!IJ$336) = base credit + a portion of half the available performance credit (performance credit = background! J342). Portion of performance credit awarded is calculated by where N falls on a percent scale where baseline = 0% and average = 100%). 100% = half of the performance credit. Bucket 4: N is greater than or equal to the average but less than the max (max = background!IJ$349) = base credit + half of the performance credit + Portion of the second half of the performance credit awarded calculating where N falls on a percent scale where average = 0% and max = 100%). Bucket 5: N is greater than or equal to the max = base credit + full performance credit. I wrote out this formula and I thought it was going to work fine, but then I got the dreaded too many arguments fro this function error when approaching the end of the formula (specifically the last IF statement). Any help in making this work would be INCREDIBLY appreciated as I'm working with small nonprofit and we are under intense time pressure for this project. Would love ideas on how to shorten it and make it work. If certain functions need to be handled outside of the formula, I have room in my background sheet to handle those calculations. For your info, this formula will be applied to about 300 cells in the IJ column of the Formulas sheet. Here is the formula I wrote. =IF(AND(final!IJ30,final!IJ3background!IJ$348),((background!IJ $343+background!IJ$344)*background!IJ$345),IF(AND(final! IJ3=background!IJ$348,final!IJ3background!IJ$336),(((final!IJ3- background!IJ$348)/(background!IJ$336-background!IJ$348))*(background! IJ$342)/2)+background!$IJ$346),*IF(final!IJ3=background!IJ$349), (background!IJ$343+background!IJ$344)),) **the formula works fine until this point, then gives me the too many arguments error. Thank you! Mark Key final!IJ3 = number being evaluated background!IJ$336 = average background!IJ$343 = total credit (base credit + performance credit) background!IJ$344 = extra credit background!IJ$345 = ratio of base credit (e.g. ratio of base credit * total credit = base credit) background!IJ$346 = base credit background!IJ$348 = baseline background!IJ349 = max _ Get Hotmail on your iPhone Find out how here http://windowslive.ninemsn.com.au/article.aspx?id=845706 --~--~-~--~~~---~--~~ -- 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: Variable Workbook name used in range formula
Hi, I haven't tested this, but I think you need: Range(data) = =vlookup(R[-1]C,[ CarrierGrid ]OAK!L:R,7,False) ie, use square brackets instead of ' Regards - Dave Date: Tue, 8 Sep 2009 07:26:00 -0700 Subject: $$Excel-Macros$$ Variable Workbook name used in range formula From: jamison.foll...@gmail.com To: excel-macros@googlegroups.com Hello all, new to group! I will contribute as I am able, but to start, I am really hoping someone can help me with a problem. I have a named workbook as follows: CarrierGrid = Range(O CStr(R)) Range(Control!B4).Value .xls This is giving me CarrierGrid as HDMU 08.31.xls, which is what it is for this particular row R. Later, I have the following: Range(data) = =vlookup(R[-1]C,' CarrierGrid 'OAK!L:R, 7,False) I'm trying to do a lookup in the above workbook HDMU 08.31.xls, onto tab called OAK. However, the formula is reading it as workbook 08.31.xlsOAK. Any ideas on how I get the named workbook CarrierGrid into a formula for a range in VBA? Thanks much all! _ Use Messenger in your Hotmail inbox Find out how here http://windowslive.ninemsn.com.au/article.aspx?id=823454 --~--~-~--~~~---~--~~ -- 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 6,500 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: VBA - Access multiple Excel instances
Hi, Can't you just set calculations to manual while you do the Goal Seek? Dave. Date: Fri, 11 Sep 2009 03:33:04 -0700 Subject: $$Excel-Macros$$ VBA - Access multiple Excel instances From: paul...@gmail.com To: excel-macros@googlegroups.com Hi, I have a very large large spreadsheet with 2 rows and about 50 columns most with long complex formulas so calculation on this sheet takes time. The main purpose of my code is to perform a Goal Seek but depending on results the code might loop up to 100 times. Each Goal Seek takes about 9 minutes! One column from the table changes at each iteration so what I want to do is copy this column to another workbook, perform the Goal Seek, then copy back my required result. My problem is though that if the second workbook is in the same Excel instance as the actual table the process is just as slow. So, my question is how do I access a completely separate instance within my VBA code so as to paste the 2 line column, perform the goal seek in that workbook then copy the result back Any help appreciated ..pc _ View photos of singles in your area Click Here http://clk.atdmt.com/NMN/go/150855801/direct/01/ --~--~-~--~~~---~--~~ -- 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 6,500 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: VBA - Access multiple Excel instances
Hi Paul, I've seen your post in the microsoft news groups, so you may have seen this link, but take a look at it anyway. Multiple instances of XL have always been a problem as far as VBA is concerned. http://www.microsoft.com/office/community/en-us/default.mspx?query=multiple+instances+of+Excellang=encr=USguid=sloc=en-usdg=microsoft.public.excel.programmingp=1tid=e038db96-1556-4d32-a524-fe6a711b8409mid=1600366f-c5e5-42c0-8095-82242a0c87ec By the way, I put multiple instances of excel into the search thingy of the microsoft excel news group, and got lots of hits. Didn't have time to read them all. Dave. Date: Fri, 11 Sep 2009 07:06:48 -0700 Subject: $$Excel-Macros$$ Re: VBA - Access multiple Excel instances From: paul...@gmail.com To: excel-macros@googlegroups.com Hi Dave, In a way that was my problem, dispite the fact that I had calculation set to manual the sheet with the large large table was still recalculating at each iteration! I've managed to get around it by explicitly turning off calculation on that sheet during the goal seek using wks.enablecalculation = false then re-enabling after the goal seek. That said I would still like to know if it is possible and how to manage multiple instances of Excel from within one Sub? Thanks Paul On Sep 11, 2:39 pm, Dave Bonallack davebonall...@hotmail.com wrote: Hi, Can't you just set calculations to manual while you do the Goal Seek? Dave. Date: Fri, 11 Sep 2009 03:33:04 -0700 Subject: $$Excel-Macros$$ VBA - Access multiple Excel instances From: paul...@gmail.com To: excel-macros@googlegroups.com Hi, I have a very large large spreadsheet with 2 rows and about 50 columns most with long complex formulas so calculation on this sheet takes time. The main purpose of my code is to perform a Goal Seek but depending on results the code might loop up to 100 times. Each Goal Seek takes about 9 minutes! One column from the table changes at each iteration so what I want to do is copy this column to another workbook, perform the Goal Seek, then copy back my required result. My problem is though that if the second workbook is in the same Excel instance as the actual table the process is just as slow. So, my question is how do I access a completely separate instance within my VBA code so as to paste the 2 line column, perform the goal seek in that workbook then copy the result back Any help appreciated ..pc _ View photos of singles in your area Click Herehttp://clk.atdmt.com/NMN/go/150855801/direct/01/ _ Use Messenger in your Hotmail inbox Find out how here http://windowslive.ninemsn.com.au/article.aspx?id=823454 --~--~-~--~~~---~--~~ -- 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 6,500 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: Count of Colored Cells
Hi Pooja Not possible in 2003 usinf formulas. Have to use VBA. And if the cells are colored as a result of conditional formatting, it's even trickier. Regards - Dave. Date: Tue, 15 Sep 2009 11:15:43 +0530 Subject: $$Excel-Macros$$ Count of Colored Cells From: vatspoojav...@gmail.com To: excel-macros@googlegroups.com Hi Friends, I have a query regarding counting of Colored cells. I want to know, is it possible to count the no. of cells filled with a particular color with the help of a formula. If yes then please guide me and help me in resolving this. Attached is a file which has an example, how I want to count the cells. -- Best Regards Pooja Sharma _ Use Messenger in your Hotmail inbox Find out how here http://windowslive.ninemsn.com.au/article.aspx?id=823454 --~--~-~--~~~---~--~~ -- 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 6,500 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 -~--~~~~--~~--~--~---
