Posts by filo65

Re: Dynamic Formula For Reuters Feeds

I don't think has to do with Kobra version but with PowerPlus in Excel. Which version have you available?

filippo

Re: Search Records With Combination Of More Than 2 Values

filthymonk,

have you consider the hypothesis @#5?

filippo

Re: Search Records With Combination Of More Than 2 Values

OK,

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.

filippo

Re: Dynamic Formula For Reuters Feeds

RobertWhite,

welcome to the forum

Quote

=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

HTH

filippo

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

Mix,

Quote

'The macro code uses
Sub Macro1 ()
' over 1000 lines of codes,Loops/Do Until etc
'5-10 worksheets
'3-4 modules with various calculations
Activeworkbook. Save
Macro2
End Sub

Display More

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

filippo

Re: Search Records With Combination Of More Than 2 Values

filthymonk,

not really clear what you have and what you do.

Quote

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.

filippo

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

Barryj,

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

D9:=WENN($C$15<=18;LINKS(D8;WENN(ISTFEHLER(FINDEN("/";D8;1));99;FINDEN("/";D8;1))-1);RECHTS(D8;LÄNGE(D8)-WENN(ISTFEHLER(FINDEN("/";D8;1));0;FINDEN("/";D8;1))))
( I provide a first translation - hopfully is the right one; BTW separators could be "," instead of ";" )
D9:=IF($C$15<=18;LEFT(D8;IF(ISERROR(FIND("/";D8;1));99;FIND("/";D8;1))-1);RIGHT(D8;LEN(D8)-IF(ISERROR(FIND("/";D8;1));0;FIND("/";D8;1))))

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

filippo

Re: Lookup Type Function Returning Multiple Values

c2: =myfunc($A2, $E:$E)

filippo

Re: Lookup Type Function Returning Multiple Values

DaTrusHurtz,

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

Function myfunc(ref$, rng As Range)
  Dim pos&, nbr&, cl&
  cl = rng.Column + 1
  pos = WorksheetFunction.Match(ref, rng, 0)
  nbr = WorksheetFunction.CountIf(rng, ref)
  myfunc = ""
  For i = 0 To nbr - 1 Step 1
    myfunc = myfunc & Cells(pos + i, cl) & "; "
  Next i
  myfunc = Left(myfunc, Len(myfunc) - 2)
End Function

filippo

Re: Convert Variant To Typed Array

you maybe something like this can help you:

Sub ArrayofArrays()
  Dim myArr() As Variant
  Dim anArr#()
  Dim i&, j&, imax&, jmax&
  
  jmax = 3:  imax = 2
  
  ReDim myArr(jmax)
  
  For j = 0 To jmax Step 1
    ReDim anArr(j + imax)
    For i = 0 To UBound(anArr, 1) Step 1
     anArr(i) = i + 100
    Next i
    myArr(j) = anArr
  Next j
  
  For j = 0 To jmax Step 1
    Debug.Print "Array nbr:", j
    For i = 0 To UBound(myArr(j), 1) Step 1
      Debug.Print myArr(j)(i)
    Next i
  Next j
End Sub

filippo

Re: Convert Variant To Typed Array

can you show the structure of the array and the your type?

filippo

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

use

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

filippo

Re: Find Start Of Data

Quote

Zimitry:
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

filippo

Re: Find Start Of Data

joozh,

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

Sub findfirstCell()
  On Error GoTo err_Handle
  Dim strRng$, fstCell$, pos&
  strRng = ActiveSheet.Range("a1").SpecialCells(xlCellTypeLastCell).CurrentRegion.Address(False, False)
  pos = WorksheetFunction.Search(":", strRng, 1) - 1
  fstCell = Left(strRng, pos)
  MsgBox fstCell
err_Exit:
  Exit Sub


err_Handle:
  MsgBox "No range in current spreadsheet", vbCritical, "Missing Range"
  Resume err_Exit
End Sub

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.

filippo

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

filippo

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

filippo

Re: Time Manipulation:seconds To Normal Time

try to replace ";" with ","

filippo

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

barryj,

look at this attachment

filippo

Re: Time Manipulation:seconds To Normal Time

you mean:

b1: =TIME(10,0,IF($A1<=60;$A1;$A1/60)) where in A1 you have your time ?

filippo

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?

filippo