Ascii Page Break

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
    - Excel 2007 - Data May Be Lost - MRound function replaced with #N/A? I am one of the first people in my organisation to have Office 2007 installed - the majority of other users are still on Office 2003. As a result, I have to keep all my workbooks in Office 2003 format. The problem I have is that the message 'File error: Data my be lost' is frequently displayed when I open one of these workbooks and then several formulae are replaced with '#N/A'. I then have to go through and re-enter all the formulae. It's worth noting that this doesn't happen every time I open a workbook but after it's happened to a particular workbook once it will happen every time I open that file, even if I don't save it. It would seem that it's got something to do with the functions MROUND and NETWORKDAYS, which used to be included in the Excel 2003 Analysis TookPak. Does anyone know of any add-in, patch, etc that I can installed to get round this as I use these functions all the time and it's getting very tedious! Thanks Steve Tag: Ascii Page Break Tag: 150855
  - 2
    - Summing individual digits in a cell Hi there, I want a formula to add the digits of a cell e.g. 1234, the total would be 10, tried this forumla 'SUM(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))' and resulted in answer '1'. Please, help, i am in urgent need. Thanks Rashi Tag: Ascii Page Break Tag: 150849
  - 3
    - Complex task...can Excel do it? I have an organizational task I'm trying to accomplish. Here is the situation: At a high school summer camp I have a few hundred students. These students are of multiple grade levels, from multiple schools, and are a mix of male and female. For the summer camp we split them into equal sized groups (or as close to equal as possible) of around 12-15 students. When we create the groups, we try to: * Keep the male/female ratios within the groups the same as the general population of the camp (if out of all the students 75% are female, we would want all the groups to be 75% female as well, or as close to it as possible). * Students from the same school should be not be put together in a group (if for some reason one school brings a ton of students, each group should have one student from that school before we start doubling them up) * Each group should reflect the general population in the grade level of the students. If the general population had 10% - 9th grade, 20% - 10th grade, 50% - 11th grade, and 20% - 12th grade, the groups would all reflect the same percentages in their students. We've been doing this task manually for as long as I can remember, which takes forever...but I know it can be computerized. Can anyone think of a way to get Excel to do this for me automatically? I appreciate your help! -Ryan Tag: Ascii Page Break Tag: 150845
  - 4
    - Pluralize Is there a function to pluralize text in a cell? For example =PLURALIZE(e21) or something like that? Tag: Ascii Page Break Tag: 150844
  - 5
    - Change default filename Hi, Is there any way to change the default filename in excel 2003? ie. a blank file is opened and called: "Book X.xls" Whereas, I want all files to be called: Finance_Planning_X.xls" This way, I can save time by automatically having my file naming convention built into Excel, and I won't have to remember it going forward. Thanks! Chris Tag: Ascii Page Break Tag: 150843
  - 6
    - Changing Font Colour of row depending on selection made in 1st col I am currently trying to work out how to change the font colour of all cells in the row, depending on the selection made from a list in the 1st column (Col. A). Column A is based on selecting from a List, with about half of the selections already conditionally formatted to a certain colour. My question is - how can I get the other cells in the row to change to the same colour as the selected cell? Given that the list contains 17 different cells to select from, and 10 of these when selected display as a different colour. Currently I am manually selecting the row and changing the font colour, but would like to have it done automatically. Any help will be most welcomed. Cheers. Tag: Ascii Page Break Tag: 150834
  - 7
    - linking spreadsheets to word hi, i've linked 1 spreadsheet to another spreadsheet in excel, but now i need to link the spreadsheet to MS Word. therefore whenever i update the spreadsheet in Excel, the MS Word one will also get updated how would i do that? thankyou very much Tag: Ascii Page Break Tag: 150831
  - 8
    - Excel automatically changing my formula Hello, This is a very annoying issue I have run in to and I don't know how to work around it. I have designed a sheet with 5 rows of blank cells followed by 2 rows which sum up the previous 5. So the formulas work like this: Cell | Formula A1 | (empty) A2 | (empty) A3 | (empty) A4 | (empty) A5 | (empty) A6 | "=sum(A1:A4)" A7 | "=sum(A1:A5)" The idea is that the 6th row sums the first 4 and the 7th row sums the first 5 rows. Users enter data in the blank cells and the last two rows give them two different summaries. The problem is that Excel wants to "fix" my formula in cell A6. If a user enters a number in cell A1, then A2, etc.. through A5, then when they enter that last data point, Excel changes my formula in A6 so that it sums the first 5 rows instead of the first 4. Note that if they enter data in cell A5 first, this problem does not occur. This acts like it is some kind of annoying (dare I say, dangerous?) error checking feature and I desperately want it to stop. I am using Office Professionsal '03 with SP3 installed. However the person who brought this to me was using the sheet I created (on my computer) on his computer, which has a newer version (Vista?) of Office installed and this problem manifests when you enter data on either computer. Anyone have any ideas? Tag: Ascii Page Break Tag: 150824
  - 9
    - Marked cells more visible When marking cells in 2007, is it possible to change the color to a more visible one? Tag: Ascii Page Break Tag: 150823
  - 10
    - type date for next year I'm entering dates for 2009 in my workbook and like to enter them quick.When I type 10/29 then by default the date is returned as 10/29/2008. How can I enter a date like 10/29 in a cell so that this cell automatically sets it to 10/29/2009 and not 10/29/2008? Thanks. Tag: Ascii Page Break Tag: 150817
  - 11
    - Macro Button I used to have a button on a toolbar that I clicked and made a new button in an excel worksheet that I could assign a macro to. It's not on my toolbar anymore, and I have no idea what toolbar menu to look in to find it. Anybody know? Tag: Ascii Page Break Tag: 150809
  - 12
    - copy new worksheet I want to take a worksheet called "data" and make a copy to a closed workwork called "all data". I tried Worksheets("data").Copy after:=Workbooks("T:\me\the Data\all Data").Worksheets(Sheet1) It doesn't work...Do I have to first open the closed workbook inorder to copy this worksheet over? Thanks Tag: Ascii Page Break Tag: 150806
  - 13
    - Combining Formulas I have two columns that get data from two different formulas. The reason I have two columns is becasue I couldn't figure out how to combine the two formulas. I worked on it for a while, but I had to give up and divide it in half. I'll post the two formulas. Maybe someone can figure out an easy way to combine them. If you need more info, let me know. Thanks. =IF(C3="","",IF(B2="",IF(C3<=0,IF(D3<C3,B4,IF(B5="",IF(C3<=0,IF(D3<C3,B3,IF(B2="",IF(C3<=0,IF(D3>C3,B3,IF(B5="",IF(C3<=0,IF(D3>C3,B3))))))))))))) =IF(C3="","",IF(C3>0,"",IF(B4="",IF(C3<=0,IF(D3<C3,B2,IF(B4="",IF(C3<=0,IF(D3<C3,B3,IF(B4="",IF(C3<=0,IF(D3>C3,B3,IF(B4="",IF(C3<=0,IF(D3>C3,B3)))))))))))))) Tag: Ascii Page Break Tag: 150799
  - 14
    - For Each Next help Here is my code. Dim C As Range For Each C In Range("TotalsCells").Resize(1, 0) -----IT FAILS AT THIS LINE Cells.FormulaR1C1 = "SUM(R2C:R[-1]C)" Next C I have a couple of questions. Is my variable declaration correct? I want to inspect each cell in the range. Is my Resize syntax correct for inspecting each cell? Is my Sum syntax correct? Thanks in advance for your help. Tag: Ascii Page Break Tag: 150796
  - 15
    - copy function in excel When working on an existing worksheet, I have run into a situation sometimes whereby the cell turns blue and I am unable to copy. I don't understand what is going on and what I need to change to allow me to copy. I am currently using Excell 2007 version. Tag: Ascii Page Break Tag: 150791
  - 16
    - Converting Miles and Chains Is there a function that would convert this: 24M 23Â½C To this: 024.0517 Basically that's miles and chains converted to miles and yards with the removal of the alpha characters and returned in the exact format as shown. Thanks Tag: Ascii Page Break Tag: 150790
  - 17
    - How to delete a checkbox on a Excel sheet ? Hello, I am using Excel 2003. I have created a template (.xlt) with some checkboxes. No macro in the template. No action related to the checkbox (only for printing). I would like to delete these checkboxes now but I cannot go back to the design mode. Whenever I click on a checkbox (left or right), it does nothing but check the box ! How can I do ? Thank you in advance, Gilbert Tag: Ascii Page Break Tag: 150783
  - 18
    - Formula Problem I seem to have trouble w/ a particular formula. I am attempting to round a number to the nearest tenth. No big deal...simple stuff if it were to stop there. I have rounded the number which returns as a decimal. I don't have trouble with any other number except when dealing with a zero in the 10ths spot. When I add text to the formula the decimal is not shown. Is there a way to have Excel first determine whether I have a zero in the tenths column and if so, have it show the decimal? Here's an example of the formula... FORMULA RESULT =ROUND(Battery!F30,0) 2.00 =ROUND(Battery!F30,0)& " Days" 2 Days I want it to show 2.0 Days Make sense? Thanks! Tag: Ascii Page Break Tag: 150776
  - 19
    - Help on setting a range Here is my code. Dim LastRow, LastCol As Long Dim LastCell As Range Dim LastCellAddress As Range Dim intNumCols As Long Dim DataCells As Range 'Find the last row, column and last cell of the worksheet. LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set LastCell = ActiveSheet.Cells(LastRow, LastCol) 'Select from "A2" to the last cell. Range("A2").Select Set DataCells = Cells("A2").Resize(LastRow, LastCol).Select I keep failing at the last line, trying to set the range variable. I get a Type Mismatch error. I have tried everything. I don't understand the mismatch error. DataCells is a range correct? Are my LastRow and LastCol arguments to the Resize method good? This should be so easy. Thanks in advance for your help. Tag: Ascii Page Break Tag: 150772
  - 20
    - Summing instances of text across worksheets Hello, I have a workbook with separate sheets for each month. The sheets contain a list client names (entered exactly the same on each sheet). I need to summarize how many times a client name appears in total. For example, John Brown may appear Jan & Feb (=2) where Mary Brown may appear Jul, Sep, Dec (=3). I've been trying pivot tables without luck. Hoping someone can help. Thanks in advance! Tag: Ascii Page Break Tag: 150764
  - 21
    - text to operator Have fields that look, for example, like "2A5". I know I can use the REPLACE function to get to "2*5" by replacing the letter with the asterisk. At this point the 2*5 is a text string. Is there a way to get it to be 2 times 5 so that it gives a result of 10? Thanx in advance! Tag: Ascii Page Break Tag: 150754
  - 22
    - Click Me I've drawn a command button - ClickMe - on a work sheet and written a little code for it. [I am used to vb code in the Access environment] NOW!! how do I make the button user clickable please? -- Jim Bunton Tag: Ascii Page Break Tag: 150744
  - 23
    - order of conditional formatting rules I have a sheet that has some cells with multiple conditional formatting rules. These rules are arranged in some order, so that it stops executing the conditional formats once a certain condition has been met. My problem is that when I make a copy of this sheet, the order of conditional formatting rules is not maintained on the copy. Why is this? Does anyone know how to fix it, short of manually (and tediously) rearranging the order of rules on the copy? Thanks in advance. Tag: Ascii Page Break Tag: 150741
  - 24
    - Nothing in cell only in formula bar Why is it (2007) that sometimes when I type a formula (or anything for that matter) into a cell nothing shows up in the cell, but it will show up in the formula bar? Tag: Ascii Page Break Tag: 150739
  - 25
    - Excel 2002 debug prompt for all macros We have 5 PCs in our department that should all be configured alike. When we run any macro (includes VBS script) in Excel 2002 on one of the PCs, there is a debug prompt. On the other 4 PCs the macros work fine. Is there a setting that could have been turned on that is causing the debug prompt on this computer? Chris Wieburg Tag: Ascii Page Break Tag: 150738
