Date Formula Microsoft Excel 97 2000 2002 free tutorial format function workbook worksheet sum report printing pivot table forms templates template Date Data CountIf Count VBA Macro excel formula subtotal filtering chart graph

Microsoft Excel Add-In

ArticleBody
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