Posts by neverland

    I was making a form where the user could enter a number; eg. 4.61


    However, depending on the user's background he might enter this as 4,61 (comma) or as 4.61


    In the form this variable comes in as a string. How can I convert this in VBA to a digit if I don't know in advance if the users system uses a . or , as the digit-seperator??


    Any help would be very grateful


    I thought that in access you can actually predefine the type that is entered into the form but I don't believe this is possible in Excel (or at least i could not find something like that)


    thanks for the help


    ---
    <snip by admin. Read the rules you have agreed to or you may be banned>

    I have some keys in a file that are of the form
    WK05, meaning that it refers to the 5th calendar week (of this year). I would like to write a function that gives me the first day of this particular week, in this case it would be 28/1/2008


    How can I do this?


    Any help would be appreciated.

    About 2 hours ago I opened an excel file with external links (links to other excel files).


    It asked me if it should update


    Unfortunately I said yes


    It is still opening as I am typing here.


    The problem is that there is yet another file open in the same excel that has not been saved.


    Is there any way of recovering the other file or interrupting excel?


    I tried control+break but that does not work


    By the way, the other file is actually an addin file with code.


    Kind regards

    Good morning,


    I would like to activate/deactivate some reference through code. I mean the checkboxes you see when you go in VBA-editor to
    Tools -> References


    I see some items in this list and I want to be able to activate them or deactivate them through code.


    Why?
    The references refer to things I can only do on one certain PC wich contains a Bloomberg-link. But the same file is also used on other PC's once the links have been updated. At that time I don't need those references anymore and they give some problems because the reference cannot be found on the other pc


    Thanks in advance

    Re: My function got too long...


    thanks for the help but storing in a column does not really work for me since all code is inside an addin.


    But I just worked around it like this



    or something like that. Anyway, thanks a lot for the help

    Re: My function got too long...


    It looks something like:
    but there more than 2000 cases by now. And all growing. The new cases are generated automatically in the code, so combining them is not an option.



    Perhaps I can stop and add a "otherwise select" and refer to another function and start there all over...

    Hi guys,


    I have discovered something bad within my code.


    I have a function that contains a lot of Cases (Select Case). Each month there are about 20 new cases.


    But now I have run into a problem.


    <COMPILE ERROR: FUNCTION TOO LONG>


    Is there anything that can be done about it? Or do I have to rewrite the function into several functions?


    Many thanks

    Re: Slow performance


    I could post the code but it's rather long.


    The first file contains a lot of information. Some lines have to be skipped, others have to be added and combined into new ones.


    It's a bit hard to explain, but 15 minutes is perfect for me. It used to take 4 hours to maintain the second file by hand. Now it not maintained anymore, but build from scratch.


    Thanks for the help anyway

    Hi guys,


    I have just finished creating a large macro to create a new file I need every two weeks.


    In order to speed up the performance, I added


    ScreenUpdate:= False


    But my macro took more than 5 hours to finish only partly.


    Then I replaced it by


    screenupdate:=True


    and now it takes about 15 minutes...


    I thought this screenupdate was supposed to speed things up? Without it, you can actually see the program switch between two excel-files. Is has to read information from one, and create formules and stuff in the second.


    Any explanations?


    Kind regards


    Peter

    In VBA I want to access the first line following the last filled cell.


    I use Range("A1:I" & Range(A65536).End(xlUp)).Select


    to go to the last filled line. But now I realise I don't know how to go the next line.


    Can anyone help me out?

    Hi,


    I have this file eg


    Hello 3
    test 11
    never 12
    old 10


    And I would like to find the matching value for 11. Vlookup (11, Range,...) does not work since the key needs to be in the first column and nog in the second.


    changing the file is not an option. What can I do?


    Kind regards


    Peter

    Hello,


    I am looking for a VBA-way to check whether a certain string contains the other string.


    Eg:
    string1 = "Hello World"
    string2 = "Hello everybody"


    teststring = "orld"


    String1 contains the teststring, string2 does not.


    Is there an easy way?


    I was playing around with the worksheetfunction find but this gives an error if the teststring is not found


    Kind regards


    Peter

    Hi,


    I have a lot of files that contain links to other files.
    C:\old_excel\file1.xls
    C:\old_excel2\file2.xls
    C:\old_excel3\file3.xls


    Now I moved these files to
    C:\new_excel\file1.xls
    C:\new_excel2\file2.xls
    and I removed file3 (see below)


    The links to file1 and file3 are basic links, eg:

    Code
    [INDENT]='C:\old_excel\[file1.xls]Sheet1'!$A$1[/INDENT]

    while the links to file2 are links to functions, eg:

    Code
    [INDENT]='C:\old_excel2\file2.xls'!function1(10)[/INDENT]


    I would like to have a subroutine that

    • opens all files in a directory
    • scans each sheet for links to file1, file2 and file3 and do the following:
      [INDENT]
    • links to file1 should be redirected to file1 in its new location
    • links to file2 should be redirected to its new location
    • links to file3 should be redirected to the same cell in the sheet "input" in the currentfile (so no external link anymore)[/INDENT]


    Can anybody help me? I was doing this by hand but there are over 600 files...


    I would be very grateful


    Many thanks in advance