- Next
  - 1
    - Macro Question Hi: I have a group of several files with a simple macro that automatically goes to a specific worksheet at the time the file is opened. The code is as follows: Private Sub Workbook_Open() Worksheets ("TITLE").Select Range ("a1").Select End Sub The macro works properly when a single file is opened, but when I try to open several of the files at the same time I get the following error message: Run Time Error 1004 Select method of Worksheet Class Failed All of the files do open, but the macro appears to run on only the first file selected. All of the other files open to the page that was active when the file was last saved. Can my macro be modified to eliminate this behavior? TIA for any advice. Crownman Tag: Ascii Page Break Tag: 150728
  - 2
    - Linking cells containing text from one worksheet to a cell with text I have a workbook with two pages, the first page has cells that contain the main description heading of the cells on the second page. The second page has the heading in one cell and then the breakdown of each section under that, and repeated as there are about 11 sections. When I paste a link or use a hyperlink on the first page, it does take you to the heading cell on the second page, however I was wondering if there is a way to force the cells on the bottom of the page to the top for easier viewing. Right now it just pulls those cells up enough to see them at the bottom of the screen, and it's hard to tell where you should be looking. It would be so much cleaner if they automatically were pulled up to the top so the reader could start reading without having to scroll further. I know this is a bad description, but now sure how else to describe. I'm not really familiar with VB, I've muddled through it before, but am not good with it. thanks for any help. Tag: Ascii Page Break Tag: 150727
  - 3
    - Value Error I copied some data from a web page and pasted it into a spread sheet. It looks like the following: 10/12/2008 BALTIMORE RAVENS 39 39 13:00 EST INDIANAPOLIS COLTS -6 -4.5 Simple enough. when I try to subtract (or add) -6 and -4.5 into a new cell I get a value error. If I retype the -4.5 to -4.5 (I can't see why there is any difference) it works. (I don't have to retype the -6???, just the -4.5) I don't want to manually change all of the numbers so I can do simple subtraction and addition. This is frustrating. Tag: Ascii Page Break Tag: 150723
  - 4
    - Adding comma seperated values within a single cell I need to come up with a custom function that will add up comma seperated numerals contained within a single cell. For example if the the cell A1 contains: 4,5,3 I want the result of the fuction (say in a2) to display the result of 12 Any help greatly appreciated. Regards Mike Tag: Ascii Page Break Tag: 150716
  - 5
    - Apply any mathematical function Hi I have a bit of a tricky question. Three identical integers (ex. 3, 3, 3). Apply any mathematical funtion to each to make the sum=6. In the case of 3: 3*3-3=6 or -3+3*3. The integers are from 1 through 9. Always in identical triplets. Obviously this is one of those annoying viral emails. I thought I might give it a shot with Excel rather than to just brute force it. Got stuck though. Any thoughts? /Sune Tag: Ascii Page Break Tag: 150714
  - 6
    - Apply any mathematical funtion Hi I have a bit of a tricky question. Three identical integers (ex. 3, 3, 3). Apply any mathematical funtion to each to make the sum=6. In the case of 3: 3*3-3=6 or -3+3*3. The integers are from 1 through 9. Always in identical triplets. Obviously this is one of those annoying viral emails. I thought I might give it a shot with Excel rather than to just brute force it. Got stuck though. Any thoughts? /Sune Tag: Ascii Page Break Tag: 150712
  - 7
    - Excel Autofilters Hi All, I have a user who is experiencing problems with Autofilters in her excel spreadsheet. The user is running windows XP with office 2002 (XP). When the user receives the spreadsheet she turns the Autofilters on but after a while of working in the spreadsheet the filters will stop working. If the user tries to click on a filter it doesn't show the drop down list. The user is able to scroll around the sheet and enter new data just cannot select the list. The user and me have been able to replicate the problems on other computers. Does anyone have any ideas what could be causing this ? Tag: Ascii Page Break Tag: 150710
  - 8
    - Addition in Excel. Help! Hi, I work in a bank and I am working on a spreadsheet adds values ONLY the two decimal places and disregard the rest. i.e. +29.901 -report displayed as --> 29.90 +29.902 -report displayed as --> 29.90 +29.903 -report displayed as --> 29.90 +29.904 -report displayed as --> 29.90 +29.905 -report displayed as --> 29.91 ------------ ------------ =149.52 =149.51 (total from the displayed values.) The problem is that the customers only see up to two decimal places of the amounts. On the example, if the customer adds these value on his calculator what is displayed, they would get a different total from excel's total. I realized that limiting the number of decimal places or changes the format (general, accounting, or currency) only changes how the number is displayed but not how excel treats the number in doing operations. Any suggestions/solutions would be greatly appreciated ! Tag: Ascii Page Break Tag: 150708
  - 9
    - SUMIF Problem - Help please I am trying to sum a column of figures in one owksheet if it matches the name in the appropriate cell in another worksheet. I know it can do it but I cannot get the formast of the formula correct - any help greatly appreciated. I am trying to sum a group of marks for particyular students names that could appear several times in the whole column ie What I want to do is sum the marks in sheet 1 over the range F4 to F10 if name in Sheet 1 A4:A100 = the name in Sheet 2 A4. I then intend to copy this formula down so it will give me total scores for each name in the class. thanks David Tag: Ascii Page Break Tag: 150704
  - 10
    - another VBA Question I've tried a couple options with no luck. Trying to come up with code to Save / Close a spreadsheet automatically after x amount of time, when worksheet is inactive. Preferable amount of time is 30 mintues of inactivity. This final time, I was given the following code....which does not seem to be working. Any ideas on what might be wrong here? Option Explicit Dim Lasttime As Double Dim Thistime As Double Sub Auto_Open() Application.OnEntry = "zerotime" Lasttime = Now Thistime = Now CounterTime End Sub Sub Auto_Close() Application.StatusBar = False Application.OnEntry = "" Application.OnTime Now() + TimeSerial(0, 5, 0), procedure:="countertime", schedule:=False End Sub Sub CounterTime() Thistime = Now - Lasttime Application.StatusBar = "Unused for " + Format(Thistime, "hh.mm.ss") + ". Closes in 00.05.00" If Thistime > TimeSerial(0, 5, 0) Then ThisWorkbook.Close True End End If Application.OnTime Now() + TimeSerial(0, 0, 1), "countertime" End Sub Sub Zerotime() Lasttime = Now() End Sub Tag: Ascii Page Break Tag: 150684
  - 11
    - Fixed Field Length Issue We have an Excel spreadsheet that needs to be saved as a text file to be picked up by another application. The problem is when we save it as a text file, additional spaces are added in between the data in Col A and Col B, etc. even though we've defined the length for each column. Help would be greatly appreciated.... Thanks! -- Pat Tag: Ascii Page Break Tag: 150673
  - 12
    - VBA Question I am currently writing a macro that will read data out of columns and write that data out to rows on a separate sheet. I need to write the data out to rows in a separate sheet (going to save that as a CSV). When I do the first for each loop it works fine. However when it goes to do the second one - it is reading out of the S column - rather than the specified J range. I figured out that S is the exact distance from J that J is from A - so it is almost as if the sheet has become offset. I am not sure why. I don't usually develop in the Excel model - so I suspect there are some protocols that I am unaware of. Here is the code (its a bit rough - I am still working on the logic and haven't taken the time to polish it) Sub MoveInfo() Dim wSheet As Worksheet Dim rCell As Range Dim vCell As Range Dim rowCount As Integer rowCount = 0 For Each wSheet In ActiveWorkbook.Sheets If wSheet.Name <> "Sheet3" Then Sheets(wSheet.Name).Select rowCount = rowCount + 1 For Each rCell In Range("A1:A33") If rCell.Cells.Value <> "" Then With rCell.Range("A1:A1") .Copy Sheets("Sheet3").Select Cells(rowCount, Columns.Count).End(xlToLeft)(1, 2).PasteSpecial xlValues Sheets(wSheet.Name).Select End With End If Next rCell End If rowCount = rowCount + 1 Sheets(wSheet.Name).Select For Each vCell In Range("J1:J33") If vCell.Cells.Value <> "" Then With vCell.Range("J1:J1") .Copy Sheets("Sheet3").Select Cells(rowCount, Columns.Count).End(xlToLeft)(1, 2).PasteSpecial xlValues Sheets(wSheet.Name).Select End With End If Next vCell Next wSheet End Sub Thanks in advance for any help or pushes in the right direction. Thanks, Matt Tag: Ascii Page Break Tag: 150671
  - 13
    - pivot table multiple criteria Hi, I have a pivot table in which I can select a weeknumber to get the number of visitors in a pivot table and diagram. I'm looking for a possibility to select multiple weeknumbers and get the combined result in a pivot table and diagram. I was thinking of a kind of selection buttons or something like that. Any help or suggestion is more than welcome. Kind regards, Chris Tag: Ascii Page Break Tag: 150668
  - 14
    - open spread worksheet in differnt excel windows I running Vista Ultimate 64bit with Office 2007 I looking the solution to open spread worksheet in differnt excel windows on my dual monitor. I work it success on Windows XP and Office 2003, that trick on the file type assisate setting but Vista seem not easy to change the file type open action. Some answer is check "ignore DDE" setting in excel option --> advance, but this is not work, any one has good solution ? Tag: Ascii Page Break Tag: 150666
  - 15
    - Sum won't show I've googled till I'm blind and I can not figure out why my sum won't show up in any other form than the formula I entered? No matter if it's addition/sub/multiplication - when I click on the cell where I want the answer, then type =C3*C4 - it just remains that formula - it doesn't show the answer - I've formatted the cells as currency - but nothing I do seems to make a difference - I'm using Office Suite 2007 - can anyone help me please? Pam Tag: Ascii Page Break Tag: 150663
  - 16
    - Alerts disabled in DoEvents loop - Deletion of protected-locked ce When running a macro that includes a loop with DoEvents alert messages are not shown to the user. In my case locked cells of protected Worksheets do not allow data entry but do not alert the user as to why. Even more problematic the content of these protected cells are delete when the delete key is pressed! This does not occur if the macro is not running. Instead an alert dialog is displayed and the action is cancelled. The problem can be reproduced with the following steps: 1. Create a Workbook and set all cells in a Worksheet to locked 2. Enter values into some of the cells 3. Protect the Worksheet 4. Select a cell and press delete (alert message should be shown) 5. Run a macro with a doevents loop 6. While the loop is running select a cell and press delete (contents are removed and no alert is shown!) example macro (runs a doevents loop for 2 minutes): Sub executeDoEvents() Application.DisplayAlerts = True For i = 1 To 1200 Sleep (100) DoEvents Next i MsgBox "Done executing DoEvents" End Sub Tag: Ascii Page Break Tag: 150662
  - 17
    - Shortcuts on french keyboard Hi, I have recently moved to a french office and am having trouble with the shortcuts in Excel using my french keyboard. My main problem is that I can not work out the auditing short which moves from a cell with a simple formula in it to its dependent cell. On a qwerty keyboard this would be ctrl + ] Please help! Thanks in advance. Tag: Ascii Page Break Tag: 150638
  - 18
    - Submit Order Form Hi, I am trying to create an order form which allows users to submit the completed order form to our inbox at the hit of a button. I have found "Send" buttons but I am struggling to get it to work! If anyone could help that'd be great. Thanks. Tag: Ascii Page Break Tag: 150637
  - 19
    - Remove macro warning Hi - I have a spreadsheet that I take some raw data that I run a macro on to clean the data then (once the macro is complete) I send it on to managers in my department. I don't want to send the file with the macro held within it, so I remove it from the copy I send out. The thing is, even with the macro deleted, when people open the file, they still get a message offering them the choice to enable macros, even though there is no macro. Can anyone tell me how I go about removing the reference to the macro so that this message no longer appears? Thanks Neil Tag: Ascii Page Break Tag: 150633
  - 20
    - filtering data with a variable i wish to filter a list by using the contains option, but i want to filter with contains a varible with its cell, then return the filtered result, cell a only to another part of my sheet, can this be done. example Selection.AutoFilter Field:=1, Criteria1:="=*test*", Operator:=xlAnd i want to replace test with a variable name, I have tried removing the speech marks but cannot seem to get it right, any help greatly recieved johny Tag: Ascii Page Break Tag: 150630
  - 21
    - Help addressing the last cell Here is my code: Dim LastRow As Long Dim LastCol As Long Dim LastCell As Range Dim intNumCols As Long Dim DataCells As Range 'Find the last row, column and last cell of the worksheet. LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column LastCell = ActiveSheet.Cells(LastRow, LastCol).Address Excel fails at the LastCell line. It tells me that the object is not defined, but clearly my variable is defined. Both LastRow and LastCol return the correct value. What is the problem? Thanks in advance. Tag: Ascii Page Break Tag: 150623
  - 22
    - When Excel 2007 opens a xls file all links are changed In my work environment we have one user who has just moved to Excel 2007 while everyone else is still using Excel 2003. When this person opens a 2003 .xls file that contains hyperlinks, all the hyperlinks, on all tabs, are changed. The following string is inserted into the front of the hyperlink string replacing the http://: file:///C:\users\frankp\Documents\Sharepoint Drafts (the original file was on a sharepoint fwiw) Is there some setting in excel 2007 that needs to be changed to eliminate this annoying behavior??? Tag: Ascii Page Break Tag: 150622
  - 23
    - Highlight intersection in a table using conditional formatting I used the index-match function to return a value from a source table (see below). Now I want to highlight that specific value from the source table using conditional formatting. Here's the Source Table: A B C D 1 5.5 6.4 7.2 8.1 2 3.5 7.8 6.4 5.5 3 6.7 7.2 1.1 6.4 Row Col Value 2 C 6.4 --> (used index-match to return value) How can I use conditional formatting to highlight the return vaule "6.4" on the source table itself (which is the intersection between row 2 and col C)? Thanks in advance! Tag: Ascii Page Break Tag: 150612
  - 24
    - Class The following data is pasted into a worksheet in cell A3. Two examples are listed below. Would it be possible to parse the data so that only the digit in brackets is displayed in cell D2? The number in brackets ranges from 1-7. 5:30 Nottingham 1 Oct 2008 Amateur Handicap (Class 5) 1m2f 16 Run 6:20 Kempton 1 Oct 2008 Stakes (Class 6) 1m3f 14 Run Ideally I would like to convert '1m3f' (1 mile, 3 furlongs) to display 11 (8*3) in B2. Miles has a maximum of 4 and naturally furlongs 7. This would save having to enter the data manually each time the data is pasted. Tag: Ascii Page Break Tag: 150610
  - 25
    - Sorting multiple time data based on time value on column A Review the attachment. You'll see 2 sets of data, "What I have" and "What I want". Of course, this is only a sample data, my actual data sheet has over 10,000 rows and 25 columns. The basic idea is to have some sort of script or formula where all the time match in 1 row, yet the value stays the with time during sorting. What I have Time 1 Value 1 Time 2 Value 2 Time 3 Value 3 7:00 a 7:00 e 7:00 h 7:01 b 7:02 f 7:02 i 7:02 c 7:03 g 7:03 d What I want Time 1 Value 1 Time 2 Value 2 Time 3 Value 3 7:00 a 7:00 e 7:00 h 7:01 b 7:02 c 7:02 f 7:02 i 7:03 d 7:03 g Please advise. Thanks. Tag: Ascii Page Break Tag: 150604

