Posts by jetted

    Hi


    I was wondering why the following code does not produce an array. The message box display the proper format, but when I use UBound(myarray) it shows 0 it should read 4.


    Any suggestion...


    Re: Delete Row If Cell Text Matches Known String


    Hi


    I would try this code

    Re: Cell Find


    Thanks Sicarri your suggestion worked, but I am puzzle, because I did not provide the full code originally but if I would have you have seen this


    Code
    1. date_format = Format(dateinf, "mm/dd/yyyy")
    2. Cells.Find(What:=date_format, After:=ActiveCell, LookIn:=xlFormulas, _
    3. LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    4. MatchCase:=False).Activate


    and you would have seen this in my spreadsheet (see picture)

    Files

    • sample1.zip

      (4.67 kB, downloaded 18 times, last: )

    Hi


    I having trouble with a find function see attached sample. If I do it manually it will find it, but if try in a record macro i received a variable not set error.


    I would appreciate any input
    Thanks
    Denis


    Code
    1. Sub Macro3()
    2. Sheets("Data-Donnees").Select
    3. Range("A1").Select
    4. Cells.Find(What:="04/07/2008", After:=ActiveCell, LookIn:=xlFormulas, _
    5. LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    6. MatchCase:=False).Activate
    7. End Sub

    Files

    • sample.zip

      (16.89 kB, downloaded 19 times, last: )

    Re: Error 1004 Error Handler


    Quote from Reafidy

    Please read this http://www.ozgrid.com/forum/showthread.php?t=74932


    Try this:

    Code
    1. If Err.Number = 1004 Then
    2. Resume line1:
    3. End If


    a better way maybe to forget the error handler and use dir to check if the file exists. ie

    Code
    1. If Dir("c:\" & openfile) <> "" Then
    2. 'open file
    3. else
    4. 'do nothing
    5. end if


    Thanks your solution work perfectly resume line1:
    Thanks for the fast response very much appreciated
    Thanks for the suggestion (checking if the file exist), to be honest that suggestion never cross my mind

    I need help on this one


    I need to open 9 files in different worksheet. If the files is not present i want to get to the next available one. I don't understant the following code will work once, but not 2 times in arrow. The error handler routine works only once and then if the file is not present I will get a error 1004 (on this line Workbooks.Open Filename:="c:\" & openfile). I know that the file is not present but the error gets trap the first time and not the second time.


    I what am I missing? Any help would appreciated.


    Re: Referencing A Field


    Code
    1. Sub test()
    2. 'find last cell used in column A
    3. rowcount = [A:IV]. Find("*", [A:IV].Item(1, 1), , , _
    4. xlByRows, xlPrevious).Row
    5. 'go to the last cell
    6. Range("A" & rowcount).Select
    7. 'take the address of the last cell
    8. ende = ActiveCell.Address(rowabsolute, columnabsolute)
    9. 'from there you can use this reference (ende as the last range in column A)
    10. End Sub

    Re: Referencing A Field


    Would this help


    Code
    1. Sub test()
    2. rowcount = [A:IV].Find("*", [A:IV].Item(1, 1), , , _
    3. xlByRows, xlPrevious).Row
    4. Range("A" & rowcount).Select
    5. ende = ActiveCell.Address(rowabsolute, columnabsolute)
    6. End Sub

    Hi


    I have been trying to remove or disable the message on update to other worksheet " To update all linked click yes......"


    I have try the following unsuccessfully


    Code
    1. Sub auto_open()
    2. Application.AskToUpdateLinks = False
    3. End Sub


    On the menu bar choose Edit ---> Links Can not choose manual, as the option is grey out (disable)


    I would appreciate any help on this one, I would prefer a vba solution, but I am open to anything to get rid of this message


    Thanks in advance
    Denis

    Re: Force A Date Format


    Hi Roy


    I thought the input could be changed with Worksheet_Change event. It does if the number are higher then 12, however the format does not change if the number are lower then 12. It appears that excel, will not make a difference if the number are lower then 12 (format wise).


    Thanks
    Denis

    Re: Force A Date Format


    Hi Roy


    The problem is not with dates, it is the format (US, Canada and UK) have different date format. The purpose is to convert date to a standar regardless how it was entered.


    Thanks
    Denis

    Re: If Statement In Macro?


    Try this

    Re: Mouse Functionality


    did you try to turn them back on and we the user leave your application

    Code
    1. sub auto_close
    2. 'Allow Right-Click on WorkSheet Tab
    3. Application.CommandBars("PLY").Enabled = True
    4. 'Allow Right-Click on WorkSheet Cells
    5. Application.CommandBars("CELL").Enabled = True
    6. end sub

    Re: Toggle Conditional Formatting


    Would this help