out of range problem in workbook closing

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - VB to create histogram This is a multi-part message in MIME format. ------=_NextPart_000_001F_01C4E481.9D997310 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I am trying to create a histogram using VB with data that I input and = bin data records available. When I chose the macro recorder while = creating the histogram, I only received the following line of code: Application.Run "ATPVBAEN.XLA!Histogram", , "", , False, False, = False _ , True I am not sure what these parameters represent, and I don't see where the = input or bin ranges are entered. What code is required to make the = histogram data analysis work? Harley ------=_NextPart_000_001F_01C4E481.9D997310 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Dwindows-1252"> <META content=3D"MSHTML 6.00.2900.2523" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT size=3D2>I am trying to create a histogram using VB with data = that I=20 input and bin data records available.  When I chose the macro = recorder=20 while creating the histogram, I only received the following line of=20 code:</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>     Application.Run=20 "ATPVBAEN.XLA!Histogram", , "", , False, False, False=20 _<BR>        , True<BR></FONT></DIV> <DIV><FONT size=3D2>I am not sure what these parameters represent, and I = don't see=20 where the input or bin ranges are entered.  What code is required = to make=20 the histogram data analysis work?</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>  Harley</DIV></FONT></BODY></HTML> ------=_NextPart_000_001F_01C4E481.9D997310-- Tag: out of range problem in workbook closing Tag: 51511
  - 2
    - Comparing Fields in Different Columns I was wondering if there is a way two compare the fields in 2 different columns. Then if those fields are identical they would be removed. Is there a basic function to do this or do I need to write a macro? Thanks for the help. Tag: out of range problem in workbook closing Tag: 51509
  - 3
    - Any way to remove wayward columns? I'm not sure why, but for some reason on 1 page in one of my workbooks, the columns go all the way out to IV. On that sheet, I'm only actually using columns out to J. I'm running Excel 2003. Is there any way to remove the extra columns so Excel doesn't think the worksheet has hundreds and hundreds of columns? Thanks, Colin Tag: out of range problem in workbook closing Tag: 51495
  - 4
    - most popular business blog http://wonderfulthings.blogspot.com Tag: out of range problem in workbook closing Tag: 51492
  - 5
    - Comments When a new comment is created, the comment window opens up with the user name in bold. Can anyone tell me if it is possible to set a default so that it doesn't display the user name? I have to delete it every time and it's a bit of a pain.... Many thanks, martin Tag: out of range problem in workbook closing Tag: 51486
  - 6
    - Newbie needs macro help Excel 2000 Greetings, I need a little bit of assistance in creating a macro in Excel 2000, pc platform. I would like the macro to ask for a beginning number and an ending number. The macro would then populate the corresponding number of rows in column A (original number) and in column B a number starting with 1 and increasing by 1 until the ending number is met. For example, the macro asks for beginning number (I would enter 123) and ending number is 130: cell A1 would contain 123, cell A2 contains 124, cell A3 contains 125, cell A4 contains 126, cell A5 contains 127, cell A6 contains 128, cell A7 contains 129, cell A8 contains 130 (eight rows); cell B1 would contain 1, cell B2 would contain 2...cell B8 would contain 8. Is my question clear? Any and all help/suggestions would be greatly appreciated. TIA. Dave Tag: out of range problem in workbook closing Tag: 51475
  - 7
    - Newbie needs macro help Excel 2000 Greetings, I need a little bit of assistance in creating a macro in Excel 2000, pc platform. I would like the macro to ask for a beginning number and an ending number. The macro would then populate the corresponding number of rows in column A (original number) and in column B a number starting with 1 and increasing by 1 until the ending number is met. For example, the macro asks for beginning number (I would enter 123) and ending number is 130: cell A1 would contain 123, cell A2 contains 124, cell A3 contains 125, cell A4 contains 126, cell A5 contains 127, cell A6 contains 128, cell A7 contains 129, cell A8 contains 130 (eight rows); cell B1 would contain 1, cell B2 would contain 2...cell B8 would contain 8. Is my question clear? Any and all help/suggestions would be greatly appreciated. TIA. Dave Tag: out of range problem in workbook closing Tag: 51472
  - 8
    - free excel manuals and tutorials http://programatium.com/en/ Tag: out of range problem in workbook closing Tag: 51471
  - 9
    - Go to specific worksheet using command line switches Using a complete command line at "Start | Run" - NOT from within Excel itself. What form of filename and switches will allow me to call a workbook and open it to a specific worksheet and, if possible, a specific cell? I've tried the following without success: "C:\Program Files\Microsoft Office\Office\EXCEL.EXE" "N:\Calendars\PlanningCalendar.xls#JAN05!A1" JAN05 is one of 24 worksheets in the workbook. Any help will be much appreciated. Tag: out of range problem in workbook closing Tag: 51469
  - 10
    - Open workbook to a specific sheet What form of filename and switches will allow me to call a workbook and open it to a specific worksheet and, if possible, a specific cell. I've tried the following without success: "C:\Program Files\Microsoft Office\Office\EXCEL.EXE" "N:\Calendars\PlanningCalendar.xls#JAN05!A1" Any help will be much appreciated. Tag: out of range problem in workbook closing Tag: 51466
  - 11
    - Need a solution for automatically inserting cells and updating formulas Hi there, www.dciu.org/cspd/Excel%20Template.xls I have a worksheet that I'm working on where teachers can track student data. I have it set up to create an aimline on a chart where the goal minus the baseline divided by the number of sessions will create the change per session. (g-b/s = c). I then have an aimline that starts at the baseline and then adds the change per session for an amount of sessions. This part works great. Here's the part I need help with- I'm specifically working on the worksheet within the workbook entitled Spec Skills Ratio. I need teachers to be able to add data to new rows and still have the formulas update themselves accordingly as well as the chart attached to recognize new data. These teachers would find it difficult to copy and paste formulas so I would like the worksheet to do this for them. I do not know how to do VBA or macros, but if you walk me through it, I might be able to figure things out. I hope this is clear. Thank you in advance, cabybake Tag: out of range problem in workbook closing Tag: 51464
  - 12
    - collating information from multiple sources Hi Apologies if this has been asked before, but I'm a bit of a newbie when it comes to mucking around in excel, I've had a search but I don't really know what to call what I want to do, so finding the answer is tricky... Anyway. I have a dozen or so workbooks that are all of the same format, 1 work sheet with a basic list of test scenarios in each, with a unique reference for each row. What I'd like to do is pull information from all those workbooks into a separate workbook by the use of the unique reference. i.e If I type in the unique reference in the master workbook, it will search through the dozen workbooks and pull the information relevant to the key via a vlookup. I know how to do a vlookup across workbooks on a one-to-one relationship, but recursing that through a dozen files in different directories is stumping me. Is this possible? And if so, how would I do it? Any pointers or examples would be much appreciated. Cheers -- Trooper usenet@trooperlooper.co.uk.invalid (remove the obvious) GamerTag: TrooperNeil Tag: out of range problem in workbook closing Tag: 51461
  - 13
    - Freezing Worksheet Cells Is it possible to have different cell widths in the first two lines of a spreadsheet to the rest of the sheet? TIA, Ron. Tag: out of range problem in workbook closing Tag: 51457
  - 14
    - Pivot table for two different data variables I have the following table Name type Cost overhead amy C1 23 12 bob C2 129 17 cat C2 36 45 doo C2 100 32 I owuld like to create a pivot table that looks similar to: the standard pivot table. (Please ignore type for now.) name Data Total amy Sum of overhead 200 Sum of cost 12 bob Sum of overhead 130 Sum of cost 23 cat Sum of overhead 90 Sum of cost 19 doo Sum of overhead 87 Sum of cost 34 Total Sum of overhead 507 Total Sum of cost 88 But with overall total (overhead + cost) for each individual instead of total the two types separately. Any help would be totally appreciated. Thanks, pac Tag: out of range problem in workbook closing Tag: 51451
  - 15
    - Merge two lists I have 2 sets of data range i.e set1 from range [Sheet1!A1:A32] and set2 is from [Sheet2!B1:B32] Set1 contains Vegetables List Set2 contains Fruits List Now I want to merge both lists in [Sheet3! C1:C64] but one value from set1 and one value from set2 Below is graphical translation: SET1 A1: Tomatto A2: Cucummber A3: Carrott ..............so on SET2 B1: Apple B2: Oranges B3: Bananna ..............so on SET3 [to create] C1: Tomatto C2: Apple C3: Cucummber C4: Oranges C5: Carrott C6: Bananna ..............so on Please help Tag: out of range problem in workbook closing Tag: 51448
  - 16
    - Checkbox not part of cell. (???) Cells can move "underneath". (!!!) When I add a checkbox form to my spreadsheet, it is not part of any cell. It sits above the spreadsheet and can even overlap the row/column/cell boundary lines. When I place them so that they look like they're "within" the cell, inserting a row will move the cells with text down, but the checkboxes, as you can guess, stay put. Is there someway to stitch the checkbox form to the cell contents itself? THANKS!!!!!!!!!! -- With knowledge comes sorrow. Tag: out of range problem in workbook closing Tag: 51445
  - 17
    - Checkbox not part of cell. (???) Cells can move "underneath". (!!!) When I add a checkbox form to my spreadsheet, it is not part of any cell. It sits above the spreadsheet and can even overlap the row/column/cell boundary lines. When I place them so that they look like they're "within" the cell, inserting a row will move the cells with text down, but the checkboxes, as you can guess, stay put. Is there someway to stitch the checkbox form to the cell contents itself? THANKS!!!!!!!!!! -- With knowledge comes sorrow. Tag: out of range problem in workbook closing Tag: 51443
  - 18
    - Excel 97 vs. Excel 2002 I have plenty of workbooks created in Excel 97. My company just upgraded all the office computers to Excel 2002. The workbooks with formulas created in 97 will not print in 2002. Some of them have this error-correcting thing that comes up pertaining to conditional formats. And then repairs them by clearing the format out. But still will not print. Can you folks guide me to some information explaining this and hopefully a workaround? Thank you Tag: out of range problem in workbook closing Tag: 51439
  - 19
    - insert a check box...how? I want to insert some check boxes in a spread sheet. I just want to be able to click on the box and have the check appear and click on it again to have it disappear. I don't want any data related to this box...can this be done? Thanks Tag: out of range problem in workbook closing Tag: 51434
  - 20
    - Excel formula help I have tried unsucessfully to come up with a formula where cell A3 = A1 - A2. I would like for the Sum of A3 to not be greater than 15, and not less than -15. My formulas thus far have been, =if(A1-A2>=15,15*1,A1-A2). This will work for the greater than 15, but when I add the statement =if(A1-A2>=15,15*1,A1-A2)=if(A1-A2>=-15,-15*1)I get an error. Any help and all ideas welcome. Thank you, Rick Tag: out of range problem in workbook closing Tag: 51429
  - 21
    - using for-next index in range variable MSDN seems to say that range values must be written in the A1 format. I need to compare a value in column D (sample IDs) of worksheet import and compare it to column A (location) of worksheet data. If the values are equal I need to set a cell in worksheet data equal to the sum of columns I:M in the same row that the sample ID = the location. Will something like this work: If worksheet(import).cell[D"i"] = worksheet(data).cell[A"j"] then worksheet(data).cell[k,j] = sum(worksheet(import).[I"i":M"i"] Is there a different, perhaps easier way to do this? Tag: out of range problem in workbook closing Tag: 51420
  - 22
    - Column width: Font characters?!? not inches? All I can find on Column Width is that the measurement I see displayed is how many characters of the current font will fit across the cell. Is this correct?!? Is there any way to get the column width in inches? Ed Tag: out of range problem in workbook closing Tag: 51413
  - 23
    - Pivot Chart problems This is a multi-part message in MIME format. ------=_NextPart_000_00B3_01C4E370.755D7D40 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable I have two issues with pivot charts. 1) I create a pivot table and chart with VB. I want thick lines on the = chart, so I do as follows: For i =3D 1 To .SeriesCollection.Count .SeriesCollection(i).Select With Selection.Border .Weight =3D xlThick End With Next This works great. However, upon selecting a Page Field value from the = drop down created automatically on the chart, the new version of the = chart has thin lines. Is there a way to tell Excel to create thick = lines as a default? 2) I want the pivot chart to use a time scale. If I use the following = line: .Axes(xlCategory, xlPrimary).CategoryType =3D xlTimeScale I receive the following message: "Invalid procedure call or argument". = If I use this same code on a chart created from a table, I receive no = message and it works fine. There must be some problem using pivot = charts that I don't understand. Harley ------=_NextPart_000_00B3_01C4E370.755D7D40 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Dwindows-1252"> <META content=3D"MSHTML 6.00.2900.2523" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT size=3D2>I have two issues with pivot charts.</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>1) I create a pivot table and chart with VB.  I = want=20 thick lines on the chart, so I do as follows:</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>        For i =3D = 1 To=20 .SeriesCollection.Count<BR>       &nbs= p;   =20 .SeriesCollection(i).Select<BR>       =     =20 With=20 Selection.Border<BR>        &nbsp= ;      =20 .Weight =3D=20 xlThick<BR>          &n= bsp;=20 End With<BR>         = Next</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>This works great.  However, upon selecting a = Page Field=20 value from the drop down created automatically on the chart, the new = version of=20 the chart has thin lines.  Is there a way to tell Excel to create = thick=20 lines as a default?</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>2) I want the pivot chart to use a time scale.  = If I use=20 the following line:</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT=20 size=3D2>          &nbs= p;=20 .Axes(xlCategory, xlPrimary).CategoryType =3D = xlTimeScale<BR></FONT></DIV> <DIV><FONT size=3D2>I receive the following message: </FONT><FONT = size=3D2>"Invalid=20 procedure call or argument".  If I use this same code on a chart = created=20 from a table, I receive no message and it works fine.  There must = be some=20 problem using pivot charts that I don't understand.</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>  Harley</FONT></DIV></BODY></HTML> ------=_NextPart_000_00B3_01C4E370.755D7D40-- Tag: out of range problem in workbook closing Tag: 51412
  - 24
    - Needed: A Quick Integration Routine Hi All, I offten want to get an integrated average value of some time / temperature data. Time is usually in col 1 and temps are in the other columns. I'd like to select an arbitrary period of time /temperatures points and find the intgrated average temperature value. Does someone have a good way of doing this, perferably with worksheet functions but some VBA code would also work ? Thanks, Mike Tag: out of range problem in workbook closing Tag: 51407
  - 25
    - Securing Workbooks Gang: I would like to secure my Excel 97 (and Excel 2000) workbook from users in such a way that they can only enter values in a specific range and view the results in another range. I'd like to keep my formulas and other sheets hidden from the users. I;'ve been using the Protection properties at the range, sheet, and workbook level. Unfortunately, as soon as the user saves the workbook using the Save As method, it appears that all protection is removed. Do anyone have any suggestions on how to keep the sheets and workbook protected when the workbook is saved as another object? TIA, Ed Tag: out of range problem in workbook closing Tag: 51403
