Posts by jetted

  • Fill Array From Dynamic & Variable Range

    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...


  • Delete Row If Cell Text Matches Known String

    Re: Delete Row If Cell Text Matches Known String


    Hi


    I would try this code

  • Find Non US Dates In Column

    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
    date_format = Format(dateinf, "mm/dd/yyyy")
Cells.Find(What:=date_format, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False).Activate


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

  • Find Non US Dates In Column

    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
    Sub Macro3()


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

  • Open Multiple Files, Skip Non Existent

    Re: Error 1004 Error Handler


    Quote from Reafidy

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


    Try this:

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


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

    Code
    If Dir("c:\" & openfile) <> "" Then 
'open file
else
'do nothing
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

  • Open Multiple Files, Skip Non Existent

    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.


  • Last Used Cell in Column

    Re: Referencing A Field


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

  • Last Used Cell in Column

    Re: Referencing A Field


    Would this help


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

  • Updatelink

    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
    Sub auto_open()
      Application.AskToUpdateLinks = False
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

  • Force A Date Format

    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

  • Force A Date Format

    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

  • If Statement In Macro?

    Re: If Statement In Macro?


    Try this

  • Mouse Functionality

    Re: Mouse Functionality


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

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

  • Toggle Conditional Formatting

    Re: Toggle Conditional Formatting


    Would this help