add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
| Article | Body |
| 10 | Hi. I'm trying to add hours duration to a start time to get an end time. my worksheet has a column A with a start time (lets say 4:00 AM) , column B is hours worked (lets say 4.25 hours) I want column C to add 4.25 hours on to 4:00 AM. (in this case it would be 8:15 AM). This seems like it should be easy - but i have tried just about everything and have not been sucessful. Thanks for your help! |
| 12 | Put this code in the workbook where the lists will be created. It will add Sheets as it needs them. Select multiple files in the file open dialog It will prompt for the number of addresses per sheet. Sub GetData() Dim flist As Variant Dim i As Long, j As Long Dim rw As Long, shCnt As Long Dim FileNo As Long Dim sh As Worksheet Dim sName As String Dim sLine As String Dim NumAddresses As Variant Dim MailList() as String ChDrive "C" ChDir "C:\Data" flist = Application.GetOpenFilename( _ FileFilter:="Text Files (*.txt),*.txt", _ MultiSelect:=True) If TypeName(flist) = "Boolean" Then Exit Sub Do NumAddresses = InputBox("Enter the number of Addresses" _ & " per sheet") Loop Until IsNumeric(NumAddresses) And Len(Trim(NumAddresses)) 0 ReDim MailList(1 To NumAddresses, 1 To 1) Set sh = ThisWorkbook.Worksheets.Add( _ After:=ThisWorkbook.Worksheets( _ ThisWorkbook.Worksheets.Count)) sName = "Data" & 1 On Error Resume Next Application.DisplayAlerts = False Worksheets(sName).Delete Application.DisplayAlerts = True On Error GoTo 0 sh.Name = sName shCnt = 1 rw = 1 For i = LBound(flist) To UBound(flist) FileNo = FreeFile() Open flist(i) For Input As #FileNo j = 0 Do While Not EOF(FileNo) Line Input #FileNo, sLine j = j + 1 MailList(rw, 1) = Trim(sLine) rw = rw + 1 If rw NumAddresses Then sh.Cells(1, 1).Resize(NumAddresses, 1).Value _ = MailList ReDim MailList(1 To NumAddresses, 1 To 1) rw = 1 Set sh = ThisWorkbook.Worksheets.Add( _ After:=ThisWorkbook.Worksheets( _ ThisWorkbook.Worksheets.Count)) sName = "Data" & shCnt + 1 On Error Resume Next Application.DisplayAlerts = False Worksheets(sName).Delete Application.DisplayAlerts = True On Error GoTo 0 sh.Name = sName shCnt = shCnt + 1 End If Loop Debug.Print flist(i), j Next sh.Cells(1, 1).Resize(NumAddresses, 1).Value _ = MailList ReDim MailList(1 To NumAddresses, 1 To 1) End Sub Richard <richard@clarknet.com wrote in message news:d65101c214a2$d20278b0$37ef2ecf@TKMSFTNGXA13... Here's the deal: I have successfully created an Excel 2000 macro that imports a large amount of text into a worksheet. The file being imported is so large that I have to import the remainder into a second sheet. The content is email addresses only so only one column is used. I have many such files and need to import a lot of them. I would like help or the code to accomplish a simple task: 1. Create an automated means for importing the large text file specifying increments of 2500, 5000, 10000, and 20000 at a time with a new sheet being created based on the chosen number to extract. I want it to run until the end of the first file and automatically go to the next file. I'm not a VB programmer so I'm looking for either the scripting already done (a few modifications are ok) or very detailed and specific instructions on how to do it. Thanks. |
| 16 | My data validation uses as criteria, a list of values stored in cells off to the right of my regular data in the worksheet. This is inconvenient as somebody may inadvertantly delete certain rows that contain data. When I try to copy these cells to another hidden worksheet in the workbook, I get an error. Is there a way to use cells referenced to a list in another worksheet function proeprly with data validation? Thanks, Greg. |
| 17 | That's brilliant, fits perfectly with a little project I have. Thanks Andy "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D0A6AF7.4030905@contextures.com... You can do this on the worksheet: 1. In cell B4, enter the number 1 2. In cell C4, enter the following formula: =REPT("y",B4*100) 3. Format cell C4 as Monotype Sorts font, and add a border to the cell. 4. Adjust the width of cell C4 to fit the bar. 5. Now, you can enter a percentage in cell B4 and the bar in cell C4 will be adjusted. Note: for a smaller thermometer, use x, and for a larger one, use z Peter Kretzman wrote: Does anyone know a product or available control that will let me display a visual for percent complete? A simple horizontal thermometer, filled accordingly, is what I was thinking of. Thanks, PK -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 20 | Greetings Would appreciate advice on whether the following scenario is likely to work: I distribute a CD based web and I would like the user to be able to query a database contained on the cd. Because there is no server, I don't think its possible using client side web functionality. I propose to use the excel sql.request worksheet function to return data from an Access database. The user would enter a search criteria in a cell and the results would be returned. The excel spreadsheet would be saved as .htm (?) I will try this at work tomorrow but can't test it at home because I can't get the odbc add-in to install at home. Any opinions on my chance of success? thanks Dan |
| 23 | You can either name the list on the other sheet, insertnamedefine, select the range, type a name and use it in the list source box as =name, or you could put the list directly in the list box a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,x,y for example - Regards, Peo Sjoblom "GZ" <gz@yahoo.com wrote in message news:3D0BCF90.EDA3AFB3@yahoo.com... My data validation uses as criteria, a list of values stored in cells off to the right of my regular data in the worksheet. This is inconvenient as somebody may inadvertantly delete certain rows that contain data. When I try to copy these cells to another hidden worksheet in the workbook, I get an error. Is there a way to use cells referenced to a list in another worksheet function proeprly with data validation? Thanks, Greg. |
| 24 | Jay, one way =A1+B1/24 format as hh:mm -- Regards, Peo Sjoblom "Jay Bonham" <jaybham62@earthlink.net wrote in message news:edPO8.115$6a.9@newsread1.prod.itd.earthlink.net... Hi. I'm trying to add hours duration to a start time to get an end time. my worksheet has a column A with a start time (lets say 4:00 AM) , column B is hours worked (lets say 4.25 hours) I want column C to add 4.25 hours on to 4:00 AM. (in this case it would be 8:15 AM). This seems like it should be easy - but i have tried just about everything and have not been sucessful. Thanks for your help! |
| 34 | Hi, I am having problems with hiding columns, something in my worksheet seems to be limiting the number of columns that I can hide. If I try to hide more than 115 columns, I get an error "cannot move objects off sheet". The number of columns that I can hide seem to be affected by the number of comments on the sheet, but it causes problems even with only a few comments. Can anyone help please, otherwise I'll have to try removing all the comments and resetting them after hiding the columns! |
| 36 | Hi David Removing the Comments first and replacing them after hiding might be the best bet. Just use Copy Paste special - Comments to a new sheet, save and try again -- **Possible solution has NOT been tested** FREE EXCEL NEWSLETTER /News/2home.htm Get the OzGrid Add-in /Services/AddinExamples.htm "David Terris" <TerrisD@obadiah.swinternet.co.uk wrote in message news:aekdl4$70l$1@news5.svr.pol.co.uk... | Hi, | | I am having problems with hiding columns, something in my worksheet seems to | be limiting the number of columns that I can hide. If I try to hide more | than 115 columns, I get an error "cannot move objects off sheet". The number | of columns that I can hide seem to be affected by the number of comments on | the sheet, but it causes problems even with only a few comments. | | Can anyone help please, otherwise I'll have to try removing all the comments | and resetting them after hiding the columns! | | | | |
| 37 | The following MSKB article describes the reason for the message, and suggests a workaround: XL2000: "Cannot Shift Objects Off Sheet" Error Hiding Columns /support/kb/articles/Q211/7/69.ASP David Terris wrote: Hi, I am having problems with hiding columns, something in my worksheet seems to be limiting the number of columns that I can hide. If I try to hide more than 115 columns, I get an error "cannot move objects off sheet". The number of columns that I can hide seem to be affected by the number of comments on the sheet, but it causes problems even with only a few comments. Can anyone help please, otherwise I'll have to try removing all the comments and resetting them after hiding the columns! -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 41 | You can use Advanced Filter to do this: Note: Your list needs a heading in the first cell for the following to work correctly 1) Select a cell in the list. 2) From the Data menu, choose Filter, Advanced Filter. 3) Choose 'Copy to another location'. 4) Excel should automatically detect the list range. If not, you can select the cells on the worksheet. 5) Select a starting cell for the copy 6) Add a check mark to the Unique records only box. 7) Click OK Haggis wrote: Good Morning I have a list of values which are the levels associated with products. Thus there are repeated values as more that one product can have the same level. I need to get a list of all the levels that exist without repeating them. Does anyone know a function I can use or how I would go about doing this? Thanks Haggis -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 46 | Thanks for the response. It took a while, but I made a macro for the problem, just had to make sure that I wasn't leaving any of my fields blank. I had to extract the data from an old program manually and delete program code that was through it. I think it was cobol. Anyway, I've attached the macro incase someone can make use of it. Sub Merging() Dim propi As String Dim i As Integer With Worksheets("Sheet1") For n = 1 To .UsedRange.Column + .UsedRange.Columns.Count For irow = 3 To .UsedRange.Row + .UsedRange.Rows.Count If Not Worksheets("Sheet1").Cells(irow, n) = "" Then propi = .Cells(irow, n) property = property & Chr$(10) & propi Else Worksheets("Sheet2").Cells(irow, n) = property property = "" End If Next irow Next n End With ActiveWorkbook.Worksheets("Sheet2").Select Selection.SpecialCells(xlCellTypeBlanks) _ .Delete Shift:=xlUp End Sub George -----Original Message----- Are you using xl as a database and using Word to produce the mailmerge. If yes, then I think it would be much easier to put each person's info on one row (in separate columns). It'll make a lot of things easier. You might want to read 's notes on mailmerge: /dmcritchie/excel/mailmerg.htm and more notes at: /word/FAQs/MailMerge/index.html George J wrote: I am trying to produce a mailmerge, but require the names and addresses to be in one cell instead of cover 4/5 rows in the column. The logic as I see it would be For usedrange 1. find the first cell in column 1 with text 2. if cell in next row has text, combine (without losing any of the data) 3. repeat until next row is blank AAAname AAAaddress AAAtown BBBname BBBaddress BBBtown BBBcounty The data above would look exactly as is, but the text would be in only 2 cells. I know it should read something like A3 & Chr(10) & A4 & chr(10) etc, but i can't figure it out. I had hoped that this had been answered before, but couldn't find anything Thanks for any input George -- Dave Peterson ec35720@msn.com . |
| 51 | Thanks again Harald, I did get back to the server with your help, but I have been unable to download much from the Misc and Worksheet groups (my two favorites).......is there trouble going on with those two or is it just my connection?...... Vaya con Dios, Chuck,, CABGx3 Harald Staff <harald.staff@eunet.no wrote in message news:#7XUm3PFCHA.2640@tkmsftngp04... Hi Chuck msnews.microsoft.com HTH. Best wishes Harald "CLR" <croberts@tampabay.rr.com wrote in message news:qrSO8.21075$WI.583990@twister.tampabay.rr.com... Hi All....... I'm just restored from a HD crash and am having to re-install everything.......would someone be so kind as to tell me the proper name of the Microsoft News Server, that I might tie back in to it directly, please? tks Chuck, CABGx3 |
| 53 | I am new to VBA & VB. I wish to establish if it is possible to launch a .wav (and/or) on opening an Excel 2000 worksheet...(Note: not workbook,) I have perused newsgroups archives and web with Google and have been unable to locate any previous discussion on this. I would appreciate any direction to any tutorial or tips page that is relevant and could assist me. Many thanks Chris Hawkins |
| 59 | It's the server farm. Your results will depend on which server you connect to. In article <DgjP8.676$iL2.65451@twister.tampabay.rr.com, CLR <croberts@tampabay.rr.com wrote: Thanks again Harald, I did get back to the server with your help, but I have been unable to download much from the Misc and Worksheet groups (my two favorites).......is there trouble going on with those two or is it just my connection?...... Vaya con Dios, Chuck,, CABGx3 Harald Staff <harald.staff@eunet.no wrote in message news:#7XUm3PFCHA.2640@tkmsftngp04... Hi Chuck msnews.microsoft.com HTH. Best wishes Harald "CLR" <croberts@tampabay.rr.com wrote in message news:qrSO8.21075$WI.583990@twister.tampabay.rr.com... Hi All....... I'm just restored from a HD crash and am having to re-install everything.......would someone be so kind as to tell me the proper name of the Microsoft News Server, that I might tie back in to it directly, please? tks Chuck, CABGx3 |
| 63 | John Walkenbach has some example code for playing a .wav file: -walk.com/ss/excel/tips/tip59.htm -walk.com/ss/excel/tips/tip87.htm Should get you going into the right direction... HTH Jason Atlanta, GA -----Original Message----- I am new to VBA & VB. I wish to establish if it is possible to launch a .wav (and/or) on opening an Excel 2000 worksheet...(Note: not workbook,) I have perused newsgroups archives and web with Google and have been unable to locate any previous discussion on this. I would appreciate any direction to any tutorial or tips page that is relevant and could assist me. Many thanks Chris Hawkins . |
| 72 | I am using XL2000 and Win 98. I don’t know if this problem is a Macro issue or a general Excel issue, but I start with this news group. I get an error message, “Cannot paste that macro formula into a worksheet” when ever I try to copy and paste a cell or group of cells form one workbook to another. This only happens in workbooks with macros, however the message occurs regardless of whether the copied cell is references in a macro. I can use the Paste Special, Formulas but that loses formatting. The Paste Special, Values or Paste Special, Formats always yields the error message. The details of the error say that I have caused an invalid page fault in Excel.exe. I have seen other posts in which the same error seems to prevent the file from opening, but this is not my problem. Any advice appreciated. Ray Wright -- raycyn.wright@prodigy.net |
| 75 | Brian, Thank you so very much for taking time to reply. You solved my problem perfectly! Again, thank you. Greg -----Original Message----- Greg, There are some things the macro recorder does not do well. The solution is the concatenation function. You can use this function in a macro or on the worksheet self. So the simple way is to enter this formula in cell C1: = A1 & ", " & B1 Copy this down for all the rows. Then you'll probably want to do a copy and paste special and choose values to convert the formula to values. HTH, Brian Greg Hight wrote: Kindly email me if you have a solution This should be a simple problem with a simple solution, but I'm stumped! I have several thousand rows and two columns. I want the contents of one column to appear in the other column next to the contents of the other column. Specifically, Column A contains last name Column B contains first name I want first name following last name, separated by comma and space. I've tried recording macros, but even when the relative function is applied, the macro only records exactly what I copied when creating the macro...it does not read the contents of the next row and then paste them to the new cell. Thanks very much. . |
| 78 | i need to be able to sort an excel list by A-Z, however when i am running my macro it copies and past values into the list to be sorted, but when it copies and past blank cells that have a formula in them it sorts those cells as actually having something in them, i don't want those cells to be included before my actual values. Is there a way to capture that range or anyrange in VB so that i could somewhere on a worksheet tally up the total cells with data using counta() and then import that number into VB to delete the remainder of cells (blank cells)? or is there just a simple way to exclude cells that contain the "" value from a formula after they have been copy and pasted with values only? |
| 90 | Try: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Count 1 Then Exit Sub With Target.Interior MsgBox "ColorIndex = " & .ColorIndex & vbNewLine & _ "Red: " & .Color Mod 256 & vbNewLine & _ "Green: " & Int(.Color / 256) Mod 256 & vbNewLine & _ "Blue: " & Int(.Color / 256 ^ 2) End With End Sub In article <db6701c21615$504293e0$36ef2ecf@tkmsftngxa12, Gabriela Montero <gbm105@hotmail.com wrote: Hello, Is it possible to get the actual color (cell shading) of a cell from a selection change event? Thanks Gabriela |
| 94 | I need to know how to link a formula in a cell in one worksheet to a cell in another worksheet in the same workbook. So if it works the numbers and formulas will continue to add up in both worksheets as data is added. Thank you. |
| 97 | In VB, I am currently catching the WorkbookBeforePrint event of the Excel Application object. Inside this event, I am setting the LeftHeader property of each Worksheet to a cell value in the Worksheet... In all instances, the PageSetup dialog is brought up ahead of this. I have found that, depending on what is pressed on the PrintSetup dialog, the WorkbookBeforePrint event acts the same, but the PageSetup properties act differently. If Print or Preview are pressed on the PageSetup dialog, I am able to set the LeftHeader. If Ok is pressed on the PageSetup dialog, then I am unable to set the LeftHeader. No error is thrown. Its acts just as if it's locked. Here is the snippet of code I am using in the event. 120 For i = 1 To .Worksheets.Count 125 With .Worksheets(i) 130 .PageSetup.LeftHeader = "&B" & .Cells(1, 1).Value & "&B :: " & Replace(.Cells(3, 1).Value, " ", " ", 1, , vbTextCompare) Debug.Print .PageSetup.LeftHeader 'this generates a blank line when coming from OK. 135 .Cells(1, 1).Value = "" 140 .Cells(3, 1).Value = "" End With Next Any suggestions? Thank, T. Anderson |
| 98 | 2 files open first file enter = in formula bar, switch to 2nd book select cell, press enter Dave -----Original Message----- I need to know how to link a formula in a cell in one worksheet to a cell in another worksheet in the same workbook. So if it works the numbers and formulas will continue to add up in both worksheets as data is added. Thank you. . |
| 102 | Definitely trouble. I had ro reset absolutely everything to see this reply. Best wishes Harald "CLR" <croberts@tampabay.rr.com wrote in message news:DgjP8.676$iL2.65451@twister.tampabay.rr.com... Thanks again Harald, I did get back to the server with your help, but I have been unable to download much from the Misc and Worksheet groups (my two favorites).......is there trouble going on with those two or is it just my connection?...... |
| 111 | I am looking for a VBA statement that will coun the # of worksheets in an excel workbook. Can you help me with this? |
| 112 | I guess I just don't understand how toolbars work. I have attached TB1 to Worksheet1 and TB2 to Worksheet2. I want it to be that TB1 only "shows" when Worksheet1 is open, and v.v. As it is, have to manually open or close TB1 or TB2 depending on which one I want to appear. (TB1 and TB2 both appear in all my workbooks.) |
| 114 | Dan, ActiveWorkbook.Worksheets.Count Workbooks("My Workbook.xls").Worksheets.Count HTH, Bernie Dan Flannery wrote: I am looking for a VBA statement that will coun the # of worksheets in an excel workbook. Can you help me with this? |
| 115 | The pagesetup does NOT trigger the beforeprint event, so your code doesn't run. Only the Print or Printpreview will trigger the beforeprint event. Bob Umlas Excel MVP "Tyler Anderson" <tanderson@REMOVEadaytum.com wrote in message news:#UWLU0hFCHA.1740@tkmsftngp07... In VB, I am currently catching the WorkbookBeforePrint event of the Excel Application object. Inside this event, I am setting the LeftHeader property of each Worksheet to a cell value in the Worksheet... In all instances, the PageSetup dialog is brought up ahead of this. I have found that, depending on what is pressed on the PrintSetup dialog, the WorkbookBeforePrint event acts the same, but the PageSetup properties act differently. If Print or Preview are pressed on the PageSetup dialog, I am able to set the LeftHeader. If Ok is pressed on the PageSetup dialog, then I am unable to set the LeftHeader. No error is thrown. Its acts just as if it's locked. Here is the snippet of code I am using in the event. 120 For i = 1 To .Worksheets.Count 125 With .Worksheets(i) 130 .PageSetup.LeftHeader = "&B" & .Cells(1, 1).Value & "&B :: " & Replace(.Cells(3, 1).Value, " ", " ", 1, , vbTextCompare) Debug.Print .PageSetup.LeftHeader 'this generates a blank line when coming from OK. 135 .Cells(1, 1).Value = "" 140 .Cells(3, 1).Value = "" End With Next Any suggestions? Thank, T. Anderson |
| 118 | Splash, Your toolbars have become part of the family of toolbars stored with "Excel.xlb". If you want to have them only available when the file is open, then you will either need to delete the commandbar through code when the workbook is closed, using the before close event, or set the visible property to false (which you could then set to true in the open event). The prefered method is to create the commandbar each time the workbook is opened, and delete it each time the workbook is closed. Do a google newsgroup search on Deitrick Commandbar Create Delete and you will find some code to help. HTH, Bernie Splash wrote: I guess I just don't understand how toolbars work. I have attached TB1 to Worksheet1 and TB2 to Worksheet2. I want it to be that TB1 only "shows" when Worksheet1 is open, and v.v. As it is, have to manually open or close TB1 or TB2 depending on which one I want to appear. (TB1 and TB2 both appear in all my workbooks.) |
| 123 | I found a file named BOOK.XLT in the /XLSTART folder. It apparently was the template that each new workbook was based on. I deleted two work sheets from it and now get a one worksheet workbook when I start a new one. Save As is a little less simple. Best I can tell, this function only saves the whole workbook. It doesn't allow for saving individual worksheets. Even the Save command does not allow for selective saves of worksheets only, it save the entire workbook. I really miss being able to manipulate individual worksheets. BrianG "Barbara wiseman" <b@nbpwiseman.fsnet.co.uk wrote in message news:<uBjf67GFCHA.1996@tkmsftngp07... Brian, Save as is in the file option on the menu, or press F12. Your 3 sheets may be in a book which is opened with every session of excel. In 2000 this is usually in a directory XLSTART which is often in windows\application data\microsoft\excel if there is a excel sheet there it will be opened every time you open an excel session. If you move it to another directory or delete it this may solve your problem. NB there is sometimes a file personal.xls there, this contains any macros saved by the user, do not delete this. Barbara BrianG <decc@dec-clt.com wrote in message news:698e0e54.0206141016.4c7060c7@posting.google.com... This is old stuff I'm sure but at work we have just been forced into using Excel97 instead of 4.0. Many of us have stuck with 4.0 because it suits our purposes and is more user friendly (read "less bells and whistles that we don't use anyways"). The problem we're running into is that we are accustomed to using worksheets. We used to open an existing worksheet, modify it, do a "save as" and have both the old and new. What's happening now is that we make changes to a worksheet within a workbook but then can't figure out how to save it as a new worksheet. Best we can figure, you have to make a copy first then modify it, what a pain. Is this the way it's supposed to work or am I missing something. Also, we've set "sheets in new workbook" to 1 but new workbooks still open with 3 blank worksheets. Any suggestions would be appreciated. BrianG |
| 124 | To anwser your second question, In other words, what type of analysis would one be doing when a mixed reference cell would be needed? (E.g. Mortgage calculations? car payments? etc.) the type of analysis is irrelevant when deciding whether to use relative, mixed, or absolute references. It's dependent upon the layout of the data in your worksheets and how you go about constructing formulas and copying them to new locations. HTH Jason Atlanta, GA -----Original Message----- Can someone please give me an example of when it is most appropriate to use a mixed reference in a spreadsheet, as opposed to an absolute reference or a relative reference? In other words, what type of analysis would one be doing when a mixed reference cell would be needed? (E.g. Mortgage calculations? car payments? etc.) . |
| 129 | How do I link cells in seperate worksheets where if I change data in the cell in the first worksheet it will change in the cell in the second worksheet as well. Thank you. |
| 130 | I cannot get the following code to work (which is in the Excel Help files)--I keep getting the error message "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct and try again." However, this doesn't make sense since everything else in the Sub Procedure works. I cannot get any code to return the trendline count--I always get various types of error messages. I want to use an IF statement to determine whether a trendline exists so that it can be deleted or added for different chart datasets (series collections). (Count code is almost at end of Sub Procedure) Private Sub DataSetList_Change() Dim strfile As String, intTrendline As Integer, intCount As Integer strfile = Application.ThisWorkbook.Name With Worksheets("Review").ChartObjects(3).Chart .HasTitle = True .ChartTitle.Text = DataSetList Select Case DataSetList Case "Total Authorized-RBM (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Cum" intTrendline = 1 Case "Total Authorized-RBM (Qtr)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Qtr" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Qtr" intTrendline = 0 Case "2002 RBM Budget-Actual (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_Cum" intTrendline=1 Case "2002 RBM Budget-Actual (M)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_M" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_M" intTrendline=0 End Select End With MsgBox Worksheets("Review").Charts(3).SeriesCollection (2).Trendlines.Count 'If Worksheets("Review").Charts(3).SeriesCollection(2) '.Trendlines.Count = 1 And intTrendline = 0 Then 'ActiveSheet.ChartObjects("Chart 3").Activate 'ActiveChart.SeriesCollection(2).Trendlines(1).Select 'Selection.Delete 'Else If Worksheets("Review").Charts (3).SeriesCollection(2) '.Trendlines.Count = 0 And intTrendline = 1 Then 'Worksheets("Review").ChartObjects (3).Chart.SeriesCollection(2) '.SeriesCollection(2).Trendlines.Add Type:=xlLinear, Name:="Linear Trend" 'Worksheets("Review").ChartObjects(3).Activate 'End If Worksheets("Review").Range("B5").Activate End Sub |
| 137 | The scrolling speed is controlled by the distance you've moved the pointer off the sheet. For example, if you select row 1, and drag down till your pointer is pointing at the sheet tab (just barely off the worksheet), the scrolling should be at a moderate speed. If you point at the Status Bar (at the bottom of the Excel window), the scrolling speeds up. If you move your pointer to the bottom of the monitor, you reach those lightning speeds. There are other ways to select rows, that you might find easier. 1. Select the first row, then release the mouse button 2. Use the scroll bar to scroll down (without selecting), till you can see the last row you want to select. 3. Hold the Shift key and click in the last row. Or, if you know exactly which rows you want to select: 1. Click in the Name Box (to the left of the Formula Bar) 2. Type the range you want to select, e.g. 1:100 3. Press the Enter key 4. If you want to change the end row after using this method, hold the Shift key and click on a different ending row. George Smirnoff wrote: Is there any way to adjust the speed at which the cursor will scroll down when you use the mouse? For instance if I highlight Row 1 and want to highlight Row 1-1000, when I move the cursor down off the page, it scrolls at about 500 rows a second. So obviously I end up highlighting Rows 1-10000. Can I adjust this? -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 143 | William - You skipped a level in the hierarchy of objects. Change this line: MsgBox Worksheets("Review").Charts(3).SeriesCollection(2).Trendlines.Count to this: MsgBox Worksheets("Review").ChartObjects(3).Chart.SeriesCollection(2) .Trendlines.Count (all one line - watch the email wordwrap). - Jon _______ In article <f36f01c21633$c64fb1d0$b1e62ecf@tkmsftngxa04, bartusek_william_c@cat.com says... I cannot get the following code to work (which is in the Excel Help files)--I keep getting the error message "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct and try again." However, this doesn't make sense since everything else in the Sub Procedure works. I cannot get any code to return the trendline count--I always get various types of error messages. I want to use an IF statement to determine whether a trendline exists so that it can be deleted or added for different chart datasets (series collections). (Count code is almost at end of Sub Procedure) Private Sub DataSetList_Change() Dim strfile As String, intTrendline As Integer, intCount As Integer strfile = Application.ThisWorkbook.Name With Worksheets("Review").ChartObjects(3).Chart .HasTitle = True .ChartTitle.Text = DataSetList Select Case DataSetList Case "Total Authorized-RBM (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Cum" intTrendline = 1 Case "Total Authorized-RBM (Qtr)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_Q" .SeriesCollection(1).Values = "='" & strfile & "'! Tot_Proj_Author_Qtr" .SeriesCollection(2).Values = "='" & strfile & "'! Tot_Proj_Act_Qtr" intTrendline = 0 Case "2002 RBM Budget-Actual (Cum)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_Cum" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_Cum" intTrendline=1 Case "2002 RBM Budget-Actual (M)" .SeriesCollection(1).XValues = "='" & strfile & "'! Dates_M" .SeriesCollection(1).Values = "='" & strfile & "'! RBM_2002_Budget_M" .SeriesCollection(2).Values = "='" & strfile & "'! RBM_2002_Act_M" intTrendline=0 End Select End With MsgBox Worksheets("Review").Charts(3).SeriesCollection (2).Trendlines.Count 'If Worksheets("Review").Charts(3).SeriesCollection(2) '.Trendlines.Count = 1 And intTrendline = 0 Then 'ActiveSheet.ChartObjects("Chart 3").Activate 'ActiveChart.SeriesCollection(2).Trendlines(1).Select 'Selection.Delete 'Else If Worksheets("Review").Charts (3).SeriesCollection(2) '.Trendlines.Count = 0 And intTrendline = 1 Then 'Worksheets("Review").ChartObjects (3).Chart.SeriesCollection(2) '.SeriesCollection(2).Trendlines.Add Type:=xlLinear, Name:="Linear Trend" 'Worksheets("Review").ChartObjects(3).Activate 'End If Worksheets("Review").Range("B5").Activate End Sub |
| 151 | I have a workbook that has some 200 sheets. I would like to copy the three same cells from all worksheets and put it on a worksheet called "Foremen" within the same workbook. I tried modifying some code that I already had been using for another process but can't seem to get it quite right seeing as how I am far from a VBA master. With it the way it is now I get an error saying the subscript is out of range. Can anyone help me out with this? Below is the code I have been trying to tweak. "Foremen" is the leftmost worksheet, the first one if you will and I do not want to extract data from it. This is where all the data should be deposited. The "GBG001" worksheet in the range is just to right of "Foremen", the second worksheet in the workbook and the first one I want to extract data from. The "RWW2W0208" worksheet is the far right worksheet, the last one in the workbook. "012-204-2" is the name of the excel file. Sub CreateList2() Dim w As Range For Each w In Workbooks("012-204-2").Range("GBG001:RWW2W0208") With Worksheets("Foremen") .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value _ = Worksheets(w)Cells(4, 2).Value .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(5, 2).Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(7, 1).Value End With Next End Sub Any ideas are aprreciated. Thanks in advance! Bill |
| 160 | I have a set of about 500 worksheets from which I want to extract info in a summary sheet I am building. I have the names and locations of the workeets which are located all over our network and which are being constantly updated by people at other sites. I'm trying build a column of lookups that automatically changes whan I change the name of the file being looked into. I've created the first onebut because the name of the file is in brackets [*.wks], I can't get the value within the brackets to change as if I enter a cell refference to the file name string. Are there specical commands when dealing with bracketed data? What's a reasonable solution for this? Dan |
| 162 | Sub CreateList3() For Each ws In Sheets If ws.Name < "Foremen" Then w = ws.Name With Sheets("Foremen") .Cells(Rows.Count, "A").End(xlUp).Offset(1) = Sheets(w).Cells(4, 2) .Cells(Rows.Count, "B").End(xlUp).Offset(1) = Sheets(w).Cells(5, 2) .Cells(Rows.Count, "C").End(xlUp).Offset(1) = Sheets(w).Cells(7, 1) End With End If Next End Sub -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Bill Kelly" <bkelly@pilchuck-usa.com wrote in message news:OJAXLPuFCHA.1916@tkmsftngp13... I have a workbook that has some 200 sheets. I would like to copy the three same cells from all worksheets and put it on a worksheet called "Foremen" within the same workbook. I tried modifying some code that I already had been using for another process but can't seem to get it quite right seeing as how I am far from a VBA master. With it the way it is now I get an error saying the subscript is out of range. Can anyone help me out with this? Below is the code I have been trying to tweak. "Foremen" is the leftmost worksheet, the first one if you will and I do not want to extract data from it. This is where all the data should be deposited. The "GBG001" worksheet in the range is just to right of "Foremen", the second worksheet in the workbook and the first one I want to extract data from. The "RWW2W0208" worksheet is the far right worksheet, the last one in the workbook. "012-204-2" is the name of the excel file. Sub CreateList2() Dim w As Range For Each w In Workbooks("012-204-2").Range("GBG001:RWW2W0208") With Worksheets("Foremen") .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value _ = Worksheets(w)Cells(4, 2).Value .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(5, 2).Value .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value _ = Worksheets(w).Cells(7, 1).Value End With Next End Sub Any ideas are aprreciated. Thanks in advance! Bill |
| 168 | I guess it corrupted. Search the google excel NG's under corrupted, you will likely find some hits for companies or sites that can open the fiel for a fee Dave -----Original Message----- Dear All I have got a 3MB worksheet which I cannot open. When I try to open it, I get the following dialogue box: =================================== Application Error The instruction at "0x30099749f" referenced memory at "0x00000002". The memory could not be "read". Click on OK to terminate the application. Click on Cancel to debug the application. =================================== Even if I click on "Cancel", it doesn't open Excel, so I can't view the spreadsheet at all. Any help/advice gratefully received... Regards Alistair . |
| 172 | I have an Excel 2000,SR1(9.0.4402) workbook that will NOT recalculate properly on cell references between worksheets. Recalc set to Automatic. I can open the file on some machines and it works perfectly; not on others. Can find no settings that are different between the various machines. Saved the file from a machine on which it worked; then it worked on the first machine for awhile, but eventually reverted to not working. Any ideas? Sheila |
| 173 | Hi George, Can't think of anything in particular. I'd check the last cell (ctrl+end) to see if that places you beyond your 2000 rows of data that you say you have. Do you have event macros in the worksheet, volatile user defined functions. Formulas dependent on data being changed which could trigger volatile functions. Are other sheets dependent on the data in the sheet being changed. You might check my page on Slow Response /dmcritchie/excel/slowresp.htm "George" <gsmirnoff@yahoo.com wrote in message news:dfc301c216dd$d5df9b60$a5e62ecf@tkmsftngxa07... As of a few months ago, whenever I do a search/replace in a spreadsheet for a particular column, it seems to take unusually long to get it done. I have a spreadsheet of 2000 rows, and when asking to search for a particular string to replace just for that row, the function takes 2- 3 minutes during which the highlighted column flashes as if stuck in an endless loop. This just started happening recently. Any ideas why? |
| 174 | I've done many compound graphs like you describe. The trick is to create separate charts embedded on a worksheet. By careful use of formatting, alignment by snapping to cell intersections and grouping, I manage to get the desired effect. In your oscilloscope analogy, you would have 3 differing Y axis and want to share the X or Time axis. In other words, only the bottom time scale shows. I often do this by scaling all X axes identically on all charts then overlapping/hiding the axis for the top graphs. You might think you can just delete the unwanted axis, but the problem you will continually do battle with is Excel's distressing tendency to resize the plot area every time you alter axis formatting. You will want everything *exactly* the same except the Y axis and perhaps, the series displayed. Hint - no auto scaling/formatting whatsoever. No auto *anything*. Basically the procedure is this: 1) Create the bottom chart exactly as you would like to see it. 2) Fix (freeze) the axes by turning autoscaling off. 3) Copy/paste this chart for as many charts as you are going to stack. 4) Alter the Y scaling on the stacked charts as necessary. 5) Lay them out and glue them together (group) With a little effort, the results can be perfect. Thomas Bartkus "Microsoft" <edward@biebel.net wrote in message news:u8btS6uFCHA.2324@tkmsftngp04... Hi all: I have a question that has got me stumped and which I believe cannot done in Excel but figured I'd ask to be sure. I've been asked to construct a chart that has three different scales on it. However, the problem is not the multiple scales but the layout that is being requested. Essentially the person requesting the chart wants the bottom third of the chart done in one scale, the middle third done in a second scale and the top third in third scale. The only analogy that I can think of to describe the look he would like if from my HS physics days where you have an oscilloscope with different meter bands each with its own waveform stacked on top of each other. Anyone ever tried this and does this even sound possible? It didn't possible to me but you known how it goes when you get the "this should be relatively simple" requests. Thanks, Ed |
| 183 | Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 184 | Hi Sheila, Excel takes the calculation setting from the first workbook opened, rather than from each workbook. So this can happen if you open other workbooks which are set to manual, or have a workbook in Xlstart which is set to manual, or ... Charles Williams ______________________ Decision Models The Excel Calculation Site www.DecisionModels.com "Sheila" <sleppert@chguernsey.com wrote in message news:10d5301c216fd$78c35780$35ef2ecf@TKMSFTNGXA11... I have an Excel 2000,SR1(9.0.4402) workbook that will NOT recalculate properly on cell references between worksheets. Recalc set to Automatic. I can open the file on some machines and it works perfectly; not on others. Can find no settings that are different between the various machines. Saved the file from a machine on which it worked; then it worked on the first machine for awhile, but eventually reverted to not working. Any ideas? Sheila |
| 187 | You might want to use formula instead of value Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False Target.Formula = UCase(Target.Formula) Application.EnableEvents = True End Sub "Wilson" <jwilson@wickes.com wrote ... Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 189 | Please explain why, David? TIA "" <dmcritchie@msn.com wrote in message news:O9FYSawFCHA.2604@tkmsftngp12... You might want to use formula instead of value Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False Target.Formula = UCase(Target.Formula) Application.EnableEvents = True End Sub "Wilson" <jwilson@wickes.com wrote ... Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 193 | When you print two non-contiguous ranges (even on the same worksheet), excel likes to print (at least) two pieces of paper. One way to print your ranges (as one sheet) is to copy the second to the bottom of the first and extend the print range to include the whole thing. You could also copy both ranges as pictures and paste them into another worksheet (still have to be adjacent, though). Then print that. If you copy the ranges as pictures, you don't have to worry about columnwidths being different and screwing up the layout of your data. One way to do this is: Select one of the ranges, then do a Shift-Edit, you'll see a different option--Copy Picture. Choose the one that you like best and paste that picture into a new worksheet. Do the same for the second range. Then print that new worksheet. ========= Another even nicer way is to select the first range and do a regular copy. Then go to the other sheet and do a Shift-Edit. You'll see a different option--Paste Picture Link. If you paste this one, then any changes you make to the original range will be reflected in the picture--kind of a dynamic picture. (The other way was static. It was a picture of the data when you did the copy.) Bruce wrote: I have modified a print macro script I found on Barasch to set my print are to this: ActiveSheet.Range(Cells(2, 1), Cells(BottomRw, LastCol)).Select and my final print command to this: Sheets("List Entry").PrintOut List Entry being the worksheet with the selected cell range. I am now wanting to append cells A1:G15 on a worksheet called "Report" to the bottom of the print out - is there any way of doing this? thanks -- Dave Peterson ec35720@msn.com |
| 196 | AFAIK you can't do this with a formula, because there isn't a character that Excel recognizes as a page break character. You could record or write a macro and run it before you print the worksheet, inserting a page break based on the content of cell A5. Joseph K wrote: Hi, I need to insert/delete a page break at a cell based on if another cell has a picture in it. So far i have a formula =IF(ISBLANK(A5),<delete page break,<insert page break) The question is - can this be done, and if so, what should I put in the <delete/insert page break parts of the formula? Thanks in advance, Joseph -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 197 | Just a question and guesses: Does excel blow up (with the same error) with any other workbook? If yes, then it's probably not this workbook. If no, then maybe the workbook is corrupted (like Dave suggested). I've had luck using xl2002 opening files that both xl97 and xl2k couldn't open. Sometimes formatting was lost, but the data/formulas/macros were there. Other people have reported that Sun's OpenOffice/StarOffice can sometimes open corrupted excel files. (It used to be between 80-110 meg download. I looked and decided that it's too big for my puny modem!) Not-for-free StarOffice. /software/download/desktop_app.html Free version /dev_docs/source/1.0.0/index.html IrelandA wrote: Dear All I have got a 3MB worksheet which I cannot open. When I try to open it, I get the following dialogue box: =================================== Application Error The instruction at "0x30099749f" referenced memory at "0x00000002". The memory could not be "read". Click on OK to terminate the application. Click on Cancel to debug the application. =================================== Even if I click on "Cancel", it doesn't open Excel, so I can't view the spreadsheet at all. Any help/advice gratefully received... Regards Alistair -- Dave Peterson ec35720@msn.com |
| 214 | Create a column in for EACH list and call that column "sourcecode" assign a source code and record number: e.g. A00001 the A specifying that that record came from file "A" the next record would be A00002, and so on (use auto fill to do this quickly). Open a new worksheet - copy and past the records from all files into that sheet and sort by the field you are deduping, e.g. company name. You will then see the dupes next to each other, and based on the source code, you can manually delete the dupes. I would copy those dupes before deleting them onto another sheet just so you have a backup. Try it - allway copy your files and work with your copy files so if you screw it up you still have the untouched originals. -----Original Message----- when I have a "potential" customer list in excel, with companies name in column1, and street address in column 2, and city in column 3, and province in column 4, and postal code in column 5,and phone number in column 6, and fax number in column 7, and watts number in column 8, email, in 9, and web url in column 10. Now I would like to merge two different lists with the same layouts, but i am afraid that there will be some duplication (because some companies are listed under different categories in the yellow pages), and I want to know if there is a short cut to "pointing out", "eliminating" the doubles? . |
| 226 | Use the autofilter as necessary. Copy the selected records into another worksheet and use the advanced filter for "unique" records. This takes a bit of practice but it usally works quite well. Just make sure that the area for filtering has at least 1 blank row and 1 blank column around it. One can always export the data into Access and search for redundant records if you know how. Hope this helps! -----Original Message----- Good Morning I have a list of values which are the levels associated with products. Thus there are repeated values as more that one product can have the same level. I need to get a list of all the levels that exist without repeating them. Does anyone know a function I can use or how I would go about doing this? Thanks Haggis . |
| 230 | If you find it time consuming and difficult to spot the duplicates after sorting (e.g. xCompany versus xCompany, Inc.), try using Refinate for free to bring you to the duplicates quickly so you can review them (it is not necessary to sort first). It optionally leaves a comment tag on the cell so you can see all duplicates after you reach the end of the search. You can also make a print-out showing these comment tags. If you make changes to the duplicates, the change is logged in the comment tag. Delete any comment tag you do not need. --Brian Taylor Refinate, copyright 2001 www.adetaylor.com "lk" <lazark@precisemailing.com wrote in message news:e44301c21715$151d50b0$36ef2ecf@tkmsftngxa12... Create a column in for EACH list and call that column "sourcecode" assign a source code and record number: e.g. A00001 the A specifying that that record came from file "A" the next record would be A00002, and so on (use auto fill to do this quickly). Open a new worksheet - copy and past the records from all files into that sheet and sort by the field you are deduping, e.g. company name. You will then see the dupes next to each other, and based on the source code, you can manually delete the dupes. I would copy those dupes before deleting them onto another sheet just so you have a backup. Try it - allway copy your files and work with your copy files so if you screw it up you still have the untouched originals. -----Original Message----- when I have a "potential" customer list in excel, with companies name in column1, and street address in column 2, and city in column 3, and province in column 4, and postal code in column 5,and phone number in column 6, and fax number in column 7, and watts number in column 8, email, in 9, and web url in column 10. Now I would like to merge two different lists with the same layouts, but i am afraid that there will be some duplication (because some companies are listed under different categories in the yellow pages), and I want to know if there is a short cut to "pointing out", "eliminating" the doubles? . |
| 234 | Thanks, Wilson and Dave. Oops. I only wanted to change the values of cells in 2 columns on my worksheet -- not the entire book. (I shouldn't have said "all cell values" but "cell values -- sorry...) So, if I only want the values of two columns to become capitalized after entered in small letters, is that possible? Thanks, guys. "Wilson" <jwilson@wickes.com wrote in message news:erTxOUwFCHA.968@tkmsftngp04... Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 235 | I have recorded a macro to sort, copy and paste text data (Name, Sex, Age) in a defined dynamic range onto another worksheet. If I actually type the data into the defined range, the macro works fine, but if I copy and paste the data from another workbook into the defined range, the macro does not recognize any of the data I have pasted. I have even typed a few sets of data in, then pasted (and even tried "paste special - values") a few sets beneath them, then typed in a few more below those and when I run the macro, only the typed-in data is recognized. Can anyone explain this to me? I thought pasted or pasted-special data was treated the same as data that was keyed in directly. Thank you for any assistance. -Dave Trop- dmtrop@cox.net |
| 238 | In working with the data further, I find it is just the Age field that is affecting the macro. Even if I format it as General or Number, I cannot get the macro to work. If I type the same number into each cell that was copied there, the macro works fine. ???? "Dave Trop" <dmtrop@cox.net wrote in message news:53QP8.58314$Hn4.1998285@news1.east.cox.net... I have recorded a macro to sort, copy and paste text data (Name, Sex, Age) in a defined dynamic range onto another worksheet. If I actually type the data into the defined range, the macro works fine, but if I copy and paste the data from another workbook into the defined range, the macro does not recognize any of the data I have pasted. I have even typed a few sets of data in, then pasted (and even tried "paste special - values") a few sets beneath them, then typed in a few more below those and when I run the macro, only the typed-in data is recognized. Can anyone explain this to me? I thought pasted or pasted-special data was treated the same as data that was keyed in directly. Thank you for any assistance. -Dave Trop- dmtrop@cox.net |
| 241 | Makes a lot more sense for only two columns. If target.column < 3 and target.column < 5 then exit sub "Splash" <splash@mosquitonet.com wrote in message news:ugvj3jlbb6cmd5@corp.supernews.com... Thanks, Wilson and Dave. Oops. I only wanted to change the values of cells in 2 columns on my worksheet -- not the entire book. (I shouldn't have said "all cell values" but "cell values -- sorry...) So, if I only want the values of two columns to become capitalized after entered in small letters, is that possible? Thanks, guys. "Wilson" <jwilson@wickes.com wrote in message news:erTxOUwFCHA.968@tkmsftngp04... Put his code in the worksheet module Private Sub Worksheet_Change(ByVal Target As Excel.Range) Target.Value = Ucase(Target.Value) End Sub HTH "Splash" <splash@mosquitonet.com wrote in message news:ugv57gc1e48p7b@corp.supernews.com... Any way to make any input into a cell always "be" capitalized, as it's entered? That is, if I type "nm" in a cell, then enter, can it make it automatically "become" "NM"? |
| 243 | Hi George, See NETWORKDAYS Worksheet Function in HELP NETWORKDAYS(start_date,end_date,holidays) HTH, datetime.htm has date and time relate info on my site. , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm "George" <gjbeaupre@archchemicals.com wrote in message How can I calculate the number of days between two dates taking into account workdays only (i.e. Monday-Friday). |
| 250 | Hi tomo BackgroundQuery . Used only with query tables based on the results of an SQL query. True to return control to the procedure as soon as a database connection is made and the the query is submitted (the query is updated in the background). False to return control to the procedure only after all data has been fetched to the worksheet. Post the entire code (from the With to the End With ) of that section. The error is caused by other sections of the code, not the .Refresh . Jon tomo wrote: i have recorded a macro but when i tried to run it again, i got an error "General ODBC Error" and the statement causing the error is ..Refresh BackgroundQuery:=xxxx (TRUE or FALSE) almost most of the time, FALSE doesn't work, and when i try to change it to TRUE, it work sometimes but it also causes error sometimes do anyone know what is the function of this statement? and under what circumstances i will get the error message? and what should i do to make my macro works? (delete that statement? or add some additional statements?) i'm a newbie and it is appreciated if the answer could be a little bit more detail. thanks in advance. |
| 259 | Hi, The option buttons are designed to choose between a number of items. I'm not sure, but I think you can only have one selected option button per worksheet (haven't tried though). To do what you want, use the checkbox. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- I have one column, a1, a2, a3, a4 In each I have 2 (form) option buttons When I click on the option buttons in a4 the ones in a1 change to I want each cell to be seperate so that when I click on the option button in a1 one is on the other is off. But I do not want the option buttons to change in the other cells. Hope someone can help . |
| 263 | Hi As Jan has said Checkboxes might be better suited for you circumstance. They are designed to allow multiple choices. Option buttons are normally used to allow only one of a number of choices. Depending on the type being used, you can change the "GroupName" Property to group certain option buttons together. Or you can also do the same by placing each 'group' into a Frame control. -- "Jan Karel Pieterse" <pieterse_home@compuserve.com wrote in message news:101a801c21756$f6d58590$3aef2ecf@TKMSFTNGXA09... | Hi, | | The option buttons are designed to choose between a number | of items. I'm not sure, but I think you can only have one | selected option button per worksheet (haven't tried | though). | | To do what you want, use the checkbox. | | Regards, | | Jan Karel Pieterse | Excel TA/MVP | | -----Original Message----- | I have one column, a1, a2, a3, a4 In each I have 2 (form) | option buttons | When I click on the option buttons in a4 the ones in a1 | change to | I want each cell to be seperate so that when I click on | the option button in a1 one is on the other is off. But I | do not want the option buttons to change in the other | cells. | | Hope someone can help | . | |
| 276 | Chris, try =SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0")) Btw, why are you not using the same naming system for your sheets? if the first sheet was named P1, then you could use this =SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'P"&ROW(INDIRECT("1:10"))&"'!D38"),"<0")) also note that this will return a #DIV/0 error if all cells are empty, you can prevent that by using wrap it in an IF function like =IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0"))=0,0,SUM('Patient 1:P10'!D38)/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Patient 1","P2","P3","P4","P5","P6","P7","P8","P9","P10"}&"'!D38"),"<0"))) if there are no negative numbers you could wrap with =if(sum('Patient 1:P10'!D38)=0,0,etc.. instead.. Also answered through email with sample attached -- Regards, Peo Sjoblom "Chris Bullock" <JohnCBullock@hotmail.com wrote in message news:e94701c217b5$6b9c73d0$36ef2ecf@tkmsftngxa12... Peo Sjoblom Thank You for your help with this, I did enter the formula you gave, although I did not understand what you meant by; entered with ctrl+shift&enter. I changed the cells referenced and this is the formula as I tried to use it =AVERAGE(IF('Patient 1:P10'! D38<0,'Patient 1:P10'!D38)) I am getting the #REF! error message, cell reference not valid. I do not understand why, Worksheet 1 thru 10 column D row 38 are all valid. If you are still willing to help with this I could definitely use the expert advice -----Original Message----- Try =AVERAGE(IF(A1:A100<0,A1:A100)) entered with ctrl+shift&enter -- Regards, Peo Sjoblom "Rob Fenn" <rob-fenn@maurice-phillips.co.uk wrote in message news:eBZb0h6FCHA.1360@tkmsftngp05... Chris Try the following. SUM(A1:A100)/COUNTIF(A1:A100,"0") but this will only work if there are no values less than 0. I am sure someone can think of an easier way but presumably this would work SUM(A1:A100)/(COUNT(A1:A100)-COUNTIF(A1:A100,"0")) A1:A100 is your range of data. HTH Rob "Chris Bullock" <JohnCBullock@hotmail.com wrote in message news:1027d01c217a7$48d06770$19ef2ecf@tkmsftngxa01... I am using Average functions accross worksheets and I need it to not use the cells that have a zero value in the average calculations. Is there a formula that will take care of this? The workbook is huge and deleting each cell with a zero value by hand will be very difficult. I have tried some If statements but have not been able to make any work because of limited knowledge. . |
| 291 | Right click in a blank area of the menu bar and select customize from the dropdown. in the first tab, click on the worksheet menu bar and hit the reset button - or if you mean the whole worksheet menu bar is missing, click in the checkbox next to it. "Will Fleenor" <will@k2e.com wrote in message news:OxMOdl7FCHA.2544@tkmsftngp08... I messed up my main menus in excel by dragging them off the page. Now I have no "File" menu option. How do I fix this? Will Fleenor will_nospam_@k2e.com |
| 292 | You are super. Thanks for all your wonderful code. "" <dmcritchie@msn.com wrote in message news:#iu1L46FCHA.2272@tkmsftngp09... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column < 3 And Target.Column < 5 Then Exit Sub If UCase(Target.Value) = "XXX" Then _ Target.Value = "'" & Format(Date, "yyyy-mm-dd") End Sub Target is a variable name. See Help -- Index -- ByVal look at Sub Statement about half way down. That is about as well as I can answer what Target is you don't want to substitute activecell for anything there. The activecell for me would be target.offset(1,0) since the cursor goes down after entry (tools option) and if you want to change other cells you want to turn off EnableEvents Application.EnableEvents = False '--ooo coding ooo--- Application.EnableEvents = True Worksheet Events and Workbook Events /dmcritchie/excel/event.htm HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Splash" <splash@mosquitonet.com wrote in message news:uh19i93l24r367@corp.supernews.com... Now I'm trying something different. If every time I am in column 4 I want to check to see if the text is "XXX", and, if it is, to run a little code, do I replace "Text" with "Activecell"? [That is, in "ByVal Target As Excel.Range", have "ByVal activecell as Excel.range", and then throughout the code, where Target occurred put activecell??] If so, this opens up all possiblities. |
| 295 | Hey All, I have a workbook that has 100's of cells with formulas linking to values in another worksheet - although the locations of both of these worksheets has remained the same, somehow the worksheet with the links has 'lost' the worksheet with the source data. Every time I open it I now have to point excel to the source worksheet. is there any way to fix this short of rewriting the formulas? Thanks, Justin |
| 302 | I did that with no success. The Age field is being used as Criteria for sorting. Does that make a difference? Thanks. -Dave Trop- "Tom Ogilvy" <twogilvy@msn.com wrote in message news:uYKh8z4FCHA.2296@tkmsftngp05... I suspect the number in the Age field is being stored as Text. Changing the format after the value is in the cell will not change how it is stored. Format the cells as general before you do the paste special (although a straight paste should work - as you state). Regards, Tom Ogilvy "Dave Trop" <dmtrop@cox.net wrote in message news:hyQP8.58441$Hn4.2007994@news1.east.cox.net... In working with the data further, I find it is just the Age field that is affecting the macro. Even if I format it as General or Number, I cannot get the macro to work. If I type the same number into each cell that was copied there, the macro works fine. ???? "Dave Trop" <dmtrop@cox.net wrote in message news:53QP8.58314$Hn4.1998285@news1.east.cox.net... I have recorded a macro to sort, copy and paste text data (Name, Sex, Age) in a defined dynamic range onto another worksheet. If I actually type the data into the defined range, the macro works fine, but if I copy and paste the data from another workbook into the defined range, the macro does not recognize any of the data I have pasted. I have even typed a few sets of data in, then pasted (and even tried "paste special - values") a few sets beneath them, then typed in a few more below those and when I run the macro, only the typed-in data is recognized. Can anyone explain this to me? I thought pasted or pasted-special data was treated the same as data that was keyed in directly. Thank you for any assistance. -Dave Trop- dmtrop@cox.net |
| 304 | I'm familiar with the auditing feature. But it would be nice to be able to generate a list (maybe a separate worksheet) that lists all of the cells which have precedents and their precedents. And/or another list that lists all cells that have dependents and their dependents. |
| 311 | Excel is capable of displaying only 56 colors at a time (it's color palette). The default color palette contains 46 different colors - 10 of them are duplicated. If you use VBA to specify an RGB color that's not in the palette, Excel will use the closest match. In other words, if you write a macro to change a cell's color using RGB values, there is no guarantee that the actual color will be displayed. Here's a macro that will give you some info about the color palette in the active workboook. It adds a new worksheet and then write six columns of data. Sub DisplayColorPalette() Dim r As Long Worksheets.Add Range("A1") = "Index" Range("B1") = "Color" Range("C1") = "Value" Range("D1") = "Red" Range("E1") = "Green" Range("F1") = "Blue" Range("A1:F1").Font.Bold = True For r = 2 To 57 Cells(r, 1) = r - 1 Cells(r, 2).Interior.Color = ActiveWorkbook.Colors(r - 1) Cells(r, 3) = ActiveWorkbook.Colors(r - 1) Cells(r, 4) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 0 And 255 Cells(r, 5) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 1 And 255 Cells(r, 6) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 2 And 255 Next r End Sub John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Work Fast Party Horror" <news@busymonster.com wrote in message news:tQ5Q8.360$bK.67166@news.uswest.net... i'm trying to create a spreadsheet with 4 columns: red, green, blue, and color. the red, green, and blue cells are rgb values. i want the 4th cell to have its background color set to the rgb values in its row. help! this seems like it should be a simple thing to do in excel, but i can't find a way to make it happen. i've explored conditional formatting, script editing, and vb, but no luck. any suggestions on how to make this work? thanks! |
| 312 | Hi Justin, One possibility, if that other workbook is personal.xls it should be in your XLSTART library that you say you didn't change any locations. It should also be hidden Window, hide Perhaps you just need to open one of the workbooks before the other. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Justin" <arki3345@amtrak.com wrote in message news:1041701c217c9$04920920$19ef2ecf@tkmsftngxa01... Hey All, I have a workbook that has 100's of cells with formulas linking to values in another worksheet - although the locations of both of these worksheets has remained the same, somehow the worksheet with the links has 'lost' the worksheet with the source data. Every time I open it I now have to point excel to the source worksheet. is there any way to fix this short of rewriting the formulas? Thanks, Justin |
| 321 | I needed to do the same thing and the best way (lowest overhead) is to use a simple txt file In my case QuoteNumber.txt. Simple open Notepad and save a blank file titled QuoteNumber in the c:\ root directory, each time Sub GetQuoteNumber is run the macro will do the rest. Have fun! . Sub GetQuoteNumber() Dim TedsNumber As Long Dim FileNum As Integer 'gets any available file number FileNum = FreeFile Open "c:\QuoteNumber.txt" For Random As #FileNum Len = Len(TedsNumber) 'gets the current value Get #FileNum, 1, TedsNumber 'increments by one TedsNumber = TedsNumber + 1 'places the new value to file Put #FileNum, 1, TedsNumber Close #FileNum 'places the new incremented number into a worksheet called Sheet2 in cell C8 Sheets("Sheet2").Range("C8").Value = TedsNumber End Sub "Wilson" <jwilson@wickes.com wrote in message news:eBrqY#5FCHA.1692@tkmsftngp05... Hi Ian, One way would be to store the invoice number in an unused cell with the font color the same as the background color of that cell, say N1 for example Then get the invoice number that is visible with =N1 In ThisWorkbook module, increment the number by one whenever the file is opened Private Sub Workbook_Open() Range("N1").Value = Range("N1").Value + 1 End Sub HTH "Ian Roberts" <Ian_Roberts@SPAMOFFBTinternet.com wrote in message news:aeq2su$dds$1@nntp-m01.news.aol.com... Hi I've made a custom invoice template using Excel 2000. It would be useful if there was a way to keep a record of the invoice number so that it could automatically be incremented each time a new invoice is created. I tried doing a search but couldn't find much. The few 3rd party invoice templates I've seen have the macros hidden so I don't know how to reproduce the function on my sheet. I would appreciate any assistance or links to relevant info. Many thanks. Ian I^) |
| 323 | Thanks for the info. (50 meg might be worth it to find out if it can open files). Ron de Bruin wrote: 50 mb Dave I have download it for my brother two weeks ago.(10 minuts that is better than your puny modem) O yes I forgot somthing <vbg Regards Ron "Dave Peterson" <ec35720@msn.com schreef in bericht news:3D0FA860.66014496@msn.com... Just a question and guesses: Does excel blow up (with the same error) with any other workbook? If yes, then it's probably not this workbook. If no, then maybe the workbook is corrupted (like Dave suggested). I've had luck using xl2002 opening files that both xl97 and xl2k couldn't open. Sometimes formatting was lost, but the data/formulas/macros were there. Other people have reported that Sun's OpenOffice/StarOffice can sometimes open corrupted excel files. (It used to be between 80-110 meg download. I looked and decided that it's too big for my puny modem!) Not-for-free StarOffice. /software/download/desktop_app.html Free version /dev_docs/source/1.0.0/index.html IrelandA wrote: Dear All I have got a 3MB worksheet which I cannot open. When I try to open it, I get the following dialogue box: =================================== Application Error The instruction at "0x30099749f" referenced memory at "0x00000002". The memory could not be "read". Click on OK to terminate the application. Click on Cancel to debug the application. =================================== Even if I click on "Cancel", it doesn't open Excel, so I can't view the spreadsheet at all. Any help/advice gratefully received... Regards Alistair -- Dave Peterson ec35720@msn.com -- Dave Peterson ec35720@msn.com |
| 331 | If i use the 'find' option to find a certain instance of a value or word, excel finds me every instance on that worksheet, but how do i get it to search every worksheet also within that file ? Cheers Andy |
| 333 | I have a worksheet of product prices....in which I've hidden the column that contains my cost. I've protected the sheet with a password so the costs can't be revealed. However, if I copy my columns....including the hidden column....and paste into word.....presto...there is my hidden column with all my costs. Any way around this security glitch? |
| 335 | Hi, I wish to know the maximum number of columns which can be created in an excel worksheet. I am unable to create more tha 256 columns. I have a large volume of data which typically exceeds 256 columns. I will be happy to know any workaround for this problem. It would also be helpful to know if there is any setup change which i can make to increase the number of columns. Thank you Regards Balaji |
| 336 | Depends on what version of xl you're using. xl2k (IIRC) added an option under Data|Import External Data| Then Import Data Point at your file (*.txt ???) And you'll see the Text Import (like Text to Columns) wizard show up. Follow the wizard, but on the the last dialog, you'll be prompted to determine where to put the data. (There's a properties button there, too. You may want to click there and tell excell that you don't want it to keep track of this query. (xl won't try to refresh later.) (I'm using xl2002, so the menus might be slightly different--not sure.) I learned on xl97 and I still import to a new worksheet and just copy the data to the location that I want. Richard Nelson wrote: Is there a way to import text data to specific cells in a spreadsheet by stating the cell address and then the data value for that cell? -- Dave Peterson ec35720@msn.com |
| 339 | If you're using xl2k or higher, you can select the worksheets (click on the first, control click on subsequent) and then do the find. You may want to try Jan Karel Pieterse's FlexFind at: /MVP/Default.htm (In fact, xl2002 has an option on the Find dialog that asks where to look: Within sheet or within workbook.) Andrew wrote: If i use the 'find' option to find a certain instance of a value or word, excel finds me every instance on that worksheet, but how do i get it to search every worksheet also within that file ? Cheers Andy -- Dave Peterson ec35720@msn.com |
| 340 | Is there a way to name a tab with the contents of a cell? For example if A9 ="Soap" the worksheet tab will be labeled Soap. |
| 348 | activeworkbook.name=[a9] -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Bob" <bobnanw@optonline.net wrote in message news:103ba01c217e1$923e1da0$9be62ecf@tkmsftngxa03... Is there a way to name a tab with the contents of a cell? For example if A9 ="Soap" the worksheet tab will be labeled Soap. |
| 349 | No -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Balaji" <fd96241@rediffmail.com wrote in message news:e73001c217e0$285334d0$9ee62ecf@tkmsftngxa05... Hi, I wish to know the maximum number of columns which can be created in an excel worksheet. I am unable to create more tha 256 columns. I have a large volume of data which typically exceeds 256 columns. I will be happy to know any workaround for this problem. It would also be helpful to know if there is any setup change which i can make to increase the number of columns. Thank you Regards Balaji |
| 357 | Hi Murray 65,536 per worksheet -- Regards William willwest22@yahoo.com "Murray" <Murray.hall@markelintl.com.au wrote in message news:1091d01c217ed$57362750$39ef2ecf@TKMSFTNGXA08... | Hi, how many rows does Excel 97 have. I thought read some | where that it was just over the 16000 mark. | Thanks Murray |
| 361 | I have MS Excel spreadsheets that have multiple worksheets copied to a disk and am unable to open these files in Microsoft Works Spreadsheet with all the worksheets. How do I get my files in their original format? |
| 375 | thanks, this is a great start! but, boy, the palette size is an annoying limitation. some more questions: 1. can i set up a new color palette by assigning my own specific rgb values to the 56 colors in the palette? 2. can i change color palettes when i change worksheets? that way i could give the "illusion" of having more colors by only having 56 per page...? thanks, mark "John Walkenbach" <john@j-walk.com wrote in message news:uQxYnG9FCHA.1424@tkmsftngp04... Excel is capable of displaying only 56 colors at a time (it's color palette). The default color palette contains 46 different colors - 10 of them are duplicated. If you use VBA to specify an RGB color that's not in the palette, Excel will use the closest match. In other words, if you write a macro to change a cell's color using RGB values, there is no guarantee that the actual color will be displayed. Here's a macro that will give you some info about the color palette in the active workboook. It adds a new worksheet and then write six columns of data. Sub DisplayColorPalette() Dim r As Long Worksheets.Add Range("A1") = "Index" Range("B1") = "Color" Range("C1") = "Value" Range("D1") = "Red" Range("E1") = "Green" Range("F1") = "Blue" Range("A1:F1").Font.Bold = True For r = 2 To 57 Cells(r, 1) = r - 1 Cells(r, 2).Interior.Color = ActiveWorkbook.Colors(r - 1) Cells(r, 3) = ActiveWorkbook.Colors(r - 1) Cells(r, 4) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 0 And 255 Cells(r, 5) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 1 And 255 Cells(r, 6) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 2 And 255 Next r End Sub John Walkenbach For Excel tips, macros, & downloads... -walk.com/ss "Work Fast Party Horror" <news@busymonster.com wrote in message news:tQ5Q8.360$bK.67166@news.uswest.net... i'm trying to create a spreadsheet with 4 columns: red, green, blue, and color. the red, green, and blue cells are rgb values. i want the 4th cell to have its background color set to the rgb values in its row. help! this seems like it should be a simple thing to do in excel, but i can't find a way to make it happen. i've explored conditional formatting, script editing, and vb, but no luck. any suggestions on how to make this work? thanks! |
| 386 | How are you applying the names and what do those names look like. You can have workbook level names and you can have worksheet level names. Worksheet level names start with the worksheet name then exclamation point then the range. 'my Sheet'!Test could refer to 'my sheet'!$a$1:$b$99 But since it starts with the worksheetname, it's not available on other sheets (unless you qualify it nicely). So I can have a workbook level name called Test, and worksheet level names of Test, too. == One more thing to check. Insert|Name|Define Do you see a worksheet name to the right of that box? If yes, then you've been using worksheet names. If not, then it must be something else! Troy Thiele wrote: I have been given a workbook with many formulas. I have been trying to name cells and apply those names to references in formulas on other sheets within the same workbook. Although it works fine if I am on the same sheet, I get a message indicating that Microsoft cannot find any references if I try to apply the names on different sheets. Ideas as to why this is occuring? Thanks, Troy -- Dave Peterson ec35720@msn.com |
| 387 | Your basic problem is that you're using a VBA word in your function. Change Name to myName and this problem should go away. What's happening is that your code is under a worksheet. Excel thinks unqualified references belong to the thing (the worksheet that the code is in) owns that. Kind of like activesheet and Range("A1") in a general module. Since you're using Name (and I bet it wasn't declared), excel sees that as belonging to the sheet that owns the code. So this line Name = Range("P10").Value is equivalent to: me.Name = Range("P10").Value (in a general module terms--not really accurate here: activesheet.Name = Range("P10").Value) Fix your variable name and then you'll be off to the next fire! Bruce wrote: I have this macro which replaces a portion of a column with a value whish is entered by the user. For some unknown reason, when a number is entered it changes not only the specified cells in the column, but the Worksheet name too! The macro also converts the entered number to a predfeined string via an HLOOKUP. I have a lot of other macros which rely on this worksheet name and do not want it to change at all. the code is below. Please help! ElseIf Target.Address = "$P$10" Then OptionCheck = IsNumeric(Range("P10").Value) y = (Range("B15").Value) If OptionCheck Then MyVar = MsgBox("This will replace all the options below. Do you wish to continue?", 1, "Option Change") If MyVar = 1 Then For x = 16 To (15 + CInt(y)) Range("O" & x).Value = (Range("P10").Value) Next x Name = Range("P10").Value Range("P10").Value = "=HLOOKUP(" & Name & ",OptionNames,2)" End If End If -- Dave Peterson ec35720@msn.com |
| 388 | You can do it in xl2002, but you'd have to write a macro in earlier versions. You can protect a worksheet so that your code can make changes. But it has to be reset each time you open the workbook: something like: Worksheets("sheet1").Protect Password:="pass", UserInterFaceOnly:=True could go into workbook_open or auto_open. then give the users some buttons assigned to your macros: ActiveCell.EntireRow.Insert You could even prompt them and ask how many and where. Sue wrote: Is there any way to protect individual cells in a worksheet with disabling the ability to insert rows into the same worksheet? As soon as I turn on the protection, I loose the ability to insert. -- Dave Peterson ec35720@msn.com |
| 391 | Sort of strange. Have you tried doing a search for that file on your computer? I'm sure deleting the file wouldn't get rid of the error, but it may provide a clue as to why you are getting that error. -----Original Message----- Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff . |
| 392 | Try this Go into Windows Explorer...then Tools-Folder Options. File types tab. Scroll down to xls. Select Open in the bottom window and hit edit. Mine shows this: "c:\Program Files\Microsoft Office\Office\EXCEL.EXE" /e and: Use DDE is checked and this shows: [open("%1")] Hope it works! -----Original Message----- Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff . |
| 393 | I've never seen a missing link (huh?) cause this, but everyone should have a copy of Bill Manville's Findlink.xla. Download FINDLINK.ZIP from /mvp Links can hide in mysterious places and this'll find 'em. ============ But this sounds like there might be a macro hiding out. If you open this workbook with macros disabled, can you print without this error message? If yes, then we're on to something! Hit Alt-F11 to get to the VBE. Hit Ctrl-R to see the project explorer. Find your workbook's project. should look like: VBAProject (yourworkbooknamehere.xls) Expand any compressed items (like windows explorer with the box with +'s in them). Look for ThisWorkbook. Double click on that. Do you see any code in the code window (usually to the right)? Might start with: Private Sub Workbook_BeforePrint(Cancel As Boolean) If yes, then you've found the problem(?). The real question is what to do next. Should you delete it or modify it to work the way it was intended? Post back if any of this came true and you need help. Jeff wrote: Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff -- Dave Peterson ec35720@msn.com |
| 394 | Just tried your suggestion and my settings are currently as you've listed below. Any other ideas? Thanks, Jeff "Eric" <eric.goodrich@paccoast.com wrote in message news:f06e01c218ad$a7a6f1e0$37ef2ecf@TKMSFTNGXA13... Try this Go into Windows Explorer...then Tools-Folder Options. File types tab. Scroll down to xls. Select Open in the bottom window and hit edit. Mine shows this: "c:\Program Files\Microsoft Office\Office\EXCEL.EXE" /e and: Use DDE is checked and this shows: [open("%1")] Hope it works! -----Original Message----- Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff . |
| 395 | Hi Dave, Just tried your suggestion and no luck. I rebooted and opened the file in Excel - didn't ask if there were any macros. I then followed your instructions to see if there were any macros - there are none. I'm going to download the program you just mentioned and try that. I'll post back here if that doesn't work. Jeff "Dave Peterson" <ec35720@msn.com wrote in message news:3D125E1D.FB0944ED@msn.com... I've never seen a missing link (huh?) cause this, but everyone should have a copy of Bill Manville's Findlink.xla. Download FINDLINK.ZIP from /mvp Links can hide in mysterious places and this'll find 'em. ============ But this sounds like there might be a macro hiding out. If you open this workbook with macros disabled, can you print without this error message? If yes, then we're on to something! Hit Alt-F11 to get to the VBE. Hit Ctrl-R to see the project explorer. Find your workbook's project. should look like: VBAProject (yourworkbooknamehere.xls) Expand any compressed items (like windows explorer with the box with +'s in them). Look for ThisWorkbook. Double click on that. Do you see any code in the code window (usually to the right)? Might start with: Private Sub Workbook_BeforePrint(Cancel As Boolean) If yes, then you've found the problem(?). The real question is what to do next. Should you delete it or modify it to work the way it was intended? Post back if any of this came true and you need help. Jeff wrote: Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff -- Dave Peterson ec35720@msn.com |
| 396 | Application.ScreenUpdating = False will freeze the screen - better is not to select and activate unless absolutely necessary. If you have stuff like Worksheets("Sheet1").Activate Range("A1").Select Selection.Value = 24 you could just do Worksheets("Sheet1").Range("A1").Value = 24 and if Sheet2 were the active sheet, you would see nothing happen, but the cell would be given the value of 24. This is also much faster and essential if you ever want to get beyond mimicking physical actions. Bert Visscher <bfv@xs4all.nl wrote in message news:aetf8d$j0f$1@news1.xs4all.nl... Hello everyone, Someone suggested that there was a possibility to have a specific screen showing, while a macro is running. Rather than showing how Excel races through the macro. If this is the case and someone knows how this can be done, then could you please tell me? TIA and regards, Bert Visscher. |
| 400 | It sure sounds like a macro to me (but I've been wrong lots of times). Maybe there's an addin that's looking for print events?? Try running excel in safe mode. close excel Windows Start button|Run Excel /S It won't look the same, but it's a vanilla version of excel running. Try loading your workbook and printing then. If that works, then I (still) think it might be a macro. When you were in the VBE, did you notice the names of the workbooks that were open in the project explorer? Mine look like: atpvbaen.xls (atpvbaen.xla) funcres (funcres.xla) personal (personal.xla) vbaproject (book1) The first two are related to Excel's analysis toolpak (worksheet and VBA versions). The personal one is mine. The last one is the current workbook. Do you have more/different? (If you do and they look suspicious, you may want to experiment more. These addins usually reside in the XLStart folder (varies with versions of xl and versions of windows). The other spot is under Tools|Addins. Move all the files out of your xlstart, uncheck all the addins in Tools|Options and restart excel. Try your workbook. If it works then, you're on the way to finding the culprit. Start adding things back one at a time. Restart excel (each time) and try it out. If it breaks, you found it. If not, continue reloading stuff. === But I'm pretty much out of ideas after this. Good luck. ======= FWIW, I didn't think FindLink would work in this case, but don't throw it away. It'll come in handy someday! Jeff wrote: Hi Dave, Just tried the FINDLINK.ZIP program. Was able to install it just fine but it didn't find my "mystery" document. Any other ideas? Thanks, Jeff -- Dave Peterson ec35720@msn.com |
| 406 | Eric, Protection is really intended to prevent changes to data, not hiding it. Also, I've tried pasting some cells with a hidden column into Word in both XL97 and XP, and the hidden column remained hidden, so I can't reproduce your situation. Say more about how you're doing this. Some things that come to mind are: Move the costs column out of the range of cells that you'll be copying/pasting (Select the column, hold Shift as you drag it by the edge laterally -- the formulas will adjust as needed), or move it into another sheet altogether (Cut the column, and paste it into another sheet). Are you wanting to prevent the costs from showing when others do the copy/pasting (that is, make sure that no one working with the sheet can reveal the costs), or are you wanting to simply get it pasted into Word yourself, without the costs showing in the Word document? Regards from Virginia Beach, EarlK ------------------------------------------------------------- "Eric" <eric.goodrich@paccoast.com wrote in message news:101c901c217de$f526bb50$b1e62ecf@tkmsftngxa04... I have a worksheet of product prices....in which I've hidden the column that contains my cost. I've protected the sheet with a password so the costs can't be revealed. However, if I copy my columns....including the hidden column....and paste into word.....presto...there is my hidden column with all my costs. Any way around this security glitch? |
| 409 | Hi Jeff, Look in the insert menu for a defined name. Insertnamedefine there may be a link there. I thought Bill's findlink checked there. It may also be a hidden xl4 macro sheet. John "Jeff" <no_spam_jlamarca@fea.net wrote in message news:#4r58#KGCHA.1592@tkmsftngp10... Just tried your suggestion and my settings are currently as you've listed below. Any other ideas? Thanks, Jeff "Eric" <eric.goodrich@paccoast.com wrote in message news:f06e01c218ad$a7a6f1e0$37ef2ecf@TKMSFTNGXA13... Try this Go into Windows Explorer...then Tools-Folder Options. File types tab. Scroll down to xls. Select Open in the bottom window and hit edit. Mine shows this: "c:\Program Files\Microsoft Office\Office\EXCEL.EXE" /e and: Use DDE is checked and this shows: [open("%1")] Hope it works! -----Original Message----- Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff . |
| 422 | Hi John, Just checked Insertnamedefine and found nothing there (except for the data contained in the cell where the cursor is at the time I check). Any other ideas? This is just too weird. Thanks, Jeff "jaf" <johnf87@nohotmail.com wrote in message news:upMFAiMGCHA.2228@tkmsftngp08... Hi Jeff, Look in the insert menu for a defined name. Insertnamedefine there may be a link there. I thought Bill's findlink checked there. It may also be a hidden xl4 macro sheet. John "Jeff" <no_spam_jlamarca@fea.net wrote in message news:#4r58#KGCHA.1592@tkmsftngp10... Just tried your suggestion and my settings are currently as you've listed below. Any other ideas? Thanks, Jeff "Eric" <eric.goodrich@paccoast.com wrote in message news:f06e01c218ad$a7a6f1e0$37ef2ecf@TKMSFTNGXA13... Try this Go into Windows Explorer...then Tools-Folder Options. File types tab. Scroll down to xls. Select Open in the bottom window and hit edit. Mine shows this: "c:\Program Files\Microsoft Office\Office\EXCEL.EXE" /e and: Use DDE is checked and this shows: [open("%1")] Hope it works! -----Original Message----- Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff . |
| 425 | Hi Dave, I think you've hit on something! I just tried Excel in Safe Mode and it will print! It will not, however, when I'm in regular mode. When I enter VBE, the following are listed in the Project - Project VBA window - Start (OR Start.xla) TbRun97Project (TRRUN9.XLS) VBAPROJECT (FERPA.xls) The are sub headings under the last one, which is also the current workbook. The first two items are mysteries to me and, when I click on them, ask for a password. (Why? I haven't a clue.) I also just tried unchecking all of the addins in Tools/Options (there was only one checked) and tried to find the XLStart folder (I couldn't find it using Excel but believe it may be under Windows, Application Data, Microsoft, Excel. I removed the one file found there and rebooted Excel. Still can't print. This is VERY strange - apparently, you did find something as I can print the file in safe mode. Jeff "Dave Peterson" <ec35720@msn.com wrote in message news:3D127B06.5AB8B95D@msn.com... It sure sounds like a macro to me (but I've been wrong lots of times). Maybe there's an addin that's looking for print events?? Try running excel in safe mode. close excel Windows Start button|Run Excel /S It won't look the same, but it's a vanilla version of excel running. Try loading your workbook and printing then. If that works, then I (still) think it might be a macro. When you were in the VBE, did you notice the names of the workbooks that were open in the project explorer? Mine look like: atpvbaen.xls (atpvbaen.xla) funcres (funcres.xla) personal (personal.xla) vbaproject (book1) The first two are related to Excel's analysis toolpak (worksheet and VBA versions). The personal one is mine. The last one is the current workbook. Do you have more/different? (If you do and they look suspicious, you may want to experiment more. These addins usually reside in the XLStart folder (varies with versions of xl and versions of windows). The other spot is under Tools|Addins. Move all the files out of your xlstart, uncheck all the addins in Tools|Options and restart excel. Try your workbook. If it works then, you're on the way to finding the culprit. Start adding things back one at a time. Restart excel (each time) and try it out. If it breaks, you found it. If not, continue reloading stuff. === But I'm pretty much out of ideas after this. Good luck. ======= FWIW, I didn't think FindLink would work in this case, but don't throw it away. It'll come in handy someday! Jeff wrote: Hi Dave, Just tried the FINDLINK.ZIP program. Was able to install it just fine but it didn't find my "mystery" document. Any other ideas? Thanks, Jeff -- Dave Peterson ec35720@msn.com |
| 426 | Thnaks for your help! -----Original Message----- Your basic problem is that you're using a VBA word in your function. Change Name to myName and this problem should go away. What's happening is that your code is under a worksheet. Excel thinks unqualified references belong to the thing (the worksheet that the code is in) owns that. Kind of like activesheet and Range("A1") in a general module. Since you're using Name (and I bet it wasn't declared), excel sees that as belonging to the sheet that owns the code. So this line Name = Range("P10").Value is equivalent to: me.Name = Range("P10").Value (in a general module terms--not really accurate here: activesheet.Name = Range("P10").Value) Fix your variable name and then you'll be off to the next fire! Bruce wrote: I have this macro which replaces a portion of a column with a value whish is entered by the user. For some unknown reason, when a number is entered it changes not only the specified cells in the column, but the Worksheet name too! The macro also converts the entered number to a predfeined string via an HLOOKUP. I have a lot of other macros which rely on this worksheet name and do not want it to change at all. the code is below. Please help! ElseIf Target.Address = "$P$10" Then OptionCheck = IsNumeric(Range("P10").Value) y = (Range("B15").Value) If OptionCheck Then MyVar = MsgBox("This will replace all the options below. Do you wish to continue?", 1, "Option Change") If MyVar = 1 Then For x = 16 To (15 + CInt(y)) Range("O" & x).Value = (Range("P10").Value) Next x Name = Range("P10").Value Range("P10").Value = "=HLOOKUP(" & Name & ",OptionNames,2)" End If End If -- Dave Peterson ec35720@msn.com . |
| 429 | Are there any restrictions on the amount of data you can put into one cell in a worksheet? If so are there any ways to increase the amount of data you can insert? Tried all the usual ways but no luck! Also when converting a word table to a spreadsheet is there a global way to stop excel converting certain figures into dates or will we have to amend every affected cell? Grateful for any enlightenment! Jenny |
| 431 | Jenny, Not sure about your second question, but in regard to the first question, look in Help under Specifications for questions like this. For Excel 97 and 2000 you should find a 32,000 character limit for each cell: Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar. HTH, Brian Jenny wrote: Are there any restrictions on the amount of data you can put into one cell in a worksheet? If so are there any ways to increase the amount of data you can insert? Tried all the usual ways but no luck! Also when converting a word table to a spreadsheet is there a global way to stop excel converting certain figures into dates or will we have to amend every affected cell? Grateful for any enlightenment! Jenny |
| 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 | -- | | |
| 436 | 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. |
| 437 | Hi, There is no click event for the worksheet, but there is a before doubleclick event and a before rightclick event. You can use either of those and then use the code Dave Hawley gave you. If you don't want the cell to go into edit mode when it is double clicked, include "Cancel=True" as the final command of the event sub. Same goes for avoiding the context menu to appear when you would use the beforerightclick event. Example: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 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 Cancel=True End If End Sub Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- 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. |
| 439 | Hi Scott One method would be =IF($D3="","",IF(M$1$D3,$D5/12,"")) then if D3 is empty, the formula returns nothing "". You may decide you want to have 0, rather than "" as the result. -- Roger Govier Technology 4 U W98SR2 XL2K On Fri, 21 Jun 2002 02:07:22 -0700, "Scott Cardais" <Scott@Cardais.com wrote: 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. . |
| 442 | Roger: Thanks. This works, too. Different approach from Dave but it works just as well. Thanks very much. Scott C. -----Original Message----- Hi Scott One method would be =IF($D3="","",IF(M$1$D3,$D5/12,"")) then if D3 is empty, the formula returns nothing "". You may decide you want to have 0, rather than "" as the result. -- Roger Govier Technology 4 U W98SR2 XL2K On Fri, 21 Jun 2002 02:07:22 -0700, "Scott Cardais" <Scott@Cardais.com wrote: 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. . |
| 448 | Hi Jeff, Look in Excel's Window menu and see if unhide is enabled. If it is choose it. John "Jeff" <no_spam_jlamarca@fea.net wrote in message news:ullUZjOGCHA.1604@tkmsftngp12... Hi John, Just checked Insertnamedefine and found nothing there (except for the data contained in the cell where the cursor is at the time I check). Any other ideas? This is just too weird. Thanks, Jeff "jaf" <johnf87@nohotmail.com wrote in message news:upMFAiMGCHA.2228@tkmsftngp08... Hi Jeff, Look in the insert menu for a defined name. Insertnamedefine there may be a link there. I thought Bill's findlink checked there. It may also be a hidden xl4 macro sheet. John "Jeff" <no_spam_jlamarca@fea.net wrote in message news:#4r58#KGCHA.1592@tkmsftngp10... Just tried your suggestion and my settings are currently as you've listed below. Any other ideas? Thanks, Jeff "Eric" <eric.goodrich@paccoast.com wrote in message news:f06e01c218ad$a7a6f1e0$37ef2ecf@TKMSFTNGXA13... Try this Go into Windows Explorer...then Tools-Folder Options. File types tab. Scroll down to xls. Select Open in the bottom window and hit edit. Mine shows this: "c:\Program Files\Microsoft Office\Office\EXCEL.EXE" /e and: Use DDE is checked and this shows: [open("%1")] Hope it works! -----Original Message----- Here's a new one - I can't print a document becuase I get an error message that states: "Income Statement1.xls" could not be found..blah, blah, blah. . . . The problem is that I've NEVER created a file with this name - I don't have a clue as to what it is! The worksheet I'm trying to print has NOTHING to do with Income anything! This file is not linked to anything (at least, I've never made any such links). UGH! Any ideas? Thanks! Jeff . |
| 451 | I have a database, A column is for number B column is for the name Column C: i want to put a small picture of that product on column C, is that possible? Can someone explain me or send me a worksheet where that is working? Thank you all, Helder |
| 455 | Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK |
| 457 | See answer at your cross posting "Helder" <klix@clix.pt wrote in message news:OSPz1URGCHA.2772@tkmsftngp13... I have a database, A column is for number B column is for the name Column C: i want to put a small picture of that product on column C, is that possible? Can someone explain me or send me a worksheet where that is working? Thank you all, Helder |
| 458 | I'm trying to create a spreadsheet programmatically using OWC (using VS.NET). The file ('Book1.xls') gets created but when I try to open it excel can't display it - it appears crashed, as the main window is not painted. Also, contrary to available documentation, the excel spreadsheet becomes visible as soon as the application object is created, which creates an annoying flicker even if I hide it immediately. Excel::_ApplicationPtr a(__uuidof(Excel::Application)); a-Visible[LOCALE_USER_DEFAULT]=false; Excel::_WorkbookPtr wb = a-Workbooks-Add(); wb-Activate(LOCALE_USER_DEFAULT); Excel::_WorksheetPtr ws = wb-Worksheets-Add(); // Other stuff here, filling in worksheet etc. wb-Save(LOCALE_USER_DEFAULT); wb-Close(); a-Quit(); What have I missed? Tony |
| 467 | 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. |
| 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. . |
| 500 | Hello, Thanks for your reply. I did look into Excel help, and found the same advice you gave me, but it was not helpful. I think you misunderstand my question I don't have copies of a shared workbook, I have several data files of identical format but with different data and with different numbers of records. I am not trying to merge changes to a shared workbook, I am trying to merge/insert/join/combine these several files into a single worksheet so that I can do some sums on all the records therein. My apologies for the confusion KK "Brian Roberts" <meorelse@hotmail.com wrote in message news:e3FLlFYGCHA.2212@tkmsftngp12... Hi, From Excel help: Merge workbooks 1.. Make sure the copies of the shared workbook that you want to merge are all stored in the same folder. To do this, you must give each copy a different name. 2.. Open the copy into which you want to merge changes. 3.. On the Tools menu, click Compare and Merge Workbooks. 4.. If prompted, save the workbook. 5.. In the Select Files to Merge into Current Workbook dialog box, click a copy that has changes to be merged, and then click OK. To merge more than one copy at the same time, hold down CTRL or SHIFT and click the file names, and then click OK. "KK" <trebor@yeleek.nospam.freeserve.co.uk wrote in message news:aev43e$r$1@newsg3.svr.pol.co.uk... Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK |
| 506 | You could have a macro open each text(?) file individually and then have your macro do the copy and paste. But if you recall your old DOS commands, there was a way to merge text files into one. Let's say they are named MyFile0001.txt through myFile0999.txt. And they're in the same folder. Just shell to DOS (Windows Start button|Programs|MS DOS Prompt (for win98)) May be "command prompt" in NT (IIRC). Then traverse to that folder. when you're there, just type: copy myfile*.txt allfiles.txt All the files that start with myfile and have an extension of .txt will be copied into allfiles.txt. Then Exit from the dos prompt and open that one in excel. ======== There are a few warnings about the DOS copy command. You couldn't do this without getting into trouble. copy *.txt allfiles.txt It would try to add allfiles.txt to allfiles.txt. You could even do something like: copy c:\mydir\*.* c:\allfiles.txt by putting the "to" file into a separate folder, we can combine all the files using a wildcard. ======== If you still want a macro to open up each text file and do the copy and paste, post back with some more details. Are the files all in one folder? Are they the only files in that folder? If not is there a unique identifier for the group? (It's kind of the same answers you need to do the DOS copy solution, too.) good luck, KK wrote: Hello, Thanks for your reply. I did look into Excel help, and found the same advice you gave me, but it was not helpful. I think you misunderstand my question I don't have copies of a shared workbook, I have several data files of identical format but with different data and with different numbers of records. I am not trying to merge changes to a shared workbook, I am trying to merge/insert/join/combine these several files into a single worksheet so that I can do some sums on all the records therein. My apologies for the confusion KK "Brian Roberts" <meorelse@hotmail.com wrote in message news:e3FLlFYGCHA.2212@tkmsftngp12... Hi, From Excel help: Merge workbooks 1.. Make sure the copies of the shared workbook that you want to merge are all stored in the same folder. To do this, you must give each copy a different name. 2.. Open the copy into which you want to merge changes. 3.. On the Tools menu, click Compare and Merge Workbooks. 4.. If prompted, save the workbook. 5.. In the Select Files to Merge into Current Workbook dialog box, click a copy that has changes to be merged, and then click OK. To merge more than one copy at the same time, hold down CTRL or SHIFT and click the file names, and then click OK. "KK" <trebor@yeleek.nospam.freeserve.co.uk wrote in message news:aev43e$r$1@newsg3.svr.pol.co.uk... Hello, I have a number of data files generated by a Vbasic program. The files are of identical format though with a varying number of records. I need to open all these data files into a single Excel worksheet and do some simple sums. I'm sure I'm missing something simple, but I can't see how to merge several excel files into a single worksheet, other than using copy / paste which seems a bit clumsy. With thanks KK -- Dave Peterson ec35720@msn.com |
| 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. . |
| 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. . . |
| 536 | Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --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 "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, |
| 542 | Could somebody tell me how to do the following please: 1. Create printable labels from the ITEM NUMBER (ROW NUMBER) COLUMN A COLUMN B COLUMN C 2. Incorporate a search option onto a worksheet Thanks Piers |
| 544 | Is the check box grey, or the words (Merge cells)? If there are different formats on the worksheet, the check mark in the check box will be grey. Click once in the check box to add a check mark Click again, to remove the check mark. Click OK Pat Kung wrote: I am trying to sort my spreadsheet, but it keeps telling me I have merged cells and can't do that. The problem is I can't find the merged cells. I tried to highlight the whole spreadsheet and uncheck merged cells, but it is grayed out when I highlight the whole spreadsheet. Anybody have a way to find out what cells are merged so I can unmerge them? Thanks in advance -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 546 | Hi Tom, I don't know if a filtered list would work for your search requirement. A filtered list is treated like those are the only entries in the sheet for many purposes such as copy and paste, mail merge. To create labels with Excel as the database we generally use MS Word for the printing of labels since Excel does not have builtin capability for printing labels. See my page Using Mail Merge with data from Excel /dmcritchie/excel/mailmerg.htm I have included some summary label information to help you pick an actual label or that you can use to print same on plain paper. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "Tom" <auctionid4me@yahoo.co.uk wrote in message news:aa4R8.20086$ZR3.1838683@news6-win.server.ntlworld.com... Could somebody tell me how to do the following please: 1. Create printable labels from the ITEM NUMBER (ROW NUMBER) COLUMN A COLUMN B COLUMN C 2. Incorporate a search option onto a worksheet Thanks Piers |
| 553 | Phil - 1/ You could look up Data Type Summary in VBA help, which leads to a description of the various data types. 2/ You could set up a moving average in your worksheet. Say you have values in B2:B100, and you want a moving average of six consecutive numbers. In C7, enter this formula =average(B2:B7) And drag this down to C100. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <1075a01c21858$c6327760$9be62ecf@tkmsftngxa03, Phil said... Hi, I'm using excel to plot some experimentally determined data, however the resulting chart has glitches due to rounding errors. To what accuracy does excel store numbers? And, is there a smoothing type of function for plotted data, whereby say, 6 consecutive numbers, for example, are averaged to give a smoother curve? Thanks Phil |
| 554 | Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --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 "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . |
| 557 | Select all the numbers. Then EditReplace hold the Alt key and use the number keypad on the right of the keyboard to enter 0160 as the "Find what". Be sure the "Replace with" is empty. Replace All. That is a common HTML character that looks like a space. Alternatively, download Refinate and use it for free to convert to numbers by dragging and clicking (apply Refinate's Number-Values action). --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 "George" <george39672002@yahoo.com wrote in message news:112b301c21a32$52c03390$b1e62ecf@tkmsftngxa04... Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --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 "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . |
| 577 | Could you use a Worksheet_Change event instead? If the cell reference in one of the four cells is changed, the chart is updated. For example: Private Sub Worksheet_Change(ByVal Target As Range) Dim str1 As String Dim str2 As String Select Case Target.Address Case "$H$4", "$H$5", "$I$4", "$I$5" str1 = "Sheet1!" & Range("H4") & ":" & Range("H5") str2 = "Sheet1!" & Range("I4") & ":" & Range("I5") Worksheets("Sheet1").ChartObjects(1).Chart _ .SeriesCollection(1).Formula _ = "=SERIES(," & str1 & "," & str2 & ",1)" End Select End Sub John Vera wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 578 | You could add some code that will insert the date in the footer before the sheet is printed. For example, the following code (in the ThisWorkbook module) adds the formatted date to the right footer: Private Sub Workbook_BeforePrint(Cancel As Boolean) Worksheets("Sheet1").PageSetup.RightFooter = _ Format(Date, "dd-mmm-yy") End Sub Vittal wrote: So I am the first visitor. Can anybody tell me how to change the date format in footer. I am using WIN 98, Office 2000. Presently it is coming in dd-mm-yy format. The Regional Settings properties for date tab are as follow: dd-mmm-yy I want the date format to look like 21-Jun-02. Thanks in advance. Vittal -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 579 | John - Jason's suggestion might have worked, except it seems that the charting engine in Excel isn't as good with named ranges as Excel's worksheet engine. It won't work with names that use INDIRECT and some other select functions within their RefersTo formulas. This is a known problem, or at least I've often had the problem and have discussed it on these newsgroups. You could set up a slightly different approach: put the column in B10, the starting row in B11, and the ending row in B12. Define your name "catx" as =OFFSET(Sheet2!$A$1,Sheet2!$B$11-1,Sheet2!$B$10-1,Sheet2!$B$12-Sheet2!$B$11+1 ,1) That's all in one line, watch the dreaded newsreader word wrap. Fortunately, the charting mechanism accepts defined formulas that use OFFSET. In some cases, you can use Index in the Define Names dialog, use a range on a worksheet to store the values of this defined range in, then plot the worksheet range in the chart. This way, the chart doesn't choke on the INDIRECT function. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <1108e01c2196e$77a600a0$9be62ecf@tkmsftngxa03, John Vera said... Hi. Try as I might, I can't make it work. I changed the sheet and file names, but still it says there's an error when I type in the range name (=Mybook.xls!catx in your example) thanks for your effort! John -----Original Message----- Try defining a name that uses INDIRECT. For example, for the x values, go to Insert Name Define and in the "Refers to:" box put: =INDIRECT("Sheet2!"&Sheet2!$B$11&":"&Sheet2!$B$12) Call it something like catx and hit OK. Go into the Source Data Series box of your chart and at the bottom in the "Category (X) axis labels" put: =Mybook.xls!catx Changes the file name and worksheets names to match your setup. Apply the same logic above when for the Values in each of your series. HTH Jason Atlanta, GA -----Original Message----- Hi again. WHat happens is that I have cells with cell names on them as a string ( B11 says "$F$12", without quotes; B12 says "$F$16", without quotes). I want to use the "indirect" command to establish $F$12 and $F$16 through B11 and B12 as the x range for my xy graphs, and other similar cells for the y range. Problem is, I can't use indirect in the x, y ranges because it says it is not a valid function. Any Ideas? Thanks, JOhn . . |
| 580 | Well, this works if an extraneous nonprinting character makes Excel think there is text, not numbers, in the cells. But I see 'M's and 'B's, which are text, too. Here's how to change them into numbers that Excel understands: Select the numbers. Do a find and replace, and replace B with E9, then replace M with E6. Billions are 10^9 or 1E9, and Millions are 10^6 or 1E6. This converts the non-numbers into numbers (I did a little test just now), so they can be sorted normally. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <a36R8.17345$Fv1.1219940@newsread2.prod.itd.earthlink.net, adetaylor said... Select all the numbers. Then EditReplace hold the Alt key and use the number keypad on the right of the keyboard to enter 0160 as the "Find what". Be sure the "Replace with" is empty. Replace All. That is a common HTML character that looks like a space. Alternatively, download Refinate and use it for free to convert to numbers by dragging and clicking (apply Refinate's Number-Values action). --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 "George" <george39672002@yahoo.com wrote in message news:112b301c21a32$52c03390$b1e62ecf@tkmsftngxa04... Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --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 "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . |
| 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 | | | |
| 587 | How can I protect the format of a worksheet. For example; frame around a cell font size and color cell color I have protect all attributes of the worksheet using tools/protect but the users of the worksheet are still able to modify the items mentioned above. Thanks. |
| 599 | In most versions of Excel, if the worksheet is protected, users will not be able to change the cell formatting, which includes the borders, font colour, font size and cell colour. In Excel 2002, you can choose options as you set the protection, and cell formatting can be allowed. Maybe you have protected the workbook, instead of the worksheet. arthur w. mohr, jr. wrote: How can I protect the format of a worksheet. For example; frame around a cell font size and color cell color I have protect all attributes of the worksheet using tools/protect but the users of the worksheet are still able to modify the items mentioned above. Thanks. -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html |
| 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 |
| 606 | Hi Jon & Brian, I tried the character 160 and wasn't getting any results, so I thought it was my computer and will=20 troubleshoot that over next little while. It is useful info to know of the character 160. The transfer of data into the excel sheet and the sort=20 function works well. Thanks to both of you. Much appreciated, =20 George -----Original Message----- Sorry George. Thanks Jon. I had to run earlier and didn't take enough time to understand the question properly but figured it might help to know about character 160. --Brian "Jon Peltier" <jonpeltier@yahoo.com wrote in message news:O8Yu7dmGCHA.2404@tkmsftngp08... Well, this works if an extraneous nonprinting character makes Excel think there is text, not numbers, in the cells. But I see 'M's and 'B's, which are text, too. Here's how to change them into numbers that Excel understands: Select the numbers. Do a find and replace, and replace B with E9, then replace M with E6. Billions are 10^9 or 1E9, and Millions are 10^6 or 1E6. This converts the non-numbers into numbers (I did a little test just now), so they can be sorted normally. - Jon ------- Jon Peltier, Microsoft Excel MVP /jonpeltier/Excel/index.html _______ In article <a36R8.17345$Fv1.1219940@newsread2.prod.itd.earthlink.ne t, adetaylor said... Select all the numbers. Then EditReplace hold the Alt key and use the number keypad on the right of the keyboard to enter 0160 as the "Find what". Be sure the "Replace with" is empty. Replace All. That is a common HTML character that looks like a space. Alternatively, download Refinate and use it for free to convert to numbers by dragging and clicking (apply Refinate's Number-Values action). --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 "George" <george39672002@yahoo.com wrote in message news:112b301c21a32$52c03390$b1e62ecf@tkmsftngxa04... Mr. Taylor, Your suggestion works! Terrific! Thank you! Just one followup question: The 'sort' function creates strange results on only one particular column (market capitalization) where it appears all the 'values' are treated as characters. Sampling of data after a sort is 1.9B, 10.7B, 138.4B, 14.2B, 2.3B, 247.0M, etc. Note the 'B' is billions and 'M' is millions. How can one convert all these 'values' into the proper numbers? Thank you again, George -----Original Message----- Try dragging to select the HTML worksheet at yahoo and right-clickCopy. Open a fresh Excel worksheet and paste it. It seems to work for me. --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 =CD{=9Dw=C0=1FE=05lgQ_4=FA*_<zS=0B =EC[=04 "George" <george39672002@yahoo.com wrote in message news:112b501c21a18$94d59dc0$9be62ecf@tkmsftngxa03... Hello folks, I'm trying to download data from a webpage into an excel sheet. The resultant excel sheet does not contain the data in the appropriate rows/columns. System: Microsoft Office 2000 Professional Internet Explorer version 5.00 with update SP2 1) access to webpage /p/_noncyc- bevnon.html 2) click on "Download Spreadsheet" 3) save as filename 'abc' with format 'txt' and encoding 'Western European (Windows)'. This file already has the data mixed up. (see all errors a to e below) 4) At excel, menu 'Data', submenu item 'Get External Data', with delimitor(s) of a comma selected. Errors The result contains the following errors: a) The first column does not alway contain the entire company's name. b) The last column does not always contain one piece of info. Sometimes it contains a value and a part of the company's name. c) The company's name enclosed with double quotes d) Some second columns contains the word 'In.' or 'S.A.' subsequent data are right shifted by one column. e) Some rows only contain a partial name and the associated data is found in the subsequent row. I suspect the company's name are too long and/or contain special characters (periods, comma, hyphen). Also, there is possibly no carriage return or tab key at the end of each row. How can one create an excel sheet that contains a given company and all its data on a single row (as in the original webpage above, but with the excel capabilities to delete rows and sort by several variables) at the same time.)? Note: the header line is nice to have but is not important as one can manually enter this with little time. Please show a simple procedure to achieve the correct data. Your assistance is most appreciated. Thank you, . . |
| 610 | Hi. thanks for your advice. Still, since I'm not an advanced Excel user, would this work when I rename my worksheet? What happens is, I'm trying to make a template that will then become various files with different data, and each file consists of 8 sheets with almost the same configuration. But each file and each sheet in each file will be named differently, will I also need to rename the sheets in the code you gave me? thanks, John -----Original Message----- Could you use a Worksheet_Change event instead? If the cell reference in one of the four cells is changed, the chart is updated. For example: Private Sub Worksheet_Change(ByVal Target As Range) Dim str1 As String Dim str2 As String Select Case Target.Address Case "$H$4", "$H$5", "$I$4", "$I$5" str1 = "Sheet1!" & Range("H4") & ":" & Range("H5") str2 = "Sheet1!" & Range("I4") & ":" & Range("I5") Worksheets("Sheet1").ChartObjects(1).Chart _ .SeriesCollection(1).Formula _ = "=SERIES(," & str1 & "," & str2 & ",1)" End Select End Sub John Vera wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 611 | Hi. thanks for your advice. Still, since I'm not an advanced Excel user, would this work when I rename my worksheet? What happens is, I'm trying to make a template that will then become various files with different data, and each file consists of 8 sheets with almost the same configuration. But each file and each sheet in each file will be named differently, will I also need to rename the sheets in the code you gave me? thanks, John -----Original Message----- Could you use a Worksheet_Change event instead? If the cell reference in one of the four cells is changed, the chart is updated. For example: Private Sub Worksheet_Change(ByVal Target As Range) Dim str1 As String Dim str2 As String Select Case Target.Address Case "$H$4", "$H$5", "$I$4", "$I$5" str1 = "Sheet1!" & Range("H4") & ":" & Range("H5") str2 = "Sheet1!" & Range("I4") & ":" & Range("I5") Worksheets("Sheet1").ChartObjects(1).Chart _ .SeriesCollection(1).Formula _ = "=SERIES(," & str1 & "," & str2 & ",1)" End Select End Sub John Vera wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List /tiptech.html . |
| 621 | Unless you are talking about using VBA code to do this, just using worksheet functions - then the formula that displays the results would have to be in the cell where the results would be displayed. In this case, the formula would have to be in column C. Column A would have not role. in C1 =If(len(trim(D1))=0,"",D1) would be an example. John Vera <emperorj@hotmail.com wrote in message news:12cc101c21ba9$faeaf3c0$35ef2ecf@TKMSFTNGXA11... Hi again. Is there any way to make a cell change the contents of another cell or cells? for example, I'd like to have a column A verify whether column D is blank or has a value, and then make column C be blank or have a value (depending only on the D values). Thanks in advance, John |
| 626 | thx "Debra Dalgleish" <dsd@contextures.com wrote in message news:3D163458.8080606@contextures.com... I agree with Jon -- I'd import the list from Access, and use the tools there to weed out the duplicates. WARNING: Make a backup copy of the original table before you append the new records. Import the Data 1. Open the existing Access database. 2. Choose FileGet External DataImport 3. Change the 'File of Type' to Excel. 4. Locate and select your Excel list file, click Import. 5. Select the worksheet, click Next 6. Set the option for Column headings, click Next 7. Select to store In a New Table, click Next 8. Set any field options, such as skipping columns, click Next 9. Select a primary key option, click Next 10. Name the new table, e.g. tblExcelList, click Finish Check for Unmatched Records 1. Click the Queries object button, to view the Queries list. 2. At the top of the Database window, click the New button 3. In the New Query dialog box, select Find Unmatched Query Wizard, click OK 4. Select the new table (e.g. tblExcelList), click Next 5. Select the original table, into which you want to append the Excel list, click Next. 6. Select the fields that you want to check for duplicates, and click the double-headed arrow button between the two lists, click Next 7. Select the fields you want to see in the results, click Next 8. Name the query, select View the Results, click Finish. Append the Records 1. If the list looks correct, click the Design view button. 2. From the Query menu, choose Append Query 3. Select the original table from the dropdown list, click OK 4. In the Append To row in the grid, delete the field name for the field that is being checked for duplicates (the criteria is 'Is Null'). Leave the field in the grid -- just remove the name in Append To. 5. Click the Run button in the toolbar (red !) 6. Click Yes to append the records 7. Close and save the query. Francis wrote: 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 |
| 633 | I need to have a button in Macro which when pressed shd. access worksheet in another workbook. I want a generic macro code for this as there are many worksheets in the other workbook which I would like to access. I would like to pass on the name of the worksheet alone as a parameter and assign the macro to the button. Help me please... |
| 634 | Hi Vinod Possible indeed (macro work). But there are so many probable accidents involved this... Anyway; rightclick the sheet Tab and Choose "view code". Paste this in: Dim strFrm As String Private Sub Worksheet_Change(ByVal Target As Range) If Left(strFrm, 1) = "=" Then Application.EnableEvents = False Target.Formula = strFrm & "+" & Target.Value Application.EnableEvents = True End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) strFrm = Target.Formula End Sub Enable a cell for this horrid idea by entering =1 instead of 1. Any cell starting With = will get the addition of +whateveryoutypedin. As stated, no error checking for text, drag&drop, multiple cell selections, current formula logic, copy-paste, no Undo,... you fix. HTH. Best wishes Harald "V x" <death@death.com wrote in message news:evt4YP7GCHA.404@tkmsftngp13... One of my sales managers wants to be able to use a cell as a "tally" space, of sorts. In other words, she wants to be able to hit the number "1" key to just add one to whatever number or sum already exists in the cell. Is this possible? Normally, of course, hitting the number "1" key in a cell containing a number of formula would simply overwrite the existing contents with simply "1." This tally method, apparently, she prefers over having to edit the formula, like =1+1+1+1 Thanks in advance, Vinod |
| 640 | One way: Public Sub Button1_Click() MyMacro "Sheet1" End Sub Public Sub Button2_Click() MyMacro "Sheet2" End Sub Public Sub MyMacro(shtNum As String) With Workbooks("Workbook2.xls").Sheets(shtNum) 'Do something End With End Sub In article <120d001c21bb5$c9e4c9c0$39ef2ecf@TKMSFTNGXA08, Excelwhiz <sriam@hotamil.com wrote: I need to have a button in Macro which when pressed shd. access worksheet in another workbook. I want a generic macro code for this as there are many worksheets in the other workbook which I would like to access. I would like to pass on the name of the worksheet alone as a parameter and assign the macro to the button. Help me please... |
| 641 | I would suggest building a button or a drawing object and tying it to a short macro such as this: Sub inc() Dim x As Long x = Range("A1").Value x = x + 1 Range("A1").Value = x End Sub It is possible to do what she wants but it requires 2 cells (one can be hidden or you can change the text to white so it can't be seen). Use the following code: Private Sub Worksheet_Change(ByVal Target As Range) Dim y As Long Dim z As Long If Target.Address = "$A$1" Then y = Range("B1").Value z = y + 1 Range("A1").Value = z Range("B1").Value = z End If End Sub HTH Jeff -----Original Message----- One of my sales managers wants to be able to use a cell as a "tally" space, of sorts. In other words, she wants to be able to hit the number "1" key to just add one to whatever number or sum already exists in the cell. Is this possible? Normally, of course, hitting the number "1" key in a cell containing a number of formula would simply overwrite the existing contents with simply "1." This tally method, apparently, she prefers over having to edit the formula, like =1+1+1+1 Thanks in advance, Vinod . |
| 642 | Unfortunately, changing the color doesn't generate an event. You could use an OnTime macro to check once per second (or more likely every few seconds) - see /excel/ontime.htm or perhaps you could call it from the Worksheet_SelectionChange() event macro so that the total would update when the user selected another cell. BTW - you can find a more efficient macro that also allows you to specify in the cell call whether to count background or font color at /excel/colors.htm In article <90bc4e73.0206241153.4b074f2@posting.google.com, Paul Simon <psimon@snet.net wrote: 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? |
| 646 | Hello, I am having trouble when I run a print preview. I do not see all the text in my cells. Word wrap is on. It is generally the bottom line or so I do not see. I have formatted and reformatted the sheet and specific cells turning word wrap on and off. All looks fine on the worksheet screen. I get the same chopped results from the printer as I get from a print preview. Any Help Would Be Appreciated. Thanks, ctballos |
| 653 | Hello All, I've been working with charts that generate into userforms and I'm having alot of fun with it (can you tell that I'm in finance...). Anyway, I've been using John Walkenbach's fabulous code from the "Power Programming with VBA". It's working really well right now, but what I'd like to do is have more than one dataobject when I generate the graph. I'll show you what I have so far... Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) UserRow = ActiveCell.Row If UserRow < 2 Or IsEmpty(Cells(UserRow, 1)) Then MsgBox "Move the cell cursor to a row that contains data." Exit Sub End If CreateChart (UserRow) UserForm1.Show End Sub Sub CreateChart(r) Dim Charttitle1 As String Dim ChartTitle2 As String Dim Charttitle3 As String Dim Tempchar As Chart Application.ScreenUpdating = False Set CatTitles = ActiveSheet.Range("E2:AZ2") Set ScrRange = ActiveSheet.Range(Cells(r, 5), Cells(r, 52)) Charttitle1 = Cells(r, 2) ChartTitle2 = Cells(r, 3) Charttitle3 = Cells(r, 1) Set SourceData = Union(CatTitles, ScrRange) Set tempchart = Charts.Add With tempchart .ChartType = xlLineMarkers .SetSourceData Source:=SourceData, PlotBy:=xlRows .HasLegend = False .ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False .HasTitle = True .Charttitle.Text = "Transit: " & Charttitle3 & " " & Charttitle1 & "-" & ChartTitle2 .Charttitle.Font.Bold = True .Charttitle.Font.Size = 12 '.Axes(xlValue).MaximumScale = 30000 .Axes(xlCategory).TickLabels.Font.Size = 6 .Axes(xlCategory).TickLabels.Orientation = xlHorizontal .Location where:=xlLocationAsObject, Name:="Volumes" End With With ActiveSheet.ChartObjects(1) .Width = 600 .Height = 300 .Visible = False End With Private Sub UserForm_Initialize() Set currentchart = ActiveSheet.ChartObjects(1).Chart fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif" currentchart.Export Filename:=fname, filtername:="GIF" Sheets("volumes").ChartObjects(1).Delete Image1.Picture = LoadPicture(fname) UserForm1.Caption = "Charts 'n Stuff" Application.ScreenUpdating = True End Sub Is there any way to modify this code so that the user can select more than one row and be able to compare and contrast the data? Any help would be greatly appreciated, and a special thanks to John for the great resource book he's written, Thanks in advance, Mike |
| 656 | Can you import it to two different worksheets? If yes, you could use your favorite text editor to split it up (then import each of them) or use a workaround that MS created: /default.aspx?scid=kb;EN-US;q120596 It was written for xl95 (with its limit of 16384 rows). If you decide to use it, modify the macro accordingly. Christian wrote: i want to open a .txt file in excel but it will be just over 100000 cells deep and the limit on excel worksheets is around 65000. Is there a way around this? -- Dave Peterson ec35720@msn.com |
| 662 | Tom Excel will allow 32,000+ characters in a cell. However, it will show and print only 1024 characters. Split your text over more cells or use a TextBox. HTH Gord Dibben Excel MVP - XL97 SR2 On Mon, 24 Jun 2002 20:39:16 GMT, "Tom Ballos" <ctballos@worldnet.att.net wrote: Hello, I am having trouble when I run a print preview. I do not see all the text in my cells. Word wrap is on. It is generally the bottom line or so I do not see. I have formatted and reformatted the sheet and specific cells turning word wrap on and off. All looks fine on the worksheet screen. I get the same chopped results from the printer as I get from a print preview. Any Help Would Be Appreciated. Thanks, ctballos |
| 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 | | | |
| 688 | 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 |
| 689 | 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 |
| 690 | "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 |
| 691 | 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 . |
| 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 | | | . | . |
| 698 | Hi, Some things to do: Options to try and open a corrupt file - Set calculation to manual - open the file, but disable macros (assuming you've set macro security to medium: Tools, Macro, security) - As soon as you've clicked the disable macros button, press control-pageup or control-pagedown, thus changing sheets. If that does not work, try creating a link to the file: ='c:\my documents\[MyFileName.xls]Sheet1'!A1 and copy right and down. This at least gets you the worksheets values. Sometimes the Excel viewer (or Word) enables you to open the file and copy information out of it. Also, Excel XP can sometimes repair XL files with trouble. Lastly: Download the office suite from www dot sun dot com slash staroffice (awkward spelling to hopefully avoid another autodeletion of posting...) it's a killer app for file recovery. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Dear MS Support Team, I am having problem with one of the excel s/sheet I used until Friday evening. I am using MS-Excel 97 SR-1 and this s/sheet worked perfectly until Friday evening. It is a very simple data-only s/sheet..no macros/ scripts/ program at all. I saved this successfully on my 'C' drive on Friday night and tried to open it this morning but got a message box (attached in the file) with the following error - "Error Message : Address: excel.exe - Application Error" It doesn't even allow me to open the s/sheet. I have checked this file for virus and there are none. Would appreciate if you can help me in this matter as I spent considerable amount of time preparing this s/sheet. Regards, Ash . |
| 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 | | | | | | . | | | | | . | |
| 707 | 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 |
| 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 | | | | |
| 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 | | | . | |
| 740 | I'm not sure if Earl's code is protected, but if it is, you can look at Chip Pearson's code for importing/exporting text files at: /excel/imptext.htm These write to ASCII files--not directly to Notepad. But I guess you could use SendKeys and work around that if it's important. Or better, just write to .txt and then something simple like: Shell "notepad c:\myfile.txt" might work ok. Balaji Desikan wrote: Thanks Dave! More specifically I was looking for a way to export programmatically data from a column in excel into a comma separated text in a notepad -----Original Message----- I bet you meant multiple columns copied into notepad--not just one. You can use a worksheet formula like: =a1&","&b1&","&c1 copy down the range. Then copy|paste that one column into notepad. You could write your own export program or use Earl Kiosterud's program "text write" located on Tushar Mehta's web page: -mehta.com/ look for Text Write in the left hand frame. Balaji wrote: I would like to know if I can export data from a given column of data in Excel into a comma separated text in a notepad. Any help will be greatly appreciated -- Dave Peterson ec35720@msn.com . -- Dave Peterson ec35720@msn.com |
| 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 . . |
| 744 | You wrote: Hi again. Is there any way to make a cell change the contents of another cell or cells? for example, I'd like to have a column A verify whether column D is blank or has a value, and then make column C be blank or have a value (depending only on the D values). For what you're asking -- to make a cell blank or show a value depending upon the value in another cell -- there is a workaround. You could use conditional formatting. For example, set up conditional formatting for cell C1; in the conditional formatting window specify a formula; something like =IF(ISBLANK($D1),1,0) ;then in the format, if you want cell C1 to appear blank specify a white font on a white background in a tiny font. IF you want the cell value to appear then specify a normal font on a normal background. That's just a workaround solution that works because you want the cell value to appear or not... but, there is no normal way for a cell formula to cause a change in the value of another cell. Excel works that way so that it can 'know' how to recalculate all of the cell formulas in a worksheet in a specific sequence without circular or illogical references. |
| 748 | hello dave, thanks for the program. it works great. jj -----Original Message----- How about this: Option Explicit Sub testme() Dim FirstRow As Long Dim LastRow As Long Dim LastCol As Long Dim iRow As Long Dim iCol As Long Dim oRow As Long Dim curWks As Worksheet Dim newWks As Worksheet Set curWks = ActiveSheet Set newWks = Worksheets.Add With curWks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oRow = 1 For iRow = FirstRow To LastRow LastCol = .Cells(iRow, .Columns.Count).End (xlToLeft).Column If LastCol 1 Then For iCol = 2 To LastCol newWks.Cells(oRow, 1).Value = .Cells (iRow, 1).Value newWks.Cells(oRow, 2).Value = .Cells (iRow, iCol).Value oRow = oRow + 1 Next iCol End If Next iRow End With With newWks .Range("A:A").NumberFormat = "mm/dd/yyyy" .UsedRange.Columns.AutoFit End With End Sub Adjust your firstrow to match your data (or clean it up after the macro runs). (also the date format. I like leading 0's <vbg.) james jing wrote: Hi, I have excel data as following format column 1 2 3 4 5 7/1/2002 event a event b event c event d 7/2/2002 event a1 event b1 event c1 event d1 . . . I would like to convert the data into two columns with the following format: 7/1/2002 event a 7/1/2002 event b 7/1/2002 event c ... 7/2/2002 event d1 james -- Dave Peterson ec35720@msn.com . |
| 749 | Presently I am able to extract a set of data from a specific database and return the same to excel file / worksheet by choosing the option 'return data to excel' every time I want to transfer every query result to excel file automatically - as and when queried - without having to choose the option 'return data to excel' every time and the returned data must align themselves one by one the solution may kindly be emailed to my id at fin@hosur.sfl.co.in thanks in advance k vasudevan |
| 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. |
| 783 | See Harlan's answer to transform data by ame in microsoft.public.excel.worksheet.functions NG. Cheers "james jing" <jjing@sleh.com wrote in message news:103a901c21c66$07650ca0$a5e62ecf@tkmsftngxa07... Hi, I have excel data as following format column 1 2 3 4 5 7/1/2002 event a event b event c event d 7/2/2002 event a1 event b1 event c1 event d1 . . . I would like to convert the data into two columns with the following format: 7/1/2002 event a 7/1/2002 event b 7/1/2002 event c ... 7/2/2002 event d1 james |
| 791 | Otto, Nothing happens at all!! Thanks anyway, Rob. -----Original Message----- Rob I'm assuming that your workbook has only one sheet. If that is not the case, then add code to first select the pertinent sheet. I also added code to scroll the cell containing today's date to the top left of the display. Put this macro in your Workbook module. HTH Otto Private Sub Workbook_Open() Range("A:A").Find(What:=Date).Select With ActiveWindow .ScrollRow = ActiveCell.Row .ScrollColumn = ActiveCell.Column End With End Sub "Rob Pearce" <rob.pearce@consignia.com wrote in message news:123a201c21c5b$d54f7070$19ef2ecf@tkmsftngxa01... I have a worksheet that I use for a diary. In column A I have dates going on for the next 3 years. How can I get the workbook to open up on todays date everytime I open it up? I presume some kind of code in the auto_open sub. [Platform 2000] Thanks, Rob. . |
| 797 | I do not understand why the tendency is for suggestions to convert character 160 to nothing. CHR(160) is the non breaking space character ( ) in HTML and should be converted to a normal space. FWIW the CLEAN Worksheet Function is also sometimes suggested and would only affect char(0) through Char(31), Char(129), Char(141), char(143) and Char(144). To fix up unwanted extra spaces afterwards there is TRIM to remove spaces left and right; and replace can be used to replace 2 spaces with 1 space depending on your data. There is a difference between TRIM in Excel and in VBA. Excel will reduce internal spaces, VBA will not. HTH, , Microsoft MVP - Excel [site changed Nov. 2001] My Excel Macros: /dmcritchie/excel/excel.htm Search Page: /dmcritchie/excel/search.htm "George" <george39672002@yahoo.com wrote in message news:1146201c21ab4 I tried the character 160 and wasn't getting any results, so I thought it was my computer and will troubleshoot that over next little while. It is useful info to know of the character 160. The transfer of data into the excel sheet and the sort function works well. Thanks to both of you. |
| 799 | Hi, I'm using checkboxes for the user to decide which sections of a worksheet to print, however I can't get any values of the checkboxes to work with. all i need is something like... Sub CheckBox1_Change() If CheckBox1.Value = True Then ...... Else .... end if:endsub but this doesn't work and i keep getting error object required. So I'm doing something wrong....? thanks phil |
| 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 . |
| 813 | May I take the opportunity to add my 2 cents worth? I started with Excel in Excel 95. I learned maybe 60% of what I know about Excel from the Help files, and the rest from these newsgroups. I now have Excel 2002. And I am hugely disappointed with the change in the Help files in both Word and Excel. If N. Squire (the OP) happens to be using Excel 2002 and went searching in Help, as you might, for "horizontal scroll bar" he would have been faced with 20 possible pages of information. On my system, the first few are: "Insert subtotals", "Include different data in an offline cube", "Troubleshoot security and protection". None of the 20, from what I can see, mentions the horizontal scroll bar on the side of a worksheet or mentions Tools | Options etc. No wonder he can't find what he needs! I used to feel confident that I could tell people that they could find the information they needed in the Help files. I'm no longer confident enough to say that and send them, instead, to the nearest bookshop in search of John Walkenbach's books. With due respect, thanks, praise and genuflection, such books should be an adjunct to, not a replacement for, Help files that are structured such that users can find what they need. Maybe it's just me. But I find the new Help file system imponderable. Perhaps those of you who have Microsoft's collective ear could point out that this is not supporting the company's publicly-stated corporate policy of being driven by the market and making it easy for the user. Anne "Harald Staff" <harald.staff@eunet.no wrote in message news:u6i6$MGHCHA.2604@tkmsftngp11... Hi John Some folks must fight (or worse; persuade) the paperclip to be allowed to enter Help. It is very exhausting, especially on international systems. So I'd prefer a MVP or a tech writer answering <g. Best wishes Harald "John Walkenbach" <john@j-walk.com wrote in message news:epCpV3FHCHA.2652@tkmsftngp11... FYI, answers to these types of questions are readily available in Excel's Help system. You might find that approach more efficient than posting to a newsgroup. |
| 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 . |
| 840 | Bill, Two possibilities: 1) Create a "repeat" column of everything (you can then hide the original or something) containing the following (assuming B is repeat column, A is original): =IF(left(a1,1)=" ",right(a1,len(a1-1)),a1) 2) Create a Macro to sift through the data and look for leading spaces. You can either attach this to a button so that it runs whenever you click the button, or set it as an event procedure to run whenever the spreadsheet is updated. Something like: sub deletespaces() for each cell in worksheet data=cell.value if left(data,1)=" " then data=right(data,len(data-1)) cell.value=data end if next cell end sub Hope this helps, Steve -----Original Message----- Hi All, One more problem that I would really appreciate your help with. I do a lot of importing into a particular Excel 2000 database from all kinds of sources.I extract names, addresses, email, you name it. Included with the imports in some cells are leading spaces. They completely ruin sorts because all data sorted in these columns alphabetically, sorts a-z without the leading spaces, then continues sorting from a-z witht the leading spaces!!! How do I get rid of these leading spaces!!!! I tried using indent, left align, replace, nothing works. Do I have to go through all the cells with these leading spaces and delete them manually????!!!!!!!!!!!!!!!!! Thanks for your quick and concise response from my first question. You guys are great!!!! . |
| 846 | or, (not as "tight" a design, but...) you could use a conditional format so that if the cell value = 0, the color of the text for that cell is the same as the color of the background... ... that way the value would still be there (as a number), you just woulnd be able to see it. sometimes if you use the formula to insert "" instead of the result, and the cell is precedant to another formula, a calculation error (#VALUE!)occurs -----Original Message----- Hi Chris You could use =IF(SUMPRODUCT((Input!G3:G121<=90)*(Input!G3:G121=61)* (Input!M3:M121=R2)*(Input!L3:L121=P2))=0,"" ,SUMPRODUCT((Input!G3:G121<=90)*(Input!G3:G121=61)* (Input!M3:M121=R2)*(Input!L3:L121=P2))) Roger Govier Technology 4 U W98SR2 XL2K wrote: On Wed, 26 Jun 2002 09:16:00 -0700, "Chris Eilersen" <chrise@eilerc.com-- I am using SUMPRODUCT to calculate some columns on a worksheet. A sample of the usage would be: =SUMPRODUCT((Input!G3:G121<=90)*(Input!G3:G121=61)* (Input!M3:M121=R2)*(Inpu t!L3:L121=P2)) How can I add an if statement to leave the cell blank if the value of the formula is 0? (Right now it puts a 0 in the cell.) Thanks . |
| 849 | How can I insert the filename & path into a worksheet's footer the way Word can? |
| 850 | View headers and footers. There is an option here to insert the filename, but not the path also. "roblobster" <roblobster@hotmail.com wrote in message news:uLpXxeTHCHA.2632@tkmsftngp10... How can I insert the filename & path into a worksheet's footer the way Word can? |
| 856 | Here's one way to visually identify any cells that may have those unwanted garbage spaces you can't see: 1. Click on cell A1 and go to Format Conditional Formatting 2. Choose Formula Is and insert this: =EXACT(TRIM(A1),A1)=FALSE 3. Click on the Format button, select the Pattern tab, and pick a color. 4. OK, OK. 5. Then press Ctrl+C to copy A1, then ctrl+A to select the entire worksheet, right-click, Paste Special..., Format. Any cells that have the fill color you chose indicate that they contain extra spaces (extra spaces does NOT include single spaces between words). HTH Jason Atlanta, GA -----Original Message----- I have a very large Excel file. I want to be able to see if there are any blank spaces within the cells. For instance, one of the fields is labeled "fname". How do I know if there are any blank spaces after the first name? Is there a "view hidden characters" feature in excel or a "show/hide" feature like in MS Word? Thanks in advance. . |
| 860 | hi phil try x = (a)^(1/2) hth Owen -----Original Message----- Hi, how can i make a macro or user form button do a square root of some numbers? this doesn't work:- x = application.worksheetfunction.sqrt(a) thanks phil . |
| 862 | Hello, For the moment I tried to clear 2 cells to the right of the selected cell in which they can choose a word from a List (declared the List of that cell in the menu Data, Validation...) I 've tried it to do it as follows: Private Sub Worksheet_Change(ByVal Target As Range) If Not (Intersect(Target.Columns.Cells, Worksheets ("StatusReport").Range("D")) & _ Intersect(Target.Rows, Worksheets("StatusReport").Range ("11:22"))) Is Nothing Then Dim neighbour1 As Range Dim neighbour2 As Range neighbour1 = Target neighbour2 = Target neighbour1.Columns.Cells = Target.Columns.Cells + 1 neighbour2.Columns.Cells = Target.Columns.Cells + 2 neighbour1.ClearContents neighbour2.ClearContents End If End Sub But it doesn't work. Can anybody tell me what I 'm doing wrong? Tnx. |
| 863 | very good point thanks :) -----Original Message----- hi phil try x = (a)^(1/2) hth Owen -----Original Message----- Hi, how can i make a macro or user form button do a square root of some numbers? this doesn't work:- x = application.worksheetfunction.sqrt(a) thanks phil . . |
| 865 | Try this (put in the StatusReport sheet's code module): Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("D11:D22")) Is Nothing Then Target.Offset(0, 1).Resize(1, 2).ClearContents End If End Sub Note, the Change event is not fired in XL97 when an item from a dropdown list is selected. In article <132f801c21dd6$9ca1a000$3bef2ecf@TKMSFTNGXA10, Dijck <hans.dijckmans@laterio.com wrote: Hello, For the moment I tried to clear 2 cells to the right of the selected cell in which they can choose a word from a List (declared the List of that cell in the menu Data, Validation...) I 've tried it to do it as follows: Private Sub Worksheet_Change(ByVal Target As Range) If Not (Intersect(Target.Columns.Cells, Worksheets ("StatusReport").Range("D")) & _ Intersect(Target.Rows, Worksheets("StatusReport").Range ("11:22"))) Is Nothing Then Dim neighbour1 As Range Dim neighbour2 As Range neighbour1 = Target neighbour2 = Target neighbour1.Columns.Cells = Target.Columns.Cells + 1 neighbour2.Columns.Cells = Target.Columns.Cells + 2 neighbour1.ClearContents neighbour2.ClearContents End If End Sub But it doesn't work. Can anybody tell me what I 'm doing wrong? Tnx. |
| 870 | In Excel's Help File Under: Elements you can protect in worksheets and workbooks, it states, . . . When you protect a workbook, you can protect or unprotect the structure of the workbook or workbook windows in the Protect Workbook dialog box (Protection menu, Tools command) by selecting or clearing check boxes for each element. Structure When selected, users are prevented from: . . . Moving or copying worksheets to another workbook. This is NOT TRUE!! I have tried every thing I know to get this to work and I can copy and paste worksheets to a new workbook and the password protection is gone. Anybody can unhide, see and change my data! This is NOT GOOD! Any Ideas? |
| 872 | Hello, Roger, Elizabeth, Thanks you for your help.... I do understand how Vlookup works now. With the help of Wilson.. we did the following: You said you wanted it in AA14 can I have this Vlookup on a cell other from D14.......like AA14 This =IF(ISNA(VLOOKUP(Z14,partdesc!$A$2:$B$62749,2,FALSE)),"",VLOOKUP(Z14,partdes c!$A$2:$B$62749,2,FALSE)) prevents the #N/A errors if there is nothing to lookup or if the part number is not found This =IF(B14="","",B14) means If B14 is blank, make this cell blank, otherwise make it equal to B14 This =IF(AA14="","",AA14) means If AA14 is blank, make this cell blank, otherwise make it equal to AA14 If my part number list gets bigger......do I just increase the ranges.... ................. Now on my form..... How can I make it do the following: If a part number field.....B14 is not used and we decide to enter some information on cell D14....... This will cause the formula to be erased.... But then......I want to go back and enter a part number in B14 the description will not added to D14. In other words....I want to make it to were B14 .....a part number is added then it should override my notes in D14 and add the part number description. Could this be done. Thanks Jaime "elizabeth" <edavis@unch.unc.edu wrote in message news:10df201c21d4b$c21146a0$a4e62ecf@tkmsftngxa06... Your formula would be =vlookup($B14,partdesc!$A:$B,2,FALSE) enter the formulat into cell D14. Where $B14 = the part number you are trying to find the description for. partdesc! is the sheet where the description exists $A:$B are the columns where the item number and description exist in partdesc 2 represents column B where the description exists Hope this helps -----Original Message----- Hello, Need some help with Vlookup.... Very new to this...... If I have cell B14......I used this cell to enter a part number and if I was to enter a part number...... I would like the description for that part number to be added to cell D14. The decryption for these part numbers in a worksheet that I named "partdesc" My part numbers are in cells A2......A62749 and the descriptions for these part numbers are in cell B2...........B62749 How or what should I do to make this work... If I was to enter a part number in B14.......to pull the description for that part number (looking at my partdesc worksheet) and placing it on D14. I am very new to Vlookup and I was using the function option and that did not work. Help!! Thanks Jaime . |
| 873 | Hi Phil Try also MsgBox Sqr(9) HTH. Best wishes Harald "Phil" <pperry@acsoft skrev i melding news:12f6f01c21dd1$080dfce0$3aef2ecf@TKMSFTNGXA09... Hi, how can i make a macro or user form button do a square root of some numbers? this doesn't work:- x = application.worksheetfunction.sqrt(a) thanks phil |
| 881 | This is NOT TRUE!! It's true for me. Worksheets cannot be copied or moved. Ranges are another matter though; perhaps that's what you're doing. -- Jim Rech Excel MVP |
| 889 | One way: =MID(LEFT(CELL("filename", A1), FIND("]", CELL("filename", A1))-1), FIND("[", CELL("filename",A1))+1, 255) Note that the worksheet must be saved first. In article <e61e4oeHCHA.2436@tkmsftngp10, Paul Simon <psimon@yahoo.com wrote: Wow... thanks. Is there a way to parse that down so I only get the filename, and not the entire path to that filename? "Stephen Dunn" <steve@graydon-dawson.freeserve.co.uk wrote in message news:OhppZjeHCHA.1600@tkmsftngp13... This question must get asked about twice a week, so just in case the regulars are sick of answering it: =CELL("filename",A1) Steve D. "Paul Simon" <psimon@yahoo.com wrote in message news:uUFbXfeHCHA.2096@tkmsftngp12... What is the best way to get the name of a file into a cell within a spreadsheet? My intent is to fill out a pre-defined form, save it (providing a name for the file when saving), and then printing a copy of the spreadsheet. Since just prior to printing the spreadsheet, it has been named, I want that name to be put into a cell. All thoughts appreciated.... Paul |
| 892 | I have a Listbox in which I want to display the previous data value under certain conditions and within a Change() event. I 'll clearify with a bit of pseudo code: If Target = worksheets("blabla").Range("D11:D22") Then Target.getPreviousSelectedValue() ???? End if Can anyone tell how I can get this previous selected value? Tnx, Hans. |
| 899 | That's it! I took your formula one step further and changed the first "FIND" to subtract 5 instead of 1. This allows me to discard the extension as well. It then just gives me the file name. Again, many thanks.... Paul "J.E. McGimpsey" <jemcgimpsey@mvps.org wrote in message news:270620020906271231%jemcgimpsey@mvps.org... One way: =MID(LEFT(CELL("filename", A1), FIND("]", CELL("filename", A1))-1), FIND("[", CELL("filename",A1))+1, 255) Note that the worksheet must be saved first. In article <e61e4oeHCHA.2436@tkmsftngp10, Paul Simon <psimon@yahoo.com wrote: Wow... thanks. Is there a way to parse that down so I only get the filename, and not the entire path to that filename? "Stephen Dunn" <steve@graydon-dawson.freeserve.co.uk wrote in message news:OhppZjeHCHA.1600@tkmsftngp13... This question must get asked about twice a week, so just in case the regulars are sick of answering it: =CELL("filename",A1) Steve D. "Paul Simon" <psimon@yahoo.com wrote in message news:uUFbXfeHCHA.2096@tkmsftngp12... What is the best way to get the name of a file into a cell within a spreadsheet? My intent is to fill out a pre-defined form, save it (providing a name for the file when saving), and then printing a copy of the spreadsheet. Since just prior to printing the spreadsheet, it has been named, I want that name to be put into a cell. All thoughts appreciated.... Paul |
| 916 | You have 16,777,216 cells on just ONE worksheet. Did you run out of cells to work with? Why can't you put a formula in one cell, your constant in another, and then reference the constant cell in your formula? HTH Jason Atlanta, GA -----Original Message----- i have put in formulas, but when you put in a number it erases the formula, so im wondering if there is a way to save the formula to the cell, so that even after you erase/delete the number you put in the cell, the formula will still be there. any help is great appreciated. thanx . |
| 919 | Cells can only have formulas or values entered. Each entry replaces the previous entry. So no, you can't store a formula and value in the same cell at the same time. You can, however, use VBA and events to reconstruct the formula. Say you have the formula "=A1+A2" in cell A3. Then this event macro allows the user to make an overriding entry. Put this in the worksheet code module (right-click on the worksheet tab, choose View Code, paste the code in the window that opens, then click the XL icon on the toolbar to return to XL): Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Address(0, 0) = "A3" Then _ If IsEmpty(.Value) Then _ .Formula = "=A1 + A2" End With End Sub In article <1115801c21df3$26e7d060$36ef2ecf@tkmsftngxa12, landon <pugsly8422@hotmail.com wrote: i have put in formulas, but when you put in a number it erases the formula, so im wondering if there is a way to save the formula to the cell, so that even after you erase/delete the number you put in the cell, the formula will still be there. any help is great appreciated. thanx |
| 921 | I suspect instead that the OP is interested in creating a model that can accept overrides by the user, which can then be backed out of, leaving the original formula. There are many ways to work this type of issue, of course, but it's a fairly common request among my clients. In article <12dbb01c21df8$a6d0ec80$9be62ecf@tkmsftngxa03, Jason Morin <jason.morin@us.exel.com wrote: You have 16,777,216 cells on just ONE worksheet. Did you run out of cells to work with? Why can't you put a formula in one cell, your constant in another, and then reference the constant cell in your formula? |
| 926 | General module - tried to find ThisWorkbook module but have been unable to..... Could you point me in the right direction? Thanks. -----Original Message----- It worked ok for me. Did you put the code under ThisWorkbook module or in a General module? It's an easy mistake to make... Rob Pearce wrote: Otto, Nothing happens at all!! Thanks anyway, Rob. -----Original Message----- Rob I'm assuming that your workbook has only one sheet. If that is not the case, then add code to first select the pertinent sheet. I also added code to scroll the cell containing today's date to the top left of the display. Put this macro in your Workbook module. HTH Otto Private Sub Workbook_Open() Range("A:A").Find(What:=Date).Select With ActiveWindow .ScrollRow = ActiveCell.Row .ScrollColumn = ActiveCell.Column End With End Sub "Rob Pearce" <rob.pearce@consignia.com wrote in message news:123a201c21c5b$d54f7070$19ef2ecf@tkmsftngxa01... I have a worksheet that I use for a diary. In column A I have dates going on for the next 3 years. How can I get the workbook to open up on todays date everytime I open it up? I presume some kind of code in the auto_open sub. [Platform 2000] Thanks, Rob. . -- Dave Peterson ec35720@msn.com . |
| 937 | Hi, How do I create a list of all the worksheets that I have in a workbook. Ideally I would like to do something like =worksheetname(4) and have the formula return the name of the 4th sheet in the workbook. Thanks. |
| 944 | Function sheetname(a As Integer) sheetname = Sheets(a).Name End Function if you put this in a normal module you can use it like this =sheetname(3) Regards Ron "Salman" <sahmadmit@hotmail.com schreef in bericht news:12e5701c21e04$8f97fc00$9be62ecf@tkmsftngxa03... Hi, How do I create a list of all the worksheets that I have in a workbook. Ideally I would like to do something like =worksheetname(4) and have the formula return the name of the 4th sheet in the workbook. Thanks. |
| 945 | Salman, Use the Worksheets collection. Dim nameVar As String nameVar = Worksheets(4).Name If you want to include graphs and such, use the Sheets collection. HTH, Bernie "Salman" <sahmadmit@hotmail.com wrote in message news:12e5701c21e04$8f97fc00$9be62ecf@tkmsftngxa03... Hi, How do I create a list of all the worksheets that I have in a workbook. Ideally I would like to do something like =worksheetname(4) and have the formula return the name of the 4th sheet in the workbook. Thanks. |