Posts by the Okk

    Re: Return A Value Without Sorting The Array



    =VLOOKUP()

    Re: Get Data From Last Filled Cell In A Column


    Quote from westic

    the OKK,
    In use the "end User" never sees any of this data and does not have access to this sheet. I need to get the data from the last row of the first 9 columns into a separate worksheet which "if a certain cell is filled" triggers a macro which automatically populates a proforma document and prints it out.
    In effect the "entry" of data via the UserForm , Adds data to Sheet1, which in turn generates the "Job Number", this data is then copied to a specific area which then causes an "Auto-Completed" Hard copy to be printed with no interaction from the user other than the initial data entry.


    I don't see the problem - you always can get any data from this table by macros. Macros can set autofilter = "Comm" and then get visible rows. - I think it's even simplier.
    Anyway, if you want to print "comm" elsewhere, add this line to my code :

    Code
    If rngLastCell.Offset(, 5) = "comm" Then _
    Range("range where you want to copy") = rngLastCell.Resize(, 9)


    (before this line: Call UserForm_Initialize )

    Re: Get Data From Last Filled Cell In A Column


    Quote from westic

    On the same "Add Data" click event I would like the data (including the Job No.) from the row I have just entered to be copied to the first row of the first 9 (A-I) columns of a new worksheet if the "Type" is Comm:
    Or copy the data to the second 9 (J-R) columns of the same new worksheet if the "Type" is NOT Comm:


    Try this code (it's optimised + autofilter added, so you can filter your table by 'Type')

    Re: Lock Cell After Entry


    Quote from ecow

    Hello,
    What I would like to do is have it so that after the user enters his/her name, that cell is locked and cannot be cleared.


    It's simple. You can just Lock all non-empty cells each time.

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Protect password:="password",  userinterfaceonly:=True
    Target.SpecialCells(xlCellTypeConstants).Locked = True
    End Sub

    Re: Delete Rows Where Cell Value Equals Zero


    Quote from bbromley

    Thanks for the reply OKK, but in this case I definately needed a macro with a loop. Firstly it needs to be an automated process,and secondly the table is comprised of various subtables with headings and subtotal and so on. Writing the VBA to sort and then use filters with all the sub-headings and sub-totals getting mixed up seems like a lot more work than simply using a loop function to remove what I don't need. Thanks again.


    If you use Filter, there's no need in any sorting. There will be no 'mix' because Autofilter just hides strings without any reorder. So, if you chooses 0 in autofilter and delete visible rows you'll get the same result as if you uses loop.
    Something like that:

    Code
    Sub delZeros()
        Columns(2).AutoFilter Field:=1, Criteria1:=0
        ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        Columns(2).AutoFilter
    End Sub

    Re: Delete Rows Where Cell Value Equals Zero


    Quote from bbromley

    No, can't sort it. It definately needs a macro.


    Well, if you can't sort it, you always can Filter (or simply autofilter and choose 0) it. Of course, you can do the same by macros. In this case there's no need in any loop.

    Re: Conditional Formating To Find Largest Numbers In A Selection


    There's no need in macro. Use LARGE function in condition formating. For example:
    Value | greater then or equal | =LARGE(A1:A100;5)


    If you want use 5 different colors then you must use 5 conditional formating like:
    Value | equal | =LARGE(A1:A100;5) - 1-st color
    Value | equal | =LARGE(A1:A100;4) - 2-nd color
    ...
    And so on.

    Re: Detect If Cell Is In 'editing' Mode


    Quote from mkgan

    Example:


    I call the following function. and this cause an error when the cell is in 'editing' mode:


    appExcel.DisplayAlerts = False


    And what must happen if error occurs? Can you use

    Code
    On Error Goto errHndl
    'some code
    errHndl:
    'code if error


    or

    Code
    On Error Resume Next
    appExcel.DisplayAlerts = False
    If Err then
    'code if error
    End if


    And most interesting question: How you execute macros, when cell is in 'editing mode'. Where this macros is? Is it in another Workbook?

    Re: Detect If Cell Is In 'editing' Mode


    Quote from mkgan

    Hi,
    pushing the button will give no different. but there are difference when you calling/changing the excel application's properties, etc.


    So any clue please!


    I'll try to explain - when macros is running, there are no cells in 'editing mode'. - There's nothing to detect.
    Can you give an example?

    Re: Detect If Cell Is In 'editing' Mode


    Quote from mkgan

    Hi,
    I'm doing the vba programming for excel where I added a button to the excel, and when user click on the button, it will trigger my function.


    My question is: if currently there is a cell been focused, how can I know whether that is in the 'editing mode' (ie, focus on that cell and double click on that) or merely 'focus' on that cell? Any of the excel object property for this?


    Thanks in advance.


    Pushing the Button will give NO effect if one of cells is in 'editing mode'.

    Re: Dynamic Name Range


    Quote from swst

    Is there a max number of lines for a dynamic named range or is my formula wrong in any way


    I think COUNTA(DataSheet!$A:$A) = 10979. - There are blank cells in column A.

    Re: Type Mismatch 13 Entering A Date


    oh, sorry. I've missed one more string.

    Code
    Dim NumberEntry As Date
    On Error Resume Next
    NumberEntry = InputBox("Enter Start Date", "Start Date", "dd/mm/yyyy")
    Do While Err
        MsgBox "The FROM date is not a valid date."
        Err = 0
        NumberEntry = InputBox("Enter Start Date", "Start Date", "dd/mm/yyyy")
         'NumberEntry = InputBox("Please enter the date (dd/mm/yyyy) FROM to work with.")
    Loop
    End Sub

    Re: Type Mismatch 13 Entering A Date


    Code
    Dim NumberEntry As Date
        On Error Resume Next
    NumberEntry = InputBox("Enter Start Date", "Start Date", "dd/mm/yyyy")
        Do While Err
                    MsgBox "The FROM date is not a valid date."
                    NumberEntry = InputBox("Enter Start Date", "Start Date", "dd/mm/yyyy")
                    'NumberEntry = InputBox("Please enter the date (dd/mm/yyyy) FROM to work with.")
        Loop