- Next
  - 1
    - radio button on protected worksheet Is there a way to create a set of radio buttons within a password protected template so that the "Cell or Chart you are trying to change is protected..." error does not occur when selected by the end user? I want my template to be locked, but I want the users that use the template to be able to select either RadioButtonA or RadioButtonB. Help? Tag: out of range problem in workbook closing Tag: 51402
  - 2
    - Splitting a Cell Hello, I have a Cell that contains first and last name data. I would like to split this one cell into two cells, one with first name data and one with last name data. I have tried doing this through a Macro, but with no sucess. Can anyone point me in the right direction? Thank you! Tag: out of range problem in workbook closing Tag: 51401
  - 3
    - unable to copy to all, i cannot copy data from excel file to another files, no matter it's an excel or word file. i can ONLY copy and paste data from worksheet accross worksheet within the same excel file. Please help!! Thanks calvin Tag: out of range problem in workbook closing Tag: 51396
  - 4
    - Hide too long text in a cell How can I hide the part of the text that is too long for the cell? This automaticly happens when in the cell on the right is already a text. But I want this also when there is no text in the neighbouring cell. (Note: I don't want to use wrap text) Tag: out of range problem in workbook closing Tag: 51394
  - 5
    - how to stop program in a big loop Hi, everyone Right now I wanna do a time-comsuming loop in excel programming, for example: for i = 0 to 100000 for j = 0 to 10000 'my parts doEevents next next during run time, I still wanna use a "Cancel" button to let user have choice to stop it during run time? how to do it, Pls advice Tag: out of range problem in workbook closing Tag: 51391
  - 6
    - Web Query I am currently running a macro that initiates a web query on a https site that contains mixed content (both secure and non secure items) the query keeps failing because the warning message "This site contains both secure and non secure items. Do you want to display the non secure items?" prevents the query from proceeding. Any ideas on a work around? Is there a way to turn the warning messages off I have tried adjusting settings in IE however this does not appear to turn the warning message off in excel. Could a command be written into the macro to click yes at the point of the warning message? Any help appreciated. Thanks Reo Tag: out of range problem in workbook closing Tag: 51384
  - 7
    - ???: Freezing data at a set time I am ingesting real-time temperature data into excel. I want to know what the temperature was at certain times of the day. How do I get the computer to save the temperature every hour but also keep importing and monitoring the current temperature ? Thanks, Grinch Tag: out of range problem in workbook closing Tag: 51383
  - 8
    - Making sound with Excel Can Excel make sound ? I am ingesting real-time data into excel. When certain criteria are met, I want the computer to ring an alarm or play a sound file. How can I do this? I'm good with Excel and do macros but don't know vba at all. Thanks, Grinch Tag: out of range problem in workbook closing Tag: 51381
  - 9
    - Saving data to Jpeg or gif I'm trying to save a large block of Excel data as an image file. I have tried copying the data and pasting into "Paint" and then saving as an image. This works...but some data disappears in the strangest way. Very strange. All of the data gets copied but some is missing when you look at the image. Does anybody know a simple way to save Excel data as an image ? Thanks, Grinch Tag: out of range problem in workbook closing Tag: 51380
  - 10
    - Excel Formulas I am trying to creat a logical test and at the end have it round up the result. Here is my formula so far: =IF(ISERROR(D50/E3),0,D50/E3) . This will prevent me from getting the dreaded #DIV/0! but I cannot get it to round up the result. The idea is to keep it contained within that particular cell. Any help would be greatly appreciated. Thanks. Tag: out of range problem in workbook closing Tag: 51365
  - 11
    - Convert text to numbers Hi I have a column of numbers in text format - I need to change them to number format When I goto Format ->cells to change - it does not work Is there another way to change? Thanks Tag: out of range problem in workbook closing Tag: 51364
  - 12
    - Question on LABEL Range In a LABEL Range(say that it has 12 elements row-wise), how do one refer to the 4th elements? I have this ideas that in a worksheet, I have 12 rows single column LABELED range called MONTHS, where 1st elements is JAN(that is C6), and so on, and last element would be DEC(C17). How do I get the 4th element which will be APR, and put that value in the different cell using the LABEL reference instead of the absolute cell location(C9)? Thanks Tag: out of range problem in workbook closing Tag: 51362
  - 13
    - weeks Hi! is there a way of automating the number of weeks based on the now() date? (i.e. 52 weeks/year) Thanks! Tag: out of range problem in workbook closing Tag: 51355
  - 14
    - Microsoft far better than Google http://wonderfulthings.blogspot.com Tag: out of range problem in workbook closing Tag: 51354
  - 15
    - Calculation order What order do calculations calculate in? Are the first calculations created in the worksheet always calculated first? If the calculations are in row 1, are they calculated from left to right? If the calculations are in column 1, are they calculated from top to bottom? Any help would be welcome. Tag: out of range problem in workbook closing Tag: 51352
  - 16
    - vba coding I have a test tool that generates a 1079 row x 13 column csv file and I would like to automate its conversion into a spreadsheet we use to analyze it. The tool takes samples for different areas and has an id# associated with that area in one column. Some areas require two samples, others require ten. I would like to take the row associated with an id# and sum the data in columns 9-13 and place the sum in a column and row on another worksheet. If the next row of data in the csv file has the same id# I'd like to sum the data as before and place it in the next column over. Something like: =IF('import here'!$D1='data conv'!$A4,SUM('import here'!$I1:$M1)) then skip down one row in 'import here' =IF('import here'!$D2='data conv'!$A4,SUM('import here'!$I2:$M2)) On a false condition it needs to increment the sample id# & check the next row down in the 'import here'. Any ideas on how to do this or where I can find a good reference? Tag: out of range problem in workbook closing Tag: 51344
  - 17
    - function to return "number" of different values that occur ? Hi, Is there an function is Excel that can return the number of different values i.e COLUMNA 1 1 1 1 2 2 3 4 5 6 6 6 6 6 TOTAL = 6 (i.e 6 different values appear) Thanks for any help. Scott Tag: out of range problem in workbook closing Tag: 51341
  - 18
    - stop update link messages I have a workbook I received (via email) from someone we buy from, I take that workbook and edit it to be able to send individual sheets to my customers. Problem is, original workbook creator has links from other workbooks on his machine When I open the workbook it asks me do I want to update the links. Well I couldn't if I wanted to because I don't have the workbooks its asking about. How do I get rid of these warnings. I want to email my individual worksheets to my customers but I don't want them to see this Update message when it doesn't pertain to them (it's very frustrating). Any help suggestions would be great. Thanks in Advance! -- Kathy F Milwaukee, WI Tag: out of range problem in workbook closing Tag: 51338
  - 19
    - New Date on each new page I would like to print a page for each day for the upcoming year. 365 pages. I want to print all of them now. On each page I need the date for that day. What should I use for this? Pivot tables?? Thanks! Jim Tag: out of range problem in workbook closing Tag: 51333
  - 20
    - Print Preview Print Preview takes ages to open up initially, closes ok, opens up ok and then takes ages to close. Windows Professional, Excel 2002. any clues as to how i can overcome this. Cheers Harcon Tag: out of range problem in workbook closing Tag: 51331
  - 21
    - Excel 2000 zero-size window? Running Excel 2000 under Win98, the standard (non-maximised) Window size seems to have become zero. In other words, when starting Excel either by running excel.exe (from shortcut or directory display) or by clicking on an .XLS file, an entry appears in the taskbar, but no window opens on the screen. Right- clicking on the taskbar entry and clicking Restore does nothing. Clicking Maximize works properly. It is as if the normal window size has become 0 x 0. On trying to Restore the Maximized window, the window disappears again. An alternative description: Excel in what should be a normal window behaves as if minimized. It can be Maximized, but Restore is the same as Minimize. It can be made to work full-screen or not at all. I would be grateful for any idea on what is happening/ Is there a registry entry which governs the current size of the normal window? Best wishes, -- Michael Salem Tag: out of range problem in workbook closing Tag: 51322
  - 22
    - Trouble with "MatchRequired" in XL visual basic Hi all I have to use the MatchRequired property to limit what can be entered into the spreadsheet via the form I have set up. The problem is that when an entry to put into the form combobox that is not present in the dropdown menu, I get the message "Inval property value". THE PROBLEM(s) The field is not highlighted to display to the user that it is the field they just entered that is wrong, i.e. it does not set focus. The message is not that clear - it sounds too techi for a basic user of XL - can I chnage it ?? If the entry is deleted then it still comes up with the error message even though their is a blank as an option in the dropdown. i.e "" is coded into to reflect a blank - is this different than deleting or backspacing?? Any help appreciated. Aaron Tag: out of range problem in workbook closing Tag: 51313
  - 23
    - Large Dimensions rendering in PIVOT Tables issue Hi. I have an OLAP Cube Design <to> Excel Pivot Table rendering issue here. Our fact table is composed of sales transactions from a website. We routinely take a relatively small number (say 10,000) of these transactions and load them into Excel, where we create a PivotTable. (No OLAP used in this portion....just data right well below the 65k rec max loaded into a worksheet from SQL SERVER) Let's say it looks something like this: http://home.comcast.net/~murdockdesign/PivotOlap.jpg (*screenshot of pseudo Pivot table) **Note that we are using the transactionID (TxID) as a dimension. We want to support this kind of report in an OLAP cube so that we can build the same kind of report against much bigger fact tables. (say 1,000,000+ transactions.) The problem we run into is that Analysis Services (and perhaps OLAP in general) doesn't like dimensions that have millions of members, which is what happens when we let TxID be a dimension in our cube, since each TxID is (of course) unique. In our reading, we've seen that it's a bad idea to let dimensions have more that a few thousand members, and one reason stated is because "users can't easily comprehend a report with that many members." However, we don't typically create big reports. We use the other dimensions in the pivot to filter down the result set so that the resulting PivotReport is only a few pages long containing a few hundred TxID's. We've tried using Automatic Grouping on the huge TxID dimension, but it times out when trying to retrieve the Member Count (we have set SQL SERVER TimeOut Property to 0). We are clear on the fact that a large dimension is considered bad design practice, however we have a real business need to render our data in this way. We would appreciate it if anyone could suggest a way of creating this report in Excel against a data set with millions of unique transaction ID's? Thanks! Tag: out of range problem in workbook closing Tag: 51308
  - 24
    - Rounding Up Is it possible to perform a calculation and have the result rounded up to the next whole number? I don't ever want it rounded down. Tag: out of range problem in workbook closing Tag: 51293
  - 25
    - Combining comments. Can it be done? Hi I have multiple sheets. If things out of the ordinary happen a note will be left under that date. There is a possiblelity of there being notes on more than one sheet for the same date What I am hoping can be done is that on one page I can combine all the notes that have been done for each date for an easy summary of whats happening. Thanks Tag: out of range problem in workbook closing Tag: 51290

I got an Out of Range error closing a workbook using VBA on one machine but
not the other. Can any one help?

fName = Left(ThisWorkbook.Path, pos) & "SYS01 Report.xls"
Workbooks.Open Filename:=fName
Sheets(1).Select
Sheets(1).Copy Before:=Workbooks("SYS01 Program.xls").Sheets(1)
Workbooks("SYS01 Report").Saved = True
Workbooks("SYS01 Report").Close ... this is the code gets the error
ThisWorkbook.Sheets(1).Name = "CTL"

Top

Re: out of range problem in workbook closing by Dave

Dave
Sat Dec 18 09:44:15 CST 2004

Try adding .xls to each of these lines:

Workbooks("SYS01 Report").Saved = True
Workbooks("SYS01 Report").Close

Workbooks("SYS01 Report.xls").Saved = True
Workbooks("SYS01 Report.xls").Close

There's a windows setting that allows users to hide/show extensions. To be
careful, you can always include the extension and it'll work no matter the what
the user chose for that setting.

hlam wrote:
>
> I got an Out of Range error closing a workbook using VBA on one machine but
> not the other. Can any one help?
>
> fName = Left(ThisWorkbook.Path, pos) & "SYS01 Report.xls"
> Workbooks.Open Filename:=fName
> Sheets(1).Select
> Sheets(1).Copy Before:=Workbooks("SYS01 Program.xls").Sheets(1)
> Workbooks("SYS01 Report").Saved = True
> Workbooks("SYS01 Report").Close ... this is the code gets the error
> ThisWorkbook.Sheets(1).Name = "CTL"

--

Dave Peterson

Top