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: 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,
QuoteDisplay More'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
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.
Quoteeach 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
Display More
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
Display More
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
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
Display More
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: Time Manipulation:seconds To Normal Time
try to replace ";" with ","
filippo
Re: Left & 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