Posts by filo65

    Re: Search Records With Combination Of More Than 2 Values


    here one additional idea: if it looks like to be an address list why don't you import them in Outlook? it has plenty of funcionalities for managing addresses, meetings, journals and so on; at the end of day, it really depends on what you want to do with those records.


    Re: Dynamic Formula For Reuters Feeds


    welcome to the forum


    =REUTER|IDN!'DE113504=,PRIM ACT 1,1'

    is an old way to retrieve data from REUTERS. There is a new way even faster that allow full flexibility. Not being in the office I cannot tell exactly the name; it begins with Get... and it is roughly like:

    B2=RtGet("IDN",$A2,B$1) where in A2: DE113504= and in B1: PRIM ACT



    Re: Sorry for the Inconvenience Excel Needs to Shutdown. When Saving


    If I understand it right from your code snippet I would suggest to proceed as followand even if takes some extra work I think is worth it;

    1) split your code when possible ( when you perform i.e. a loop ) in sub-Subroutines - max 100-150 lines; it will maybe slow a little bit your program but it will allow you to overview better the sequence, and to see if you are propertly freeing memory - even if excel as a garbage collector I set to "Nothing" Object out of scope and Erase arrays not anymore in use.
    2) your functions/routines placed in different modules ( called with proper names ) sholud be called with modulename.function/routine ( for routine with the call key ), just for readability pourposes.
    3) backup the code regularly to a WordPad document, just in case and drink a couple of beers ::D. It's help anyway.


    Re: Search Records With Combination Of More Than 2 Values


    not really clear what you have and what you do.


    each records contain 30 rows of info i have 3000 over records and they are all in one sheet

    how are they placed on the sheet ( Excel2003 has 65536 rows and >3000 by 30 makes at least >90,000 )?

    Two things:

    1) You should keep a record per row ( Excel2003 has 256 Columns! )
    2) You could use Access as BackEnd and Excel as FrontEnd to reduce evtl. the size of your file

    To get more help you should provide some more infos as i.e from where the records come from, in which format etc.


    Re: Left & Right Formula To Show Number Not/as Well


    I don't see exactly what you mean.

    I entered in d9 the following formula ( it is written in German but shouldn't be any problem to translate in English :(

    ( I provide a first translation - hopfully is the right one; BTW separators could be "," instead of ";" )

    it looks to me to cover all your cases. Or am I missing something?


    Re: Lookup Type Function Returning Multiple Values


    welcome to the forum. Copy this formula in a VBA-Module and enter it in C2 and copy down
    Ref is the corresponding A cell and rng is the column E


    Re: Convert Variant To Typed Array

    you maybe something like this can help you:


    Re: Speed Check On Returning File Path Strings

    a second change is speeding even more Andy's suggestion:

    instead of

    r = Cells(Rows.Count, 1).End(xlUp).Row + 1 
    Cells(r, 1).Value = SourceFolder.Path


    Dim rowREF& ' as Module Variable
    Dim FSO    As Scripting.FileSystemObject 
    rowREF = rowREF + 1
    Cells(rowREF, 1).Value = SourceFolder.Path 

    I took me 99 sec for ca 1750 folders/sub with Andy's code, but 72 with the extra change


    Re: Find Start Of Data


    Your code works great and I got it to work. Thanks

    Worst case scenario:
    you use Excel 2007 and your range is row 1 with some zeros and some columns away. :confused:
    Prepare some sandwiches and a big bottle of Coke/Beer::D


    Re: Find Start Of Data


    it's working fine for me ( evtl catch-up for empty sheets! )
    Anyway here is a modified version

    Basically you use the Edit->GoTo->Special->LastCell functionality, select the address of the current region without absolute reference (false, false ) and store it in a string. Make a search for ":" (here if your sheet is empty raise an error ) and get the left part of it.


    Re: Find Start Of Data

    as a second idea:

    sub findfirstCell()
      Dim strRng$, fstCell$, pos&
      strRng = Range("a1").SpecialCells(xlCellTypeLastCell).CurrentRegion.Address(False, False)
      pos = WorksheetFunction.Search(":", strRng, 1) - 1
      fstCell = Left(strRng, pos)
    end sub


    Re: Return Last Friday From A Given Month

    try as well

    Function lstFriday(d As Date) As Date
      Dim wk&
      lstFriday = DateSerial(Year(d), Month(d) + 1, 0)
      wk = Weekday(lstFriday)
      If Not wk = 6 Then lstFriday = lstFriday - 1 + (wk <> 7)
    End Function


    Re: Finding The Start Of Data

    your are not giving enough infos.

    has the range any name? has always the same header? there are several ranges on the sheet? could you be a little more precise?