Is there any way to insert a page break into an ASCII .TXT file that Excel
will recognize as such when importing?

Thanks

Top

Re: Ascii Page Break by st

st
Sat Oct 11 22:42:30 CDT 2008

IkFkYW0iIDxhZGFtQG5vc3BhbS5jb20+INPPz8Ldycwv08/Pwt3JzMEg1yDOz9fP09TRyCDTzMXE
1cDdxcU6IG5ld3M6JTIzM08lMjNiNGdLSkhBLjEzMDhAVEsyTVNGVE5HUDAyLnBoeC5nYmwuLi4N
Cj4gSXMgdGhlcmUgYW55IHdheSB0byBpbnNlcnQgYSBwYWdlIGJyZWFrIGludG8gYW4gQVNDSUkg
LlRYVCBmaWxlIHRoYXQgRXhjZWwgDQo+IHdpbGwgcmVjb2duaXplIGFzIHN1Y2ggd2hlbiBpbXBv
cnRpbmc/DQo+IA0KPiBUaGFua3MgDQo+IA0KTm8gZ2FyYW50aWVzLCBidXQgdHJ5ICMxMiAoDCk=

Top

Re: Ascii Page Break by Dave

Dave
Sun Oct 12 06:52:46 CDT 2008

I don't think so.

Maybe you could include a special unique set of characters that could be
imported. Then run a macro that inserts a page break and deletes the row with
that string???

Adam wrote:
>
> Is there any way to insert a page break into an ASCII .TXT file that Excel
> will recognize as such when importing?
>
> Thanks

--

Dave Peterson

Top