Posts by Neale

    Re: Sorting Data


    Excel 2007 + 2010 will allow you to sort by colour. You could then put a sequence number in the coloured cells to represent the sort order you require and then resort using that number.


    Ctrl + Enter allows you to enter the same number in the selected region.


    If you are in Excel 2003


    The colour numbers (255,10498160 and 65535) used in the macro will need to be amended to match the colours numbers you have used


    Warning: This macro assumes No Entries in column B where the colour is. If there are entries it will need to be amended to put the number in another column.


    This macro will put a number in the cell based on the colour. Select the range in Column B and run the macro.


    Sub Add_Number_To_Colour()


    Dim c


    For Each c In Selection

    Select Case c.Interior.Color

    Case 255 'red

    c.Value = 2

    Case 10498160 'purple

    c.Value = 1

    Case 65535 'yellow

    c.Value = 3

    Case Else 'other colours

    c.Value = 4

    End Select


    Next c


    End Sub

    Re: Advanced use of Excel's comments


    Looks like a macro solution to me when using comments.


    Not sure if using photos in comments is the best way to handle this.


    You can put a photo in a cell and use range names and PastePictureLink feature to "lookup" a photo.


    Link below demonstrates a technique using range images and range names for dashboards - you may be able to apply yo your situation.


    http://www.cpaaustralia.com.au…l-intheblack-2011-10.html


    Regards


    Neale

    Re: Extract a set of data to a new worksheet based on conditions


    Check out Advanced Filters - they allow you to used formula in the criteria and you could use MIN and MAX.


    Assume ABC for your columns above


    If the lists is sorted by date and time then this formula will extract the first price for the day in cell E3.


    =INDEX(C:C,MATCH(E3,A:A,0))


    Also array formulas can allow you to extract more complex criteria


    Assume ABC for your columns above


    Max price for the day
    Press Ctrl + Shift + Enter after typing


    =MAX(IF(A:A=E2,C:C))


    E2 contains the date you are looking for


    Change MAX to min for the lowest price of the day


    Hope that helps


    Regards


    Neale

    Re: VBA SQL not updating in all situations


    I figured it out.


    The code uses the "current" folder with the data base name. When you open Excel thru Win Explorer the current folder is My Documents - not the folder of the file - which was assumed to be in the same folder as the database.


    I amended the code and defined the folder using ThisWorkbook.Path plus the DB name - now its all good.

    Re: Problem simplifying a recall look-up over several columns


    Hello


    I think an array is the only answer. This worked - you will need to fix your references as per below for copying


    in yellow cell H86


    =SUM(IF($E$32:$DD$41=F86,OFFSET($E$32:$DD$41,0,1),0))


    You have to enter this using Ctrl + Shift + Enter to enter as an array - this puts { } around


    Regards


    Neale

    Excel 2003 and Access 2003 Windows Xp and WIn 7


    I've recently started using SQL via VBA to update Access data tables.


    My problem is that if the Excel file (with the VBA SQL code) is opened through Windows Explorer (Excel closed) the code won't update Access at all. All the other macros work ok just the SQL code doesn't update Access.


    If the file is opened thru Excel it all works fine - no problems at all. If you have Excel open and then open the file thru Win Explorer it also works ok.


    Is there a setting that Excel changes when opening thru Win Explorer?


    Any help appreciated.


    Regards


    Neale

    Hi all


    The macro below runs no worries on XL2000 and XLXP but slows down to a wet week when it runs on XL2003.


    The macro hides rows in a specific range based on the value of a cell in the range.


    Any ideas why?


    I may have revert to use autofilter to do the hide if I can't find a solution.


    Thanks.


    I'd say you need a macro to do that.


    Excel won't do that dynamically. You'd need a macro run whenever an entry is made in C1 - it would check the existing list and add it if it couldn't find it.


    Alternatively
    You could write formula or use a conditional fomat to tell you whether or not the entry is in the list then have a macro copy it to the list when you hit a button


    hth

    I've heard that Access has its own problems (speed) when being used in a multi user network environment. I don't know if newer versions have solved the problems.


    I think you have to have the full Access program, there is no "viewer" as for powerpoint. Which usually means you have to buy Office Pro.


    There are some add-ins or mods to Excel that make it more network friendly tho I haven't used any personally. I've heard that they can make Excel like a multi dimensional DB.


    HTH

    How about


    Sub TryMe()
    Dim rCell As Range
    Dim rRange As Range


    Set rRange = Range("c1", Range("c65536").End(xlUp))


    For Each rCell In rRange
    If rCell = rCell.Offset(1, -2) Then rCell.Offset(1, -1) = 0
    Next rCell
    End Sub

    Try


    same code with small change - untested


    Sub TryMe()
    Dim rCell As Range
    Dim rRange As Range

    Set rRange = Range("A1", Range("A65536").End(xlUp))

    For Each rCell In rRange
    If rCell = rCell(0, 3) Then rCell(0, 2) = 0
    Next rCell
    End Sub

    Maybe something like this (untested)
    HTH


    You might need to identify the textbox more exactly as you are moving between sheets.


    Private Sub CommandButton1_Click()
    Dim c


    for each c in worksheets("form").range ("A1:A112")


    If c.Value = TextBox1.Text Then
    MsgBox "The value you entered is " & Range("A1").Value, vbInformation

    End If


    next c


    End Sub