Posts by Neale

    Not a solution but an exceuse maybe


    Excel 2000 has a limit of 64MB of RAM - no matter how much the machine has.


    Excel XP has a limit of 128MB and Excel 2003 has better memory management and uses most of the memory available.


    Pivot tables are memory hungry. From my experince a 44MB excel file in Excel 200 is usually pretty unstable anyway as it is approaching Excel 64MB limit just by opening it.


    suggestions
    Are there any calculations you can take out - eg paste special values to remove calculations.


    Are there some columns you can delete as they are not used or important??


    HTH

    CONGRATULATIONS to Dave and the others involved with the forum on it being up and running for a year.


    WELL DONE.


    Its a great community and it is great because of its members and their willingness to go out their way to help others.


    May it last for many more years.


    Neale

    It can depend on where the numbers came from, ie where they downloaded / imported from another system?


    If you format them to two decimal places and them use the sum you should see zero.


    I've had the same problem - mainly on imported data, XL obviously thinks there more decimal places than are displayed.


    HTH

    I use this macro attached to a button. You could use it in a Before Print macro.


    I use a range named HideRange for the values to be checked.


    HTH



    Sub HideZeroLines()


    Dim c
    Application.ScreenUpdating = False


    With Worksheets("report").Range("hiderange")
    .EntireRow.Hidden = False
    End With


    For Each c In Worksheets("report").Range("hiderange")
    If c.Value = 0 Then

    c.EntireRow.Hidden = True
    End If
    Next c


    ActiveSheet.DisplayAutomaticPageBreaks = False
    Application.ScreenUpdating = True



    End Sub

    Wecome to the forum


    This may be too complicated for waht you want but here goes.


    Assuming you want to look up a number of occurances. This array formula find the nth occurance. So you could input 10 of them to find the first 10 occurances.


    In this example the table is 5 columns wide and its ref is A1:E303. The 5 at the end of the formula means you want the data in the 5th column. Cell Ref G4 contains the number of the occurance to the look for as a number. Cell ref H4 has what to look up.


    =INDEX($A$1:$E$303,SMALL(IF($A$1:$A$303=H4,ROW($A$1:$A$303)-ROW($A$1)+1,ROW($A$303)+1),G4),5)


    This is an array formula so you need to Hold down three keys Ctrl + Shift and Enter after entering it in a cell. This will put {} brackets arounf the formula to indicate it is an array.


    As I said it may be too complicated but it will allow you find more than the first occurance.


    HTH

    Welcome to the forum


    Can't you import all your data first andf them once its done format the column as Wrap text and then use AUtofit rows to make sure its all visible


    something like


    Columns("C:C").Select
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Cells.Select
    Selection.RowHeight = 12
    Cells.EntireRow.AutoFit


    This was just recorded.


    HTH

    To all the ozgridders and their families around the world I hope you have a Happy and Safe Christmas.


    Thanks for all your help during the year.


    (Its going to be about 35 degrees celsius in Perth for Xmas day this year. A great day for the pool or beach.)


    Best wishes
    Neale

    Yes have looked at xlhelp.


    Unfortunately xlhelp doesn't always tell you the downside of excel's features - and it rarely quantifies them.

    Hi All


    Anyone had any problems / issues when using Tools > Track Changes.


    I'm about to use it on some large workbooks and I don't want their file sizes to blow out.


    I'm using XL2000 and likely to change to XP soon.


    TIA

    Hi All


    Does anyone have some links or experiences with having two versions of Excel installed - specifically XL2000 and XLXP?


    I've got 2000 installed and wish to add XP rather than upgrade - they are both full versions.


    Any suggestions (what to look out for) appreciated.


    Thanks

    Welcome to the forum


    Unfortunately I think there is a limitation to the OFFSET function as far as unopen workbook s are concerned.


    This formula may help depending on how big your data is.


    Cell G1 contains your first column value, cell H1 contains the value to look up.


    The table is in A1:E3. the formula look like this


    =IF(VLOOKUP(G1,A1:E3,2,0)=H1,1,IF(VLOOKUP(G1,A1:E3,3,0)=H1,2,IF(VLOOKUP(G1,A1:E3,4,0)=H1,3,IF(VLOOKUP(G1,A1:E3,5,0)=H1,4,0))))


    Should work on a closed book.


    HTH

    Welcome to the forum.


    What you could do is to create a list of number you want ot enter into your input range.


    Then something like this will do it. (select the range of input first before running the macro). Assumes A1 is input cell and B1 is output - change to suit.


    hth


    Sub CheckOutput()


    Dim c
    Dim StartValue


    StartValue = Range("a1").Value


    For Each c In Selection


    Range("A1") = c 'put value in input cell
    c.Offset(0, 1) = Range("b1").Value 'extract value form output


    Next c


    Range("a1") = StartValue


    End Sub