| Article | Body |
| 424 | thanks for your help...will check it out! cheers "" <DavidH@OzGrid.com wrote in message news:L6zQ8.24$ye5.40110@vicpull1.telstra.net... Forgot to mention that "a difference of around 10,000,000" may or may not be considerable! The only way to tell is to get the % difference. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "" <DavidH@OzGrid.com wrote in message news:r4zQ8.23$ye5.40152@vicpull1.telstra.net... | Hi Pauline | | I am certainly no expert on the 2 so I will take the cheats way and refer | you to the methods used in the 2. The trend uses a method know as "The least | squares method" You can read the technical details on this here: | /math/leastsquares/leastsquares.cfm. On this site you | should also find some explanation of the 2. There is also an "Ask an Expert" | page that could help you. | | There will no doubt someone in here that knows the technical difference, but | unfortunately there may also be some that only think they know, like me :o) | | Neither is walk in the park! | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | | | | "Pauline's News Reader" <pauline@icfrith.com.au wrote in message | news:lEyQ8.21$ye5.38955@vicpull1.telstra.net... | | I have created a table for sales history for the past 5 years eg | | 96,97,98,99,2000. with appropriate dollar figures. | | | | When I "Trend" for 2001 I get X value | | | | When I "Forecast" for 2001 I get Y value. | | | | (a difference of around 10,000,000.) | | | | What is the difference between the two and why should I use one over the | | other. | | | | Clarification of the two would be much appreciated. | | | | cheers. | | | | | | |
| 433 | Hi AussieDave Right click on the sheet name tab, select "View Code" and paste in this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Address = "$A$1" And IsEmpty(Target) Then Select Case Target.Interior.ColorIndex Case 3 Target.Interior.ColorIndex = 46 Case 46 Target.Interior.ColorIndex = 10 Case Else Target.Interior.ColorIndex = 3 End Select End If End Sub -- **Possible solution has NOT been tested** FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm "AussieDave" <dave@optushome.com.au wrote in message news:qcBQ8.57670$_j6.3018857@bin3.nnrp.aus1.giganews.com... | When clicking on a specific empty cell, I need to color it red after the | first click, orange after the second and (you guessed it!!) green after the | third. | I haven't got a clue, except I guess it's tied up with Click Events. Any | help much appreciated. xl97 by the way. | -- | Dave from Down Under | -- | | |
| 438 | Hi Scott try: =IF(AND(ISNUMBER($D3),$D31),IF(M$1$D3,$D5/12,""),"") -- "Scott Cardais" <Scott@Cardais.com wrote in message news:112b001c21903$0dc8b990$3bef2ecf@TKMSFTNGXA10... | A B C D M N O | 1 8/1/02 9/1/02 10/1/02 | 2 Account 1 | 3 Close Month 7/1/2002 | 4 License Fee $20,000 | 5 Annual Maint $24,000 2,000 2,000 2,000 | 6 Installation $23,000 | | Formula in Row 5, column E and beyond =IF | (M$1$D3,$D5/12,"") | | In the sample shown above, the value of $2,000 is placed | in cells M5, N5, etc. since the date in M1 is greater | than the date in D3. If the date in D3 is changed to | 8/1/02, the 2,000 entries are shifted right one month. | | This is exactly what I want but if no date is entered in | D3, I want the nothing or zero entered in row 5 but the | formula shown above doesn't give me that result. (It puts | 2,000) in every cell in Row 5) | | I suspect that I need nested IF statements using the | ISBLANK function but I'm not having any success with this. | | For what it's worth, this is a cash forecasting worksheet | that lists all pending sales and their forecasted close | dates and revenue projections. Changing the Close Date | helps us construct different cash positions. Entering no | date in the Close Month cell would eliminate that | particular account from the forecast. | | Any help would be appreciated. | | Scott C. |
| 440 | Hi Dave: I pasted this into my June 02 columns and beyond and it resulted in entering 2,000 in June even if the close date was August. Just to be clear, if the close date is August and the annual amount is 24,000; I want 2,000 entered in September and beyond AND if the close date is blank, I want nothing entered in any months. Thanks very much for your help. -----Original Message----- Hi Scott try: =IF(AND(ISNUMBER($D3),$D31),IF(M$1$D3,$D5/12,""),"") -- "Scott Cardais" <Scott@Cardais.com wrote in message news:112b001c21903$0dc8b990$3bef2ecf@TKMSFTNGXA10... | A B C D M N O | 1 8/1/02 9/1/02 10/1/02 | 2 Account 1 | 3 Close Month 7/1/2002 | 4 License Fee $20,000 | 5 Annual Maint $24,000 2,000 2,000 2,000 | 6 Installation $23,000 | | Formula in Row 5, column E and beyond =IF | (M$1$D3,$D5/12,"") | | In the sample shown above, the value of $2,000 is placed | in cells M5, N5, etc. since the date in M1 is greater | than the date in D3. If the date in D3 is changed to | 8/1/02, the 2,000 entries are shifted right one month. | | This is exactly what I want but if no date is entered in | D3, I want the nothing or zero entered in row 5 but the | formula shown above doesn't give me that result. (It puts | 2,000) in every cell in Row 5) | | I suspect that I need nested IF statements using the | ISBLANK function but I'm not having any success with this. | | For what it's worth, this is a cash forecasting worksheet | that lists all pending sales and their forecasted close | dates and revenue projections. Changing the Close Date | helps us construct different cash positions. Entering no | date in the Close Month cell would eliminate that | particular account from the forecast. | | Any help would be appreciated. | | Scott C. . |
| 441 | Dave: My mistake. Your formula DOES work. I had to make one small change to reference the proper cell in my spreadsheet. Thanks very much. Scott C. -----Original Message----- Hi Scott try: =IF(AND(ISNUMBER($D3),$D31),IF(M$1$D3,$D5/12,""),"") -- "Scott Cardais" <Scott@Cardais.com wrote in message news:112b001c21903$0dc8b990$3bef2ecf@TKMSFTNGXA10... | A B C D M N O | 1 8/1/02 9/1/02 10/1/02 | 2 Account 1 | 3 Close Month 7/1/2002 | 4 License Fee $20,000 | 5 Annual Maint $24,000 2,000 2,000 2,000 | 6 Installation $23,000 | | Formula in Row 5, column E and beyond =IF | (M$1$D3,$D5/12,"") | | In the sample shown above, the value of $2,000 is placed | in cells M5, N5, etc. since the date in M1 is greater | than the date in D3. If the date in D3 is changed to | 8/1/02, the 2,000 entries are shifted right one month. | | This is exactly what I want but if no date is entered in | D3, I want the nothing or zero entered in row 5 but the | formula shown above doesn't give me that result. (It puts | 2,000) in every cell in Row 5) | | I suspect that I need nested IF statements using the | ISBLANK function but I'm not having any success with this. | | For what it's worth, this is a cash forecasting worksheet | that lists all pending sales and their forecasted close | dates and revenue projections. Changing the Close Date | helps us construct different cash positions. Entering no | date in the Close Month cell would eliminate that | particular account from the forecast. | | Any help would be appreciated. | | Scott C. . |
| 447 | Hi Dan You could use the DMAX, this (I think) ignores error values. -- **Possible solution has NOT been tested** FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm "drwinterton" <daniel_winterton@hk.ml.com wrote in message news:112fe01c21910$d482aca0$3bef2ecf@TKMSFTNGXA10... | I am doing some financial calculations. I have excel | linked to bloomberg, a financial database. Bloomberg | populates cells C2, D2, and E2 with the 3-month, 6-month, | and 1-year volatility of a stock. In cell F, I have: | | =max(c2,d2,e2) | | However, if a certain stock hasn't been around for long | enough, then bloomberg spits a "Value#" in the | corresponding cell (for example, a stock has been around 9 | months. It will have a 3- and 6-month volatility, but no | 1-year volatility) | | Apparently, the MAX function does not work if one of the | cells is "value#". Any suggestions? | | Thanks, | Dan Winterton |
| 449 | Just another note that OE comes free with Internet Explorer. -- "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D130610.4010104@contextures.com... | Contact your local Microsoft office, and perhaps they can help you. | | /worldwide/ | | globird wrote: | in attempting to open excel or outlook express (meaning my | email through my cable address), I get a window saying | there's a missing MSO97.DLL file. I was able to download | this file from Dell phone support, but am still unable to | open excell. Then I was told to uninstall outlook express | and reinstall, but I can't find the CD or disk to | reinstall. How do I solve this? HELP!!! | | | -- | | Debra Dalgleish | | Excel FAQ, Tips & Book List | /tiptech.html | |
| 450 | Yep, just checked Dan. Just use: <#VALUE! As the criteria below your copied heading. =DMAX(B1:B100,1,D4:D5) Where B1:B100 is the range to find the MAX, 1 is the position of the Column to return the MAX from and D4:D5 respectively contain a copy of the heading in B1 and the criteria <#VALUE! -- "" <DavidH@OzGrid.com wrote in message news:NDDQ8.15$3n5.8632@vicpull1.telstra.net... | Hi Dan | | You could use the DMAX, this (I think) ignores error values. | | | -- | **Possible solution has NOT been tested** | | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | | | "drwinterton" <daniel_winterton@hk.ml.com wrote in message | news:112fe01c21910$d482aca0$3bef2ecf@TKMSFTNGXA10... | | I am doing some financial calculations. I have excel | | linked to bloomberg, a financial database. Bloomberg | | populates cells C2, D2, and E2 with the 3-month, 6-month, | | and 1-year volatility of a stock. In cell F, I have: | | | | =max(c2,d2,e2) | | | | However, if a certain stock hasn't been around for long | | enough, then bloomberg spits a "Value#" in the | | corresponding cell (for example, a stock has been around 9 | | months. It will have a 3- and 6-month volatility, but no | | 1-year volatility) | | | | Apparently, the MAX function does not work if one of the | | cells is "value#". Any suggestions? | | | | Thanks, | | Dan Winterton | | |
| 470 | Try Bill Manville's add-in Findlink.zip at: /MVP/Default.htm or read John Walkenbach's advice: -walk.com/ss/excel/usertips/tip054.htm HTH Jason Atlanta, GA -----Original Message----- Hello, Could someone tell me how to delete links between worksheet, please? I'm assigned a worksheet with links (not hyperlink) on it. I have no idea where the links are on the sheet; however, when I go to Edit/Links, I see about 5 links in there. I don't want those links any more, but I don't know how to delete them. Thank you for your help. . |
| 496 | Believe there was a typo with the Additional Range. This applies the filter starting in row 2, (it isn't clear what your intent is) Possibly Dave meant to add an Offset(1,0) and typed an extra range by mistake. Sub Tester7() If ActiveSheet.AutoFilterMode Then Range("B3").AutoFilter Range("B3", Range("IO2").End(xlToLeft).Offset(1, 0)).AutoFilter End Sub But if the headers are really in row 2, then just deleting the extra range should work. Sub Tester7() If ActiveSheet.AutoFilterMode Then Range("B3").AutoFilter Range("B3", Range("IO2").End(xlToLeft)).AutoFilter End Sub <DavidH@OzGrid.com wrote in message news:H6RQ8.6$os5.17928@vicpull1.telstra.net... Hi Steve I may be missing something here but you could replace you entire procedure with: Range("B3", Range("IO2").Range.End(xlToLeft)).AutoFilter -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "stindle" <stindle@yahoo.com wrote in message news:f4cc01c2195e$ae8fe000$37ef2ecf@TKMSFTNGXA13... | Sub AutoFilterRange() | 'find last column used in Row 2 | LastColumn = Range("IO2").End(xlToLeft).Column | 'select autofilter range on Row 3 | Range("B3:" & LastColumn & "3").Select | Selection.AutoFilter 'AutoFilter Off | Selection.AutoFilter 'AutoFilter On | End Sub | | The problem is LastColumn is numeric (16) | I need LastColumn to be alpha ("P") | | Is there a command to handle this? | | If I use .Address instead of .Column I could | strip the "P" but once the database gets past | column "AA" it would be even more coding. | | I understand using Offset but was hoping for | cleaner/faster code. | | Thanks, | Steve |
| 511 | Dave, That's great, thanks. Is there a way I can ask for a password in order to run this macro - if not correct then it exits? Then I could leave this macro in the workbook and just protect the code. Rob. -----Original Message----- Hi Rob This code will toggle sheet protection on/off Sub ToggleProtectContent() Dim i As Integer For i = 3 To 50 If Sheets(i).ProtectContents = False Then Sheets(i).Protect Password:="rob1" Else Sheets(i).Unprotect Password:="rob1" End If Next i End Sub It only checks for Content protection. -- "Rob Pearce" <rob.pearce@consignia.com wrote in message news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06... | I have a workbook with 50 worksheets. | | I protect sheets 3 to 50 [all being the same template for | data entry] with the same password. | | When I want to change some formula/formatting on these 48 | sheets I have to unprotect each sheet individually so that | I can eventually group them together to alter. | | Would it be possible to have some code that will loop from | shhet 3 to 50 and unprotect - the password is rob1 if | anyone could lend a hand - and conversely then to re | protect. | | I would obviously remove the macros from the workbook | afterwards! | | Thanks, | | Rob. . |
| 513 | Thanks very much for your help, now for the next question. can the time also include the date and decimal parts of a second? For example 06-22-02 08:17:54.379 , Chuck -----Original Message----- Hi Chuck You can copy paste special as values the Now() function over the top of itself, or use the simple macro: Push Alt+F11 Go to InsertModule Paste in the code below Push Alt+Q and Save Push Alt+F8 Click "AddTime" then Options assign a shortcut key Sub AddTime() ActiveCell = Time End Sub -- "chuck conroy" <cconroy@jps.net wrote in message news:115e801c219b4$76737140$39ef2ecf@TKMSFTNGXA08... | I would like to have Excel input the time in this format | hh:mm:ss | I have tried two different things. | 1. The now statement =now() this works fine however the | next time I enter it the previous time gets updated to the | new time. I want each cell to display the exact time that | is is entered and then to retain this time. | 2. I have also tried control shift : this does display | a different time in each cell, however it only displays | the hour and minute, the seconds are always 00 no mater | how I format the cell. | Does anyone have any suggestions? | Thanks, | Chuck Conroy . |
| 516 | Try Sub EnterDate_Time() ActiveCell.Value = Now ActiveCell.NumberFormat = "mm-dd-yy hh:mm:ss" End Sub I don't think you can get excel to record less than a second although it is possible to enter in a cell.. -- Regards, Peo Sjoblom "chuck conroy" <cconroy@jps.net wrote in message news:1249c01c219ff$8b3e5c70$35ef2ecf@TKMSFTNGXA11... Thanks very much for your help, now for the next question. can the time also include the date and decimal parts of a second? For example 06-22-02 08:17:54.379 , Chuck -----Original Message----- Hi Chuck You can copy paste special as values the Now() function over the top of itself, or use the simple macro: Push Alt+F11 Go to InsertModule Paste in the code below Push Alt+Q and Save Push Alt+F8 Click "AddTime" then Options assign a shortcut key Sub AddTime() ActiveCell = Time End Sub -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "chuck conroy" <cconroy@jps.net wrote in message news:115e801c219b4$76737140$39ef2ecf@TKMSFTNGXA08... | I would like to have Excel input the time in this format | hh:mm:ss | I have tried two different things. | 1. The now statement =now() this works fine however the | next time I enter it the previous time gets updated to the | new time. I want each cell to display the exact time that | is is entered and then to retain this time. | 2. I have also tried control shift : this does display | a different time in each cell, however it only displays | the hour and minute, the seconds are always 00 no mater | how I format the cell. | Does anyone have any suggestions? | Thanks, | Chuck Conroy . |
| 527 | Here is one approach. This will not re-protect the sheets. You will have to use the code from the previous response for that. Sub Get_Password() On Error GoTo errPassword Dim strPassword As String Dim i As Integer '*** GET THE PASSWORD FROM USER strPassword = InputBox("Enter Password", "Unprotection Password") '*** TRY TO UNPROTECT THE FIRST SHEET Sheets(3).Unprotect Password:=strPassword '*** IF NO ERROR THEN UNPROTECT THE REST OF THE SHEETS For i = 4 To 50 Sheets(i).Unprotect Password:=strPassword Next i Exit Sub errPassword: '*** DISPLAY A MESSAGE IF PASSWORD INCORRECT AND EXIT MsgBox "You entered and incorrect password!" End Sub -----Original Message----- Dave, That's great, thanks. Is there a way I can ask for a password in order to run this macro - if not correct then it exits? Then I could leave this macro in the workbook and just protect the code. Rob. -----Original Message----- Hi Rob This code will toggle sheet protection on/off Sub ToggleProtectContent() Dim i As Integer For i = 3 To 50 If Sheets(i).ProtectContents = False Then Sheets(i).Protect Password:="rob1" Else Sheets(i).Unprotect Password:="rob1" End If Next i End Sub It only checks for Content protection. -- "Rob Pearce" <rob.pearce@consignia.com wrote in message news:f81c01c219bb$7dfb11a0$a4e62ecf@tkmsftngxa06... | I have a workbook with 50 worksheets. | | I protect sheets 3 to 50 [all being the same template for | data entry] with the same password. | | When I want to change some formula/formatting on these 48 | sheets I have to unprotect each sheet individually so that | I can eventually group them together to alter. | | Would it be possible to have some code that will loop from | shhet 3 to 50 and unprotect - the password is rob1 if | anyone could lend a hand - and conversely then to re | protect. | | I would obviously remove the macros from the workbook | afterwards! | | Thanks, | | Rob. . . |
| 548 | Hi, I am trying to convert an Excel list to an Access db. I went to the help in Excel and it said that all I need to do is click on the Data menu - click Convert to MS Access. The problem is I don't find this feature, despite numerous attemps to install Excel and add-ins. I made a very simple list, Type Price Dairy 45 Food 23 and clicked in one of the cells before I looked for that feature, but it still wouldn't give me anything in the data menu. Any suggestion is appreciated. Fran |
| 551 | Have you installed the add-inn? If not you have to download it at Microsoft's download site /downloads/2002/acclnk.aspx -- Regards, Peo Sjoblom "Fran" <fkwlau@hotmail.com wrote in message news:f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07... Hi, I am trying to convert an Excel list to an Access db. I went to the help in Excel and it said that all I need to do is click on the Data menu - click Convert to MS Access. The problem is I don't find this feature, despite numerous attemps to install Excel and add-ins. I made a very simple list, Type Price Dairy 45 Food 23 and clicked in one of the cells before I looked for that feature, but it still wouldn't give me anything in the data menu. Any suggestion is appreciated. Fran |
| 556 | Hi Fran - I think I've done this by opening Access and importing the spreadsheet. Access' wizard will turn the flat spreadsheet into a relational database. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07, Fran said... Hi, I am trying to convert an Excel list to an Access db. I went to the help in Excel and it said that all I need to do is click on the Data menu - click Convert to MS Access. The problem is I don't find this feature, despite numerous attemps to install Excel and add-ins. I made a very simple list, Type Price Dairy 45 Food 23 and clicked in one of the cells before I looked for that feature, but it still wouldn't give me anything in the data menu. Any suggestion is appreciated. Fran |
| 559 | Yes i've installed it from the cd. Is it the same as downloading it from the site? "Peo Sjoblom" <terre08@mvps.org wrote in message news:uP8lSCjGCHA.2668@tkmsftngp13... Have you installed the add-inn? If not you have to download it at Microsoft's download site /downloads/2002/acclnk.aspx -- Regards, Peo Sjoblom "Fran" <fkwlau@hotmail.com wrote in message news:f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07... Hi, I am trying to convert an Excel list to an Access db. I went to the help in Excel and it said that all I need to do is click on the Data menu - click Convert to MS Access. The problem is I don't find this feature, despite numerous attemps to install Excel and add-ins. I made a very simple list, Type Price Dairy 45 Food 23 and clicked in one of the cells before I looked for that feature, but it still wouldn't give me anything in the data menu. Any suggestion is appreciated. Fran |
| 560 | And btw, i am using excel 2000 not 2002. "Peo Sjoblom" <terre08@mvps.org wrote in message news:uP8lSCjGCHA.2668@tkmsftngp13... Have you installed the add-inn? If not you have to download it at Microsoft's download site /downloads/2002/acclnk.aspx -- Regards, Peo Sjoblom "Fran" <fkwlau@hotmail.com wrote in message news:f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07... Hi, I am trying to convert an Excel list to an Access db. I went to the help in Excel and it said that all I need to do is click on the Data menu - click Convert to MS Access. The problem is I don't find this feature, despite numerous attemps to install Excel and add-ins. I made a very simple list, Type Price Dairy 45 Food 23 and clicked in one of the cells before I looked for that feature, but it still wouldn't give me anything in the data menu. Any suggestion is appreciated. Fran |
| 561 | Yes i know. But I want access to append the data and/or remove any duplicates. How would i do that? "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:#ilZ6NjGCHA.2388@tkmsftngp09... Hi Fran - I think I've done this by opening Access and importing the spreadsheet. Access' wizard will turn the flat spreadsheet into a relational database. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07, Fran said... Hi, I am trying to convert an Excel list to an Access db. I went to the help in Excel and it said that all I need to do is click on the Data menu - click Convert to MS Access. The problem is I don't find this feature, despite numerous attemps to install Excel and add-ins. I made a very simple list, Type Price Dairy 45 Food 23 and clicked in one of the cells before I looked for that feature, but it still wouldn't give me anything in the data menu. Any suggestion is appreciated. Fran |
| 573 | Hi Richard I would say the background fill color has been changed to White on these cells. -- "buckjordan" <buckjordan@thegrid.net wrote in message news:yE9R8.18567$Fv1.1255901@newsread2.prod.itd.earthlink.net... | The lines separating cells - and columns and rows - have simply disappeared | in a small area of a long list of stock market data. Specifically there are | no cell lines from columns A through N at rows 155 through 161. What have I | done and how do I undo it? The View menu under Tools/Options has the | gridline box checked. Must be something else. Please help. | -------------------------------------------------------------------------- -- | ---- Richard B. Jordan buckjordan@thegrid.net (Fresno)559-439-9550 | (Cambria)805-927-3897 (Fresno Fax)559-447-0124 | | |
| 574 | Hi John Try using a Named range to refer indirectly to the cells storing the addresses (InsertNameDefine), should work. -- **Possible solution has NOT been tested** FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm "John Vera" <emperorj@hotmail.com wrote in message news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | Hi again. I'm trying to automatize my graphs so they read | from a cell the range in which they are supposed to get | points. Say, I have X data in column B and Y data in | colmun D. My spreadsheet locates the appropriate range and | displays it as text in four cells (upper and lower x | range, upper and lower y range), say H4 says "$B$10" H5 | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the | ranges in the graph to "read" the text strings in H4, H5 | as the range in X and I4,I5 as the range for Y. Help very | much appreciated. Typing indirect in the graph range is | invalid (Excel 2000). | | Thanks, | John |
| 581 | Francis - I'm trying to work this out myself. I'm not an Access guy (not yet, anyway), and they're giving me about 15 minutes a day to work on it. So far I've printed out a bunch of help files to read at home, but instead I'm reading the Excel groups, hehe. What I gather is that Access is lots faster at this than Excel, especially at weeding out duplicates in a large unsorted table. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <MY6R8.104837$831.61951@news01.bloor.is.net.cable.rogers.com, Francis said... Yes i know. But I want access to append the data and/or remove any duplicates. How would i do that? "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:#ilZ6NjGCHA.2388@tkmsftngp09... Hi Fran - I think I've done this by opening Access and importing the spreadsheet. Access' wizard will turn the flat spreadsheet into a relational database. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <f71c01c21a2d$2ca66cb0$a5e62ecf@tkmsftngxa07, Fran said... Hi, I am trying to convert an Excel list to an Access db. I went to the help in Excel and it said that all I need to do is click on the Data menu - click Convert to MS Access. The problem is I don't find this feature, despite numerous attemps to install Excel and add-ins. I made a very simple list, Type Price Dairy 45 Food 23 and clicked in one of the cells before I looked for that feature, but it still wouldn't give me anything in the data menu. Any suggestion is appreciated. Fran |
| 582 | Hi Dave - I just responded to an earlier post by John. The problem with charting from a named range, is that while it seems as though it should work, and while the same named range works in a worksheet, Excel's charting engine chokes on named ranges that use INDIRECT in their RefersTo formula. A few other functions also cause problems. I suggested a slightly different approach using OFFSET, which works fine with charts. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said... Hi John Try using a Named range to refer indirectly to the cells storing the addresses (InsertNameDefine), should work. -- **Possible solution has NOT been tested** FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm "John Vera" <emperorj@hotmail.com wrote in message news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | Hi again. I'm trying to automatize my graphs so they read | from a cell the range in which they are supposed to get | points. Say, I have X data in column B and Y data in | colmun D. My spreadsheet locates the appropriate range and | displays it as text in four cells (upper and lower x | range, upper and lower y range), say H4 says "$B$10" H5 | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the | ranges in the graph to "read" the text strings in H4, H5 | as the range in X and I4,I5 as the range for Y. Help very | much appreciated. Typing indirect in the graph range is | invalid (Excel 2000). | | Thanks, | John |
| 583 | Thanks Jon, didn't know that. I assumed that as a chart can read a dynamic range, it would accept other named ranges that uses the INDIRECT. -- "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:#U$mtjmGCHA.2452@tkmsftngp08... | Hi Dave - | | I just responded to an earlier post by John. The problem with charting from | a named range, is that while it seems as though it should work, and while the | same named range works in a worksheet, Excel's charting engine chokes on | named ranges that use INDIRECT in their RefersTo formula. A few other | functions also cause problems. | | I suggested a slightly different approach using OFFSET, which works fine with | charts. | | - Jon | ------- | Jon Peltier, Microsoft Excel MVP | /jonpeltier/Excel/index.html | _______ | | In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said... | | Hi John | | | Try using a Named range to refer indirectly to the cells storing the | addresses (InsertNameDefine), should work. | | | -- | **Possible solution has NOT been tested** | | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | | "John Vera" <emperorj@hotmail.com wrote in message | news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | | | | Hi again. I'm trying to automatize my graphs so they read | | from a cell the range in which they are supposed to get | | points. Say, I have X data in column B and Y data in | | colmun D. My spreadsheet locates the appropriate range and | | displays it as text in four cells (upper and lower x | | range, upper and lower y range), say H4 says "$B$10" H5 | | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the | | ranges in the graph to "read" the text strings in H4, H5 | | as the range in X and I4,I5 as the range for Y. Help very | | much appreciated. Typing indirect in the graph range is | | invalid (Excel 2000). | | | | Thanks, | | John | | | |
| 585 | Hi Andonny If you are converting numeric text constants to numbers then a Loop is a pretty slow method, as loops are generally slow. Try the code below: Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Range("IV65536").Copy Rng1.PasteSpecial , xlPasteSpecialOperationAdd Application.CutCopyMode = False End Sub Just select the entire Column and run the code. It assumes that cell IV65536 is empty. -- "Andonny" <wje@multiline.com.au wrote in message news:#5hmxioGCHA.2324@tkmsftngp09... | Hi, | I am trying to convert this macro that it works on the column selected | instead of column A:A. | I just like to highlight the column in question and then run the macro. | | Your help is very much appreciated | Andonny | | Sub ConvertTextToNumber() | Dim Cell As Range, Rng1 As Range | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | For Each Cell In Rng1 | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | Cell = CDbl(Cell) | End If | Next Cell | End Sub | | |
| 590 | Hi Dave, Thanks a million for your reply. This is great. Even though the code I mentioned is slow it also does something which I need quite often. It places the minus from data extracted from JDEdwards to the front. Like 22- to -22 which is a good thing in my case. So if I could get the previous code to work with only selected columns would help me a great deal. Then I could use both in various situations. Thanks Andonny <DavidH@OzGrid.com wrote in message news:W5fR8.16$TG5.28441@vicpull1.telstra.net... Hi Andonny If you are converting numeric text constants to numbers then a Loop is a pretty slow method, as loops are generally slow. Try the code below: Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Range("IV65536").Copy Rng1.PasteSpecial , xlPasteSpecialOperationAdd Application.CutCopyMode = False End Sub Just select the entire Column and run the code. It assumes that cell IV65536 is empty. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Andonny" <wje@multiline.com.au wrote in message news:#5hmxioGCHA.2324@tkmsftngp09... | Hi, | I am trying to convert this macro that it works on the column selected | instead of column A:A. | I just like to highlight the column in question and then run the macro. | | Your help is very much appreciated | Andonny | | Sub ConvertTextToNumber() | Dim Cell As Range, Rng1 As Range | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | For Each Cell In Rng1 | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | Cell = CDbl(Cell) | End If | Next Cell | End Sub | | |
| 592 | Hi Andonny I have modified my code to reverse any imported numbers like: 55- I have also modified your original code to work on only the selection. Sub ConvertTextToNumber() Dim Cell As Range, Rng1 As Range Set Rng1 = Intersect(ActiveSheet.UsedRange, Selection) For Each Cell In Rng1 If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then Cell = CDbl(Cell) End If Next Cell End Sub ========================================= Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Rng1.EntireColumn.Insert Rng1.Offset(0, -1).FormulaR1C1 = _ "=IF(RIGHT(RC[1])=""-"",SUBSTITUTE(RC[1],""-"","""")" _ & "*-1,RC[1]*1)" Rng1.Offset(0, -1) = Rng1.Offset(0, -1).Value 'OPTIONAL Rng1.EntireColumn.Delete Application.CutCopyMode = False End Sub I think you will find the second more effcient as it works on the range as a whole and not one cell at a time. -- "Andonny" <wje@multiline.com.au wrote in message news:OquvRTpGCHA.2808@tkmsftngp10... | Hi Dave, | Thanks a million for your reply. This is great. Even though the code I | mentioned is slow it also does something which I need quite often. It places | the minus from data extracted from JDEdwards to the front. Like 22- to -22 | which is a good thing in my case. So if I could get the previous code to | work with only selected columns would help me a great deal. Then I could use | both in various situations. | | Thanks | Andonny | | | <DavidH@OzGrid.com wrote in message | news:W5fR8.16$TG5.28441@vicpull1.telstra.net... | Hi Andonny | | If you are converting numeric text constants to numbers then a Loop is a | pretty slow method, as loops are generally slow. Try the code below: | | Sub ConvertTextToNumberFaster() | Dim rCell As Range, Rng1 As Range | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) | | If Rng1 Is Nothing Then | MsgBox "No constants" | Exit Sub | End If | | Range("IV65536").Copy | Rng1.PasteSpecial , xlPasteSpecialOperationAdd | Application.CutCopyMode = False | End Sub | | | Just select the entire Column and run the code. It assumes that cell | IV65536 | is empty. | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | "Andonny" <wje@multiline.com.au wrote in message | news:#5hmxioGCHA.2324@tkmsftngp09... | | Hi, | | I am trying to convert this macro that it works on the column selected | | instead of column A:A. | | I just like to highlight the column in question and then run the macro. | | | | Your help is very much appreciated | | Andonny | | | | Sub ConvertTextToNumber() | | Dim Cell As Range, Rng1 As Range | | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | | For Each Cell In Rng1 | | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | | Cell = CDbl(Cell) | | End If | | Next Cell | | End Sub | | | | | | | | |
| 597 | Hi Justin you will need to be a bit more specific. Values are all you see from formulas by default. As a guess try using Ctrl+` -- "Justin" <r4al98gy@aol.com wrote in message news:1124601c21a9d$cde21a50$9ae62ecf@tkmsftngxa02... | How can I do this? Thanks in advance. |
| 598 | Hi Anne Type: What's new in Microsoft Excel? into the help -- "Anne Lambert" <AnneTLambert@hotmail.com wrote in message news:ODTBn7pGCHA.2448@tkmsftngp10... | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's | changed over the years. | | Anne | | | "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... | You must install the help files Anne. | If you install Office custom the vba helpwill not be installed I believe.?? | | So run your setup again and install it | | Regards Ron | | "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht | news:uvAYyupGCHA.1772@tkmsftngp09... | Are the Excel 97 Help files available on the Microsoft site? I can find | the 2000 Help text, but in spite of a lot of searching, I | can't find the Excel 97 help. I particularly need the VBA help | information. | | If they're available, I'd be grateful for a URL. | | Thanks. | | Anne | | | | | | | | |
| 600 | Hi Dave Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find out if the text of the XL 97 help files is also available somewhere. Anne "" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net... Hi Anne Type: What's new in Microsoft Excel? into the help -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Anne Lambert" <AnneTLambert@hotmail.com wrote in message news:ODTBn7pGCHA.2448@tkmsftngp10... | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's | changed over the years. | | Anne | | | "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... | You must install the help files Anne. | If you install Office custom the vba helpwill not be installed I believe.?? | | So run your setup again and install it | | Regards Ron | | "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht | news:uvAYyupGCHA.1772@tkmsftngp09... | Are the Excel 97 Help files available on the Microsoft site? I can find | the 2000 Help text, but in spite of a lot of searching, I | can't find the Excel 97 help. I particularly need the VBA help | information. | | If they're available, I'd be grateful for a URL. | | Thanks. | | Anne | | | | | | | | |
| 604 | AFAIK, the Excel 97 Help files aren't online, but the VBA Programmer's Guide may help: /library/default.asp?url=/library/en-us/office97/html/web/fulltoc.asp Anne Lambert wrote: Hi Dave Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find out if the text of the XL 97 help files is also available somewhere. Anne "" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net... Hi Anne Type: What's new in Microsoft Excel? into the help -- "Anne Lambert" <AnneTLambert@hotmail.com wrote in message news:ODTBn7pGCHA.2448@tkmsftngp10... | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's | changed over the years. | | Anne | | | "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... | You must install the help files Anne. | If you install Office custom the vba helpwill not be installed I believe.?? | | So run your setup again and install it | | Regards Ron | | "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht | news:uvAYyupGCHA.1772@tkmsftngp09... | Are the Excel 97 Help files available on the Microsoft site? I can find | the 2000 Help text, but in spite of a lot of searching, I | can't find the Excel 97 help. I particularly need the VBA help | information. | | If they're available, I'd be grateful for a URL. | | Thanks. | | Anne | | | | | | | | -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 605 | Hi Debra Yes, indeed. Thanks for this. Anne "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D15B24B.2040004@contextures.com... AFAIK, the Excel 97 Help files aren't online, but the VBA Programmer's Guide may help: /library/default.asp?url=/library/en-us/office97/html/web/fulltoc.asp Anne Lambert wrote: Hi Dave Thanks for this. But this won't distinguish (as one example of several) the changes that have been made to parameters to the Worksheet.Protect command since 97. I can access the details from the Excel 2000 help files on the MS site. I'm trying to find out if the text of the XL 97 help files is also available somewhere. Anne "" <DavidH@OzGrid.com wrote in message news:pEhR8.18$kP5.5272@vicpull1.telstra.net... Hi Anne Type: What's new in Microsoft Excel? into the help -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Anne Lambert" <AnneTLambert@hotmail.com wrote in message news:ODTBn7pGCHA.2448@tkmsftngp10... | Thanks for this. But I don't want to *use* XL 97: I just want to read the help files. I use XL 2002, and I can't remember all that's | changed over the years. | | Anne | | | "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:O07bGzpGCHA.2992@tkmsftngp13... | You must install the help files Anne. | If you install Office custom the vba helpwill not be installed I believe.?? | | So run your setup again and install it | | Regards Ron | | "Anne Lambert" <AnneTLambert@hotmail.com schreef in bericht | news:uvAYyupGCHA.1772@tkmsftngp09... | Are the Excel 97 Help files available on the Microsoft site? I can find | the 2000 Help text, but in spite of a lot of searching, I | can't find the Excel 97 help. I particularly need the VBA help | information. | | If they're available, I'd be grateful for a URL. | | Thanks. | | Anne | | | | | | | | -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 612 | Just to add to this thread: SpecialCells requires error handling in case the range does not contain constants, e.g.: On Error Resume Next Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) On Error GoTo 0 ' Rest of code... "" <DavidH@OzGrid.com wrote in message news:W5fR8.16$TG5.28441@vicpull1.telstra.net... Hi Andonny If you are converting numeric text constants to numbers then a Loop is a pretty slow method, as loops are generally slow. Try the code below: Sub ConvertTextToNumberFaster() Dim rCell As Range, Rng1 As Range Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) If Rng1 Is Nothing Then MsgBox "No constants" Exit Sub End If Range("IV65536").Copy Rng1.PasteSpecial , xlPasteSpecialOperationAdd Application.CutCopyMode = False End Sub Just select the entire Column and run the code. It assumes that cell IV65536 is empty. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Andonny" <wje@multiline.com.au wrote in message news:#5hmxioGCHA.2324@tkmsftngp09... | Hi, | I am trying to convert this macro that it works on the column selected | instead of column A:A. | I just like to highlight the column in question and then run the macro. | | Your help is very much appreciated | Andonny | | Sub ConvertTextToNumber() | Dim Cell As Range, Rng1 As Range | Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A")) | For Each Cell In Rng1 | If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then | Cell = CDbl(Cell) | End If | Next Cell | End Sub | | |
| 637 | Jan's method works with foreground colors. Dave's works with background colors. Both are great, and I can make use of this myself. The major problem with both methods is that when you change a color, the sum or the count does not change. For example, if I change a green color to a red, neither the green totals nor the red totals changes. Can either of you offer a solution to this? Many thanks, Paul "" <DavidH@OzGrid.com wrote in message news:<jiDR8.19$926.7591@vicpull1.telstra.net... Hi Debbie I have a UDF that does this here: /VBA/Sum.htm It looks remarkably similar to Jans. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:1186301c21b6d$0194cf30$9be62ecf@tkmsftngxa03... | Hi, | | I assumed the text has the color, not the interior. | Try this user defined function: | | Option Explicit | | Function CountColour(rRange As Range, iColor As Integer) | Dim rCell As Range | Dim lTotal As Long | Application.Volatile | For Each rCell In rRange | If rCell.Font.ColorIndex = iColor Then | lTotal = lTotal + 1 | End If | Next | CountColour = lTotal | End Function | | Now to count red cells in the range A1:A5, use this | function: | | =CountColour(A1:A5,3) | | How to enter a macro (=VBA code): | | 1. Open the Excel file you want to add the code to. | | 2. Choose Tools, Macro, Visual Basic Editor to open the | Visual Basic Editor (or press Alt+F11). This is the | design environment that stores the VBA code. If this | is | the first time you have opened the editor, you will | probably see three windows: the Project window, the | Properties window, and the Code window. | | 3. In the Project window, select the name of the workbook | you want to add the sub to (one project is listed for | each open file). | | 4. After selecting the project icon, choose Insert, | Module. | This inserts a VBA code module into the project and | places the insertion point in the Code window of that | module. | | 5. You can either type the snippet code exactly as | written, | or you can copy the code directly from this message | and | paste it into the Code window. | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | I am wanting to count dates in a range, but by colour. | Ie, all red dates to give a total number, all blue dates | to give a total number. | | Can anyone help?? | | Debbie. | . | |
| 671 | No problem..we all have those days :) "" <DavidH@OzGrid.com wrote in message news:28yR8.29$FU5.40386@vicpull1.telstra.net... Sorry Tim. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Tim Zych" <tzych@earthlink.net wrote in message news:OteXv7zGCHA.1932@tkmsftngp10... | "" <DavidH@OzGrid.com wrote in message | news:bKxR8.27$FU5.37950@vicpull1.telstra.net... | You took | the time to point out possible errors but offered no alternative. | | Yikes...are we both reading the same thread? | | Here's a copy of my first response. (Note the 'On Error Resume Next' I | inserted). | | | | "Tim Zych" <tzych@earthlink.net wrote in message | | news:#QxLrqtGCHA.1156@tkmsftngp13... | | | Just to add to this thread: | | | | | | SpecialCells requires error handling in case the range does not | contain | | | constants, e.g.: | | | | | | On Error Resume Next | | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) | | | On Error GoTo 0 | | | ' Rest of code... | | | Contructive critisism is good, outright negativity is bad, don't you agree | ? | | Um, yeah. | | | "" <DavidH@OzGrid.com wrote in message | news:bKxR8.27$FU5.37950@vicpull1.telstra.net... | Bugger :o) Who removed the On Error Resume Next. | | It would have been more helpful to the OP if you guys demonstrated the use | of an error handler rather than just saying it doesn't have one. You took | the time to point out possible errors but offered no alternative. | Contructive critisism is good, outright negativity is bad, don't you agree | ? | | | | Sub ConvertTextToNumberFaster() | Dim rCell As Range, Rng1 As Range | | On error Resume next | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) | | If Rng1 Is Nothing Then | MsgBox "No constants" | On error GoTo 0 | Exit Sub | End If | | On error GoTo 0 | Rng1.EntireColumn.Insert | Rng1.Offset(0, -1).FormulaR1C1 = _ | "=IF(RIGHT(RC[1])=""-"",SUBSTITUTE(RC[1],""-"","""")" _ | & "*-1,RC[1]*1)" | Rng1.Offset(0, -1) = Rng1.Offset(0, -1).Value | 'OPTIONAL | Rng1.EntireColumn.Delete | Application.CutCopyMode = False | End Sub | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | | "Tim Zych" <tzych@earthlink.net wrote in message | news:#duQFnzGCHA.2448@tkmsftngp10... | | Hmmm, Excel returns an error (1004, no cells were found) for me if there | are | | no "special cells". I'm using XL2000 and I know the same occurs with 97. | | | | | | "" <DavidH@OzGrid.com wrote in message | | news:qRwR8.19$FU5.33944@vicpull1.telstra.net... | | I agree Tim, but my code already has this covered:o) | | | | | | | | -- | | | | | | | | | | FREE EXCEL NEWSLETTER | | /News/2home.htm | | Get the OzGrid Add-in | | /Services/AddinExamples.htm | | If it's Excel, then it's us! | | | | "Tim Zych" <tzych@earthlink.net wrote in message | | news:#QxLrqtGCHA.1156@tkmsftngp13... | | | Just to add to this thread: | | | | | | SpecialCells requires error handling in case the range does not | contain | | | constants, e.g.: | | | | | | On Error Resume Next | | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) | | | On Error GoTo 0 | | | ' Rest of code... | | | | | | | | | "" <DavidH@OzGrid.com wrote in message | | | news:W5fR8.16$TG5.28441@vicpull1.telstra.net... | | | Hi Andonny | | | | | | If you are converting numeric text constants to numbers then a | Loop | | is | | a | | | pretty slow method, as loops are generally slow. Try the code | below: | | | | | | Sub ConvertTextToNumberFaster() | | | Dim rCell As Range, Rng1 As Range | | | | | | Set Rng1 = Selection.SpecialCells(xlCellTypeConstants) | | | | | | If Rng1 Is Nothing Then | | | MsgBox "No constants" | | | Exit Sub | | | End If | | | | | | Range("IV65536").Copy | | | Rng1.PasteSpecial , xlPasteSpecialOperationAdd | | | Application.CutCopyMode = False | | | End Sub | | | | | | | | | Just select the entire Column and run the code. It assumes that | cell | | | IV65536 | | | is empty. | | | | | | | | | -- | | | | | | | | | | | | | | | FREE EXCEL NEWSLETTER | | | /News/2home.htm | | | Get the OzGrid Add-in | | | /Services/AddinExamples.htm | | | If it's Excel, then it's us! | | | | | | "Andonny" <wje@multiline.com.au wrote in message | | | news:#5hmxioGCHA.2324@tkmsftngp09... | | | | Hi, | | | | I am trying to convert this macro that it works on the column | | selected | | | | instead of column A:A. | | | | I just like to highlight the column in question and then run the | | macro. | | | | | | | | Your help is very much appreciated | | | | Andonny | | | | | | | | Sub ConvertTextToNumber() | | | | Dim Cell As Range, Rng1 As Range | | | | Set Rng1 = Intersect(ActiveSheet.UsedRange, | | ActiveSheet.Range("A:A")) | | | | For Each Cell In Rng1 | | | | If Not IsEmpty(Cell) And Not Cell.HasFormula And | IsNumeric(Cell) | | Then | | | | Cell = CDbl(Cell) | | | | End If | | | | Next Cell | | | | End Sub | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 681 | I have beat my head against that wall so many times. The ranges work fine in the worksheet, and either don't work at all in the chart, or only show one point, or other weird things happen. I stick to the OFFSETs now, because I know they will work. What can be done, of course, is define the named range with INDIRECT, then array enter it into a worksheet range, and then refer to this range in the chart, either directly through the address, or through another dynamic range, if the number of cells changes. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <J9cR8.7$TG5.21637@vicpull1.telstra.net, said... Thanks Jon, didn't know that. I assumed that as a chart can read a dynamic range, it would accept other named ranges that uses the INDIRECT. -- "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:#U$mtjmGCHA.2452@tkmsftngp08... | Hi Dave - | | I just responded to an earlier post by John. The problem with charting from | a named range, is that while it seems as though it should work, and while the | same named range works in a worksheet, Excel's charting engine chokes on | named ranges that use INDIRECT in their RefersTo formula. A few other | functions also cause problems. | | I suggested a slightly different approach using OFFSET, which works fine with | charts. | | - Jon | ------- | Jon Peltier, Microsoft Excel MVP | /jonpeltier/Excel/index.html | _______ | | In article <3O9R8.3$TG5.14797@vicpull1.telstra.net, said... | | Hi John | | | Try using a Named range to refer indirectly to the cells storing the | addresses (InsertNameDefine), should work. | | | -- | **Possible solution has NOT been tested** | | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | | "John Vera" <emperorj@hotmail.com wrote in message | news:1130001c21a30$e59a6b10$9be62ecf@tkmsftngxa03... | | | | | | Hi again. I'm trying to automatize my graphs so they read | | from a cell the range in which they are supposed to get | | points. Say, I have X data in column B and Y data in | | colmun D. My spreadsheet locates the appropriate range and | | displays it as text in four cells (upper and lower x | | range, upper and lower y range), say H4 says "$B$10" H5 | | says "$B$15", I4 has "$D$10" and I5 "$D$15" . I want the | | ranges in the graph to "read" the text strings in H4, H5 | | as the range in X and I4,I5 as the range for Y. Help very | | much appreciated. Typing indirect in the graph range is | | invalid (Excel 2000). | | | | Thanks, | | John | | | |
| 694 | Hi Mr NDP Not sure what you really need but is you want "Plot" to become the range A1, A2 etc you must use the Set statement (same for most Objects). Once you have the variable Plot set to a range you can then easily get the cells value, address or any other Property from it. Just type: Plot. and you will get a list of all Properties and Methods. Run the example below to see what I mean. Sub test() Dim Plot As Range Dim CN As Integer For CN = 0 To 7 Set Plot = Range("A2").Offset(0, CN) MsgBox (Plot & Chr(13) & Contents) Next CN End Sub -- "Mr NDP" <mr_ndp@yahoo.com wrote in message news:12f1001c21c07$ae3dcdf0$35ef2ecf@TKMSFTNGXA11... | All, | | In the following example, I want the variable "Plot" to | equal the Location (A1, A2, A3, etc.). Instead, I | get "True". I'm still learning. Any suggestions? | | Sub test() | For CN = 0 To 7 | Contents = Range("A2").Offset(0, CN).Value | Plot = Range("A2").Offset(0, CN).Select | MsgBox (Plot & Chr(13) & Contents) | Next CN | End Sub | |
| 695 | Hi Mr NDP Not sure whether you set your Workbook to manual calculation on purpose or not, but if you do be very careful that you do not feed yourself erroneous results. It's all too easy to forget to force a calculation when not in Automatic. If you have a Workbook that needs to run in manual calculation it normally means you have a design problem. See the links below for spreadsheet designs: /News/CalculationRangeIndirect.htm /News/ConFormatCheckFind.htm /News/GoodVsBadDesignSpeedUpEvents.htm -- "Mr NDP" <mr_ndp@yahoo.com wrote in message news:1245801c21bfe$a8004bb0$3bef2ecf@TKMSFTNGXA10... | That was it. I had my calculations set to manual. | | Thanks for the help... | | | -----Original Message----- | Hi: | | Do you have the calculation mode set to manual? If so, | hitting F9 should | update the formulas. | -- | Regards, | | Vasant. | | **No direct emails please--keep discussion in | newsgroup.** | | | "Mr NDP" <mr_ndp@yahoo.com wrote in message | news:12ed401c21bfb$928d7300$35ef2ecf@TKMSFTNGXA11... | All, | | I've noticed in my spreadsheets where I use the countif | function that the cells will not always update. Saving | the document seems to refresh the sheet and the | calculations are emplemented. Is there a way to | refresh | the formulas in a worksheet without having to save it? | | Thanks, | | Mr NDP | | | . | |
| 697 | Hi Dave, Not sure if I agree on the design problem Dave. I have a not-too-large workbook, but since it contains lots of array formula's it's recalc is very time consuming. When I need to do some data entry in that workbook I am glad I set calc to manual... Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Hi Mr NDP Not sure whether you set your Workbook to manual calculation on purpose or not, but if you do be very careful that you do not feed yourself erroneous results. It's all too easy to forget to force a calculation when not in Automatic. If you have a Workbook that needs to run in manual calculation it normally means you have a design problem. See the links below for spreadsheet designs: /News/CalculationRangeIndirect.htm /News/ConFormatCheckFind.htm /News/GoodVsBadDesignSpeedUpEvents.ht m -- "Mr NDP" <mr_ndp@yahoo.com wrote in message news:1245801c21bfe$a8004bb0$3bef2ecf@TKMSFTNGXA10... | That was it. I had my calculations set to manual. | | Thanks for the help... | | | -----Original Message----- | Hi: | | Do you have the calculation mode set to manual? If so, | hitting F9 should | update the formulas. | -- | Regards, | | Vasant. | | **No direct emails please--keep discussion in | newsgroup.** | | | "Mr NDP" <mr_ndp@yahoo.com wrote in message | news:12ed401c21bfb$928d7300$35ef2ecf@TKMSFTNGXA11... | All, | | I've noticed in my spreadsheets where I use the countif | function that the cells will not always update. Saving | the document seems to refresh the sheet and the | calculations are emplemented. Is there a way to | refresh | the formulas in a worksheet without having to save it? | | Thanks, | | Mr NDP | | | . | . |
| 699 | Hi Jan If you read the links you will see that I say array formulae are the main cause of sloooowww calculations. Arrays (IMO) are a quick fix superficial approach to a possible complex problem. The use of Excels database functions can often be used in place of arrays as can Pivot Tables etc I still hold firm that any spreadsheet that requires manual calculation has a spreadsheet design problem. You run the risk of reading uncalculated results. It's a bit like you car brakes are rubbing (and slowing you down) so you disconnect them and rely on the handbrake. -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:ff2101c21c0e$9c93ffa0$9ee62ecf@tkmsftngxa05... | Hi Dave, | | Not sure if I agree on the design problem Dave. | I have a not-too-large workbook, but since it contains | lots of array formula's it's recalc is very time | consuming. When I need to do some data entry in that | workbook I am glad I set calc to manual... | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | Hi Mr NDP | | Not sure whether you set your Workbook to manual | calculation on purpose or | not, but if you do be very careful that you do not feed | yourself erroneous | results. It's all too easy to forget to force a | calculation when not in | Automatic. If you have a Workbook that needs to run in | manual calculation it | normally means you have a design problem. See the links | below for | spreadsheet designs: | | /News/CalculationRangeIndirect.htm | /News/ConFormatCheckFind.htm | /News/GoodVsBadDesignSpeedUpEvents.ht | m | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | "Mr NDP" <mr_ndp@yahoo.com wrote in message | news:1245801c21bfe$a8004bb0$3bef2ecf@TKMSFTNGXA10... | | That was it. I had my calculations set to manual. | | | | Thanks for the help... | | | | | | -----Original Message----- | | Hi: | | | | Do you have the calculation mode set to manual? If so, | | hitting F9 should | | update the formulas. | | -- | | Regards, | | | | Vasant. | | | | **No direct emails please--keep discussion in | | newsgroup.** | | | | | | "Mr NDP" <mr_ndp@yahoo.com wrote in message | | news:12ed401c21bfb$928d7300$35ef2ecf@TKMSFTNGXA11... | | All, | | | | I've noticed in my spreadsheets where I use the | countif | | function that the cells will not always update. | Saving | | the document seems to refresh the sheet and the | | calculations are emplemented. Is there a way to | | refresh | | the formulas in a worksheet without having to save | it? | | | | Thanks, | | | | Mr NDP | | | | | | . | | | | | . | |
| 703 | Hi drwinterton I am sure someone will come along with a possible answer for you. I just wanted to see if your area aware of the EditLinksChange source option available in Linked Workbooks. -- "drwinterton" <daniel_winterton@hk.ml.com wrote in message news:1223601c21c1a$285a9e30$3aef2ecf@TKMSFTNGXA09... | I have a problem that appears to be fairly widespread. I | haven't, however, found a solution to my exact problem on | this site... | | Here's the trouble. I have a system of files that I need | to be able to transfer over to a different drive. | | I've read that as long as everything is in the same | directory and the sub-directory structure and names of the | sub-directories are left intact that Excel will pretend | the references are relative (aka, I can move the files and | they will work). Alas, it doesn't appear to be working | for me. | | Here's the magnitude of data that I'm worried about: 26 | directories, 25 of which are designed to collect data from | different users (the whole reason this all isn't just one | file anymore), and 1 directory to compile this information | (and also serve as a source of a single piece of | information for every file in the other directories). In | each of the 25 directories, I will have 9 or 36 | (undecided) files that I need to reference. | | The collection folder contains two files - fetcher (which | gets the data for me) and sesdata (which the input files | need to reference). The collection folder is | named "master", the other inputs are all numbered. All | these directories are in the directory "qpa." | | I try to move the directory containing both master and the | input folders (qpa), and all my links which, if relative, | would read "../master/sesdata" break. I would like to be | able to design this system on my pc, record it to a CD, | then copy it onto different computers. Due to the number | of links, the edit links option simply won't work. I am | willing to move the directory structure (to some extent), | try VBA (throw code at me, I don't do Basic), or whatever. | Or if I need to try to use Quattro Pro, tell me. | | |
| 709 | Hi Jason I am sure Tom will be along soon to help you, but in the mean time you might find these links of use: /VBA/VBACode.htm /VBA/SpeedingUpVBACode.htm /VBA/VBALoops.htm Hope they help -- "ims" <kft_icq@myrealbox.com wrote in message news:#jjYydCHCHA.368@tkmsftngp13... | Dear Tom | | Hope you remember me whom you helped to write the macro below. | This really helpful to my job, however when I apply it to an excel worksheet | of 15 columns, 40 thousand records I find it has to work for several hours, | which is impractical. | I don't really know which part of the marco waste most of the time. | So I write here and want to know if the marco can be trimmed so as to make | it run more faster. | Your help is very much appreciated! | | Best regards | | Jason | | | | ----- Original Message ----- | From: "Tom Ogilvy" <twogilvy@msn.com | Newsgroups: microsoft.public.excel.misc | Sent: Monday, May 13, 2002 5:46 AM | Subject: Re: How to handle worksheet and save it as xls.file? | | | That revision was based on an earlier, slightly different version of the | original - it had hard coded paths for the file operations. This has the | sPath variable (like the original) to make it easier to specify the | location | for the new files: | | Sub StartSplitout() | Dim sh As Worksheet | Dim sh1 As Worksheet | Dim varr As Variant | Dim rng As Range | Set rng = Range(Cells(1, 1), Cells(1, "IV").End(xlToLeft)) | varr = rng.Value | Cells(1, 1).EntireRow.Delete | ActiveSheet.Copy | Set sh = ActiveSheet | Splitout sh, varr | ThisWorkbook.Activate | Cells(1, 1).EntireRow.Insert | Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | End Sub | | Sub Splitout(sh As Worksheet, varr As Variant) | Dim bContinue As Boolean | Dim rng As Range, Cell As Range | Dim rw As Long, sh1 As Worksheet | Dim sPath As String | sPath = "C:\Data\" | bContinue = False | Set rng = sh.Range(sh.Cells(1, 1), _ | sh.Cells(1, 1).End(xlDown)) | For Each Cell In rng | If Cell.Row < 1 Then | If Cell.Value < _ | Cell.Offset(-1, 0).Value Then | bContinue = True | rw = Cell.Row | Exit For | End If | End If | Next | If bContinue Then | sh.Copy | Set sh1 = ActiveSheet | sh1.Range(sh1.Cells(1, 1), sh1.Cells(rw - 1, 1)). _ | EntireRow.Delete | sh.Range(sh.Cells(rw, 1), _ | sh.Cells(Rows.Count, 1).End(xlUp)) _ | .EntireRow.Delete | sh.Cells(1, 1).EntireRow.Insert | sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | On Error Resume Next | Kill sPath & sh.Cells(2, 1).Value & _ | ".xls" | On Error GoTo 0 | sh.Parent.SaveAs sPath & _ | sh.Cells(2, 1).Value & ".xls" | sh.Parent.Close SaveChanges:=False | Splitout sh1, varr | Else | On Error Resume Next | Kill sPath & sh.Cells(2, 1).Value & _ | ".xls" | On Error GoTo 0 | sh.Cells(1, 1).EntireRow.Insert | sh.Cells(1, 1).Resize(, UBound(varr, 2)).Value = varr | sh.Parent.SaveAs sPath & _ | sh.Cells(2, 1).Value & ".xls" | sh.Parent.Close SaveChanges:=False | End If | End Sub | | Regards, | Tom Ogilvy | | | | |
| 715 | Hi John Try formatting the cells (before pasting) as Text. -- "John Ricketts" <master@westnet.com.au wrote in message news:erNSZ6CHCHA.2436@tkmsftngp11... | When copying and pasting ( say a table from FrontPage to Excel ) fractions | like 6/11 get translated into dates. I have a large number of data tables | to copy and paste and going through editing each cell with a fraction is not | an option. | | How does one prevent Excel from making this conversion? | | | | |
| 717 | Don't think INDIRECT works for closed Workbooks <bg -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:1005401c21c35$215b2120$9ee62ecf@tkmsftngxa05... | Hi, | | Some questions if I may. | | - How did you copy your files? | - Did you copy the complete tree with the files? | - what is a correct link and what does it look like after | copying you files (and opening the master copy) | | I guess simplest should be to simply copy the entire tree | to another drive. But if the links travel with that also | depends on their exact type. If they are direct cell to | cell links, AFAIK they should adjust to the new location. | | But if any part of them are hardcoded (like in the | INDIRECT function), well... But I guess you already knew | that <g. | | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | I have a problem that appears to be fairly widespread. I | haven't, however, found a solution to my exact problem on | this site... | | Here's the trouble. I have a system of files that I need | to be able to transfer over to a different drive. | | I've read that as long as everything is in the same | directory and the sub-directory structure and names of | the | sub-directories are left intact that Excel will pretend | the references are relative (aka, I can move the files | and | they will work). Alas, it doesn't appear to be working | for me. | | Here's the magnitude of data that I'm worried about: 26 | directories, 25 of which are designed to collect data | from | different users (the whole reason this all isn't just one | file anymore), and 1 directory to compile this | information | (and also serve as a source of a single piece of | information for every file in the other directories). In | each of the 25 directories, I will have 9 or 36 | (undecided) files that I need to reference. | | The collection folder contains two files - fetcher (which | gets the data for me) and sesdata (which the input files | need to reference). The collection folder is | named "master", the other inputs are all numbered. All | these directories are in the directory "qpa." | | I try to move the directory containing both master and | the | input folders (qpa), and all my links which, if relative, | would read "../master/sesdata" break. I would like to be | able to design this system on my pc, record it to a CD, | then copy it onto different computers. Due to the number | of links, the edit links option simply won't work. I am | willing to move the directory structure (to some extent), | try VBA (throw code at me, I don't do Basic), or | whatever. | Or if I need to try to use Quattro Pro, tell me. | | | . | |
| 718 | Thanks for the suggestion but I had already tried that. I had also tried formatting as Fraction but that didn't help the cause either. "" <DavidH@OzGrid.com wrote in message news:gTXR8.7$1h6.12510@vicpull1.telstra.net... Hi John Try formatting the cells (before pasting) as Text. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "John Ricketts" <master@westnet.com.au wrote in message news:erNSZ6CHCHA.2436@tkmsftngp11... | When copying and pasting ( say a table from FrontPage to Excel ) fractions | like 6/11 get translated into dates. I have a large number of data tables | to copy and paste and going through editing each cell with a fraction is not | an option. | | How does one prevent Excel from making this conversion? | | | | |
| 720 | You should get the Paste special option when you right click? Maybe one of these options will help, a normal paste would undo the Text format. -- "John Ricketts" <master@westnet.com.au wrote in message news:uE0TjlDHCHA.704@tkmsftngp08... | Thanks for the suggestion but I had already tried that. | I had also tried formatting as Fraction but that didn't help the cause | either. | | | "" <DavidH@OzGrid.com wrote in message | news:gTXR8.7$1h6.12510@vicpull1.telstra.net... | Hi John | | Try formatting the cells (before pasting) as Text. | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | "John Ricketts" <master@westnet.com.au wrote in message | news:erNSZ6CHCHA.2436@tkmsftngp11... | | When copying and pasting ( say a table from FrontPage to Excel ) | fractions | | like 6/11 get translated into dates. I have a large number of data | tables | | to copy and paste and going through editing each cell with a fraction is | not | | an option. | | | | How does one prevent Excel from making this conversion? | | | | | | | | | | | | |
| 721 | Hi, Itried creating an add-in tohave some Macros and functions of my own available on any workbook, but Ican't seem to be able access the macros and funs on the add-in I create after I install it with the add-in manager. I can access the code with the VB Editor, but can't find neither macros' name on the "run macro" menu nor functions' name in the insert "function menu" Guess there's something wrong with the code itself, but I can't figure out what exactly. Please mail me an answer directly, since I got problem accessing newsgroups. In case you do, please remove "NOSPAM" from address. Thanks a lot |
| 724 | Hi, I am able to pass parameters to a VBA macro from the 'Assign Macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke Macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. |
| 731 | Jan, I agree to disagree. If your methods work for you then stick with them. -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:11fa701c21c4e$f10c30d0$b1e62ecf@tkmsftngxa04... | Hi Dave, | | | | If you read the links you will see that I say array | formulae are the main | cause of sloooowww calculations. Arrays (IMO) are a quick | fix superficial | approach to a possible complex problem. | | I totally disagree on the last remark. Yes, they tend to | be slow. But they are often an elegant solution to an | indeed complex problem. They are far from superficial and | seldomly a quick fix. | | The use of Excels database functions | can often be used in place of arrays as can Pivot Tables | etc | | That may go for extracting information from simple tables. | As soon as one has to combine data from different subsets, | XL's database functions just aren't good enough. Sometimes | an array formula can then come to rescue. | | | I still hold firm that any spreadsheet that requires | manual calculation has | a spreadsheet design problem. You run the risk of reading | uncalculated | results. | | Disagreed again. I just happen to have a lot of (very | complex) data and calc sometimes. But there is always the | risk of uncalculated data, indeed. It is a matter of | knowing ones business. These workbooks are not for the | masses, they are for me. Printouts are for my colleagues, | which are always automatically calculated. | | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | |
| 735 | A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . |
| 738 | Hi Laura: Sub DeleteHiddenRows() Dim c As Range For Each c In Rows(1).Cells If c.Width = 0 Then c.EntireColumn.Delete Next End Sub -- Regards, Vasant. **No direct emails please--keep discussion in newsgroup.** "Laura Wilde" <lwilde@new.rr.com wrote in message news:1278901c21cb4$bc02b420$3aef2ecf@TKMSFTNGXA09... A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . |
| 739 | Hi Laura Push Alt+F11, then go InsertModule and paste in the code below: Sub DeleteHiddenCols() Dim rCell As Range 'Runs on the active sheet Application.Calculation = xlCalculationManual For Each rCell In Rows(1) If rCell.EntireColumn.Hidden = True Then rCell.EntireColumn.Delete End If Next rCell Application.Calculation = xlCalculationAutomatic End Sub Psuh Alt+Q, then Alt+F8 click "DeleteHiddenCols" then Options and asign a shortcut key. -- Hope this helps. "Laura Wilde" <lwilde@new.rr.com wrote in message news:1278901c21cb4$bc02b420$3aef2ecf@TKMSFTNGXA09... | A solution using VBA is fine, I just have a lot to learn | in that area (writing them myself, I mean). | | The only hitch with your answer is that there are usually | multiple (anywhere from 1 to 20) hidden columns | throughout each worksheet (possible example: A, B, C, F, | G, J, T, S, and AB are hidden). That would mean that I | would have to check manually which columns are hidden and | then type them each into the name box to delete them. I | am looking for something a little more functional. | | Thanks! | | Laura | | | -----Original Message----- | Hi Laura | | | I assume you mean without VBA, if so assume Column "C" | is hidden, type C:C | in the name box (left of the formula bar) and push | enter. Now go to | EditDelete. There is no doubt a shortcut key for | deleting Columns, you | should find it here: | /Excel/ExcelKeyBoardShortcutKeys.htm | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | "Laura Wilde" <lwilde@new.rr.com wrote in message | news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | | Is it possible to delete hidden columns without | unhiding | | them? I get many workbooks sent to me with hidden | | columns that contain data I do not need. Some are | | formulas but I usually "copy" and "paste values" so | there | | is no worry of linking errors. | | | | Thanks for any help you can give me. | | | | Laura | | | . | |
| 741 | As long as your careful with your formulas/values: Option Explicit Sub delhiddencols() Dim iCol As Long Dim deleteme As Range For iCol = 1 To 256 If Columns(iCol).Hidden = True Then If deleteme Is Nothing Then Set deleteme = Cells(1, iCol) Else Set deleteme = Union(Cells(1, iCol), deleteme) End If End If Next iCol If deleteme Is Nothing Then 'can't do anything Else deleteme.EntireColumn.Delete End If End Sub You may want to add some checks for protection/filters, etc. Things that will stop you from deleting columns. Maybe even a little check to ask "are you sure"? Laura Wilde wrote: A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.htm -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . -- Dave Peterson ec35720@msn.com |
| 743 | Thanks everyone...all work perfectly! -----Original Message----- A solution using VBA is fine, I just have a lot to learn in that area (writing them myself, I mean). The only hitch with your answer is that there are usually multiple (anywhere from 1 to 20) hidden columns throughout each worksheet (possible example: A, B, C, F, G, J, T, S, and AB are hidden). That would mean that I would have to check manually which columns are hidden and then type them each into the name box to delete them. I am looking for something a little more functional. Thanks! Laura -----Original Message----- Hi Laura I assume you mean without VBA, if so assume Column "C" is hidden, type C:C in the name box (left of the formula bar) and push enter. Now go to EditDelete. There is no doubt a shortcut key for deleting Columns, you should find it here: /Excel/ExcelKeyBoardShortcutKeys.ht m -- "Laura Wilde" <lwilde@new.rr.com wrote in message news:105fc01c21caa$e493ba60$a5e62ecf@tkmsftngxa07... | Is it possible to delete hidden columns without unhiding | them? I get many workbooks sent to me with hidden | columns that contain data I do not need. Some are | formulas but I usually "copy" and "paste values" so there | is no worry of linking errors. | | Thanks for any help you can give me. | | Laura . . |
| 747 | Mel, What version of Excel are you using? In Excel 2002, you would go to Tools - Macro - Security and then on the Trusted Sources tab, check the box: Trust all installed add-ins and templates. Paul "Mel" <shakey@sonic.net wrote in message news:3D18F17A.5AC5C7CD@sonic.net... I do not know what I did but lately I must go thru three screens of marco warnings to open a blank or any excel sheet. First--- C:\whatever\library\sumif.xla then-----C:\whatever\analysis\analys32.xll then-----c:\whatever\start\personal.xls I can understand peresonal and then my requested sheet as they are medium security but what made the first two start requesting permission, they are of MS origion and should always be safe. Mel S. -- Nothing will ever be attempted if all possible objections must first be overcome. |
| 752 | Paul Watson wrote: Mel, What version of Excel are you using? In Excel 2002, you would go to Tools - Macro - Security and then on the Trusted Sources tab, check the box: Trust all installed add-ins and templates. Paul "Mel" <shakey@sonic.net wrote in message news:3D18F17A.5AC5C7CD@sonic.net... I do not know what I did but lately I must go thru three screens of marco warnings to open a blank or any excel sheet. First--- C:\whatever\library\sumif.xla then-----C:\whatever\analysis\analys32.xll then-----c:\whatever\start\personal.xls I can understand peresonal and then my requested sheet as they are medium security but what made the first two start requesting permission, they are of MS origion and should always be safe. Mel S. -- Nothing will ever be attempted if all possible objections must first be overcome. Thanks Paul, That works in 2000 version as well. Darned if I know how it got unchecked. Mel -- Nothing will ever be attempted if all possible objections must first be overcome. |
| 756 | Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign Macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign Macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke Macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com |
| 759 | Hi Bruce Try something like Columns(1).entireColumn.Hidden= (WorksheetFunction.Counta(Columns(1)=0) Columns(2).entireColumn.Hidden= (WorksheetFunction.Counta(Columns(2)=0) -- "Bruce" <bruce@nha.co.za wrote in message news:108e501c21ce9$b7e82fc0$36ef2ecf@tkmsftngxa12... | Hi | | I have a macxro which prints a certain section of a | spreadsheet. I have two comlumns which are optional - if | these hav not been used then I would like to prevent them | from being printed. If they have values then they can be | printed - is there a way to do this? | | any help would be much appreciated. |
| 764 | Hi Wim Look into DataConsolidation. -- "Wim Vanmaele" <vanmaelew@hotmail.com wrote in message news:9a87c3e7.0206260115.1484365@posting.google.com... | Hi, | | I made a XLS-woorkbook in which data are used obtaining statistical | results and charts. All data used, could be found on several | XLS-sheets which have a uniform form. Is there someone who can tell | me, how I can insert one of thes xls-sheets dynamicaly. So, when | calculating, I can enter somewhere the wanted xls-file-name and all | data are so automatically retrieved. | | Thanks, | | Wim Vanmaele |
| 767 | Hi Dolores DataFilterAuto filter Custom Does not contain: + OK F5 Special Visible cells Copy Paste You could also use the Advanced Filter with: <*+ as the criteria below the copied heading -- "Dolores" <dolores_s24@yahoo.com wrote in message news:1259e01c21cf4$0023a850$9be62ecf@tkmsftngxa03... | I have a column containing cells with only text. Like this; | | HHT | AFG | PJK | AFG+ | BSSR | PJK+ | YTTX | | I would like to make a filtered copy of this column where | I don't have the cells containing the "+". | | How do I do this? | |
| 775 | Hi Jeffrey Just to add to what Charles has said, any Function which *requires* a range as it's argument(s) cannot be used in a Calculated field of a Pivot Table. That's about 90% of Excels functions :o( -- "Charles Williams" <Charles@DecisionModels.com wrote in message news:3d199c6b$0$27345$afc38c87@news.easynet.co.uk... | Hi Jeffrey, | | Excel will not let you use DSUM as a function in a Pivot Table calculated | field, you would have to create extra columns. | | Charles Williams | ______________________ | Decision Models | The Excel Calculation Site | www.DecisionModels.com | | "Jeffrey S. Pidel" <Jeffrey.Pidel@comcast.net wrote in message | news:ORF#KsKHCHA.1604@tkmsftngp12... | I am trying to create a Pivot tables with calculated fields | that compare | YTD and average per month per account | of previous month | versus | YTD and actual per month per account | of current month | I am thinking of using dsum and daverage for the | calculated fields. Do you think this will work | | Table layout | Previous Month Current Month | Accounts YTD Average YTD Actual | 1000 | 2000 | 3000 | 4000 | . | . | | | | | | | | |
| 778 | Hi Ireland If only text use: =INDEX(B1:B100,MATCH("*",B1:B100,-1),1) If Numeric use: =INDEX(B1:B100,MATCH(-9E+306,B1:B100,-1),1) Both will look past blank cells. You might also find this link of great use: /Excel/DynamicRanges.htm -- "IrelandA" <IrelandA@Syntegra.Bt.Co.Uk wrote in message news:afc854$r0f$1@pheidippides.axion.bt.co.uk... | Dear All | | Is there a formula which would show me the last popuated cell contents in a | column: | e.g. | | Column B | Row 1 J Smith | Row 2 J Bloggs | Row 3 J Doe | Row 4 <blank | Row 5 <blank | | Formula would return "J Doe". | Later, if I entered "P Jones" in cell B4, the formula would automatically | update with the new details. | | Thanks for any advice | | Al | | |
| 794 | In article <s2DR8.14$926.5678@vicpull1.telstra.net, DavidH@OzGrid.com says... Hello Dave, When this goes to different filenames - I do not have the option of replacing filenames. Then it is like - say - total of A7 of sheet JT101 of 100 different files named serially (though the formulas would turn out to be lot more complex than this). Or is there any other way that I can pick up data from different sheets of different files dynamically and cummulate that into a results a new file / sheet? Thanx for the help. -- Jasvinder Taneja Hi Jasvinder RE: That would be very cumbersome What is so cumbersome about pushing Ctrl+H an typing 2 sheet names? RE: they would not even reside in the same file Then the INDIRECT function will be of no use. The ADDRESS and INDIRECT function are both Volatile functions, so if you are too liberal with their use you will be heading in recalculation problems. -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! If yu "Jasvinder Taneja" <jassitCORRECT@THIShotmail.com wrote in message news:MPG.17814d5763ebe694989681@msnews.microsoft.com... | In article <u4BR8.7$926.1707@vicpull1.telstra.net, DavidH@OzGrid.com | says... | | Hi Dave | | That would be very cumbersome and moreover the sheets would go on | increasing. In the long run, they would not even reside in the same file. | So I would also need to somehow build the file names into the formula | (the filenames could also be standardized also). | | | -- | | Jasvinder Taneja | | | Better ignore that I misread the question :o) | | Just copy them into the other Columns, then use EditReplace to convert the | sheet names. |
| 805 | I posted this through google, but it seemed to crash. So I thought I'd use CDO: I _think_ I understand: I dropped a button from the forms toolbar onto a worksheet. I have 3 examples of the .onaction stuff. Comment out two of them. Option Explicit Sub testme() Dim cb As Button Set cb = ActiveSheet.Buttons(1) Dim c As Range Set c = Range("A1") c.Value = "rangeA1value" Dim i As Long i = 8 With cb .OnAction = ThisWorkbook.Name _ & "!'testme1 " & Chr(34) & c.Value & Chr(34) & "'" .OnAction = ThisWorkbook.Name _ & "!'testme2 ""Hello"", 3'" .OnAction = Workbooks("book2.xls").Name _ & "!'testme3 " & Chr(34) _ & "different wkbk" & Chr(34) & "'" End With End Sub Sub testme1(myval As Variant) MsgBox "From Testme1: " & myval End Sub Sub testme2(mystr As String, myval As Long) MsgBox "From Testme2: " & mystr & " " & myval * 10 End Sub and from Book2.xls Option Explicit Sub testme3(mystr As String) MsgBox "From " & ThisWorkbook.Name & ": " & mystr End Sub I've never used the CDO before. Watch out for linewrap problems. -----Original Message----- Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign Macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign Macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke Macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com . |
| 835 | Hi Gianfranco, (posted with email copy) Add-ins, Private Subs, and User Defined Functions to not appear in the macro list. Was that the only problem, or are they also not working. Email copy sent, if you wish to continue this question please keep conversation in this thread within the newsgroup. You are connected directly to the newsgroups using Microsoft CDO there have been recent problems but they are fixed on Microsoft's side. I would suggest that Outlook Express is a better vehicle for reading newsgroups than anything web based. microsoft.public.excel no longer exists on microsoft servers, the correct newsgroup is microsoft.public.excel.misc HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Gianfranco Pellacani" <gianfranco_pellacani@NOSPAMcomergroup.com wrote in message news:ec6901c21c3b$41462740$95e62ecf@tkmsftngxs02... Hi, Itried creating an add-in tohave some Macros and functions of my own available on any workbook, but Ican't seem to be able access the macros and funs on the add-in I create after I install it with the add-in manager. I can access the code with the VB Editor, but can't find neither macros' name on the "run macro" menu nor functions' name in the insert "function menu" Guess there's something wrong with the code itself, but I can't figure out what exactly. Please mail me an answer directly, since I got problem accessing newsgroups. In case you do, please remove "NOSPAM" from address. Thanks a lot |
| 836 | Hi Dave, I think I may have made it unclear what I am trying achieve. I would like any old Excel developer to call an existing macro in an excel add-in I have written, passing parameters to it without the need to create a Macro themselves. The way I perceived it would work is by the developer dragging a button from the 'Forms' toolbar (which I wrongly called a control) and then right-button mouse clicking on the button and selecting the 'Assign Macro...' dialog, and from there just typing in something like: 'MyAddin.xla'!'MyMacro "param1","param2"' without worrying about any VBA code. Sorry I confused things with the example I gave.. Thanks again, Steve "Dave Peterson" <ec35720@msn.com wrote in message news:106f001c21d1d$b90eb0c0$9ee62ecf@tkmsftngxa05... I posted this through google, but it seemed to crash. So I thought I'd use CDO: I _think_ I understand: I dropped a button from the forms toolbar onto a worksheet. I have 3 examples of the .onaction stuff. Comment out two of them. Option Explicit Sub testme() Dim cb As Button Set cb = ActiveSheet.Buttons(1) Dim c As Range Set c = Range("A1") c.Value = "rangeA1value" Dim i As Long i = 8 With cb .OnAction = ThisWorkbook.Name _ & "!'testme1 " & Chr(34) & c.Value & Chr(34) & "'" .OnAction = ThisWorkbook.Name _ & "!'testme2 ""Hello"", 3'" .OnAction = Workbooks("book2.xls").Name _ & "!'testme3 " & Chr(34) _ & "different wkbk" & Chr(34) & "'" End With End Sub Sub testme1(myval As Variant) MsgBox "From Testme1: " & myval End Sub Sub testme2(mystr As String, myval As Long) MsgBox "From Testme2: " & mystr & " " & myval * 10 End Sub and from Book2.xls Option Explicit Sub testme3(mystr As String) MsgBox "From " & ThisWorkbook.Name & ": " & mystr End Sub I've never used the CDO before. Watch out for linewrap problems. -----Original Message----- Thanks Dave, However my problem is not calling it from within a VBA macro but I want an Excel developer who may know nothing about VBA to pass parameters to a VBA macro contained in a loaded Add-in from a button he has added to the spreadsheet. The syntax I used ('MyAddin.xla'!'Foo "param1"') from within the Assign Macro dialog of the excel button control seemed to be ignoring the 'MyAddin.xla' prefix and assuming it was in the workbook containing the button (Book1.xls). Steve What I was hoping for was "Dave Peterson" <ec35720@msn.com wrote in message news:3D18E8DF.4D4DEB54@msn.com... When I tried to pass a string to your Msg sub, I could do it like this: Application.Run "'book1.xla'!msg", "hi there!" I could also do it like this, if I did a Tools|references and set a reference to that other project. msg "hi there2" But I'm not sure if this was the answer to your question.... Steve Legg wrote: Hi, I am able to pass parameters to a VBA macro from the 'Assign Macro' dialog by enclosing in single quotes. e.g. Book1!'Msg "Hello"' to execute the macro in the open workbook 'Book1': Public Sub Msg(m As String) MsgBox m End Sub However I am unable to do the same for a macro in an open Add-in. If I try 'MyAddin.xla'!'Foo "param1"' I am getting an error saying The macro 'Book1'!'Foo "param1"' cannot be found. I can invoke Macros with no parameters in the Add-in. Anybody know why this is not working, and if there is a way to make it work? Thanks in advance. -- Dave Peterson ec35720@msn.com . |
| 842 | Thank you Steven for the information... They also mentioned Lookup......but I can't seem to be able to add that function in. It's an add-in. Thanks.... JR "Steven McDonald" <a@a.com wrote in message news:12a8c01c21d27$b9729720$3aef2ecf@TKMSFTNGXA09... Hi, You don't really need a macro for this. The VLOOKUP commmand should do the job nicely. Just search for vlookup in excel help for all the info you need. It would be easier if your sheet containing part numbers and descriptions was a different sheet in the same workbook, rather than in another workbook. HTH, Steve -----Original Message----- Hello, I have a form template (write-up rev 031902) and need help with the following: In my form I have B14......B29 ...in these cells I put in a part number... and would like the description for that part number to be added into C14....C29 For the description and part number I have created another workbook named "partdesc"......were column A..has the part numbers and they start from Cell 3 down and column B has the description for those part numbers. How can I write a macro for this... Thanks -- JR . |
| 864 | You can quickly get a list of duplicate names listed along with their counts by using the Excel add-in "Refinate". It is free to try. To count duplicates: ------------------------------------- Turn off Refinate's GoTo_Duplicates option. Select the list of names and click Get/Test. -----Done counting duplicates-------- Every duplicate will be counted and the name along with it's count will be in a comment box. You can paste the contents of the comment box into cells to do more work on those results. Use =proper(B1) to set the case if necessary. Refinate works on comma delimited entries so, if your addresses have commas, then first use ExcelReplace to change commas to another character (e.g. " ` ") until after the duplicate test and then Replace it back with a comma. That will count duplicates - not singles. You can get counts of singles, too. One way would be to copy the column of interest into another column and select both columns at once and click Refinate's Get/Test. Now there will be a duplicate of everything -- the count is doubled. To divide by 2, copy the contents of the comment box into cells and apply Refinate's TextToColumns to put the counts into a separate column. Then copy a cell containing the value 2 to the clipboard. Select the totals and EditPasteSpecialDivide. Now you have the count for singles and duplicates. If you would like to be brought to each duplicate and have a tag placed at each one, turn on Refinate's GoTo_Duplicates. If you edit the duplicate, the changes will be noted in the comment tag as a log. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "larry" <lwhersey@aol.com wrote in message news:10a8701c21dab$b1b9eb80$9ee62ecf@tkmsftngxa05... if I am entering a list of names or addresses in a column How can I count the number of times the same name or address appears. Example Column A smith jones AL Smith frank todd smith=2 |
| 971 | Yep. the way it's written. If you wanted the cell formatting to revert to none when you clearcontents, you could do something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing _ And Date = DateSerial(2002, 6, 1) Then If Not IsEmpty(Target) Then Target.Interior.ColorIndex = 36 Else Target.Interior.ColorIndex = xlNone End If End If End Sub You could still change the cell background via the toolbar icon or by Format|cells if you want to have something in the cell and non-red fill. Garry Sabino wrote: Ooopss.... it seems that I cant undo any changes I made on the cells were the event is applied. Is it meant to work that way? Garry "Garry Sabino" <oas@brunet.bn wrote in message news:uYuYdraHCHA.1604@tkmsftngp09... Super duper. Thanks a lot Dave, you're a Hero. Garry "" <DavidH@OzGrid.com wrote in message news:lJyS8.38$eA6.34565@vicpull1.telstra.net... Hi Gary This one will apply to A1:A10 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Me.Range("A1:A10")) _ Is Nothing And Date = DateSerial(2002, 6, 1) Then Target.Interior.ColorIndex = 36 End If End Sub If the cells are non-contiguous then use something like: If Not Intersect(Target, Me.Range("A1:A10,C5:D20")) -- FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm If it's Excel, then it's us! "Garry Sabino" <oas@brunet.bn wrote in message news:OZPKxcaHCHA.1604@tkmsftngp09... | That's super but it only applies to cell A1. How can I apply the same on a | range of cells. | | Thanks a lot, | | Garry | | | "" <DavidH@OzGrid.com wrote in message | news:lvxS8.27$eA6.30865@vicpull1.telstra.net... | Hi Gary | | Right click on the sheet name tab, select "View Code" and paste in the | code | below: | | Private Sub Worksheet_Change(ByVal Target As Range) | If Target.Cells.Count 1 Then Exit Sub | | If Target.Address = "$A$1" And _ | Date = DateSerial(2002, 6, 1) Then | Target.Interior.ColorIndex = 36 | End If | End Sub | | Just change A1 to suit your needs. I have a UDF here: | /VBA/ReturnCellColor.htm that you can use to easily | get | the ColorIndex Property of a cells fill color. | | | Hope this helps | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | Get the OzGrid Add-in | /Services/AddinExamples.htm | If it's Excel, then it's us! | | | | "Garry Sabino" <oas@brunet.bn wrote in message | news:OAkolkZHCHA.2200@tkmsftngp08... | | Hi, | | | | How do I change the color of a cell value if it is entered or modified | after | | an specific date. | | | | Say I'd like all cells value that are modified or entered after June 1, | 2002 | | to become red in color. | | | | Any suggestions would be highly appreciated. | | | | Garry. | | | | | | | | -- Dave Peterson ec35720@msn.com |
| 1012 | In the userform's code module, assuming that your text box is named textBox1, add the following code: Private Sub TextBox1_Change() 'call the validate routine and pass to it the textbox object Validate_Number_Entry Me.TextBox1 End Sub In a regular module, put the following: Sub Validate_Number_Entry(oBox) Dim tempS As String 'store the text in a variable for later use tempS = oBox.Text 'if user has removed all entries, just exit If tempS = "" Then Exit Sub 'if the entry is numeric, then exit If IsNumeric(tempS) Then Exit Sub 'if the entry is not numeric, remove the last entry oBox.Text = Mid(tempS, 1, Len(tempS) - 1) End Sub Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "phil" <pperry@acsoft.co.uk wrote in message news:1166b01c21ebd$49fc29f0$36ef2ecf@tkmsftngxa12... Hi, how can i allow only numbers to be entered into a textbox on a user form? thanks phil |
| 1015 | excellent, thanks phil -----Original Message----- In the userform's code module, assuming that your text box is named textBox1, add the following code: Private Sub TextBox1_Change() 'call the validate routine and pass to it the textbox object Validate_Number_Entry Me.TextBox1 End Sub In a regular module, put the following: Sub Validate_Number_Entry(oBox) Dim tempS As String 'store the text in a variable for later use tempS = oBox.Text 'if user has removed all entries, just exit If tempS = "" Then Exit Sub 'if the entry is numeric, then exit If IsNumeric(tempS) Then Exit Sub 'if the entry is not numeric, remove the last entry oBox.Text = Mid(tempS, 1, Len(tempS) - 1) End Sub Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "phil" <pperry@acsoft.co.uk wrote in message news:1166b01c21ebd$49fc29f0$36ef2ecf@tkmsftngxa12... Hi, how can i allow only numbers to be entered into a textbox on a user form? thanks phil . |
| 1094 | Hi Thanks for the replies!!! Well the spreadsheets are from my companies supplier: and are a list of products. From time to time the product prices change, new products appear and some obviously get discontinued. There are over 1500 different products on this speadsheet! The row consists of fields stating "product category", "product code", "description", "price ex VAT", "price Inc VAT", The product code is unique. When I am sent an updated spreadsheet, I want to be able to compare this with the previous, and easily see what products have changed, and what products are new! Hopefully you have the answer! I am looking at the Compare add-in stated by Harald. Each time a new spreadsheet is recieved, the total number of products is likely to be different! Many thanks again in advance Matt "Tom Ogilvy" <twogilvy@msn.com wrote in message news:uDxyeQ3HCHA.1712@tkmsftngp08... That should be doable. Since you don't define what makes a row the same or different or how to compare between sheets or how your data is laid out, it would be difficult to provide any additional information. Regards, Tom Ogilvy Matt <matt@slippednospampleasedisk.fsnet.co.uk wrote in message news:afjoak$fag$1@news6.svr.pol.co.uk... Hi All I have a workbook that contains similar worksheets of data. Is there an easy way of taking two worksheets, original and new and running a function that will compare the two, possibly giving the results in a new worksheet? I would like to have one result which will display new rows in the new sheet that do not appear in the original, and also another result that will show rows where data has changed. There must be an easier way than simply visually comparing each row one by one! I hope this explains what I want to do with these sheets. Any help is much appreciated. Many Thanks in Advance! Matt |
| 1095 | There's a shareware addin called OAK from operis which will do just this. You can get it here /oak.htm The full version is quite expensive though, so you might want to see if theres an addin which can align your sheets (ie see where to add new rows), and then use the compare addin. HTH Ian G "Matt" <matt@slippednospampleasedisk.fsnet.co.uk wrote in message news:afkfkr$a5j$1@news8.svr.pol.co.uk... Hi Thanks for the replies!!! Well the spreadsheets are from my companies supplier: and are a list of products. From time to time the product prices change, new products appear and some obviously get discontinued. There are over 1500 different products on this speadsheet! The row consists of fields stating "product category", "product code", "description", "price ex VAT", "price Inc VAT", The product code is unique. When I am sent an updated spreadsheet, I want to be able to compare this with the previous, and easily see what products have changed, and what products are new! Hopefully you have the answer! I am looking at the Compare add-in stated by Harald. Each time a new spreadsheet is recieved, the total number of products is likely to be different! Many thanks again in advance Matt "Tom Ogilvy" <twogilvy@msn.com wrote in message news:uDxyeQ3HCHA.1712@tkmsftngp08... That should be doable. Since you don't define what makes a row the same or different or how to compare between sheets or how your data is laid out, it would be difficult to provide any additional information. Regards, Tom Ogilvy Matt <matt@slippednospampleasedisk.fsnet.co.uk wrote in message news:afjoak$fag$1@news6.svr.pol.co.uk... Hi All I have a workbook that contains similar worksheets of data. Is there an easy way of taking two worksheets, original and new and running a function that will compare the two, possibly giving the results in a new worksheet? I would like to have one result which will display new rows in the new sheet that do not appear in the original, and also another result that will show rows where data has changed. There must be an easier way than simply visually comparing each row one by one! I hope this explains what I want to do with these sheets. Any help is much appreciated. Many Thanks in Advance! Matt |
| 1176 | Hi Sandy I am glad you bought this up as I wrote the code about a year ago and it was for a user wanting non-repeating random numbers between 1 and 10. I hade forgotten about this :o) I will revisit this and see if I can loosen it up somewhat, starting with Jims suggestion. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Sandy Mann" <sandymann@tiscali.co.uk wrote in message news:uwNIHS7HCHA.2012@tkmsftngp13... | Dave, | | I am not trying to be a smart-ass here - I just trying to learn good | programming techniques through these NG's - and I know that your function is | just a bit of fun but it seems, to me at least, to have a fatal flaw when | the number of random numbers approaches the total of numbers available. | | For example with numbers 1 to 20, when you ask for 16 random numbers, (i.e. | *Amount*)' Excel seem to freeze. I assume that the reason is that it ends | up going through an endless loop. I think what is happening is that when 12 | is selected then when either 1 or 2 is selected, the InStr function will | find then in the number 12 ans so will reject them. (i.e. Running the | function numerous times I noticed that if 12 is selected early then neither | 1 nor 2 will be but if 1 and 2 are selected then 12 can still appear in the | list.) It must be that the odds are that the numbers 16 through 20 have | already been selected and so when 1, 2, 6, 7, 8 or 9 are selected, the InStr | function will find that number and so will continue the loop without end | because it still requires another number. At least when I added a counting | variable to see how many times the loop had executed I got a message box at | 20,000,000 (I didn't have the patients to wait any longer.) | | Of course I could be wrong about the above but I would value your comments | to further my knowledge of Excel & VBA | | | | Sandy | | "" <DavidH@OzGrid.com wrote in message | news:imUS8.40$0O6.53272@vicpull1.telstra.net... | Hi Wayne | | I have a fun little Function that you could use: Click the link below for | full easy instructions if are unsure of how to use in the Worksheet. | /VBA/RandomNumbers.htm | | Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer) | Dim iNum As String | Dim strNum As String | Dim i As Integer | | Application.Volatile | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | | For i = 1 To Amount | strNum = Trim(strNum & " " & iNum) | Do Until InStr(1, strNum, iNum) = 0 | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | Loop | Next i | | RandLotto = strNum | | End Function | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | | | | "Wayne Robinson" <waynerobinson@bigpond.com wrote in message | news:M9US8.22710$Hj3.70951@newsfeeds.bigpond.com... | | I am trying to use the rand function to generate several random numbers. | | That's fine. But how do I make sure that no two numbers are ever the | same??? | | | | Any help would be greatly appreciated! | | | | | | | | |
| 1177 | Hi Jim You are right, the original Function was written to work on numbers between 1 and 10 and I hade forgotten that. I have updated this code on my site with a thank you to you for modifying it: /VBA/RandomNumbers.htm -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "J.E. McGimpsey" <jemcgimpsey@mvps.org wrote in message news:300620021308012025%jemcgimpsey@mvps.org... | Dave's routine is vulnerable to lock-up any time Amount is = the | number of single digits in the range Bottom to Top and Top-Bottom is | =10. Of course, it's unusual for any lotto to have more than 9 picks, | so this would happen rarely in Lotto picking. However the routine below | generalizes this a bit and returns up to Bottom-Top + 1 (i.e., all) | numbers: | | Public Function Rands(Bottom As Integer, Top As Integer, _ | Amount As Integer) As String | Dim iArr As Variant | Dim i As Integer | Dim r As Integer | Dim temp As Integer | | Application.Volatile | | ReDim iArr(Bottom To Top) | For i = Bottom To Top | iArr(i) = i | Next i | For i = Top To Bottom + 1 Step -1 | r = Int(Rnd() * (i - Bottom + 1)) + Bottom | temp = iArr(r) | iArr(r) = iArr(i) | iArr(i) = temp | Next i | For i = Bottom To Bottom + Amount - 1 | Rands = Rands & " " & iArr(i) | Next i | Rands = Trim(Rands) | End Function | | | | In article <uwNIHS7HCHA.2012@tkmsftngp13, Sandy Mann | <sandymann@tiscali.co.uk wrote: | | Dave, | | I am not trying to be a smart-ass here - I just trying to learn good | programming techniques through these NG's - and I know that your function is | just a bit of fun but it seems, to me at least, to have a fatal flaw when | the number of random numbers approaches the total of numbers available. | | For example with numbers 1 to 20, when you ask for 16 random numbers, (i.e. | *Amount*)' Excel seem to freeze. I assume that the reason is that it ends | up going through an endless loop. I think what is happening is that when 12 | is selected then when either 1 or 2 is selected, the InStr function will | find then in the number 12 ans so will reject them. (i.e. Running the | function numerous times I noticed that if 12 is selected early then neither | 1 nor 2 will be but if 1 and 2 are selected then 12 can still appear in the | list.) It must be that the odds are that the numbers 16 through 20 have | already been selected and so when 1, 2, 6, 7, 8 or 9 are selected, the InStr | function will find that number and so will continue the loop without end | because it still requires another number. At least when I added a counting | variable to see how many times the loop had executed I got a message box at | 20,000,000 (I didn't have the patients to wait any longer.) | | Of course I could be wrong about the above but I would value your comments | to further my knowledge of Excel & VBA | | | | Sandy | | "" <DavidH@OzGrid.com wrote in message | news:imUS8.40$0O6.53272@vicpull1.telstra.net... | Hi Wayne | | I have a fun little Function that you could use: Click the link below for | full easy instructions if are unsure of how to use in the Worksheet. | /VBA/RandomNumbers.htm | | Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer) | Dim iNum As String | Dim strNum As String | Dim i As Integer | | Application.Volatile | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | | For i = 1 To Amount | strNum = Trim(strNum & " " & iNum) | Do Until InStr(1, strNum, iNum) = 0 | iNum = Int((Top - Bottom + 1) * Rnd + Bottom) | Loop | Next i | | RandLotto = strNum | | End Function | | | | -- | | | | | FREE EXCEL NEWSLETTER | /News/2home.htm | | | | "Wayne Robinson" <waynerobinson@bigpond.com wrote in message | news:M9US8.22710$Hj3.70951@newsfeeds.bigpond.com... | | I am trying to use the rand function to generate several random numbers. | | That's fine. But how do I make sure that no two numbers are ever the | same??? | | | | Any help would be greatly appreciated! | | | | | | | | |
| 1183 | Hi Andie Just a note about the URL /OzGrid.html that is a very old one, the new address is -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Barbara wiseman" <b@nbpwiseman.fsnet.co.uk wrote in message news:OhVbDBFICHA.2312@tkmsftngp12... | Andie, | If you do a search on 'excel tutorial' you will find some ideas. | / | | Or here is a list I have gathered. | /Pages/Excel/homepage.html | /view/cobrand/beginners/i/19 | /excel/pivots.htm | (on pivot tables) | /dmcritchie/excel/excel.htm | (go to the 'Excel Lessons & Tutorials' section) | http://205.236.230.101/xl/ | | and some on VBA programming | /trio/tut/excel/index.html | 6.net/ | /toc.htm | /OzGrid.html | /support/excel/content/vba101/default.asp | /support/excel/content/Automation/automation.asp | | | Andie <andiecorbin@hotmail.com wrote in message | news:139ea01c2204b$f9fba5d0$b1e62ecf@tkmsftngxa04... | After doing an unsuccessful search, do you know where I | can get an Excel tutorial? | | Regards, | AC | | |
| 1184 | Hi Try using the start-up switch: Excel /regserver Via StartRun when Excel is closed. Note the space after Excel. Just a note though, the Controls from the ControlToolbox do not have an "Assign Macro" option like the Forms do. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Tigerhawk71" <tigerhawk71@hotmail.com wrote in message news:11a5801c220ac$61055b80$9ee62ecf@tkmsftngxa05... | Uhhh.... i have a slight problem with the command buttons | in excel.. | | they don't work, or to be precise, they do work, but they | ignore the edit mode button, on or off. and adding a new | button via the control toolbox bar adds a "commandbutton" | of the same type as in on an excel form, wwithout the | assign macro functions etc. Translation: The darn things | broke. | | help!!! |
| 1190 | Hi William Try: Worksheets(7).Select ActiveCell.Copy Destination:= _ Worksheets(2).Range(ActiveCell.Address) You cannot specify ActiveCell after referencing a Worksheet. OR better yet, use the sheets CodeName (name not in brackets in the Project Explorer). So the code might look something like: Sheet7.Select ActiveCell.Copy Destination:= _ Sheet2.Range(ActiveCell.Address) The CodeName of a Sheet is not altered by changing its position or renaming it. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "William" <wapfu@xtra.co.nz wrote in message news:11d8001c220b3$49b8d540$36ef2ecf@tkmsftngxa12... | Hi, | Easy Peasy question. | Using the Books "Excel 2002 Bible" and "Excel 2002 VBA" | I wrote the following code. | Worksheets(7).ActiveCell.Offset(0, 1).Copy Destination = | Worksheets(2).ActiveCell.Offset(c, 0) | | Which when dedugged is ok but when runs states "Object | doesn't support this property or method" | C= "some vertical number" | | What would the correct syntax be to copy a value from one | cell in a workbook sheet to another cell in a workbook | sheet. | | William |
| 1201 | Hi William I haven't read all through your code but a few things do catch my eye, it appears you could replace a lot of your code with the snippet below Select Case Sheet8.Cells(2, 3) ' Gets Case 1 To 3 C = Sheet8.Cells(2, 3) + 10 Case 4 To 12 C = Sheet8.Cells(2, 3) - 2 Case Else 'Whatver End Select Sheet8.Select ActiveCell.Offset(0, 1).Copy _ Destination:=Sheet2.Range(ActiveCell.Offset(C, 0).Address) Not too sure why you are using the ActiveCell, but consider setting a range variable at the start like below: Set rActCell = ActiveCell Then use the Object variable in place of ActiveCell. Just be aware the ActiveCell Always refers to the active cell on the Active sheet. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "William" <wapfu@xtra.co.nz wrote in message news:13f9801c220d3$bf0b1cc0$19ef2ecf@tkmsftngxa01... | Hi, | Dedugging a routine. Thanks to all that have got me this | far. | The idea is to match two tables, One horizontal to one | vertical. When there is a match(there will be more than | one where nonidentical pairs match in both tables) the | value, one cell across, from the active cell in the | vertical table, Sheet8, is copied to a cell in the table | (horizontal) sheet2 where it is offeset vertically from | the matching pair value in that table. The routine runs | to all pairs that can be matched are anf values have been | copied across. I get "Method or data member not found for | SelectActiveCell in the following. The full code follows. | Thanking anyone that can give my brain help. | Regards William | | Sub test() | 'table in worksheet(7) is vertical | 'table in worksheet(2) is horizontal | | Dim rng1 As Range, rng2 As Range | Dim cell As Range | Dim res As Variant | Set rng1 = Sheet2.Range("D3:AD3") | Set rng2 = Sheet8.Range("A0:A16") | For Each cell In rng1 | res = "" | res = Application.Match(cell.Value, | rng2, 0) | If Not IsError(res) Then | ' the match, do something | Select Case Sheet8.Cells(2, 3) ' Gets | the month value to set the offset "c" | 'verti | cally from the active cell in | 'works | heet(2). | Case Is = 1 | C = 11 | Case Is = 2 | C = 12 | Case Is = 3 | C = 13 | Case Is = 4 | C = 2 | Case Is = 5 | C = 3 | Case Is = 6 | C = 4 | Case Is = 7 | C = 5 | Case Is = 8 | C = 6 | Case Is = 9 | C = 7 | Case Is = 10 | C = 8 | Case Is = 11 | C = 9 | Case Is = 12 | C = 10 | End Select | Sheet8.SelectActiveCell.Offset(0, 1).Copy | Destination:=Sheet2.Range(ActiveCell.Offset(C, 0)) | Rem MsgBox cell.Value & " equals " & rng2 | (res).Value | Exit Sub | Else | ' the don't match | End If | ' continue the search | Next | | | | | | | End Sub | |
| 1203 | Dave, Again I thankyou, and am indebted to your valued=20 assistance. Read about the active cell in "Excel 2002 Bible" and=20 thought if the match had been made then the refernce=20 would be stable for the want of a better word. That is,=20 both sheets would have a cell that the routine knew=20 about, and shifting the value would be clean, and then=20 the routine would step again to a new match. William -----Original Message----- Hi William I haven't read all through your code but a few things do=20 catch my eye, it appears you could replace a lot of your code with the=20 snippet below Select Case Sheet8.Cells(2, 3) ' Gets Case 1 To 3 C =3D Sheet8.Cells(2, 3) + 10 Case 4 To 12 C =3D Sheet8.Cells(2, 3) - 2 Case Else 'Whatver End Select Sheet8.Select ActiveCell.Offset(0, 1).Copy _ Destination:=3DSheet2.Range(ActiveCell.Offset(C,=20 0).Address) Not too sure why you are using the ActiveCell, but=20 consider setting a range variable at the start like below: Set rActCell =3D ActiveCell Then use the Object variable in place of ActiveCell.=20 Just be aware the ActiveCell Always refers to the active cell on the=20 Active sheet. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "William" <wapfu@xtra.co.nz wrote in message news:13f9801c220d3$bf0b1cc0$19ef2ecf@tkmsftngxa01... | Hi, | Dedugging a routine. Thanks to all that have got me=20 this | far. | The idea is to match two tables, One horizontal to one | vertical. When there is a match(there will be more than | one where nonidentical pairs match in both tables) the | value, one cell across, from the active cell in the | vertical table, Sheet8, is copied to a cell in the=20 table | (horizontal) sheet2 where it is offeset vertically from | the matching pair value in that table. The routine runs | to all pairs that can be matched are anf values have=20 been | copied across. I get "Method or data member not found=20 for | SelectActiveCell in the following. The full code=20 follows. | Thanking anyone that can give my brain help. | Regards William | | Sub test() | 'table in worksheet(7) is vertical | 'table in worksheet(2) is horizontal | | Dim rng1 As Range, rng2 As Range | Dim cell As Range | Dim res As Variant | Set rng1 =3D Sheet2.Range("D3:AD3") | Set rng2 =3D Sheet8.Range("A0:A16") | For Each cell In rng1 | res =3D "" | res =3D Application.Match(cell.Value, | rng2, 0) | If Not IsError(res) Then | ' the match, do something | Select Case Sheet8.Cells(2, 3) ' Gets | the month value to set the offset "c" | 've rti | cally from the active cell in | 'wo rks | heet(2). | Case Is =3D 1 | C =3D 11 | Case Is =3D 2 | C =3D 12 | Case Is =3D 3 | =CD{=9Dw=C0=1F=BC=03\v=DCqo=A5=BEq=CCC-=08 =EC?=04 = C =3D 13 | Case Is =3D 4 | C =3D 2 | Case Is =3D 5 | C =3D 3 | Case Is =3D 6 | C =3D 4 | Case Is =3D 7 | C =3D 5 | Case Is =3D 8 | C =3D 6 | Case Is =3D 9 | C =3D 7 | Case Is =3D 10 | C =3D 8 | Case Is =3D 11 | C =3D 9 | Case Is =3D 12 | C =3D 10 | End Select | Sheet8.SelectActiveCell.Offset(0, 1).Copy | Destination:=3DSheet2.Range(ActiveCell.Offset(C, 0)) | Rem MsgBox cell.Value & " equals " & rng2 | (res).Value | Exit Sub | Else | ' the don't match | End If | ' continue the search | Next | | | | | | | End Sub | . |
| 1205 | Hi William You cannot use Code like: Sheet.ActiveCell or Sheets(1).ActiveCell etc as ActiveCell is not a Data member of the Sheet Object. When you type the CodeName of a Sheet (which is good practice to use) and then type the Dot (.) you will see a list of all the Properties and Methods available. If you wish to copy from Sheet8 the same address as the ActiveCell address you can use: Sheet8.Range(rActCell.Address).Offset(0, 1).Copy Or if you are wanting the Active Cell on sheet8 you MUST activate the sheet first, then just use ActiveCell Sheet8.Select ActiveCell.Offset(0, 1).Copy You must remember that ActiveCell Always refers to the active cell on the Active sheet and nowhere else. Perhaps if you could explain what the Copy code is suppose to do....... -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "William" <wapfu@xtra.co.nz wrote in message news:13c8f01c220ea$a13ef8d0$b1e62ecf@tkmsftngxa04... | Hi, | Replaced the case select in its shortened form, | set the range as | set rActCell = Activecell | and | used the following | Sheet8.rActCell.Offset(0, 1).Copy | Destination:=Sheet2.Range(rActCell.Offset(c, 0).Address) | | Still returning Method or data not found. | | Read about the active cell in "Excel 2002 Bible" and | thought if the match had been made then the refernce | would be stable (for the want of a better word). That is, | both sheets would have a cell that the routine knew | about, and shifting the value would be no problem, and | then the routine would step again to a new matching pair. | Can't think of another way to make the unique cell from | the matching , the only one that can be copied, hence the | offeset and active cell course. | Could you Run the object variable by me please or am I | missing something very simple. | | William | |
| 1211 | I need to track changes to an excel spreadsheet, to produce an audit trail. By enabling "TRACK CHANGES" and then using "HIGHLIGHT CHANGES LIST CHANGES ON A NEW SHEET" I can get a nice list of changes made since the last save of a certain date. Q1) Many of the cells in the spreadsheet have names (i.e. Stock_Count), but the track changes sheet continues to refer to these as B12 or D7 - is there a way to get the sheet of changes to use cell names (as well as or instead of cell references). It would make the audit log a lot clearer. Q2)Everyone on the network is identified as "Administrator" - am I doing something wrong. Is there some setup on the network that I need to get the tech boys to do ? Q3) Instead of using this method - are there any better methods, add-ins, 3rd part apps that do a better job of this ? All help appreciated. Please feel free to e-mail direct as well as post. Cheers. RW. robert_welburn@hotmail.com |
| 1212 | Hi Ben, Try running Excel in Safe Mode and see if the error occurs. For this go to Start/Run and run the following command: Excel.exe /s Note that there is a space before the forward slash. If everything works fine in Safe Mode, then the problem could be due to an add-in Go to Tools/Add-ins and uncheck all the add-ins. Then start Excel without using the /s switch. If this solves the problem then enable the add-ins one-by-one, restarting Excel each time, to find the offending add-in. If the problem still exists then go to Tools/Options/General and make sure that the 'Alternate Startup file location' is blank. If this doesn't work, move all the files out of C:\...\Office\XLStart. This could also be caused by a bad xlb file. Search for files "*.xlb" and move them to another folder.. ~~~~~~~~~~~~~~~ Beth Melton Microsoft Office MVP Please post replies/further questions to the newsgroup so that all may benefit. Personal requests for assistance can not be acknowledged. "Ben Owen" <thebigo@nospam.iinet.net.au wrote in message news:3d1fdfdc$0$23244$5a62ac22@freenews.iinet.net.au... Hi We are receiving the following error message when certain users try to use excel 2002 "An error occurred initializing the VBA libraries (265)" All the computers are running Windows 2000 with Office XP professional connected to a Win2000 SBS. We first noticed the problem on 2 computers which were upgraded to Win2000 from Win98. On those computers the only user who doesn't get the message is the Administrator. The only other 2 computers experiencing the problem have always been Win2000. On those computers only one particular user get the problem on each machine. I have noticed that on the machines that have always been Win2000 each time a new user logs on office is installed however in the upgraded machines each user opens up office straight away without running through the initial setup. We recently upgraded our practice management software which may have caused the problem however the software was removed from 2 upgraded machines then the operating system was reinstalled. We have tried uninstalling office then reinstalling the operating system before reinstalling office. We also tried repairing the office installation Any assistance would be appreciated Thanks Ben |
| 1214 | Rob Installing SBE tends to put lots of lovely add-ins and templates in the system, which are a pain when changing versions. If you no longer need them, load the new version of office and look for any strange .xla files or .dot files. These will be called things like sbfm.xla. (Small business financial manager), etc. If you still have these, start the process of removing them, by opening apps, Excel for example, going to ToolsAdd-ins and unchecking them so they don't load. This is probably safer than attempting to delete the add-ins, templates, etc. -- HTH Nick Hodge Southampton, England Microsoft MVP - Excel nick_hodge@btinternet.com Excel XP\WinXP "rob" <peterson@owc.net wrote in message news:#REsieSICHA.1600@tkmsftngp13... I had Office 2000 SBE installed and had made many custom templates. I installed Office 2000 PRO so I could get Access. Now I can't get VB to work? If I go into templates and try to run Calendar wizard I get "Errors occurred during load" then "Word could not fire the event". Trying to work with macros in VB (templates in Word) gives me"Could not open macro storage" KB had me unregister vbe6.dll file and rename and recreate. This didn't help. What should I do? I have uninstalled ALL office and reinstalled just the PRO. NO Good. Same with the SBE. ???? thanks. |
| 1239 | Hi Ted The best way for this is via the DMAX function, this can take up to 256 separate criteria. There are array formulae that look more efficient but they wouldn't come close is efficiency. =DMAX($A$1:$H$5000,$C$1,Criteria) Where "$A$1:$H$5000 is your Data Table C1 is a reference to the column heading for the data you want to get the MaxIf from "Criteria" is a named range that has copy of your heading that needs evaluation with a criteria directly underneath, eg 50 I have some examples of the Database function here that you can download if you wish: /download/default.htm under "Dfunction with validation.zip" -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Ted Chee" <tchee@jamcracker.com wrote in message news:143d601c22162$38aabb60$9be62ecf@tkmsftngxa03... | Excel has a COUNTIF and SUMIF command. Is there a way to | make a MAXIF command? |
| 1241 | Hi jc It sounds like there might be some code in the Sheet Object, right click on the sheet name tab select "View Code" if there anything there post it here, or delete it if you feel comfortable doing so. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "jc" <halfmybrain@yahoo.com wrote in message news:140ec01c22150$75e97690$b1e62ecf@tkmsftngxa04... | Recurring problem: Selected cells won't respond to | formatting. The following problems happen: | | * Apply Border toolbar shorcut doesn't work. | * A right-click from selected cells, choice of FORMAT | CELLS... produces no sub-menu | * Accessing the main menu Format/Cells... produces no | submenu. | | I had this problem in one part of my sheet and I decided | to insert a new leftmost column. After this I could format | some cells that now had an empty column to the side. But | other areas still don't respond. | | Pass the clue, please. | | -jc | | | Using Excel 2000 | (Version 5.0.2195 Service Pack 1 Build 2195) | |
| 1242 | Hi Mark Just so you know, Excel stores dates as serial numbers with 1 being 1/Jan/1900, 2 being 2/Jan/1900 etc. When you reference as cell that has a date format Excel assumes you want a date returned as your result and formats it the same, painful at times, handy at other times. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Mark" <Markh@bwacc.org wrote in message news:Oup0JnUICHA.2632@tkmsftngp09... | When I use the DATEDIF() function This is what I get | | DATEDIF(G2, K2, "Y") Where G2 = 7/6/65 And K2 = 7/31/01 | I Get 2/5/1900. I want to see the difference of Years. | | | |
| 1244 | Hi Annette Just change the TextBox CellLink property to any cell then spell check the cell. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Nick Hodge" <nick_hodge@btinternet.com wrote in message news:umTexsUICHA.2052@tkmsftngp08... | Annette | | This is not possible, but you could almost certainly drop it into a sheet | with VBA code, spell check that and then advise user if incorrect | | -- | HTH | Nick Hodge | Southampton, England | Microsoft MVP - Excel | nick_hodge@btinternet.com | | Excel XP\WinXP | "Annette Balboa" <abalboa@whopper.com wrote in message | news:124d801c22144$85e1b690$a4e62ecf@tkmsftngxa06... | I want to run spell check to text that is inside of a | textbox, is there any way to do this?? | I have a spreadsheet setup with instructions in a cell and | then I provide a textbox to be completed with the answer. | Several user have asked how they could run a spell check | to what they have written. I have tried and have not been | able to do so. | | |
| 1247 | Hi Bill ToolsOptionsError Checking. You can record a macro toggling the options to get the code. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Bill Murphy" <wmurphy@NO*SPAM!houston.rr.com wrote in message news:377U8.25029$p85.681329@twister.austin.rr.com... | I have a spreadsheet that was created in Excel 2000 which displays an error | condition, but only when opened in Excel 2002. Some cells containing | numbers are displayed with a small green triangle in the upper left corner | of the cell. When you place your mouse pointer in this cell an error box | appears to the left of the cell which contains an exclamation point and a | drop down menu. The drop down menu tells you that you have a number in the | cell which has been stored as text, and asks whether you want to correct | this "error" by converting it to a number. Once you do this the green | triangle is removed, and you are presumably okay. | | I opened a new spreadsheet in 2002 and pre-formatted a cell to text before | entering a number. The green triangle appeared, and reformatting the cell | to general or number did not make it go away. You have to fix the "error" | with the special drop down box to the left of the cell. However, if you | format a cell as text after a number has been entered into it using general | or number format this error does not occur. | | I also encountered this problem when trying to import this spreadsheet into | Access 2000. These cells cause an import error to occur - error 3349 | numeric overflow. Once the "error" cells are fixed this import error | disappears. | | Is anyone aware of a general fix for this problem by way of a service pack | for Excel 2002? | | Bill | | |
| 1254 | Dave, Thanks for this tip. I turned off this error check and it immediately resolved my problem, including the problem when importing to Access. Bill "" <DavidH@OzGrid.com wrote in message news:6o7U8.18$tm.19011@vicpull1.telstra.net... Hi Bill ToolsOptionsError Checking. You can record a macro toggling the options to get the code. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Bill Murphy" <wmurphy@NO*SPAM!houston.rr.com wrote in message news:377U8.25029$p85.681329@twister.austin.rr.com... | I have a spreadsheet that was created in Excel 2000 which displays an error | condition, but only when opened in Excel 2002. Some cells containing | numbers are displayed with a small green triangle in the upper left corner | of the cell. When you place your mouse pointer in this cell an error box | appears to the left of the cell which contains an exclamation point and a | drop down menu. The drop down menu tells you that you have a number in the | cell which has been stored as text, and asks whether you want to correct | this "error" by converting it to a number. Once you do this the green | triangle is removed, and you are presumably okay. | | I opened a new spreadsheet in 2002 and pre-formatted a cell to text before | entering a number. The green triangle appeared, and reformatting the cell | to general or number did not make it go away. You have to fix the "error" | with the special drop down box to the left of the cell. However, if you | format a cell as text after a number has been entered into it using general | or number format this error does not occur. | | I also encountered this problem when trying to import this spreadsheet into | Access 2000. These cells cause an import error to occur - error 3349 | numeric overflow. Once the "error" cells are fixed this import error | disappears. | | Is anyone aware of a general fix for this problem by way of a service pack | for Excel 2002? | | Bill | | |
| 1258 | Hi Ted You can use a sheets Index number or CodeName instead of it's Tab name. In regards to the Relative Recording the macro will be relative to you Activecell or selection when you start recording, so you can start with Absolute, select a specific cell then switch to Relative. Quite frankly though Ted, if you are having problems with recording macros, automating Pivot Tables might become a nightmare for you. They are really jumping in the VBA deep end. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Ted Chee" <tchee@jamcracker.com wrote in message news:1202a01c2216f$7342f690$a5e62ecf@tkmsftngxa07... | I want to record a macro to make pivot tables from .csv | files, then send this macro to people who have probably | never seen a pivot table so they will have an excellent | way to analyze data without knowing much about Excel. I'm | just using the "Record new macro" and "Stop recording" | feature. There are 2 problems I'm having. | | 1. The macro only works on spreadsheets with the same name. | | 2. The macro uses an absolute range for the pivot table. | I tried clicking on the Relative Reference icon when | making the macro, but the macro still uses absolute values. |
| 1260 | Hi AKS If you can give me the URL of the page I will see what I can come up with. Are you aware of Excels Web query feature, this will do way with the need to do anything on your part once set up. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "AKS" <akskelly@mail.com wrote in message news:1213201c22175$2dfa16d0$37ef2ecf@TKMSFTNGXA13... | ya i've tried that it still doesnt format right thanks | -----Original Message----- | Someone probably has a better idea than this, but | whenever | I copy data from a webpage and paste into Excel. I copy | the columns again and use the paste special command to | past only the values. After that I format the columns | the | way I like. | | | -----Original Message----- | I'm trying to copy from html web page and paste to an | excel worksheet. The numbers are all like 4 to the | fourth | power. a 4 and then a small 4 elevated. i try formatting | the cell to paste to but excel always reads as a date. | Any | ideas. | . | | . | |
| 1265 | Hi Vittal Right click on the Excel Icon top left next to "File", select "View Code" and insert this code: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.LeftFooter = Format(Date, "dd-mmm-yyyy") End Sub -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Vittal" <ryerson@giascl01.vsnl.net.in wrote in message news:uWma5PYICHA.2632@tkmsftngp09... | Dear Friends, | | Can anybody tell me how to change the date format in footer. I am using WIN | 98, Office 2000. Presently it is coming in dd-mm-yy format. The Regional | Settings properties for date tab are as follow: | | dd-mmm-yy | | I want the date format to look like 21-Jun-02. | | Any help is solicited. | | Vittal | | | | |
| 1275 | Hi Steve Place then in a GroupBox, then just link one in the group to any cell and the others in the same GroupBox will automatically link to the same cell. Each group of OptionButtons will then work independently -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Steven McDonald" <a@a.com wrote in message news:1271b01c221ac$8716dff0$a4e62ecf@tkmsftngxa06... | I'm using sets of option buttons from the forms menu to | control the value in cells. Generally, I have three | grouped buttons to set the corresponding cell value to 1, | 2 or 3 (simple enough). My problem is that some of the | sets of buttons seem to get "stuck together", i.e., I get | values from 1-6 in one cell instead of 1-3 in two separate | cells. | | Does anyone know who to "separate" groups of buttons like | this? I have tried changing the linked cell, but it seems | to change the link for both of the "stuck" groups, not | just the selected one. It probably doesn't help that I'm | creating new groups of buttons by copying/pasting old | ones, but I have a lot of groups to create and it would be | very laborious otherwise. | | Thanks in advance, | | Steve |
| 1281 | Thanks Dave, This is what I was doing, but your having confirmed that I was doing it correctly, I went back and checked and it seems that the problem was that my group boxes were too small, and therefore not quite containing the option buttons. It would be useful if you could simply group the buttons, rather than having to use the box, but clearly this isn't possible. Steve -----Original Message----- Hi Steve Place then in a GroupBox, then just link one in the group to any cell and the others in the same GroupBox will automatically link to the same cell. Each group of OptionButtons will then work independently -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Steven McDonald" <a@a.com wrote in message news:1271b01c221ac$8716dff0$a4e62ecf@tkmsftngxa06... | I'm using sets of option buttons from the forms menu to | control the value in cells. Generally, I have three | grouped buttons to set the corresponding cell value to 1, | 2 or 3 (simple enough). My problem is that some of the | sets of buttons seem to get "stuck together", i.e., I get | values from 1-6 in one cell instead of 1-3 in two separate | cells. | | Does anyone know who to "separate" groups of buttons like | this? I have tried changing the linked cell, but it seems | to change the link for both of the "stuck" groups, not | just the selected one. It probably doesn't help that I'm | creating new groups of buttons by copying/pasting old | ones, but I have a lot of groups to create and it would be | very laborious otherwise. | | Thanks in advance, | | Steve . |
| 1290 | Hi Steven You can do this without GroupBoxes but you would need to use the OptionButton from the Control ToolBox (ActiveX Controls) these have a GroupName Property that you can set. However, they would add overhead (If lots of them)and require some VBA knowledge. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Steven McDonald" <a@a.com wrote in message news:1223501c221b5$5da46fd0$37ef2ecf@TKMSFTNGXA13... | Thanks Dave, | | This is what I was doing, but your having confirmed that I | was doing it correctly, I went back and checked and it | seems that the problem was that my group boxes were too | small, and therefore not quite containing the option | buttons. It would be useful if you could simply group the | buttons, rather than having to use the box, but clearly | this isn't possible. | | Steve | | -----Original Message----- | Hi Steve | | Place then in a GroupBox, then just link one in the group | to any cell and | the others in the same GroupBox will automatically link | to the same cell. | Each group of OptionButtons will then work independently | | | -- | | | \ | Get 8 Add-ins in one! For less than the price of 1 | /Services/AddinExamples.htm | FREE EXCEL NEWSLETTER | /News/2home.htm | | "Steven McDonald" <a@a.com wrote in message | news:1271b01c221ac$8716dff0$a4e62ecf@tkmsftngxa06... | | I'm using sets of option buttons from the forms menu to | | control the value in cells. Generally, I have three | | grouped buttons to set the corresponding cell value to | 1, | | 2 or 3 (simple enough). My problem is that some of the | | sets of buttons seem to get "stuck together", i.e., I | get | | values from 1-6 in one cell instead of 1-3 in two | separate | | cells. | | | | Does anyone know who to "separate" groups of buttons | like | | this? I have tried changing the linked cell, but it | seems | | to change the link for both of the "stuck" groups, not | | just the selected one. It probably doesn't help that I'm | | creating new groups of buttons by copying/pasting old | | ones, but I have a lot of groups to create and it would | be | | very laborious otherwise. | | | | Thanks in advance, | | | | Steve | | | . | |
| 1300 | The TextToColumns affect on pasting can be confusing. I find it a little hard to believe that the feature helps more than it hurts. Nice when you need it but confounding when you don't. If you do a lot of pasting and importing but do not want to keep "turning off" the TextToColumns settings, then try the add-in "Refinate". It opens the TextToColumns wizard in one click and then clears the settings after you use it so that it will not continue to affect pastes. Refinate also provides extra settings which can be used for parsing or importing that might do a cleaner job of splitting your data into cells. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, much more. www.adetaylor.com "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:1497501c221bb$d3fba300$3bef2ecf@TKMSFTNGXA10... Hi, Press F2 (edit) and then press paste. (No help if you need to paste more than one row though). Otherwise, I could reset it by doing a bogus data, text-to-columns with no delimiters specified. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- In Excel, when I use the "text to column" option and then try to paste some text that includes spaces in a cell (even in a new workbook), it spreads the text over many cells with one word per cell. I looked around but can't seem to find a way to disable that feature. When I start a new Excel session, it seems to be working fine, but I need to find another way around the problem... |
| 1334 | "Edward Thornton" <edward.thornton@btinternet.com wrote in message news:<afqe1u$9n6$1@helle.btinternet.com... Can anyone please advise on how to open spreadsheets created with MS Works Version 4 for Windows 95 using Excel 2002? I can use Works to save spreadsheets in Excel format and then open them in Excel 2002 but I'd prefer to copy them across to a new PC as *.wks files and open them in Excel 2002 as required. Thanks, Ed RL-Software's Works spreadsheet to Excel Converter does this perfectly with almost all formulae and formatting of the cells maintained. You can find a free evaluation copy of this Excel Add-Inn at www.rl-software.com HTH Rob |
| 1335 | "Edward Thornton" <edward.thornton@btinternet.com wrote in message news:<afqe1u$9n6$1@helle.btinternet.com... Can anyone please advise on how to open spreadsheets created with MS Works Version 4 for Windows 95 using Excel 2002? I can use Works to save spreadsheets in Excel format and then open them in Excel 2002 but I'd prefer to copy them across to a new PC as *.wks files and open them in Excel 2002 as required. Thanks, Ed RL-Software's Works spreadsheet to Excel Converter does this perfectly with almost all formulae and formatting of the cells maintained. You can find a free evaluation copy of this Excel Add-Inn at www.rl-software.com HTH Rob |
| 1341 | Whilst this looks as if it would provide the solution, unfortunately, I work for the National Health Service and I'll find it difficult to persuade my manager to pay for it! Thanks anyway. Any other suggestions from elswhere????? -----Original Message----- Airlie, take a look at the Data Loader on my site. Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "B Airlie" <bernard.airlie@laht.scot.nhs.uk wrote in message news:1231301c221e0$8b661830$a5e62ecf@tkmsftngxa07... I am desperate! All I am trying to do is merge two different spreadsheets, where the common field is Order Number. Where the order nubmer exists on both sheets, I would like to transfer the Cost Centre from the 2nd sheet onto the first sheet and show it against the line on that sheet where the order number matches. Hopefully, the result would be as shown on the green area of the attached file. I've used the Excel Help facility and dabbled with Lookup, but with (very!) limited success. Any help would be greatly appreciated. Thanks. . |
| 1344 | Hi Dave Drag the Page Field to the inner Row area, right click and select Group.... It will require that the dates are true dates and not simply text. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "David Fodden" <david.fodden@panp-tr.northy.nhs.uk wrote in message news:14c0501c2226b$27f79fe0$19ef2ecf@tkmsftngxa01... | Hello everyone | | I have Excel 97 and have created a Pivot Table from | columns of data. One of them is headed "Month" and, when | I put this into the <Page of the Pivot Table, it presents | me with a list of months from Jan to Dec as well as | <All. I'd like to be able to add further to this list so | the table will present aggregated data, such as Quarter1, | Quarter2 etc. which are simply the data from Jan, Feb and | Mar added and also Apr, May and Jun added etc. I thought | I might be able to do this by using a Calculated Item but | I've not been able to do it. Is there some other way or | am I wasting my time? | | Thanks in anticipation. | | David | |
| 1345 | Arrh yes, you can also get this by right clicking on the Sheet scroll tabs. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eAYeCWmICHA.1928@tkmsftngp13... | Sub menu() | Application.CommandBars("Workbook tabs").ShowPopup 500, 200 | End Sub | | do you mean this | | Regards Ron | | "Peter Loveridge" <Peter.Loveridge@dpws.nsw.gov.au schreef in bericht | news:1e1401c22248$a8b4dc10$9de62ecf@tkmsftngxs01... | Can anyone tell me how to get a list of the tabs in a | workbook. I get workbooks sent to me with a sheet per | site. Problem is I cannot find a way to get a simple list | of sheets so as to check them off when recieved. | Properties / contents will list them but you cannot copy | and paste the information. | | Peter | | |
| 1346 | Hi peter you see that if you don't hide the tabs you can use what Dave posted. The sub that I posted do the same like right clicking on the Sheet scroll tabs. I forgot to say it. But you have your solution and thats count. Regards Ron "" <DavidH@OzGrid.com schreef in bericht news:nfzU8.2$iK.5000@vicpull1.telstra.net... Arrh yes, you can also get this by right clicking on the Sheet scroll tabs. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eAYeCWmICHA.1928@tkmsftngp13... | Sub menu() | Application.CommandBars("Workbook tabs").ShowPopup 500, 200 | End Sub | | do you mean this | | Regards Ron | | "Peter Loveridge" <Peter.Loveridge@dpws.nsw.gov.au schreef in bericht | news:1e1401c22248$a8b4dc10$9de62ecf@tkmsftngxs01... | Can anyone tell me how to get a list of the tabs in a | workbook. I get workbooks sent to me with a sheet per | site. Problem is I cannot find a way to get a simple list | of sheets so as to check them off when recieved. | Properties / contents will list them but you cannot copy | and paste the information. | | Peter | | |
| 1355 | Hi Magnus As Ron has said DataValidation with "List" as the option is what's needed. I have some information on this very handy feature here: /Excel/Formulas.htm -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:Ou5ihznICHA.1596@tkmsftngp13... | you are using data-validation-list. | | That is normal for this | | You can put a lisbox or combobox on the screen from the forms or control | toolbox. | This ones you see aleays | | Right click on a commandbar and choose one of them, with the controltoolbox | you can do more things than the forms. | | Regards Ron | | | "Magnus" <magnyz@hotmail.com schreef in bericht | news:eKydilnICHA.2424@tkmsftngp08... | Hi, | | I would like to add, to a worksheet, the type of "dropdown listbox" (or | whatever it is) that cannot be seen unless the cell is selected. When the | cell is selected the down arrow is shown to the right of the cell but | otherwise the arrow cannot be seen. I've looked in the help to find info | about this but I cannot find anything. | Thanks. | /Magnus | | | | |
| 1402 | Hi all Is there a add-inn that you can use to fax a sheet away. And the number is hardcoded on the sheet or in VBA. I can change the printer to fax with Application.ActivePrinter = "hp officejet k series fax on Ne00:" But I still have to say wich number and to how. Regards Ron |
| 1408 | I did a search and took care of them. I uninstalled and did a completely new Off Pro install. I still get "COULD NOT OPEN MACRO STORAGE" when I go into Macro, Visual Basic when I have a template loaded. Any other ideas???? thanks. "Nick Hodge" <nick_hodge@btinternet.com wrote in message news:uRl83sUICHA.2052@tkmsftngp08... Rob Installing SBE tends to put lots of lovely add-ins and templates in the system, which are a pain when changing versions. If you no longer need them, load the new version of office and look for any strange .xla files or .dot files. These will be called things like sbfm.xla. (Small business financial manager), etc. If you still have these, start the process of removing them, by opening apps, Excel for example, going to ToolsAdd-ins and unchecking them so they don't load. This is probably safer than attempting to delete the add-ins, templates, etc. -- HTH Nick Hodge Southampton, England Microsoft MVP - Excel nick_hodge@btinternet.com Excel XP\WinXP "rob" <peterson@owc.net wrote in message news:#REsieSICHA.1600@tkmsftngp13... I had Office 2000 SBE installed and had made many custom templates. I installed Office 2000 PRO so I could get Access. Now I can't get VB to work? If I go into templates and try to run Calendar wizard I get "Errors occurred during load" then "Word could not fire the event". Trying to work with macros in VB (templates in Word) gives me"Could not open macro storage" KB had me unregister vbe6.dll file and rename and recreate. This didn't help. What should I do? I have uninstalled ALL office and reinstalled just the PRO. NO Good. Same with the SBE. ???? thanks. |
| 1437 | Thanks Dave I got it to work but now have two drop down lists in the <Page area. One is for the individual months (plus <All) and the other is for the grouped months (plus <All). Is it possible to amalgamate the lists into just one drop down list? I suspect not but there's no harm in asking. I can certainly live with what I have, thanks to your expertise! D. "" <DavidH@OzGrid.com wrote in message news:XdzU8.1$iK.4471@vicpull1.telstra.net... Hi Dave Drag the Page Field to the inner Row area, right click and select Group.... It will require that the dates are true dates and not simply text. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "David Fodden" <david.fodden@panp-tr.northy.nhs.uk wrote in message news:14c0501c2226b$27f79fe0$19ef2ecf@tkmsftngxa01... | Hello everyone | | I have Excel 97 and have created a Pivot Table from | columns of data. One of them is headed "Month" and, when | I put this into the <Page of the Pivot Table, it presents | me with a list of months from Jan to Dec as well as | <All. I'd like to be able to add further to this list so | the table will present aggregated data, such as Quarter1, | Quarter2 etc. which are simply the data from Jan, Feb and | Mar added and also Apr, May and Jun added etc. I thought | I might be able to do this by using a Calculated Item but | I've not been able to do it. Is there some other way or | am I wasting my time? | | Thanks in anticipation. | | David | |
| 1476 | Also if you are using Excel 07 there were some problems with the calculation routines that could incorrectly identify circular refs: if you are on Excel97 make sure you have installed the SR2 level (Help -- About) Charles Williams ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Tom Ogilvy" <twogilvy@msn.com wrote in message news:OQWTmq3ICHA.1772@tkmsftngp09... If you checked iteration, it tells Excel to allow circular references - doesn't mean it is fixed. Regards, Tom Ogilvy Stuart <stuart.green@Adelphi.co.uk wrote in message news:12c2701c22357$f3252560$9ee62ecf@tkmsftngxa05... Thanks very much for your reply - I hadn't spotted this, and had tried looking on the support site but nothing seemed to quite match what was happening. It seems to be working OK now - I guess I just need to get rid of a few (a lot?) of formulae! Thanks again, Stuart -----Original Message----- Hi Stuart If you have not changed any formulae references, try going to ToolsOptionCalculation and see whether "Iterations" is checked. From Excel Help: Limits iteration for goal seeking or for resolving circular references. Unless you specify otherwise, Microsoft Excel stops after 100 iterations or when all values change by less than 0.001. You can limit iteration by changing the number in the Maximum iterations box, the Maximum change box, or both boxes. If you toggle this and have your Status bar visible, you should see the address of the Circular in there when you select a Sheet that has one. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Stuart" <stuart.green@adelphi.co.uk wrote in message news:12df501c2234b$fd01fb00$a5e62ecf@tkmsftngxa07... | I have a file that seems to be working fine. When I save | it as a new version it suddenly finds lots of circular | references. I have not changed anything and this will be | the 9th version of the same file - no previous versions | have had this problem. Does anyone have any ideas why? | | many thanks in advance for your help! | Stuart . |
| 1487 | Hi Dave, Thanks for taking the time to get back to me. Unfortunately on my spreadsheet of Project Tasks I will be allowing the users to add more rows as required and therefore I would need to dynamically add combo boxes/delete combo boxes as rows are added and deleted. I have done this before and it is all a bit messy and sometimes things go wrong. As this spreadsheet will be distributed to hundreds of remote users I would rather go for the more simple solution of Validation lists if I can get them to work. I appreciate you taking the time to reply and I will have a good look at the solutions you pointed me to just in case. Regards Tony "" <DavidH@OzGrid.com wrote in message news:BWYU8.8$lY.21766@vicpull1.telstra.net... Hi Tony I have a working example here that should help: /download/default.htm It's under "ChangingCombo2.zip" -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Tony Starr" <tonyXXXnospamxxxStarr@ozemail.com.au wrote in message news:ugoshexICHA.2464@tkmsftngp12... | Hi Folks, | | I need some help with implementing data validation lists. | I want to have a validation list for column B that is based upon the value | the user selects in column A and a validation list in Column C that depends | on the choices made in columns A and B. | | | On a sheet named Projects users enter the details of people working on | Projects | The sheet looks like this | | Department Project Person | Accounts Payroll System John Smith | Accounts Payroll System Helen Brown | Accounts Debt Recovery Allan Welling | Despatch New Loading Bay Mike Lang | Despatch New Loading Bay Sharon Stone | HR Leave System John Smith | HR Leave System Mike Lang | HR Training Jodi Rich | | (It is possible that two departments may have a project of the same name) | | | The values entered in the Department Column are from a predefined list. | The values entered in the Project and Person columns are user defined. (i.e. | absolutely anything) | | | My "Project Tasks" Sheet looks like this | | Department Project Person | Task | Accounts Payroll System John Smith | Scoping | Accounts Payroll System John Smith | Planning | Accounts Payroll System John Smith | Purchase | Accounts Payroll System Helen Brown | Installation | | | | I want the values entered in the Department, Project and Person columns to | be picked from a validation list. | If the user selects Accounts in the Department column, the validation list | for Project Column should consist of only projects entered on the "Projects" | sheet against the Accounts department. | Likewise the validation list for the Person column should only consist of | people listed in the "Projects" sheet for the nominated department/project. | | | How do I do this? | (Just stressing again I do not know in advance the values the users will be | entering as Projects and Persons on the "Projects" sheet.) | | | TIA | Tony | | |
| 1507 | Hi Stuart I hope I didn't give you the same impression that Tom has got from my response. I was only suggesting that you see if the "Iterations" checkbox was un-checked now, where it might have been checked before. That it why i copied in the Excel Help about the Checkbox. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Tom Ogilvy" <twogilvy@msn.com wrote in message news:OQWTmq3ICHA.1772@tkmsftngp09... | If you checked iteration, it tells Excel to allow circular references - | doesn't mean it is fixed. | | Regards, | Tom Ogilvy | | Stuart <stuart.green@Adelphi.co.uk wrote in message | news:12c2701c22357$f3252560$9ee62ecf@tkmsftngxa05... | Thanks very much for your reply - I hadn't spotted this, | and had tried looking on the support site but nothing | seemed to quite match what was happening. | | It seems to be working OK now - I guess I just need to get | rid of a few (a lot?) of formulae! | | Thanks again, | Stuart | | | -----Original Message----- | Hi Stuart | | If you have not changed any formulae references, try | going to | ToolsOptionCalculation and see whether "Iterations" is | checked. From Excel | Help: | | Limits iteration for goal seeking or for resolving | circular references. | Unless you specify otherwise, Microsoft Excel stops after | 100 iterations or | when all values change by less than 0.001. You can limit | iteration by | changing the number in the Maximum iterations box, the | Maximum change box, | or both boxes. | | If you toggle this and have your Status bar visible, you | should see the | address of the Circular in there when you select a Sheet | that has one. | | | -- | | | \ | Get 8 Add-ins in one! For less than the price of 1 | /Services/AddinExamples.htm | FREE EXCEL NEWSLETTER | /News/2home.htm | | | | "Stuart" <stuart.green@adelphi.co.uk wrote in message | news:12df501c2234b$fd01fb00$a5e62ecf@tkmsftngxa07... | | I have a file that seems to be working fine. When I save | | it as a new version it suddenly finds lots of circular | | references. I have not changed anything and this will be | | the 9th version of the same file - no previous versions | | have had this problem. Does anyone have any ideas why? | | | | many thanks in advance for your help! | | Stuart | | | . | | | |
| 1508 | Guess who needs glasses :o) -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Myrna Larson" <myrnalarson@chartermi.net wrote in message news:e0v8iu4nbuvm4ftaqv3vl6akdf5umaidrc@4ax.com... | No, it's the module-level variables that are being preserved (as they should be). He said: | | "I declared all the variables at the module-level (i.e., at the beginning of the module). The | problem is: now when I run the procedure more than once, the values are preserved between | calls." | | | On Thu, 4 Jul 2002 16:34:42 +0800, "" <DavidH@OzGrid.com wrote: | | Hi Spre | | I am a bit confused by you saying the Procedure level variables are | retaining their values between Calls. As far as I am aware variables | declared at the procedure level lose their value between Calls, unless they | are Static. | | Can you use the End statement? It will destroy ALL variable at ALL levels. | Or perhaps simply use: | | var1=0 | | at the end of the Procedure | |
| 1510 | Hi Tony You have misunderstood me. I meant for you to use the same type of Dynamic Range in the List Source of the Validation. The example does use Comboboxes, but as no VBA is needed (al via single dynamic range) it can very easily be used in many situations. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Tony Starr" <tonyXXXnospamxxxStarr@ozemail.com.au wrote in message news:OuOXQa6ICHA.2280@tkmsftngp12... | Hi Dave, | | Thanks for taking the time to get back to me. Unfortunately on my | spreadsheet of Project Tasks I will be allowing the users to add more rows | as required and therefore I would need to dynamically add combo boxes/delete | combo boxes as rows are added and deleted. I have done this before and it is | all a bit messy and sometimes things go wrong. As this spreadsheet will be | distributed to hundreds of remote users I would rather go for the more | simple solution of Validation lists if I can get them to work. | | I appreciate you taking the time to reply and I will have a good look at the | solutions you pointed me to just in case. | | Regards | Tony | | "" <DavidH@OzGrid.com wrote in message | news:BWYU8.8$lY.21766@vicpull1.telstra.net... | Hi Tony | | | I have a working example here that should help: | /download/default.htm It's under "ChangingCombo2.zip" | | | -- | | | \ | Get 8 Add-ins in one! For less than the price of 1 | /Services/AddinExamples.htm | FREE EXCEL NEWSLETTER | /News/2home.htm | | "Tony Starr" <tonyXXXnospamxxxStarr@ozemail.com.au wrote in message | news:ugoshexICHA.2464@tkmsftngp12... | | Hi Folks, | | | | I need some help with implementing data validation lists. | | I want to have a validation list for column B that is based upon the | value | | the user selects in column A and a validation list in Column C that | depends | | on the choices made in columns A and B. | | | | | | On a sheet named Projects users enter the details of people working on | | Projects | | The sheet looks like this | | | | Department Project Person | | Accounts Payroll System John Smith | | Accounts Payroll System Helen Brown | | Accounts Debt Recovery Allan Welling | | Despatch New Loading Bay Mike Lang | | Despatch New Loading Bay Sharon Stone | | HR Leave System John Smith | | HR Leave System Mike Lang | | HR Training Jodi Rich | | | | (It is possible that two departments may have a project of the same | name) | | | | | | The values entered in the Department Column are from a predefined list. | | The values entered in the Project and Person columns are user defined. | (i.e. | | absolutely anything) | | | | | | My "Project Tasks" Sheet looks like this | | | | Department Project Person | | Task | | Accounts Payroll System John Smith | | Scoping | | Accounts Payroll System John Smith | | Planning | | Accounts Payroll System John Smith | | Purchase | | Accounts Payroll System Helen Brown | | Installation | | | | | | | | I want the values entered in the Department, Project and Person columns | to | | be picked from a validation list. | | If the user selects Accounts in the Department column, the validation | list | | for Project Column should consist of only projects entered on the | "Projects" | | sheet against the Accounts department. | | Likewise the validation list for the Person column should only consist | of | | people listed in the "Projects" sheet for the nominated | department/project. | | | | | | How do I do this? | | (Just stressing again I do not know in advance the values the users will | be | | entering as Projects and Persons on the "Projects" sheet.) | | | | | | TIA | | Tony | | | | | | | | |
| 1513 | I have over 600 1x360 arrays. I am looking at bond cashflows projected out over 30 years. Each bond need several variables to describe its characteristics over time. Furthermore, the each bond is dependent on the characteristics of the other bonds. While it is possible to do all of this using simple worksheet functions, putting this all in macro form allows for more flexibility in changing the paydown structures, etc. Regards, -----Original Message----- Do you mean you have more than 600 arrays, or 600 scalar variables that you've moved to arrays? I can't imagine what you are doing that you have 600 arrays all needed at the same time. Can you give more details? On Thu, 4 Jul 2002 02:00:22 -0700, "spre" <spre17@yahoo.com wrote: Thanks Dave and Charles, I have a few hundred array variables. I tried it both ways. 1. Declare variables at the module-level and at the end of the procedure use the "End" statement preceding the "End Sub". -- This works. 2. Create a macro to initialize the first period array variables to 0 and call that in my procedure. -- This works. Unclear as to which one has the better performance but I imagine 1. will. The only reason that I declared the variables at the module-level was because I was running out of space ( 64k) at the procedure-level. I basically have over 600 array variables. If you guys think of anything else, it would be much appreciated. Thanks again. Spre -----Original Message----- Hi Spre I am a bit confused by you saying the Procedure level variables are retaining their values between Calls. As far as I am aware variables declared at the procedure level lose their value between Calls, unless they are Static. Can you use the End statement? It will destroy ALL variable at ALL levels. Or perhaps simply use: var1=0 at the end of the Procedure -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "spre" <spre17@yahoo.com wrote in message news:104ba01c22329$097f1570$95e62ecf@tkmsftngxs02... | I have a long macro that I am writing that requires | several hundred variables. I previously declared all of | these variables at the procedure-level but ran out of | space due to a procedure 64k size constraint. | | In order to save space at the procedure-level I declared | all the variables at the module-level (i.e., at the | beginning of the module). | | The problem is: now when I run the procedure more than | once, the values are preserved between calls. | | Example: | | var1 = var1 + 1 | | In the 1st call, var1 = 0 and then is set to = 1. | In the 2nd call, var1 = 1 and then is set to = 2. | | Is there any way to reinitialize var1 at each call and | also declare variables at the module-level? I have a few | hundred variables to deal with. | | Thanks . . |
| 1514 | Hi Jim If you mean the true meaning of "does not contain any data?" you should use: =IF(AND(ISBLANK(I31),ISBLANK(I36)),"",I31-H31-I36-I27-(I35*0.5)) If the cells might themselves contain "" (Empty Text) you cannot use =IF(AND(I31="",I36=""),"",I31-H31-I36-I27-(I35*0.5)) To do this correctly though I would suggest using: =IF(OR(ISTEXT(I31),ISTEXT(H31),ISTEXT(I36),ISTEXT(I27),ISTEXT(I35)),"",I31-H 31-I36-I27-(I35*0.5)) And apply a Custom Format like: General;-General; This will hide a Zero value which means that any other cells that reference it in a function that will not fall over referencing text ("" is text). -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Jim" <jimbeam@onemain.com wrote in message news:n46V8.401$A43.32206@newsread2.prod.itd.earthlink.net... | =I31-H31-I36-I27-(I35*0.5) | Adding to this formula is it possible to have the cell containing this | formula to be blank if I31 and or I36 does not contain any data? | | Thankx | | | |
| 1547 | HI AP You will need to open it to copy the sheet as a whole, but the users shouldn't see anything, try some code like: Sub OpenCopy() Application.ScreenUpdating = True Workbooks.Open Filename:="C:/MyDocs/Book1.xls" Sheets(2).Copy Before:=Workbooks(ThisWorkbook.Name).Sheets(2) ActiveWorkbook.Close SaveChanges:=False ThisWorkbook.Activate Application.ScreenUpdating = False End Sub Hope this helps -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "AP" <APhang@whispertech.co.nz wrote in message news:eZaV8.923$JN4.302282@news02.tsnz.net... | Is there any way of inserting a worksheet (from another unopened workbook) | into the active workbook ? | | Alternatively, if the source workbook does need to be open, is there any way | of bringing it up without flashing it at the user ? | | Thanks, | AP | | |
| 1551 | Hi Bruce To convert to Values only use Sheet1.UsedRange=Sheet1.UsedRange.Value To protect it use: Sheet.Protect Password:="Secret" -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "bruce" <bruce@nha.co.za wrote in message news:12fba01c223fd$83b9b8c0$9ee62ecf@tkmsftngxa05... | In connection with my earlier posting - would it be | possible to email a copy of the spreadhseet which is | values only but no formulas? |
| 1557 | Thanks Dave! I am not quite sure how to work it into the email script. The sheet in particular that i am trying to get the text only values is called List Entry so I modified your code to reflect this. but a get a 400 error (?). Sub SendIt() Dim EmailAddress EmailAddress = InputBox("Please enter the email address below.", "Email Quote") If EmailAddress < "" Then Sheets("List Entry").UsedRange = Sheets("List Entry").UsedRange.Value Application.Dialogs(xlDialogSendMail).Show arg1:=EmailAddress, arg2:="Quote: " & Date End If End Sub -----Original Message----- Hi Bruce To convert to Values only use Sheet1.UsedRange=Sheet1.UsedRange.Value To protect it use: Sheet.Protect Password:="Secret" -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "bruce" <bruce@nha.co.za wrote in message news:12fba01c223fd$83b9b8c0$9ee62ecf@tkmsftngxa05... | In connection with my earlier posting - would it be | possible to email a copy of the spreadhseet which is | values only but no formulas? . |
| 1566 | Hi Kumar You can also have the Calendar Control embedded onto your Worksheet if you prefer. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "kumar" <s_palanikumar@hotmail.com wrote in message news:1529901c223da$6dd05e10$9ae62ecf@tkmsftngxa02... | Hi, | | Could anyone inform the possibility of selecting the date | value for a cell through calendar controls? | | Thanks & Best Regards, | | Kumar | | | |
| 1567 | Hi Phil Just as a future tip, you only need to double click any Control while in the VBE and it will default to it's default Procedure, normally Click. You can then easily select the Event you want from the "Procedure" list in the Top right of the Module window. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "phil" <pperry@acsoft wrote in message news:12fce01c22400$9498ff90$9ee62ecf@tkmsftngxa05... | yep, that seems to have done the trick thanks | p | | -----Original Message----- | Phil, | | The problem is with the line | | Sub Userform1_Initialize() | | You don't want "Userform1" here. Rather, you want the | name of the object type, | not the name of the specific object. Change | | Sub Userform1_Initialize() | | to | Sub Userform_Initialize() | | | -- | Cordially, | Chip Pearson | Microsoft MVP - Excel | Pearson Software Consulting, LLC | www.cpearson.com chip@cpearson.com | | | | | "phil" <pperry@acsoft wrote in message | news:1589001c22377$a9bcfa90$3bef2ecf@TKMSFTNGXA10... | Hi, | I can't seem to initialize a user form. | i'm using: | Sub Userform1_Initialize() | to put some values in combo boxes, etc, but this doesn't | work. Am i missing something obvious? | thanks | Phil | | . | |
| 1568 | Actually, it turned out to be some sort of error with the workbook (it then crashed when saving or deleting more than a handful of cells). After opening on an XP machine and re-saving everything went back to normal again! Thanks for all your help, Stuart -----Original Message----- Also if you are using Excel 07 there were some problems with the calculation routines that could incorrectly identify circular refs: if you are on Excel97 make sure you have installed the SR2 level (Help - - About) Charles Williams ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Tom Ogilvy" <twogilvy@msn.com wrote in message news:OQWTmq3ICHA.1772@tkmsftngp09... If you checked iteration, it tells Excel to allow circular references - doesn't mean it is fixed. Regards, Tom Ogilvy Stuart <stuart.green@Adelphi.co.uk wrote in message news:12c2701c22357$f3252560$9ee62ecf@tkmsftngxa05... Thanks very much for your reply - I hadn't spotted this, and had tried looking on the support site but nothing seemed to quite match what was happening. It seems to be working OK now - I guess I just need to get rid of a few (a lot?) of formulae! Thanks again, Stuart -----Original Message----- Hi Stuart If you have not changed any formulae references, try going to ToolsOptionCalculation and see whether "Iterations" is checked. From Excel Help: Limits iteration for goal seeking or for resolving circular references. Unless you specify otherwise, Microsoft Excel stops after 100 iterations or when all values change by less than 0.001. You can limit iteration by changing the number in the Maximum iterations box, the Maximum change box, or both boxes. If you toggle this and have your Status bar visible, you should see the address of the Circular in there when you select a Sheet that has one. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Stuart" <stuart.green@adelphi.co.uk wrote in message news:12df501c2234b$fd01fb00$a5e62ecf@tkmsftngxa07... | I have a file that seems to be working fine. When I save | it as a new version it suddenly finds lots of circular | references. I have not changed anything and this will be | the 9th version of the same file - no previous versions | have had this problem. Does anyone have any ideas why? | | many thanks in advance for your help! | Stuart . . |
| 1570 | Sounds like corrupted dependency trees. Charles Williams ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Stuart" <stuart.green@adelphi.co.uk wrote in message news:1371c01c2241f$24761620$2ae2c90a@hosting.microsoft.com... Actually, it turned out to be some sort of error with the workbook (it then crashed when saving or deleting more than a handful of cells). After opening on an XP machine and re-saving everything went back to normal again! Thanks for all your help, Stuart -----Original Message----- Also if you are using Excel 07 there were some problems with the calculation routines that could incorrectly identify circular refs: if you are on Excel97 make sure you have installed the SR2 level (Help - - About) Charles Williams ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Tom Ogilvy" <twogilvy@msn.com wrote in message news:OQWTmq3ICHA.1772@tkmsftngp09... If you checked iteration, it tells Excel to allow circular references - doesn't mean it is fixed. Regards, Tom Ogilvy Stuart <stuart.green@Adelphi.co.uk wrote in message news:12c2701c22357$f3252560$9ee62ecf@tkmsftngxa05... Thanks very much for your reply - I hadn't spotted this, and had tried looking on the support site but nothing seemed to quite match what was happening. It seems to be working OK now - I guess I just need to get rid of a few (a lot?) of formulae! Thanks again, Stuart -----Original Message----- Hi Stuart If you have not changed any formulae references, try going to ToolsOptionCalculation and see whether "Iterations" is checked. From Excel Help: Limits iteration for goal seeking or for resolving circular references. Unless you specify otherwise, Microsoft Excel stops after 100 iterations or when all values change by less than 0.001. You can limit iteration by changing the number in the Maximum iterations box, the Maximum change box, or both boxes. If you toggle this and have your Status bar visible, you should see the address of the Circular in there when you select a Sheet that has one. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Stuart" <stuart.green@adelphi.co.uk wrote in message news:12df501c2234b$fd01fb00$a5e62ecf@tkmsftngxa07... | I have a file that seems to be working fine. When I save | it as a new version it suddenly finds lots of circular | references. I have not changed anything and this will be | the 9th version of the same file - no previous versions | have had this problem. Does anyone have any ideas why? | | many thanks in advance for your help! | Stuart . . |
| 1573 | I believe that there are a couple of problems with Dave's code: first swap the true/false on the screenupdating stuff. And after the copy is done, the receiving workbook becomes the activeworkbook. So the line that closes the activeworkbook will close the wrong one. And the code will never finish. Something like this might work for you: Sub OpenCopy2() Application.ScreenUpdating = False Dim curWkbk As Workbook Dim newWkbk As Workbook Set curWkbk = ActiveWorkbook Set newWkbk = Workbooks.Open _ (Filename:="C:\my documents\excel\Book4.xls") newWkbk.Worksheets(1).Copy _ before:=curWkbk.Worksheets(1) newWkbk.Close savechanges:=False Application.ScreenUpdating = True End Sub wrote: HI AP You will need to open it to copy the sheet as a whole, but the users shouldn't see anything, try some code like: Sub OpenCopy() Application.ScreenUpdating = True Workbooks.Open Filename:="C:/MyDocs/Book1.xls" Sheets(2).Copy Before:=Workbooks(ThisWorkbook.Name).Sheets(2) ActiveWorkbook.Close SaveChanges:=False ThisWorkbook.Activate Application.ScreenUpdating = False End Sub Hope this helps -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "AP" <APhang@whispertech.co.nz wrote in message news:eZaV8.923$JN4.302282@news02.tsnz.net... | Is there any way of inserting a worksheet (from another unopened workbook) | into the active workbook ? | | Alternatively, if the source workbook does need to be open, is there any way | of bringing it up without flashing it at the user ? | | Thanks, | AP | | -- Dave Peterson ec35720@msn.com |
| 1591 | Hi Ron /Google.htm Maybe this add-in can help you if you will search often Regards Ron(great name <vbg) "Ron Hagley" <ron@hagley.freeserve.co.uk schreef in bericht news:ag6cus$l0e$1@newsg1.svr.pol.co.uk... Hi, I'm new to this group and wonder if there is any way I can gain access to archive material, from the past month or so to get some background and avoid asking the same questions already answered elsewhere. If so please advise how. BTW I joined beginnig July. -- Regards Ron Hagley ron@hagley.freeserve.co.uk |
| 1676 | Nathan, Do you have a "Debug" button the on error message that comes up? If so, repeat the process, click Debug and copy the entire procedure an post in the body of your message (NOT as an attachment). If you do not have a "Debug" button, then the problems lies in a password-protected add-in that is loaded when you start Excel (or perhaps just some workbook that resides in the XLStart folder). Go to the Tools menu, Add-Ins, and see what is checked. Repeat the following until there are no add-ins checked: Uncheck one. Close and Restart Excel If Error Occurs Then Repeat Else That Add-In Is The Problem End IF If you can get to the VBA code of the offending Add-In, you (or we) can fix it. If you cannot get to the VBA code, then you must either (or both) 1) not use that add-in, 2) get the author to fix it. It isn't an "Excel" problem per se, but rather a problem in code written by someone else that Excel is having problems with. If the above methods don't work, repeat the same process but instead of "uninstalling" add-ins, move one-by-one files out of your XLStart folder (typically something like C:\Program Files\Microsoft Office\Office\XLStart ). When you move the offending file, Excel won't open it (Excel is design to open every file in XLStart when it starts up), you'll find the culprit. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Nathan Gutman" <ngutman@cshore.com wrote in message news:3d28c04e.31676567@news.cshore.com... Usin Excel97 under win95. Out of nowhere, when I invoke Excel it comes up with a blank worksheet Book1 but immediately displays a message about Runtime error "13" Type mismatch. This started showing up recently and I have no idea what is causing it. This happens before I do anything to this worksheet. I have to click on "End" to get rid of it. Where is this coming from and how do I fix that? Thanks, |
| 1693 | Dave, Thank you very much. You solution works very nicely. Regards Tony "" <DavidH@OzGrid.com wrote in message news:XH6V8.15$G11.29347@vicpull1.telstra.net... Hi Tony You have misunderstood me. I meant for you to use the same type of Dynamic Range in the List Source of the Validation. The example does use Comboboxes, but as no VBA is needed (al via single dynamic range) it can very easily be used in many situations. -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "Tony Starr" <tonyXXXnospamxxxStarr@ozemail.com.au wrote in message news:OuOXQa6ICHA.2280@tkmsftngp12... | Hi Dave, | | Thanks for taking the time to get back to me. Unfortunately on my | spreadsheet of Project Tasks I will be allowing the users to add more rows | as required and therefore I would need to dynamically add combo boxes/delete | combo boxes as rows are added and deleted. I have done this before and it is | all a bit messy and sometimes things go wrong. As this spreadsheet will be | distributed to hundreds of remote users I would rather go for the more | simple solution of Validation lists if I can get them to work. | | I appreciate you taking the time to reply and I will have a good look at the | solutions you pointed me to just in case. | | Regards | Tony | | "" <DavidH@OzGrid.com wrote in message | news:BWYU8.8$lY.21766@vicpull1.telstra.net... | Hi Tony | | | I have a working example here that should help: | /download/default.htm It's under "ChangingCombo2.zip" | | | -- | | | \ | Get 8 Add-ins in one! For less than the price of 1 | /Services/AddinExamples.htm | FREE EXCEL NEWSLETTER | /News/2home.htm | | "Tony Starr" <tonyXXXnospamxxxStarr@ozemail.com.au wrote in message | news:ugoshexICHA.2464@tkmsftngp12... | | Hi Folks, | | | | I need some help with implementing data validation lists. | | I want to have a validation list for column B that is based upon the | value | | the user selects in column A and a validation list in Column C that | depends | | on the choices made in columns A and B. | | | | | | On a sheet named Projects users enter the details of people working on | | Projects | | The sheet looks like this | | | | Department Project Person | | Accounts Payroll System John Smith | | Accounts Payroll System Helen Brown | | Accounts Debt Recovery Allan Welling | | Despatch New Loading Bay Mike Lang | | Despatch New Loading Bay Sharon Stone | | HR Leave System John Smith | | HR Leave System Mike Lang | | HR Training Jodi Rich | | | | (It is possible that two departments may have a project of the same | name) | | | | | | The values entered in the Department Column are from a predefined list. | | The values entered in the Project and Person columns are user defined. | (i.e. | | absolutely anything) | | | | | | My "Project Tasks" Sheet looks like this | | | | Department Project Person | | Task | | Accounts Payroll System John Smith | | Scoping | | Accounts Payroll System John Smith | | Planning | | Accounts Payroll System John Smith | | Purchase | | Accounts Payroll System Helen Brown | | Installation | | | | | | | | I want the values entered in the Department, Project and Person columns | to | | be picked from a validation list. | | If the user selects Accounts in the Department column, the validation | list | | for Project Column should consist of only projects entered on the | "Projects" | | sheet against the Accounts department. | | Likewise the validation list for the Person column should only consist | of | | people listed in the "Projects" sheet for the nominated | department/project. | | | | | | How do I do this? | | (Just stressing again I do not know in advance the values the users will | be | | entering as Projects and Persons on the "Projects" sheet.) | | | | | | TIA | | Tony | | | | | | | | |
| 1760 | PMFJI, Where is this macro? Does it reside in the same workbook that you want to send? If yes, then this line: ActiveWorkbook.Close SaveChanges:=False Closes the workbook (which contains the code) and these two lines: Kill ("C:\ProtectorQuote.xls") Workbooks.Open Filename:=wkname are never seen (as well as the "end if" and "end sub") ----- I was testing your macro (I had it in the same workbook as the one that needs to be sent) and it just disappeared on me when I was stepping through the code. ---- On the otherhand, if the activeworkbook is not the workbook containing the code, then you can ignore this answer! bruce wrote: Hi Dave I don't get any error messager - it just doesn't seem to do anything. I have included the macro below, but i'll explain it here: A user clicks on an email button, which prompts them for an address. once entered, the users gets prompted to save the file, then i do a whole lot of formatting so that the emailed version is not editable. I then save that file and email it. At this stage i want to close and delete the second file and reopen the original saved file. hope this makes some sense! thanks bruce Sub SendIt() Dim EmailAddress EmailAddress = InputBox("Please enter the email address below.", "Email Quote") If EmailAddress < "" Then Application.ScreenUpdating = False InitPath = "c:\" FName = Application.GetSaveAsFilename (InitialFileName:=InitPath, filefilter:="Excel File (*.xls), *.xls") ActiveWorkbook.SaveAs Filename:=FName wkname = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name ActiveSheet.Unprotect Password:="phxy" Range("B15:Z1015").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B13:L13").Select Application.CutCopyMode = False Selection.ClearComments Range("H12:M12").Select Selection.ClearComments Range("J12").Select Selection.Font.ColorIndex = 2 Selection.Interior.ColorIndex = 2 Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Range("M12").Select Selection.Font.ColorIndex = 2 Selection.Interior.ColorIndex = 2 Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Range("M1:N6").Select Selection.ClearContents Range("P10:Z10").Select Selection.ClearComments ActiveSheet.Shapes("Button 65").Select Selection.Cut ActiveSheet.Shapes("Button 64").Select Selection.Cut ActiveSheet.Shapes("Button 63").Select Selection.Cut ActiveSheet.Shapes("Button 19").Select Selection.Cut ActiveSheet.Shapes("Button 26").Select Selection.Cut ActiveSheet.Shapes("Button 50").Select Selection.Cut ActiveSheet.Shapes("Button 20").Select Selection.ShapeRange.IncrementLeft -132 ActiveSheet.Shapes("Button 67").Select Selection.ShapeRange.IncrementLeft -190.5 Cells.Select Selection.Locked = True Selection.FormulaHidden = True Range("B16").Select ActiveSheet.Protect Password:="phxy" ChDir "C:\" ActiveWorkbook.SaveAs Filename:="C:\ProtectorQuote.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False Application.Dialogs(xlDialogSendMail).Show arg1:=EmailAddress, arg2:="Protector Health Quote: " & Date ActiveWorkbook.Close SaveChanges:=False Kill ("C:\ProtectorQuote.xls") Workbooks.Open Filename:=wkname End If End Sub -----Original Message----- Hi Bruce As long as the names and paths are correct it should work. Maybe you need to use something like: ChDir "C:\" as you are in another directory? What is the error message you get? -- \ Get 8 Add-ins in one! For less than the price of 1 /Services/AddinExamples.htm FREE EXCEL NEWSLETTER /News/2home.htm "bruce" <bruce@nha.co.za wrote in message news:160a101c2265f$b87aa830$19ef2ecf@tkmsftngxa01... | I have these two lines at the end of a macro which don't | work. I am trying to delete the file called quote and | open the one called Quote2 (wkname=C:\Quote2.xls). Is | there something I am doing wrong? | | Kill ("C:\Quote.xls") | Workbooks.Open Filename:=wkname | | . -- Dave Peterson ec35720@msn.com |
| 1766 | J, if you don't want to write the macro yourself, take a look at the Directory List at -ins.com/directory_lister.htm. I think it will do what you want, and more. Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "J Civille" <jcciville@ucdavis.edu wrote in message news:167b101c226b6$c3df9b70$39ef2ecf@TKMSFTNGXA08... I would like to import a Windows directory into an Excel spreadsheet, so I could arrange and print subdirectories and track progress on a set of over 330 files. Has anyone ever tried doing this? I've used SnagIt! to do screen captures, very successfully, but would like to have the data in columns, not just as pixels. |
| 1977 | #2. ThisDocument should be ThisWorkbook Oops, that's what I meant. Sorry, Word-itis. #4. I'm not sure why you want to make it an addin (hidden??) I don't want another workbook visible. #6. If you made it an addin, why not use .xla? Well, because this is the only way I know to make XL add-ins. ;) You can also accomplish the same thing by putting your code in a general module named Auto_open. A *module* (not macro) named Auto_Open? In what workbook? Again, I don't want a macro to run *every* time I open Excel. Also, the code *isn't* associated with a specific workbook, so I can't build the desktop shortcut to open a given workbook & then run the macro. (I know how to do that, but it wouldn't help.) For example, several times a day I want Excel to open and then run an Application.FileSearch procedure to find the file in a given folder whose name is alphabetically last, then open it. I don't want this to happen each time I open Excel, just when I want that file -- and that file may change unpredictably. Am I missing something obvious? (You should spend more time in here. Things like this get mentioned pretty often <bg.) That's why I've spent so much time searching for the answer on Google. I know from roaming the Word groups that things that are mentioned often can usually be located there and that the question-answerers are usually tired of hearing the same queries over and over. -- Mark Tangard <mtangard@speakeasy.net, Microsoft Word MVP Word MVP FAQ: /word "Life is nothing if you're not obsessed." --John Waters Mark Tangard wrote: After years of wishing for an Excel equivalent to Word's "/m" startup switch -- to have Excel automatically run a macro upon startup but only when you want it to, e.g., to make a desktop shortcut for that action, not to have the macro run whenever Excel opens -- I think I've stumbled upon the answer, and while it works fine, it seems waaaaaay too simple. Excel gurus, please tell me if something's wrong with this, or if I'm in one of those damn parallel universes again: 1. Create a new workbook 2. Place the macro you want to run in the workbook's ThisDocument module. 3. Name the macro Workbook_Open. 4. Change ThisWorkbook's IsAddIn property to True 5. Save it, but put it somewhere other than the XLStart folder. 6. Make a desktop shortcut to open the file as if it were a regular wkbk, e.g., Excel C:\path\MySneakyAddIn.xls It works. But I've never seen it mentioned. Is it just really bad form? -- Mark Tangard <mtangard@speakeasy.net, Microsoft Word MVP Word MVP FAQ: /word "Life is nothing if you're not obsessed." --John Waters -- Dave Peterson ec35720@msn.com |
| 1981 | Hi All How do I appropriately reference the solver add in in VBA. I have tried following the KB advice on this subject but could not locate the solver add-in library reference. Thanks Matt |
| 1986 | The foll. works in XL XP and should be the same in earlier versions. Look up 'Solver' (w/o the quotes) in XL VBE help. Click on any of the Solver functions. The 2nd para will tell you how to establish a reference to solver.xla. The file is in the Office\Library\Solver folder. -- Regards, Tushar Mehta www.tushar-mehta.com Microsoft MVP -- Excel -- In <1745801c22889$d9ac1800$9be62ecf@tkmsftngxa03, Matt <mstedman@idx.com.au wrote Hi All How do I appropriately reference the solver add in in VBA. I have tried following the KB advice on this subject but could not locate the solver add-in library reference. Thanks Matt |
| 2015 | Sometimes two add-ins fail to load when opening Excel via keyboard shortcuts to xls files with error - "Cannot open Microsoft Excel 97 Add-in for editing. Please edit the source document instead" Pressing enter twice and XL & the file open normally, except for the two add-ins (although they remain checked in the addin list). Opens normally via shortcut icons or explorer to xls files, also first opening XL then the keyboard shortcut. If I deselect the two failing add-ins, everything works normally next time I open from a shortcut. Whether or not add-ins fail to load on keyboard shortcut opening, seems (not totally sure) to be related to the order in which the add-ins were previously de & reselected. But bizarre as to why the problem only occurs with keyboard shortcut and not icon shortcut opening etc. Hardly the most serious XL problem I've ever had but slightly irritating, so any ideas appreciated. Sandy PS "Jean" reported a similar unresolved issue in this newsgroup and I've followed all the checks suggested by Nick & Jimmy; ref Subject: "Excel 97 : cannot open add-in for editing", Date: 25 Jan 2002 |
| 2020 | Simon, modify your pivot table so that it just has one field. Then sort it. Then add back the other fields. Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "simon" <simon.wanderer@tnsofres.com wrote in message news:1553601c228d1$1b0259d0$a4e62ecf@tkmsftngxa06... I'm trying to sort data in a pivot table in order of one of the fields (date). I'm sellecting ascending in the autosort options section of the pivot table field advanced options box. But the data is staying as it was and not getting in order. |
| 2076 | Now that you've got this running the way you want, you should stick around more. If I stuck around everywhere I wanted to, I'd be, well, *very* sticky. And probably never sleep. <-(It's approaching that point anyway.) But I'll be baaaaack. Excel is much easier (and more fun) to work with than Word <gdr. Believe it or not, I often reach the point -- usually deep in the softest, moistest part of VBA, like in a userform, where the otherwise dark purple line between Word and Excel actually blurs. (Sometimes I'll even forget which program birthed the macro I'm working on.) It's a nice, er, escape. ;) One follow-up: Why would I have been told (early in my VBA career and I think even in this very newsgroup) to use an .XLS with the IsAddIn property changed, rather than an .XLA? Is there any easily explained advantage to the former? Thanks again, Mark Mark Tangard <mtangard@speakeasy.net, Microsoft Word MVP Word MVP FAQ: /word Userform demystification ritual begins at: /~mtangard/userforms.html "Life is nothing if you're not obsessed." --John Waters Dave Peterson wrote: Bernie did devine what I meant about auto_open macro in a general module. When you do File|SaveAs, there's an option near the bottom that allows you to specify Addin (in the "save as type" box). You'll probably have to point to your favorite directory again, though. But my point was that you didn't have to call it .xls. You could have its extension .xla (which sounds more normal to me). But it sounds like it'll do what you want. (I have a bad habbit of picking on details instead of the seeing big picture. The reason that's it's a bad habbit is sometimes I make detailed mistakes (module named auto_open for example)!) Now that you've got this running the way you want, you should stick around more. Excel is much easier (and more fun) to work with than Word <gdr. Mark Tangard <mtangard@speakeasy.net wrote in message news:<3D2CF0FC.3C2C975A@speakeasy.net... #2. ThisDocument should be ThisWorkbook Oops, that's what I meant. Sorry, Word-itis. #4. I'm not sure why you want to make it an addin (hidden??) I don't want another workbook visible. #6. If you made it an addin, why not use .xla? Well, because this is the only way I know to make XL add-ins. ;) You can also accomplish the same thing by putting your code in a general module named Auto_open. A *module* (not macro) named Auto_Open? In what workbook? Again, I don't want a macro to run *every* time I open Excel. Also, the code *isn't* associated with a specific workbook, so I can't build the desktop shortcut to open a given workbook & then run the macro. (I know how to do that, but it wouldn't help.) For example, several times a day I want Excel to open and then run an Application.FileSearch procedure to find the file in a given folder whose name is alphabetically last, then open it. I don't want this to happen each time I open Excel, just when I want that file -- and that file may change unpredictably. Am I missing something obvious? (You should spend more time in here. Things like this get mentioned pretty often <bg.) That's why I've spent so much time searching for the answer on Google. I know from roaming the Word groups that things that are mentioned often can usually be located there and that the question-answerers are usually tired of hearing the same queries over and over. -- Mark Tangard <mtangard@speakeasy.net, Microsoft Word MVP Word MVP FAQ: /word "Life is nothing if you're not obsessed." --John Waters Mark Tangard wrote: After years of wishing for an Excel equivalent to Word's "/m" startup switch -- to have Excel automatically run a macro upon startup but only when you want it to, e.g., to make a desktop shortcut for that action, not to have the macro run whenever Excel opens -- I think I've stumbled upon the answer, and while it works fine, it seems waaaaaay too simple. Excel gurus, please tell me if something's wrong with this, or if I'm in one of those damn parallel universes again: 1. Create a new workbook 2. Place the macro you want to run in the workbook's ThisDocument module. 3. Name the macro Workbook_Open. 4. Change ThisWorkbook's IsAddIn property to True 5. Save it, but put it somewhere other than the XLStart folder. 6. Make a desktop shortcut to open the file as if it were a regular wkbk, e.g., Excel C:\path\MySneakyAddIn.xls It works. But I've never seen it mentioned. Is it just really bad form? -- Mark Tangard <mtangard@speakeasy.net, Microsoft Word MVP Word MVP FAQ: /word "Life is nothing if you're not obsessed." --John Waters -- Dave Peterson ec35720@msn.com |
| 2108 | Dave, You are, or should I say Myrna is right. But how did you remember yet alone relate that thread to my posting, not immediately obvious. Thanks for picking it up. Assigning anything but Shift to my keyboard shortcuts allows all add-ins to load normally. Also as I hinted before, the problem is indeed related to the order in which add-ins had previously been de & reselected. I can now recreate and solve the problem and allow Shift in shortcuts. For anyone else troubleshooting the "orders" are not in reverse, instead somewhat illogical in my case. I had recently installed ASAP-utilities (www.asap- utilities.nl) which has many Ctrl+Shift shortcuts to macros, duplicating all my *.xls file shortcuts. De & reselecting caused both this and XL original Analysis ToolPak to fail. Odd, as the latter contains no shortcuts. Thanks again. Regards, Sandy -----Original Message----- A couple of years ago, there was a discussion about why a macro would not fire when it was started by using a shortcut key (assigned to a macro in excel). This is part of what Myrna Larson wrote then. I'm not sure if it fits your case, but it sounds pretty close: ===== From Myrna: It has to do with the fact that the shortcut key uses SHIFT. Excel "remembers" that the shift key was used to start the macro and behaves as though it's still depressed when the 2nd file is opened. Opening a file with the shift key down suppresses the running of a macro, and evidently also aborts one that is already running. In my book, this is a bug. In my case, the shortcut key was CTRL+SHIFT+U. The only workaround that I found was to use CTRL+U instead, i.e. assign a shortcut key that doesn't use the SHIFT key. ===== You can read the whole thread at: /groups? threadm=u7ufks038r9jjaaa3jrt2c3cpilu7rshja%404ax.com Sandy V wrote: Sometimes two add-ins fail to load when opening Excel via keyboard shortcuts to xls files with error - "Cannot open Microsoft Excel 97 Add-in for editing. Please edit the source document instead" Pressing enter twice and XL & the file open normally, except for the two add-ins (although they remain checked in the addin list). Opens normally via shortcut icons or explorer to xls files, also first opening XL then the keyboard shortcut. If I deselect the two failing add-ins, everything works normally next time I open from a shortcut. Whether or not add-ins fail to load on keyboard shortcut opening, seems (not totally sure) to be related to the order in which the add-ins were previously de & reselected. But bizarre as to why the problem only occurs with keyboard shortcut and not icon shortcut opening etc. Hardly the most serious XL problem I've ever had but slightly irritating, so any ideas appreciated. Sandy PS "Jean" reported a similar unresolved issue in this newsgroup and I've followed all the checks suggested by Nick & Jimmy; ref Subject: "Excel 97 : cannot open add- in for editing", Date: 25 Jan 2002 -- Dave Peterson ec35720@msn.com . |
| 2121 | Hi Jussi, Not sure what you're after, but suggest you take a look at the free add-in at this URL: /foxes/index.htm HTH Cheers, John "Jussi" <jra2@www.fi wrote in message news:17a2701c22977$7b44f900$9ae62ecf@tkmsftngxa02... Hello! Is it possible to increase the precision Excel uses? I have got a matrix which determinant differs from zero for sure. But Excel cant't find the inverse matrix because of the numerical precision. I've heard rumours that there is some "double precision" mode. Thanks in advance! Jussi |
| 2147 | If you have XL97 or later suggest dumping DATEDIF when working with geneology and use John Walkenbach's XDATEDIF Extended Date Functions Add-In, eliminating problems with negative dates involving subtraction in MS date system and incorrect leap years in older MS 1900 date system. (also dates prior to 1900). -walk.com/ss/excel/files/xdate.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm |
| 2206 | Thanks Cracked it with all your help Earliest Birthday =XDATE(XDATEYEAR(F2)-G2-1,XDATEMONTH(F2),XDATEDAY(F2)+1) Latest Birthday =XDATE(XDATEYEAR(F2)-G2,XDATEMONTH(F2),XDATEDAY(F2)) Cell F2 is the Date Cell G2 is the Age This can be performed for all known data and then select the Latest "Earliest" and the Earliest "Latest" Birthday for the range. "" <dmcritchie@msn.com wrote in message news:eHFvO#aKCHA.2984@tkmsftngp13... | | If you have XL97 or later suggest dumping DATEDIF | when working with geneology and use John Walkenbach's | XDATEDIF Extended Date Functions Add-In, eliminating | problems with negative dates involving subtraction in MS | date system and incorrect leap years in older MS 1900 | date system. (also dates prior to 1900). | -walk.com/ss/excel/files/xdate.htm | | HTH, | , Microsoft MVP - Excel [site changed Nov. 2001] | My Excel Macros: /dmcritchie/excel/excel.htm | Search Page: /dmcritchie/excel/search.htm | | | |
| 2296 | I have a user who receives a compile error message when opening Excel. She is able to click past that. She also had one in Word, but I followed the procedure from MPV and deleted a file in the startup folder. I assumed that this would take care of Excel also, but it did not. I followed the suggestion from Hank Scorpio in removing the Add-Ins, but that also did not work. Any suggestions? Thank you. |
| 2304 | Otto, If you type the following in cells A1:A3: '1/1/02 '3/3/99 '4/4/00 and sort the cells, they'll stay in that order. If you enter the following formula in cell B1: =A1+1 you'll get 37258 as the result, even though A1 contains text. If you copy it down, each original date is increased by one, and the result is a number, not text. This new column sorts properly. From reading the OP's messages, I wasn't sure if she had tried sorting this new column, or if she had only tried creating the formula, so I thought it might at least be worth trying. I don't know if a corrupt sheet would do that, but she could copy those few cells to another sheet, and find out. Debra Otto Moehrbach wrote: Debra Appreciate your help, but the OP said that the dates resulting from the A1+1 wouldn't sort either. Any ideas? She said that other workbooks sorted fine. Could a corrupt sheet do this? Also, if the date 15 July 02 were text, wouldn't it be true that it would not have a 5-digit number base? And adding 1 to it would then equal 1? Thanks for your help. Otto "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D332D68.7090008@contextures.com... Even if the dates are entered as text, when you use the formula: A1+1 in an adjacent cell, you'll get a date as a result. Try the following, which will convert 'text' dates to real dates, then sort the column: 1. Select an empty cell and copy it. 2. Select the dates 3. Choose EditPaste Special 4. Select Add, click OK amber wrote: I did try that...same thing. It doesn't do anything... amber -----Original Message----- Amber Do that "A1+1" thing for each of your date cells (or just a few) and then see if the new dates will sort. HTH Otto "amber" <amber_hancock@abicon.com wrote in message news:18c9e01c22c2a$b7ed8380$9be62ecf@tkmsftngxa03... Hi again. It is formatted as a date. If I put your formula in another cell (A1+1) it does go to the next date. I'm baffled. I've played with all different formatting, but it will not sort. The only odd thing about this data is this: I have a 3rd party add-in that I use to get my data. It automatically populates my columns and rows (e.g. A1:B2). I cannot sort this data, so I have another column where I have (=A1, =A2 etc). These are dates, but I cannot sort them. I get no error messages, just nothing happens. I do this same thing in other spreadsheets and it works fine (I've double checked). I'm getting a bit frustrated!! Thanks again, amber -----Original Message----- Amber I would say that your dates are not recognized as dates by Excel. Do this. Select another cell away from these cells. Say A1 is one of your date cells. Type in "=A1+1" without the quotes. If A1 contains a date instead of text, then the above formula should return the date of the next day. If not, then delete the dates, format the cells General, and enter the dates again. HTH Otto "amber" <amber_hancock@abicon.com wrote in message news:18f5801c22c1c$ddba5d80$3bef2ecf@TKMSFTNGXA10... Hello, I'm sure I'm doing something wrong, because this has to be simple... I have a 'date' column and a 'number' column. There are two rows of data, e.g. 15-July-02 0.0 04-July-02 0.0 I am trying to sort these 4 cells, so that the 4th of July is ABOVE the 15th, but nothing will work. What am I doing wrong???? Thanks, amber . . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 2316 | Hi Sheila, Excel has a different default startup directory than Word does. It's called XLStart and it's located in the same folder hierarchy as the Word Startup directory. Checked this directory to see if it contains any files, and if it does, remove them and see if that solves the problem. Note that if the user is running Office 2000, Excel's startup directory can exist in multiple locations and you have to check all of them. Under Windows 2000 there's an additional XLStart directory under: C:\Documents and Settings\<username\Application Data\Microsoft\Excel\XLStart and in Windows 98 it's located at: C:\Windows\Application Data\Microsoft\Excel\XLStart -- Rob Bovey, MCSE, MCSD, Excel MVP Application Professionals / * Please post all replies to this newsgroup * * I delete all unsolicited e-mail responses * "Sheila" <sbayard@ci.puyallup.wa.us wrote in message news:168b701c22c42$b8c46ea0$2ae2c90a@hosting.microsoft.com... I have a user who receives a compile error message when opening Excel. She is able to click past that. She also had one in Word, but I followed the procedure from MPV and deleted a file in the startup folder. I assumed that this would take care of Excel also, but it did not. I followed the suggestion from Hank Scorpio in removing the Add-Ins, but that also did not work. Any suggestions? Thank you. |
| 2319 | Ooh. Good point. Maybe the formula to check should be closer to: =ISNUMBER(a1) (or just formatting the cells as general and see if they look like one of those big numbers) Debra Dalgleish wrote: Otto, If you type the following in cells A1:A3: '1/1/02 '3/3/99 '4/4/00 and sort the cells, they'll stay in that order. If you enter the following formula in cell B1: =A1+1 you'll get 37258 as the result, even though A1 contains text. If you copy it down, each original date is increased by one, and the result is a number, not text. This new column sorts properly. From reading the OP's messages, I wasn't sure if she had tried sorting this new column, or if she had only tried creating the formula, so I thought it might at least be worth trying. I don't know if a corrupt sheet would do that, but she could copy those few cells to another sheet, and find out. Debra Otto Moehrbach wrote: Debra Appreciate your help, but the OP said that the dates resulting from the A1+1 wouldn't sort either. Any ideas? She said that other workbooks sorted fine. Could a corrupt sheet do this? Also, if the date 15 July 02 were text, wouldn't it be true that it would not have a 5-digit number base? And adding 1 to it would then equal 1? Thanks for your help. Otto "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D332D68.7090008@contextures.com... Even if the dates are entered as text, when you use the formula: A1+1 in an adjacent cell, you'll get a date as a result. Try the following, which will convert 'text' dates to real dates, then sort the column: 1. Select an empty cell and copy it. 2. Select the dates 3. Choose EditPaste Special 4. Select Add, click OK amber wrote: I did try that...same thing. It doesn't do anything... amber -----Original Message----- Amber Do that "A1+1" thing for each of your date cells (or just a few) and then see if the new dates will sort. HTH Otto "amber" <amber_hancock@abicon.com wrote in message news:18c9e01c22c2a$b7ed8380$9be62ecf@tkmsftngxa03... Hi again. It is formatted as a date. If I put your formula in another cell (A1+1) it does go to the next date. I'm baffled. I've played with all different formatting, but it will not sort. The only odd thing about this data is this: I have a 3rd party add-in that I use to get my data. It automatically populates my columns and rows (e.g. A1:B2). I cannot sort this data, so I have another column where I have (=A1, =A2 etc). These are dates, but I cannot sort them. I get no error messages, just nothing happens. I do this same thing in other spreadsheets and it works fine (I've double checked). I'm getting a bit frustrated!! Thanks again, amber -----Original Message----- Amber I would say that your dates are not recognized as dates by Excel. Do this. Select another cell away from these cells. Say A1 is one of your date cells. Type in "=A1+1" without the quotes. If A1 contains a date instead of text, then the above formula should return the date of the next day. If not, then delete the dates, format the cells General, and enter the dates again. HTH Otto "amber" <amber_hancock@abicon.com wrote in message news:18f5801c22c1c$ddba5d80$3bef2ecf@TKMSFTNGXA10... Hello, I'm sure I'm doing something wrong, because this has to be simple... I have a 'date' column and a 'number' column. There are two rows of data, e.g. 15-July-02 0.0 04-July-02 0.0 I am trying to sort these 4 cells, so that the 4th of July is ABOVE the 15th, but nothing will work. What am I doing wrong???? Thanks, amber . . -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html -- Dave Peterson ec35720@msn.com |
| 2323 | This describes your problem: XL2000: AutoSave Settings Are Not Retained Between Sessions of Excel 2000 (Q231117) /default.aspx?scid=kb;EN-US;q231117 "Roger L. Waer" wrote: I've installed the Autosave Add-In for excel 2000 and it defaults to saving the active workbook only, every 10 minutes and prompting me before autosaving. This is NOT what I want. Is there a way to change the default settings permanently? I'm tired of changing them every time I start excel! Thanks in advance. Roger L. Waer -- Dave Peterson ec35720@msn.com |
| 2409 | /advanced_group_search?q=group:*Excel* use exact phrase if you look often maybe this add-in is helpfull /Google.htm Regards Ron "Jack Gillis" <jgilNOSPAM@widomaker.com schreef in bericht news:eViSVIRLCHA.1740@tkmsftngp10... I just tried to search, for the first time, this newsgroup via the MS Help and Support site. I got to the search form OK and entered my search phrase on the message body line. The phrase was "My Documents" (without the quotes). I got a good number of hits but NONE of them contained "MY Documents." They all had the words My and Documents but not the phrase My Documents. Is it possible to search for a multi-word phrase? I would think so but darned if I can work out how to do it. The search engine didn't like for me to put quotes around the words and asked me to remove them in no uncertain terms. Any help for this newbie to searching the news group will be appreciated. Thank you. |
| 2414 | Thank you Ron. I did use the exact phrase, at least I thought I did. :) It was My Documents but, as I said I got hit on articles that contained My as well as Documents but no My Documents. Go figure! "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eZkbOPRLCHA.2400@tkmsftngp08... /advanced_group_search?q=group:*Excel* use exact phrase if you look often maybe this add-in is helpfull /Google.htm Regards Ron "Jack Gillis" <jgilNOSPAM@widomaker.com schreef in bericht news:eViSVIRLCHA.1740@tkmsftngp10... I just tried to search, for the first time, this newsgroup via the MS Help and Support site. I got to the search form OK and entered my search phrase on the message body line. The phrase was "My Documents" (without the quotes). I got a good number of hits but NONE of them contained "MY Documents." They all had the words My and Documents but not the phrase My Documents. Is it possible to search for a multi-word phrase? I would think so but darned if I can work out how to do it. The search engine didn't like for me to put quotes around the words and asked me to remove them in no uncertain terms. Any help for this newbie to searching the news group will be appreciated. Thank you. |
| 2474 | I currently have a pivottable generated by olap club, but as data size is huge (~ 50Mb). Does anybody knows any macros commands, add-in components or other tools that can help to improve the pivottable performance (i.e pivotitem change etc...)? Thanks! |
| 2480 | Hi Ron I have just downloaded and used your Add In. It is excellent and I will find it extremely useful. Many thanks. -- Regards, Roger Govier Technology 4 U "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eZkbOPRLCHA.2400@tkmsftngp08... /advanced_group_search?q=group:*Excel* use exact phrase if you look often maybe this add-in is helpfull /Google.htm Regards Ron "Jack Gillis" <jgilNOSPAM@widomaker.com schreef in bericht news:eViSVIRLCHA.1740@tkmsftngp10... I just tried to search, for the first time, this newsgroup via the MS Help and Support site. I got to the search form OK and entered my search phrase on the message body line. The phrase was "My Documents" (without the quotes). I got a good number of hits but NONE of them contained "MY Documents." They all had the words My and Documents but not the phrase My Documents. Is it possible to search for a multi-word phrase? I would think so but darned if I can work out how to do it. The search engine didn't like for me to put quotes around the words and asked me to remove them in no uncertain terms. Any help for this newbie to searching the news group will be appreciated. Thank you. |
| 2494 | This is a problem which I am having at the moment and I will be interested in theresponses you get. However, I found that if you publish the pivottable as html with a limited number of fields actually used within the pivottable, the rest unused in the field list, the actual performance of drag-and-drop from the field list and the re-formatting of the fields is much improved. -----Original Message----- I currently have a pivottable generated by olap club, but as data size is huge (~ 50Mb). Does anybody knows any macros commands, add-in components or other tools that can help to improve the pivottable performance (i.e pivotitem change etc...)? Thanks! . |
| 2515 | Ron, Life will now be easier. Thanks -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Ron de Bruin" <rondebruin@kabelfoon.nl wrote in message news:eZkbOPRLCHA.2400@tkmsftngp08... /advanced_group_search?q=group:*Excel* use exact phrase if you look often maybe this add-in is helpfull /Google.htm Regards Ron "Jack Gillis" <jgilNOSPAM@widomaker.com schreef in bericht news:eViSVIRLCHA.1740@tkmsftngp10... I just tried to search, for the first time, this newsgroup via the MS Help and Support site. I got to the search form OK and entered my search phrase on the message body line. The phrase was "My Documents" (without the quotes). I got a good number of hits but NONE of them contained "MY Documents." They all had the words My and Documents but not the phrase My Documents. Is it possible to search for a multi-word phrase? I would think so but darned if I can work out how to do it. The search engine didn't like for me to put quotes around the words and asked me to remove them in no uncertain terms. Any help for this newbie to searching the news group will be appreciated. Thank you. |
| 2652 | Data Flow Manager 3.0 by Peters Software, Inc. is on the internet. Use Google to search/find. It converts .qif and many other extensions. It's 30 day trial & about $65. thereafter. Their support is great!! -----Original Message----- Does anyone know of a utiliy, add-in, or other method to parse a .qif file? Thanks in advance, Justin . |
| 2661 | just wondering if the template with data tracking add-in can accept a computername as a path rather than a direct path when creating or adding to the data store. it seems to accept it, but the data store isn't being updated at all. -- to reply via email please remove NOSPAM_ Julian Cram IT Manager Direct Mail Centre of Australia any views in this email are STRICTLY my own, and in no way reflect the attitudes and policies of my place of employment |
| 2803 | with the HEX2DEC() function. If not available, ToolsAdd-ins, check Analysis Toolpak. -- Regards, Niek Otten Microsoft MVP - Excel "GARY" <gcotterl@co.riverside.ca.us wrote in message news:1b35101c23039$6b8654f0$3aef2ecf@TKMSFTNGXA09... How do I convert packed numbers (i.e., comp-3) to decimal numbers? |
| 2809 | Hi Gary, I'll not admit to working on COBOL for 30 years but I doubt that you have packed decimal data on a PC. If you really have packed decimal numbers from an IBM mainframe you would have garbage. If you have them on a PC implementation of COBOL, I doubt that the PC implementation of COMP-3 is really packed decimal. I expect that COMP-3 on a PC would be same as Binary. Am curious if COMP-3 was ever accepted into the standard. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Niek Otten" <nicolaus@xs4all.nl wrote in message news:OG$a9YJMCHA.1724@tkmsftngp08... with the HEX2DEC() function. If not available, ToolsAdd-ins, check Analysis Toolpak. -- Regards, Niek Otten Microsoft MVP - Excel "GARY" <gcotterl@co.riverside.ca.us wrote in message news:1b35101c23039$6b8654f0$3aef2ecf@TKMSFTNGXA09... How do I convert packed numbers (i.e., comp-3) to decimal numbers? |
| 2816 | Hi David, Gary, One could have a hex dump printfile downloaded, for example -- Regards, Niek Otten Microsoft MVP - Excel "" <dmcritchie@msn.com wrote in message news:O1F0qoKMCHA.1468@tkmsftngp13... Hi Gary, I'll not admit to working on COBOL for 30 years but I doubt that you have packed decimal data on a PC. If you really have packed decimal numbers from an IBM mainframe you would have garbage. If you have them on a PC implementation of COBOL, I doubt that the PC implementation of COMP-3 is really packed decimal. I expect that COMP-3 on a PC would be same as Binary. Am curious if COMP-3 was ever accepted into the standard. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Niek Otten" <nicolaus@xs4all.nl wrote in message news:OG$a9YJMCHA.1724@tkmsftngp08... with the HEX2DEC() function. If not available, ToolsAdd-ins, check Analysis Toolpak. -- Regards, Niek Otten Microsoft MVP - Excel "GARY" <gcotterl@co.riverside.ca.us wrote in message news:1b35101c23039$6b8654f0$3aef2ecf@TKMSFTNGXA09... How do I convert packed numbers (i.e., comp-3) to decimal numbers? |
| 2961 | 65536 rows is the limit. It can not be expanded. If it is a text file, you can find an example at Chip Pearson's side of how to read in across 2 worksheets. His site is Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "Patsy Rogge" <patsyr@vippayroll.co.za wrote in message news:#riOG7jMCHA.1236@tkmsftngp08... I have a spreadsheet which I would like to import into Excel. The only problem is it has 80 000 lines and Excel only has 65 000 lines. Can an Excel spreadsheet be expanded to import more than 65 000 lines Thanks Patsy |
| 2987 | "MJP" <valakan@hotmail.com wrote... Excel does have some statistical functions like correlate, mean, etc but are there any add ins or accessory programs that can make excel do more of the stats stuff like scatterplots, Histograms, Box charts and so on? Any ideas? Scatter plots and histograms - yes. Box charts, no. Excel comes with an add-in called the Analysis ToolPak that includes various data analysis tools. However, these were not well written - there are several known bugs in the calculations and some really annoying shortcomings. But to top it all off, Excel doesn't provide missing data capabilities like real stats software. If you want to do real stats, get a real stats package. GNU R and GNU Plot, both available in Win32 ports, are capable and freely available. |
| 3002 | I sent an Excel file to a friend with some VBA I wrote in Excel 5c. He's using Excel 97. ( I tested on my Excel 97 before sending) He emailed me back... ----------------------------(email clip start)---------------------------------------------------- I could not run, kept getting error messages, both with enable macros and disable macros, error messages are; 1. This program has performed an illegal operation and will be shut down. If problem persists, contact the program vendor. 2. Could not register the Visual Basic Environment. Please Run Setup. 3. An error occured initializing the VBA Librarys (1157) Details are EXCEL caused an invalid page fault in module <unknown at 0000:4d001940. Registers: EAX=00000002 CS=017f EIP=4d001940 EFLGS=00010246 EBX=00000000 SS=0187 ESP=00629f94 EBP=00629fa4 ECX=d27acc60 DS=0187 ESI=6509f094 FS=11c7 EDX=c00301b0 ES=0187 EDI=650a00c0 GS=0000 Bytes at CS:EIP: Feels like old times, Ha ha ha. I'm running Excel 97 SR-2. ----------------------------(email clip end)---------------------------------------------------- I emailed him a version saved as Excel 5/97 for him to try, but.... ----------------------------(email clip start)---------------------------------------------------- Same problem, This program has performed an illegal operation and will be shut down. If the problem persists, contact the program vendor. EXCEL caused an invalid page fault in module <unknown at 0000:4d001940. Registers: EAX=00000002 CS=017f EIP=4d001940 EFLGS=00010246 EBX=00000000 SS=0187 ESP=00629d94 EBP=00629da4 ECX=d27739d0 DS=0187 ESI=6509f094 FS=0f27 EDX=c00301b0 ES=0187 EDI=650a00c0 GS=0000 Bytes at CS:EIP: Could not register the Visual Basic Environment. Please Run Setup. An error occured initializing the VBA Libraries (1157) This leaves me in Excel, Under "Tools/Visual Basic Editor", I get the same errors. Under "Visual Basic/Tools", my only option is Source Safe. ----------------------------(email clip end)---------------------------------------------------- The file uses only the add-ins that came with Excel. It needs both the ToolPaks to run right. Other than that, my only guess (and you know how this is) is that his Excel is missing some files. I am using Win98se. I believe he is too, if that makes any difference. Anyone know what could cause this and how I can fix it? -Ray |
| 3232 | Does anyone have a simple method for converting an Excel 4.0 macro (.xlm) to a vba macro or add-in for Excel 2000? (I am not an Excel expert) |
| 3300 | Sorry, but the only way to make the conversion is manually. In many cases, there is really no need to convert the XLM macro. Excel still supports that macro language. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "George Brink" <brink@aurum.wits.ac.za wrote in message news:1027678958.203398@nntp... Does anyone have a simple method for converting an Excel 4.0 macro (.xlm) to a vba macro or add-in for Excel 2000? (I am not an Excel expert) |
| 3321 | Dave, the following works if you used a combobox created via the VB toolbar sheets("sheet1").ComboBox1.Value If you created the combobox via the Forms toolbar, then right click on the box, select format control, and in the cell link box, specify a cell. This returns the number of the item in the list that was selected. Use this to lookup the value. Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "Dave Hammond" <dhammond@meridiansds.com wrote in message news:1027695608.7255.0.nnrp-14.3e31d733@news.demon.co.uk... hi I need to be able to get the string of the selected item from a drop down menu, what is the correct syntax for doing this, Excel2K don't provide many clues. Dim strSelectSheet As String strSelectSheet = ActiveSheet.Shapes("Drop Down 7").??????? Cheers David. |
| 3335 | dim newN newN = range("a1").value Worksheets.Add activesheet.name = newN will add a new worksheet and name it the name of the contents of A1 of the activesheet before creating the new worksheet. Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "gabrielrms" <gabrielrms@aol.com wrote in message news:19e701c2359b$5e590310$9be62ecf@tkmsftngxa03... i would like a macro to read a text string in a specific cell, and use that string as the name for a new worksheet. ie: the macro would read the string at $A$1, and create a new worksheet in the same workbook using the string as it's name. thank you |
| 3342 | Or Worksheets.Add.Name = [a1] -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Bob Flanagan" <noreply@noreply.net wrote in message news:OktVBCaNCHA.436@tkmsftngp11... dim newN newN = range("a1").value Worksheets.Add activesheet.name = newN will add a new worksheet and name it the name of the contents of A1 of the activesheet before creating the new worksheet. Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "gabrielrms" <gabrielrms@aol.com wrote in message news:19e701c2359b$5e590310$9be62ecf@tkmsftngxa03... i would like a macro to read a text string in a specific cell, and use that string as the name for a new worksheet. ie: the macro would read the string at $A$1, and create a new worksheet in the same workbook using the string as it's name. thank you |
| 3474 | I have 3 different sheets in the same workbook. Is it possible to create a scenario using data from each of the three different sheets? If not is there an add-In that I can use? |
| 3481 | If it is groupwise, XL2000: Close Button Is Dimmed After Groupwise Is Installed /default.aspx?scid=KB;EN-US;Q260199& Thought it would make Jen's life easier. Murthy wrote: Jen, Do you have GroupWise installed on your machine? If so, check our Microsoft's site to get rid of the add-ins that were installed at the time of installation of GroupWise. I think the keyword "Groupwise" will fetch you the article from KB. -- Regards, Murthy "jen" <jenmar@rocketmail.com wrote in message news:e9d9ee93.0207290753.2685bae3@posting.google.com... prompts me to save the document, which i don't want to do sometimes. also, the exit "X" at the top right corner is not bolded, not allowing me to exit by click on it. any advice? -- Dave Peterson ec35720@msn.com |
| 3520 | Instead of putting the macros in each file, put the in an Excel add-in workbook. And, write code that creates a new menu with items to run the various macros. This way, if you modify the macros, all the users have to do is download the new release and install. You can convert a regular workbook to an add-in workbook by changing the workbook add-in property in the VB editor. The menu will be needed as add-in macros are not visible by Tools, Macro, Run. Bob Flanagan Macro Systems -ins.com Productivity add-ins and downloadable books on VB macros for Excel "AP" <APhang@whispertech.co.nz wrote in message news:iim19.252$wn1.65224@news02.tsnz.net... I have created an Excel file that has many built-in macros. This file is the "template" for our inhouse documentation i.e. when someone wants to create a new document, they simply duplicate this "template" file and start editing the duplicate. At present, if I want to make any changes to the macros, I have to edit not only those of the "template" but also the ones inherited by the "clones". I would appreciate it someone could suggest a more practical way of managing this. Thanks, AP |
| 3580 | Many people asked me to make the add-in directly available due to some form processing glitch on the yahoo site. Please find the direct link below: ------------------------------------------------------------ I originally wrote this Excel add-in to address some of the pet peeves of MS Excel, like error handling within a cell. This add-in is convenient to use as it adds cell-level functionality using the right click of the mouse. It also has undo capabilities! Please feel free to use it and provide feedback! If my time permits, I would like to add more features based on user feedback. ALLRIGHT!- A Right-Click Menu Utility for Microsoft Excel 97-2000 Features - Adds powerful functionality where you need them at the cell or worksheet tab level - SMART formatting - Text formatting only affects the text, no overwriting of formulas - Add Error Trap feature enables the user to add IF ISNA/ISBLANK or ISERROR conditions to cell formulas - No annoying questions - Are you sure? - but UNDO is available - Fast! To register: /gygulyas/index-page3.html To download directly: /gygulyas/index-page4.html I would appreciate any feedback. Gyula |
| 3605 | Star I have no idea what you are asking. What do you mean by "a scenario"? Be more specific about what you want to do. HTH Otto "Star" <chona@sos.ca wrote in message news:1ffe01c23742$ab499290$a4e62ecf@tkmsftngxa06... I have 3 different sheets in the same workbook. Is it possible to create a scenario using data from each of the three different sheets? If not is there an add-In that I can use? |
| 3623 | Otto, Under Tools, there's a Scenarios... control. I've never used it (until now!), but when I tried to include cells from a different worksheet (using A1,C2,sheet1!a1, excel yelled that the "reference is not valid"). I tried giving that cell on another worksheet a range name, and xl still yelled. So on the basis of 37 seconds of testing (xl2002), I'd say scenarios "changing cells" box is limited to the same sheet. I'll defer to someone else on the addin stuff? (And I'll gladly accept being wrong on the first part if there's a workaround--or I just did it wrong!) Otto Moehrbach wrote: Star I have no idea what you are asking. What do you mean by "a scenario"? Be more specific about what you want to do. HTH Otto "Star" <chona@sos.ca wrote in message news:1ffe01c23742$ab499290$a4e62ecf@tkmsftngxa06... I have 3 different sheets in the same workbook. Is it possible to create a scenario using data from each of the three different sheets? If not is there an add-In that I can use? -- Dave Peterson ec35720@msn.com |
| 3863 | Hi, AFAIK one can distribute and sell MS Office based solutions without permission of MS, provided one does not distribute (part of) the installation files of Office themselves. So a set of workbooks/add-ins you have produced can be sold. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- I have a family company which has developed an Excel based application which we would like to sell to a small group of customers. We would license the model which is in Excel format and requires an existing licensed version of Excel to operate. Can anyone tell me whether this is permitted by the Excel license or where I need to go to get an official answer? The key point is that we are not selling Excel itself, just the functionality of the model. Barry Knowles . |
| 3865 | Excel wont open my xls-file. I installed the Solver add-in. When trying to use Solver there came an error message that Solver could not perform the calculation, which it couldn't (after I closed and opned the computer it now works). When saving the xls-file I received another error message stating something like the file format was not correct or supported(?) and that I should remember to save in the correct format. I then saved the file as usual and closed the computer. When trying to open the file I get the message "Unable to read file". The file is saved as and show up as an xls-file. (I do not have any back-up of the file so I am in deep ....) |
| 3875 | Thanks Hank, Will do as you suggested. I'm new to database so need to learn a lot very quickly. Regards Geoff Google (and Rob Bovey) are your friends. See: /groups?safe=images&ie=UTF-8&oe=UTF-8&as_usubject=Error%20%28TBRun%209%29%20at%20startup&lr=&hl=en in which Rob stated: " This problem is caused by a malfunctioning Excel add-in installed by the TextBridge OCR software that came with your scanner. Use Windows Explorer to search your hard disk(s) for TBRun9*.* and remove any files you find that match that description. This should solve the problem for you." I am also trying to convert the spreadsheet to Access for use on my Visor but there is no convert to Access button anywhere to be found in Excel, or anything I can see in Add or Remove Features in Maintenance Mode for Office 2000 for converting. As for THIS part of your problem, you don't export to Access from Excel. That's because there's a whole lot of database schema stuff inside an .mdb that Excel can't do for you. Rather, you open Access, create a new database if necessary, then go to the file menu, Import, and bring in the saved Excel workbook, usually as a new table. Just follow the Wizard's prompts. --------------------------------------------------------- Hank Scorpio apolloXVIII@ozemail.com.au Change XVIII to 18 for real address. * Please keep all replies in this Newsgroup. Thanks! * |
| 3898 | Hello! I have a workbook with 3 sheets of information. I am wondering if there is a way I can create a scenario that would change cells on each of the 3 sheets. I realize that the basic scenario feature does not allow you to do this. What I am really asking is if anyone knows of a workaround or add-in that will give me this ability. thanks, Frances |
| 3984 | Using Advanced Filter, as suggested to you, is quite adequate for the task. A formula-based approach that might be of interest is: =INDEX(UNIQUEVALUES(A1:50,1),ROW(1:1)) if you install the morefunc add-in, downloadable at no cost from: /english/index.html Copy down the formula as far as needed. Aladin "Ron" <rlkaufman@lucent.com wrote in message news:042701c23a32$f5a34910$39ef2ecf@TKMSFTNGXA08... Hello, I am trying to perform a unique sort. That is, if I have the data 2,4,1,3,4,1 my output should be 1,2,3,4. I know how to do this in VBA code but I was wondering if there is an Excel or VBA function that can perform this task more directly. thanks Ron |
| 4045 | This is posting 9 of Frequently Asked Questions for the Excel newsgroups microsoft.public.excel.misc, microsoft.public.excel.programming and microsoft.public.excel.worksheet.functions. Topics are: The Excel application and Excel files Worksheet functions and formats Dates and times Macros, VBA functions I didn't find my answer here, now what ? Good resources on the web Collected by Harald Staff, Microsoft Excel MVP. This FAQ can also be found at Debra Dalgleish's website /xlfaqIndex.html -it's worth a bookmark. If you reply to this posting, reply to a single group and quote as little as possible. ************************************************* **** The Excel application and Excel files **** * When I start Excel, why do a million files open up automatically? Menu Tools Options General has an entry for "Startup directory", and all files there will be loaded when Excel starts. Alter or remove this entry. * When I start XL, I get the error message "Compile error in Hidden Module" An add-in with a programming error is bothering you. 1 Tools, Add-ins 2 note which are checked 3 uncheck all (but one) 4 restart XL 5 if no error, check the next one and repeat from step 4 Got the error? uncheck the one you last checked. If no luck, see if there's an .xla file in the startup directory (see previous tip). Scanner software typically place itself there, sometimes with code errors like the one in question. Not an add-in? It will be a hidden workbook then. 1 Try (in Excel) Windows, Unhide and write down which files are listed. 2 Unhide them. 3 Now go to the VBE (press Alt-F11). 4 On the left side there should be the project explorer. 5 Select the first project you unhid 6 From the menu choose Debug, compile 7 No Errors? select the next project listed, repeat 6. * Why do the column headers show numbers instead of letters? How do I change my column headings so they are back to alpha letters instead of numbers ? Tools / Options / General / Settings / R1C1 Reference Style (uncheck) * How can I change the color of the sheet tabs? Excel 10 (XP) is the only version that can color sheet tabs. * I want to lock in my Title Row and keep it there Visible while I scroll down to see the rest of my data. Any ideas? Assuming title row is 1, select A2 then WindowFreeze Panes. * I need more than 256 columns and/or 65536 Rows. Excel has no more. Quattro Pro v9 has 1 million rows and 18278 columns. * I just began to design an intricate spreadsheet and after over an hour got an error message in Excel - which then closed automatically. Unfortunately, I did not save my file. Excel did not automatically recover the file. Is there anything I can do to get it back? No. There should be an Autosave add-in in Tools Addin menu that you now might consider start using. (But then again, you may not want to overwrite an existing file with every little test you do in it, so be careful). Jan Karel Pieterse has an add-in Autosafe.zip downloadable from www.bmsltd.co.uk/mvp. See also 's /dmcritchie/excel/backup.htm for more on backup and recovery. Finally, Excel XP has great backup and recovery tools, so upgrading is a good future solution to those problems. * All of a sudden a number of my Excel 2000 files have become "read-only". Clear out c:\windows\temp directory on the machine that houses the files, reboot. * I have an excel file that I use every day at work. Some time ago, mysteriously, the file began opening two copies of itself every time I double-click the icon. If I close one of the copies, both close. Any changes made to one copy show up in the other. This sounds like you just have two windows open that are displaying the same workbook. You can close the extra window by clicking the lower of the two X buttons in the upper right corner of the screen (don't click the top X button, as that will close Excel). Then save the workbook. * When I type a number; example 44 it becomes 0.44 even if I format the cell without decimals. Goto Tools/Options/Edit and uncheck the fixed decimals checkbox. * When I click on an Excel file to open it, the file opens but an error message is displayed that says a file with that name is already open and that I can not open two files open with the same name. If you are sure it's not really happening -you may have Book1 open and then attempt to open a Book1 from another folder- then try re-register Excel. click startrun and "C:\Program Files\Microsoft Office\Office\Excel.Exe" /regserver (include the quotes) adapt to fit your path. Other things to look at: Tools=Option=General Tab, make sure "Ignore Other Applications" is not checked * Why does Excel say my file has links, when I know it doesn't? Links come in several flavors; linked formulas, defined names (Insert Name Define menu), objects (buttons and stuff) assigned to remote macros, ... You might find the FINDLINK.XLA program useful - you can get it from Stephen Bullen's web site: * Is there a way to allow the use of autofilter on a protected worksheet ? This needs a macro to run first: Sub Protect_keep_filter() With ActiveSheet .EnableAutoFilter = True .Protect DrawingObjects:=True, _ contents:=True, Scenarios:=True, UserInterfaceOnly:=True End With End Sub Note that the .enableautofilter has to be reset each time you open your file. (It's not persistent between closes.) * How many worksheets I can put in a workbook? It is not a limit per se, but of course there is a practical one which depends on the computer resources.. * How many Characters can be placed in a Cell? In Excel 97 and above, you can have up to 32K characters in a worksheet cell, but only the first 1000 or so can be displayed (the exact number depends on your font and display characteristics). * I've lost my password ! Yeah sure. Well, there are passwords and there are passwords. One is to open the file, one other is to unprotect the spreadsheet, still another is to unprotect the workbook and yet another one is to unprotect the macro code. File and VBA passwords can not be cracked by a "normal macro", workbook and worksheet passwords are fairly easy. A search for "excel password" at / will find both commercial and free solutions of varying quality and brutality. * Can anyone advice how to protect an Excel file (and associated code) from un-authorized copying and/or create time limited functionality ? There is no fool-proof method for this, and most solutions limit themselves to require macros enabled on opening. This said, creating your own system is a fun challenge allowing lots of "evil creativity". * When I try to open XL, it freezes and all I can do is reboot * Excel crashes on me regularly, what can I do * EXCEL caused an invalid page fault... * Illegal Operation Error when starting Excel To-Do List: Try opening Excel without any addins or hidden workbooks: Start, Run, "C:\Program Files\Microsoft Office\Office\Excel.exe" /Automation Note you may have to change the path. If your error disappears, there is either an add-in or a (hidden) workbook that is loaded upon XL's start, which bothers you. - Try locating the XLSTART directory, move everything from there. - In XL: Tools, Addins, note the ones checked and uncheck them one at the time, each time restarting XL - In XL, Tools, options, general. Check if a path is entered after "Alternate startup file location". Clear it. Another option is to open XL in Safe mode: "C:\Program Files\Microsoft Office\Office\Excel.exe" /Safe Also, you might try: "C:\Program Files\Microsoft Office\Office\Excel.exe" /Regserver Yet another possible problem is a corruption of your toolbar customisation file. Locate all files with extension .xlb and rename the extension(s) to something like .old .Now try and start XL again. Finally, this is what MS has to say: /default.aspx?scid=kb;en-us;Q280504 **** Worksheet functions and formats **** * How can I protect a formulas from being deleted or changed? Select all cells that users ARE allowed to change. Go menu Format Cells Protection and uncheck Locked. When done, protect the worksheet in menu Tools Protection Protect worksheet. * How can I enter the date into a cell so it doesn't change every day? Press Ctrl ; (that's holding Ctrl down while pressing semicolon.) Ctrl : will enter the time. * I have to enter the Expenses and to select from some criteria. That means, the user can only enter 'DHL', 'FEDEX', 'UPS'. In other word once the user move into the corresponding cell under expenses it will popup a combo box with the above 3 Companies and user will select one. Select the region you want to apply this to, then select menu item Data/Validation. In the Allow dropdown, select List. In the Source textbox, enter "DHL,FEDEX,UPS" (without the quotes) * Is there a way to create a formula that will do this type of function =IF(D25 DOES NOT EQUAL E25 THAN D25 FONT WILL TURN RED) ? Try Format=Conditional Formatting: Select D25. Click on FormatConditional Formatting. Set Condition 1 - Cell Value Is, not equal to, click on E25 on the worksheet. Click on Format, select the Font tab. Under Colour, choose red. OK, OK. * Is there a way to enter a formula that will round a value to the nearest increment of 5? =ROUND(A1/5,0)*5 * or to the nearest quarter ? =ROUND(A1/0.25,0)*0.25 * I am looking for a formula that will look at an adjacent column then find that value in another sheet and return the data from an adjacent column. Try VLOOKUP. =VLOOKUP(A2,Sheet2!A2:B100,2,FALSE) * I want to add the largest/smallest 5 entries in A column. =SUM(LARGE(A:A,{1,2,3,4,5})) * I have data stored in rows and I want to change these rows to columns Select the data, copy it, select where you want it, do editpaste special, check the transpose option, click OK * How do I pick 20 random items from a list of 100 ? Enter the items down A1:A100. In B1:B100 enter formula =RAND(). Sort the list by B column; top 20 rows is your selection. Press F9 for new B numbers and repeat for a new selection. * Is it possible to write a SUMIF worksheet formula to sum visible cells only? If the cells are hidden as a result of AutoFiltering, you can use the SUBTOTAL function, with 9 as the first argument * I can sum all numbers =10 with =SUMIF(A1:A20,"=10") . But how do I enter two criterias so I can sum numbers between 5 and 10 ? That equals sum of all =5 minus sum of all 10: =SUMIF(A1:A20,"=5")-SUMIF(A1:A20,"10") Or you can use this method: =SUMPRODUCT((A1:A20=5)*(A1:A20<=10)*A1:A20) * Using DataSubtotals, I would like to create a table that has just these subtotals, not the hidden detail rows. 1. Once you've applied the Subtotal, collapse the outline so that only the rows you want to copy are visible. 2. Select the cells. 3. Choose EditGo To, click the Special button 4. Select 'Visible Cells Only', click OK 5. Click the Copy button 6. Go to another sheet, and paste * When I use AutoFilter I don't see all the items in the drop down list. Why not? An AutoFilter dropdown list will only show 1000 entries. You could add a new column, and split the list into 2 or three groups, e.g.: =IF(LEFT(C2,1)<"N","A-M","N-Z") Filter on this column first, then by the intended criteria. Another option is to choose Custom from the drop-down list, and type the criteria. * In a cell I have "lastname, firstname". I want to put lastname in one cell and first name in another. Use DataText to columns and specify the comma as a delimiter. * How can I prevent hyperlinks from appearing when I type an email address? You can turn that option off in Excel XP only. All versions: Select the cell and press Ctrl+Z, this will convert the hyperlink back to text. The code below, when run on a selection, will also delete the hyperlinks. Sub delHyperlinks() Dim myCell As Range For Each myCell In Selection myCell.Hyperlinks.Delete Next myCell End Sub * When I import information from my merchant account the field which holds the 16 digit credit card information does not display it properly. Excel only maintains 15 significant digits for numbers and cannot be used for credit cards which require 16 digits. If you don't need to do math on them, input long numbers into a range that you've formatted as text. Both above -and also entries automatically converted to dates- can be prevented by entering a single ' before the actual entry. Excel will now treat the cell as pure text and change nothing. * Why does my function display #NAME? The function may point to an add-in function that is not avaliable to this Excel. Most frequently it's an Analysis Toolpack function; go menu Tools Add-Ins and check that there are checks against Analysis Toolpak. Unlike Excel's built-in functions, Add-in functions do not translate themselves to regional language, so american add-in functions are by default unavaliable on a Norwegian computer and vice versa. * Is there way of returning the name of a sheet in a cell without using code? =CELL("Filename",A1) returns the complete file path and sheet name =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) will stip away everything but the sheet name. The file must be saved or the formula will not work. * My spreadsheet does not calculate correctly ! I'm right, Excel's wrong ! There are three common causes for messages like this. 1. What is displayed in the cells is not what's really there. A cell can show 1 (no decimals format) but it may well contain real or calculated values like 0.6 or 1.4. Add or multiply a bunch of those and you're surprised; Excel will calculate with real cell contents, NOT displayed contents. You may choose "precision as displayed" in the tools options menu for a workaround, but make sure you know what you do. 2. A computer use binary numbers, and this has its limitations. It can not represent numbers like 1/10 exact. Numbers like that are rounded to nearest 15 significant decimal digits, and Excel will be "wrong" around 15th-16th digit. Some operations suffer from this, and some boolean tests (tests that may appear as 0.1=0.1) can return False because of this. "Normal work" like sensible-number budgetting and day-to-day math is usually not affected, but this may not be the tool for advanced science. 3. You are using Excel's statistical functions. Some of those are not good enough. LINEST() (also SLOPE(), INTERCEPT(), VAR(), STDEV(), LOGEST(), TREND(), FORECAST(), etc.) uses a numerically unstable algorithm. You're right, Excel's wrong. Links to backgrounders and tools at /Excel/Statistics/ * My spreadsheet does not calculate at all ! Calculation is set to Manual, alter this in Tools Options menu. International issues: These functions are in english, and you can not enter them i a Swedish Excel as is. Run this macro: Sub EnterEnglishFunction() ActiveCell.Formula = InputBox("English function:") End Sub paste the function in and OK, and in most cases it translates. **** Dates and times **** Very very many Excel questions are about dates and times. Chip Pearson's webpage /excel/datetime.htm will give you understanding of how this works in Excel , and it has lots of useful samples. Here are a very few common questions: * How do I add times together ? Just add together just like any number (=A1+A2+A3). Use custom number format [h]:mm in the result cell to prevent rollover at 24 hours * How do I subtract time? Just subtract, like =B1-A1. Use Calendar format 1904 if you need to display negative results; Tools/Options/Calculation and check the 1904 Date System checkbox. Note that 1904 calendar will offset all dates by 4 years 1 day, so be careful. * I need to calculate a column with hh:mm (formatted for TIME) against a hour rate. So 0:45 minutes needs to be calculated against 120 per hour - with an answer of 90. Now it says 3.75 ??? 1 is a day; 24 hours. So one hour is 1/24. =A1*B1*24 will bring the desired result. Format result cell as currency or number (it tends to pick the date format by default). * When I copy-paste dates, they end up one day wrong. * When I copy-paste dates, they end up four years wrong. One workbook is using 1904 calendar, the other one 1900 (in the first example, years are not displayed, but definitely present). Go menu Tools/Options/Calculation and make them equal, preferrably also correct if you know what the dates was supposed to be. * When I try to sum the time data in the format: 5:20, 12:02, 20:12 etc. I get the value that is the real sum minus N*24, eg. 2:07 instead of 50:07. Use custom number format [h]:mm to prevent rollover at 24 hours * I'm adding up a large number of cells with seconds in them, i.e... 25, 50 47, etc... the result I would like is 1:10, 1:50: 2:03 Since XL stores times as fractions of days, in order to convert integers into times you need to divide the sum by (24*60*60) or 86,400 * How do I add 3 months to a date ? =DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) * Could someone give me the series of worksheet functions that would emulate EOMONTH; last day of month? The last day of the month equals the zero'th of next month for some strange reason: =DATE(YEAR(A1),MONTH(A1)+1,0) * Excel thinks 1900 is a leap year. It's not. Yes it does and it's not. **** Macros, VBA functions **** * I have a user defined function that doesn't recalculate. Include all the cells that your UDF depends on in the argument list. Or enter this as the first statment in your Function: Application.Volatile This will cause the function to be executed whenever a calculation occurs in the workbook. * All of a sudden, when I open the file, it asks if I want to "Enable or Disable a Macro". There are no macros in this workbook. A macro has been added and then removed, leaving an empty module. Open the file, right click on a sheet tab and choose View Code. Look for modules and delete them. Empty modules trigger the macro query, as does an actual macro. While there, make sure all other object's modules are completely empty. *When a button is drawn onto a sheet the assign macro is not displayed. When right-clicking on the button the "assign macro" context menu item is no present. There are buttons and there are buttons, from the Forms toolbar or from the Control Toolbox. If "assign macro" is no option then it's the second kind. Choose "View code" and call your macro from it like this: Private Sub CommandButton1_Click() Call Macro1 End Sub * Some of the functions that we want our macros to accomplish are not possible without first unprotecting the worksheet/workbook with the password. Worksheets("MySheet").Unprotect password:="drowssap" 'your code here Worksheets("MySheet").Protect password:="drowssap" Be sure to protect your macro code to hide the sheet password. * I want Excel to run this macro automatically every time the Excel file is opened. Place the code in (or call it from) the Workbook_open event of the ThisWorkbook module in the VB editor. Or simply name your macro Auto_Open. If you choose to use both then Workbook__open will run before Auto_open. Auto_open will not run if the workbook is opened by another macro, you must use the RunAutoMacros method. Contrary; Workbook_open will run if the workbook is opened by a macro, you must use Application.EnableEvents = False to prevent it. * I want to add a toolbar to my spreadsheet that when clicked, brings up Userform1. Assing the toolbar button to this macro, which should be in a standard VBA module: Sub ShowForm () Userform1.Show End Sub * I want to show a userform each time my file is opened. Combine the two solutions above: Private Sub Workbook_Open() UserForm1.Show End Sub or Sub Auto_open() UserForm1.Show End Sub See Chip Pearson's /excel/events.htm for detail and many more useful events. * Can I ask my user for confirmiation before executing the macro ? Sub AskAndDo() If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = _ vbNo Then Exit Sub 'Code goes here instead of MsgBox "Actions here" End Sub * Can I have my Macro make Excel NOT to ask "the file already exists, do you want to overwrite" type of questions ? Application.DisplayAlerts = False 'code to save, overwrite, delete, whatever goes here Application.DisplayAlerts = True * Is it possible to call a macro from the condition true or false side of a worksheet formula? ie. if(a2="ok",Run macro1,run macro2) Basically, the answer is No. You can write functions in VBA that you can call from worksheet cells, but these functions can only return a value. They can't modify other cells or alter any part of the Excel environment. (See next Q for a macro solution to the problem) * How do I run a macro everytime a certain cell changes it's value? There is an event called Worksheet_Change which is triggered when a value is entered (it will not fire when a formula result changes). One of the arguments to this event is 'Target' which is a reference to what changed. Since this event will occur whenever a value changes - you can use the target to see if it is the cell you are interested in: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub Else 'The cell you are monitoring has changed! 'Do whatever you need to do... End If End Sub * How do I find the first empty cell in A column ? If ActiveSheet.UsedRange.Count < 2 Then MsgBox 1 Else MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row End If * How do I find the cell below the last entry in A column ? MsgBox Cells(65000, 1).End(xlUp).Row + 1 (This will return 2 on an empty A column) * How do I find the last row in my spreadsheet ? MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row * I want to loop through all selected cells and perform an operation on each of them. Sub DoOnSelection() Dim oCell As Range For Each oCell In Selection oCell.Font.Bold = True Next End Sub * I want to loop through all worksheets and perform an operation on each of them (unprotecting or whatever). Sub AllSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets MsgBox ws.Name Next End Sub * I want to loop through all workbooks in a folder and perform an operation on each of them. Sub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile < "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End Sub * How can I tell if a file exists in a spesific folder ? Function bFileExists(rsFullPath As String) As Boolean bFileExists = Len(Dir$(rsFullPath)) End Function * How can I tell if a spesific workbook is open ? Function bWorkbookIsOpen(rsWbkName As String) As Boolean On Error Resume Next bWorkbookIsOpen = Len(Workbooks(rsWbkName).Name) End Function * I want to let the user select a file within my macro. Sub SelectWebPageToOpen() Dim ThePage As Variant ThePage = _ Application.GetOpenFilename("Webpage (*.htm*), *.htm*", _ , "Pick one:") If ThePage = False Then MsgBox "You cancelled" Else MsgBox "Do something with file " & CStr(ThePage) End If End Sub * I want to let the user enter a "Save As" location in my macro. Sub SelectSaveFileName() Dim TheFile As Variant TheFile = Application.GetSaveAsFilename("C:\Temp\File.xls", _ "Workbook (*.xls), *.xls", , "Your choice:") If TheFile = False Then MsgBox "You cancelled" Else MsgBox "Do something with file " & CStr(TheFile) End If End Sub * Is there a way to hide the process of executing macro? Application.ScreenUpdating = False 'code here Application.ScreenUpdating = True *Is there a way to protect the macros I create so people can't see or alter them? Go to Tools VBAProject properties, lock the project for viewing, and enter a password. * How can I unprotect a VBA project using code ? You can not. A workaround is to simulate keystrokes with the SendKeys method. * How do I close a file/close Excel with a macro ? ActiveWorkbook.Close savechanges:=False 'true ??? will close the active workbook Workbooks("mywkbk.xls").Close savechanges:=False 'true ??? will close mywkbk.xls ThisWorkbook.Close savechanges:=False 'true ??? will close the workbook that holds the code that's running. Application.Quit will close all of Excel. Be careful with this one. **** I didn't find my answer here, now what ? **** First, do a search on /advanced_group_search to see wether a similar question has been answered before. Use *excel* in the newsgroup field. See 's /dmcritchie/excel/xlnews.htm for more on archive search. If no luck, post a question. Please follow these guidelines for a best possible result: * Tell us what versions of Excel and Windows you're using * Use an illustrative subject line, not "Excel problem" or "Help" * Don't post to more than one group. People don't like wasting time helping you if your problem is already solved elsewhere. * If you've already tried using some formulas or VBA, include what you've already tried. You may be very close. * Please don't ask us to email and don't follow up answers by direct email unless you are invited to. * Finally; do not attatch files. Attachments are frowned upon for a variety of reasons: - newsgroup Bloat. - download time. Many (most?) people pay their phone company a per-minute rate for connections. - virus concerns. Many won't or aren't allowed to open such files. - they takes up space on individual hard drives - they are not stored on newsgroup archives. - Take the time to explain your problem. Who knows, by writing out the question, you may even figure it out yourself. This list is condensed from Chip Pearson's webpage /excel/newposte.htm. **** Good resources on the web **** There is a very good Excel functions workbook by Peter Noneley at /noneley/ . Recommended. Comp.Apps.Sprreadsheets FAQ is located at /faqs/spreadsheets/faq/ .That one's stuffed with good links, some may be too old though. There are many good Excel webpages, and MVPs' / Frequent posters' signature addresses are all worth a visit. Instead of creating yet another links collection, let's just say Start Here: -walk.com/ss/excel/links/index.htm Finally: * What is an MVP and which exams do I take to become one ? MVP is an award that Microsoft give those who help people with using MS products and do it well. So stay here and provide lots of brilliant answers, then see what happens. There are no other exams than "practice, practice, practice". The MVP program is presented at / |
| 4179 | Gary, - When you attach files it uses up everybody's resources such as download time that might need to be paid for, and storage space. And your file could contain a virus. This sample begins at A1 (A1 = 1, C1 = 2): A B C D 1 2 2 4 3 5 4 6 5 9 6 10 7 11 8 12 9 15 10 16 11 17 12 13 14 15 16 - You can try the add-in "Refinate" to do what you want for free. Turn off Refinate's Sort/Duplicates option. Select the numbers in column C and click Refinate's Get/Test button. Put the cursor at A1 and apply Refinate's Subrtact-Dsply-V from the drop-down action list. - Then select the numbers in column A and click Get/Test. Move the cursor to C1 and apply Subrtact-Dsply-V from the drop-down action list. Only 17 will be placed in column D since all other numbers in C are also in column A. It will look like this, A B C D 1 1 2 2 4 3 3 5 4 6 5 9 6 10 7 7 11 8 8 12 9 15 10 16 11 17 17 12 13 13 14 14 15 16 - This works even if there is a mix of text numbers and number values. The new numbers in columns B and D will be text numbers. Select them and apply Refinate's Number-Value from the drop-down action list if you want to convert them to number values. --Brian Taylor Refinate (C)2001, 20 free trials. Click to: Rearrange Cells or Cell Content, Find Duplicates, Count Items, Import, and more. www.adetaylor.com "gary" <gcotterl@co.riverside.ca.us wrote in message news:08cc01c23db4$95ccf4c0$9ae62ecf@tkmsftngxa02... In the attached file, numbers 2, 4, 5, 6, 9, 10, 11, 12, 15 and 16 are in columns A and B. How can I eliminate these matching cells (from both columns) so only non-matching numbers remain? |
| 4238 | Worth checking. Uncheck the addin/close/re-open Excel to see if this is so. Regards BrianB -------------------------------------- "Jim Crawford" <jcrawford@alden.co.uk wrote in message news:<ue4g3uUPCHA.2048@tkmsftngp08... When I open Excel (Office 97) I get a message box come up with the following "This workbook is currently referenced by another workbook and cant be closed" It clears with the OK button and everything is normal. It appears after the special add-in libraries are loaded. These libraries are not the porblem as they have been around for ages. I have just started the autosave adin and wonder if that is related thoug |
| 4270 | You need to use a 3rd-party add-in that enables you to specify an arbitrary range for your data labels (a feature that, amazingly, Excel does not provide). You can download Rob Bobey's add-in here: /utilities/Labeler.asp Or download my "JWalk Charting Tools" add-in here: -walk.com/ss/excel/files/charttools.htm Both of these are free. John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "David Kelly" <david.kelly@hutchison3g.com wrote in message news:9af8cac1.0208070605.2620b53e@posting.google.com... I have created a mixed stacked bar and line graph and need to assign data labels to the information. However, as the bars are stacked I want only the total values. I have created a new set of info that contains only data label relevent data but I cannot plot this onto the graph. Any ideas? Dave |
| 4305 | Can anyone direct me to a Shareware to unlock the password on a VBA project in Excel? Thanks. -----Original Message----- Thank you for the quick response. I've installed the add- in below. It works, but it only removes the worksheet and workbook passwords. I still cannot open the macros and the Visual Basic editor. -----Original Message----- Try this add-inn /excel/password.html works for workbook and sheet protection -- Regards, Peo Sjoblom "Joseph Henry" <jhenry@olf.com wrote in message news:1a3701c23e4e$0c4561c0$3aef2ecf@TKMSFTNGXA09... I have my desktop PC with Excel 2000. I wrote a peace of macros in Excel to do a task. I've protected the Workbook and now, one year later, cannot remember the password. I don't know if I were sleeping and introduced some kind of special character or blank in the password I always use. I need to open that file for editing. Is there a way to do that or some kind of "open sesame" password. Because, I am thinking in terms of: if I am at the administrator level for that machine (which I think any PC owner should be), I should have been able to by-pass the password. Thank you, in advance, for your expert advice. . . |
| 4350 | In Excel 2000 I was able to make a PO system that used this numbering add-in to share numbers accross a network (stored in another excel file) - Now when I try to open it up in Excel 2002 it says I need this numbering add-in - I cannot find it any-where.. What is going on here? I upgrade and LOOSE features? HELP! -Michael |
| 4379 | The ODBC Add-In just appeared in my addin list. I just installed MapPoint 2002. Is this what installed this addin? Just currious. Jon |
| 4482 | Hi Kelly The message is probably due to a reference made to potentially a third party Excel Add-In that references to a control of some sort, that has not been installed correctly on your computer or you don't have the license to use that Excel Add-In. To view the current Excel Add- Ins, select from the menu bar Tools and the Add-Ins. A dialog box will open. Note all Add-Ins that have a 'tick' and uncheck them all. Restart Excel and if the error message does not appear you can add the Excel Add-Ins again (one by one) and find the offending Add-In. If this is not the case it will be a internal reference to a Microsoft control or object. Thus the object has misplaced its registry entry (License), easiest way to resolve this is to reinstall Excel. Best regards KM -----Original Message----- Each time we open our excel program (Microsfot office XP) this grey box with the heading "Microsoft Visual Basic" pops up and says that the object library is not registered. It allows us to click "ok" or go to "help" but we cannot find anything useful by choosing help. I was wondering how we could get rid of this problem- any suggestions would be greatly appreciated! . |
| 4530 | =SUBSTITUTE(SUBSTITUTE(A1,WMID(A1,-1,1,CHAR(92)),""),"C:","C:\NEW") WMID is part of Longre's morefunc add-in, downloadable from: /english/index.html Aladin "Ademar" <ademar@marketfx.com wrote in message news:e674auKQCHA.2724@tkmsftngp09... In column A I have a long list of file names and their paths: Row1 C:\Program Files\fileA.xls Row2 C:\Program Files\Folder\fileB.doc I would like a formula to use on column B that would result in the following: Row1 C:\NEW\Program Files\ Row2 C:\NEW\Program Files\Folder\ Basically it needs to add "\NEW" and remove the filename. Thanks, Ademar. |
| 4617 | Hi, Try opening Excel without any addins or hidden workbooks: Start, Run, "C:\Program Files\Microsoft Office\Office\Excel.exe" /Automation Quotes mandatory, Note you may have to change the path. If your error disappears, there is either an add-in or a (hidden) workbook that is loaded upon XL's start, which bothers you. - Try locating the XLSTART directory, move everything from there. - In XL: Tools, Addins, note the ones checked and uncheck them one at the time, each time restarting XL - In XL, Tools, options, general. Check if a path is entered after "Alternate startup file location". Clear it. Another option is to open XL in Safe mode: "C:\Program Files\Microsoft Office\Office\Excel.exe" /Safe Also, you might try: "C:\Program Files\Microsoft Office\Office\Excel.exe" /Regserver Yet another possible problem is a corruption of your toolbar customisation file. Locate all files with extension .xlb and rename the extension(s) to something like .old Now try and start XL again. Also, NAV has shown to cause "Excel caused an invalid page fault in module VBE6.DLL". You might try disabling the Office plug in of NAV. Finally, this is what MS has to say: /default.aspx?scid=kb;en- us;Q280504 Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- All of a sudden, my Excel programs will not open. . .any ideas?? I repaired, then reinstalled Office 2000 Premuium without luck. When I try to open Excel, my computer freezes. . . Please e-mail any advice. Steve . |
| 4830 | Thanks, the AutoSave AddIn is loaded automatically in our configuration. Any other ideas? -----Original Message----- Frank The autosave feature in XL2000, is provided by means of an add-in (Not too good a one at that). Therefore it will not perform as the 'built-in' versions in Word for example. Finally, in XL2002 (XP), the autosave is 'built-in' and performs flawlessly. Worth the upgrade if nothing else. -- HTH Nick Hodge Southampton, England Microsoft MVP - Excel nick_hodge@btinternet.com Excel XP\WinXP "Frank" <Frank.Brusky@parexel.com wrote in message news:1e4101c24300$ee444df0$b1e62ecf@tkmsftngxa04... Can anyone help me get Excel Auto Save to work? We cannot get it to autosave correctly. If an existing sheet is changed and Excel is interrupted, the sheet will not come back automatically. If a new sheet is interuppted before manually saving it, it is lost. In Word & PPT, the autosaved files appear automatically. Any help would be appreciated. . |
| 4876 | What you seem to call a parameter is a combination of relational operator and a value like "<=0.2", which is rather an expression. Let E1 house 0.2 and E2 130, the so-called conditions or criteria or parameters. The type of formula that you need is: =SUMPRODUCT((Margins<=E1)*(Sales<=E2)*Sales) If you insist on having expressions like: E1: <=0.2 E2: <=130 then use: =SUMPRODUCT((EVAL("Margins"&E1))*(EVAL("Sales"&E2))*Sales) The SUMPRODUCT formulas are rather costly compared to, say, SUMIF. Adding EVAL will make them more so. EVAL is part of the morefunc add-in, downloadable from: /english/index.html Aladin "cbs" <cbs@vianet.net.au wrote in message news:01c2440e$f90b6660$0a4ea5ca@laptop... Hello, I will be grateful for some assistance/guidance in the following issue: In A8:C25, there is a Products Table showing A B C Product ID Margins % Sales(000's) Margins in column B are shown as 15%, 20% etc. Range name is Margins. Sales (in 000's) in column C are sorted by margin. Range name is Sales. In a separate table, I have stratified the Products Table into: Margins <=20% and Sales<=$130, Margins=20% and Margins <=35%, and Sales <= $130 etc. I need a formula that will sum the sales that fit within each strata, without having to hard-code the parameters (e.g. <=.2) in the formula. The following formulas work when the parameters are hard-coded: =SUMPRODUCT((Margins<=0.2)*(Sales<=130)*Sales) =SUMPRODUCT((Margins=0.2)*(Margins<0.35)*(Sales<=130)*Sales) etc. These formulas do not work (G33 contains <=.2 and G34 contains <=130): =SUMPRODUCT((Margins=INDIRECT("G33")*(Sales=INDIRECT("G34")*Sales))) Results in #VALUE! or =SUMPRODUCT(((Margins)=INDIRECT("G33"))*((Sales)=INDIRECT("G34"))*Sales) Results in $0.00 I have looked at the Dsum function without success. TIA Mike |
| 4878 | Thank you for your prompt reply. I was endeavouring to avoid hard-coding expressions such as "=" in the formula itself, so I will look at the suggested add-in. , Mike |