Posts by filo65

filo65 · May 22nd 2007

Re: Formula For Pv Factor

enter the following formula in J7 and copy down:

J7: =ROUND(1/(1+$J$4)^$G7,4)

filippo

filo65 · May 21st 2007

Re: Vlookup On Multiple Tabs

mccloudm,

why instead of importing the table, don't you run a query in Excel for the value in E5?
let do the work to the database. It's faster, takes less memory and you have no row limitations

filippo

filo65 · May 21st 2007

Re: Self Destruct Workbook After Not Logging In Within 30 Days

Malathay,

Sicarii is right! In addition if your work is a company program you should have it well documented and accessible for compliance if containing or elaborating sensible data.

A solution could be to E-mailed you the code and print it out with the incoming date and hour or send it to you via post ( keep the envelope closed! you have the post stamp as proof ) to be able, in case of "piracy", to dismask the thief.

A second possibility could be to copy the program on a cd/dvd with a (sub)directory system ( i.e ..\myPrgA\20070520,..\myPrgA\20070521 ) to keep track of the changes; via WinExplorer you can proof anyway when the file has been created.

And finally, they can steal you everything but not your intelligence and creativity.

filippo

filo65 · May 21st 2007

Re: Dynamic Array Seems Invalid

Bas,

first of all welcome to the forum.

the code you provide is missing of some variable initialization
where are the initial values for:

Code

TBV ??
TIV2 ??
ClusterMembers() ??

what do you want to achieve with this line?

Code

ClusterValues() = Array(TStV4)

you will assign the array to the first element of the array. Is what you want

can you give some more details and evtl. post a small workbook?

filippo

filo65 · May 18th 2007

Re: Convert A String To A "real" String (vba)

try

Code

Sub test()
Application.run "'Book No.1.xls'!my_macro"
End Sub

filippo

filo65 · May 18th 2007

Re: Double Loop To Copy And Paste

OK, let's try this one

Code

Sub myloop_new()
  Dim UltFila As Long, i As Long, k As Long, imax&, tmp&, j&
  Dim myRng As Range, myArray As Variant
  Dim refRow&
  UltFila = Range("A65536").End(xlUp).Row
  Set myRng = Range("B1:B" & UltFila)
  myArray = Array(4102, 4104, 4106, 4108, 4147, 4110, 4153, 4258)
  k = 2:  j = 0
  For i = 0 To UBound(myArray, 1) Step 1
    tmp = myArray(j)
    refRow = myRng.Find(what:=tmp).Row
    imax = Application.WorksheetFunction.CountIf(myRng, "=" & tmp)
    If imax >= 16 Then
      Select Case tmp
        Case 4102, 4104, 4106, 4108, 4147, 4110, 4153, 4258
          Range(Cells(refRow, 3), Cells(refRow + 15, 6)).Copy Sheets("Final").Cells(k, 1)
          k = k + 19:  j = j + 1
          If j > UBound(myArray, 1) Then Exit For
        Case Else
        'do nothing
      End Select
    End If
  Next i
End Sub

Display More

filippo

filo65 · May 18th 2007

Re: Double Loop To Copy And Paste

I think I got it

Code

Sub myloop()
  Dim UltFila As Long, i As Long, k As Long, imax&
  Dim myRng As Range
  UltFila = Range("A65536").End(xlUp).Row
  Set myRng = Range("B2:B" & UltFila)
  k = 2
  For i = 2 To UltFila Step 1
    imax = Application.WorksheetFunction.CountIf(myRng, "=" & Cells(i, 2))
    Select Case Cells(i, 2)
      Case 4102, 4104, 4106, 4108, 4147, 4110, 4258, 4153
        Range(Cells(i, 3), Cells(i + 15, 6)).Copy Sheets("Final").Cells(k, 1)
        k = k + 19
        i = i + imax - 1
      Case Else
      'do nothing
    End Select
  Next i
End Sub

Display More

filippo

filo65 · May 18th 2007

Re: Double Loop To Copy And Paste

albatros81,

in your file portfolio 4102 has 21 entries, 4104 has 24, etc. Is there a special reason to choose the first 16? What exactly do you mean with

Quote

step = 19

?

what exactly would you like to do? is it the total exposure for issuer?

filippo

filo65 · May 18th 2007

Re: Weird Behavior Of Debug Statements

a possibility could be:

in the first case

Code

For Each r1 In Rng1
  r1.Interior.ColorIndex = 6
  For Each r2 In Rng2
' this condition is true: you exit the first loop assigning a value to r2 
    If r2.Value = r1.Value Then
      r1.Interior.ColorIndex = xlColorIndexNone
      Exit For
    End If
  Next r2
Next r1
'r1 is out of range

Display More

Code

For Each r2 In Rng2
  r2.Interior.ColorIndex = 6
  For Each r1 In Rng1
' the condition is always false
    If r2.Value = r1.Value Then
      r2.Interior.ColorIndex = xlColorIndexNone
      Exit For
    End If
  Next r1
Next r2
'both "r"s are out of range

Display More

filippo

filo65 · May 18th 2007

Re: Double Loop To Copy And Paste

just to avoid misunderstandings, could you psot a scracth of your workbook?

filippo

filo65 · May 18th 2007

Re: Double Loop To Copy And Paste

try

Code

Sub myloop()
  Dim UltFila As Long, i As Long, k As Long
  UltFila = Range("A65536").End(xlUp).Row
  k = 2
  For i = 2 To UltFila Step 1
    Select Case Cells(i, 2)
      Case 4102, 4104, 4106, 4108, 4147, 4110, 4258, 4153
        Range(Cells(i, 3), Cells(i + 15, 6)).Copy Sheets("Final").Cells(k, 1)
      Case Else
      'do nothing
    End Select
    k = k + 19
  Next i
End Sub

Display More

filippo

filo65 · May 18th 2007

Re: Linked Data With Variable File Name

frateg8r,

welcome to the forum.

you could get away from the "linked" solution and copy the values your looking for. Doing this you solve two problems. The first is the one you have explained, the second is the updating time when openeing your source workbooks.

filippo

filo65 · May 18th 2007

Re: Array From A Range And Then Returning It From Function

HI Andy,

wrong tags. this time I cannot edit for you!!: D

filippo

filo65 · May 18th 2007

Re: Array From A Range And Then Returning It From Function

mechanik,

welcome to the forum.

you probably miss the array definition "()" for correlrange

Code

Dim correlrange [B][COLOR="Red"]()[/COLOR][/B]As Range

filippo

filo65 · May 18th 2007

Re: Extract Dynamic Data From Closed Workbook

I suggest you post two workbooks, the target and the source one with some consistent data. In the previous attachment it was not included what your were looking after

filippo

filo65 · May 16th 2007

Re: Extract Dynamic Data From Closed Workbook

thunder,

instead of

Quote

selectedcells = Range(begArea.Address & ":" & endArea.Address).Select.Copy

try

Code

selectedcells = Range(begArea.Address & ":" & endArea.Address).Copy

where it doesn't work?, do you get an error message? which one?

filippo

filo65 · May 15th 2007

Re: Countif Vba Arrays

try

Code

Sub mem_countif()
  Dim a(), i&, iCnt&
  a = Array(1, 2, 3, 4, 5)
  iCnt = 0
  For i = 0 To UBound(a, 1)
    iCnt = iCnt - (a(i) > 3)
  Next i
  MsgBox iCnt
End Sub

filippo

filo65 · May 15th 2007

Re: Declare Constant

but on vba help!

filo65 · May 15th 2007

Re: Lowering Memory Usage

cbjorgol,

it is difficulkt to give any advise, without knowing which data contains and what how your workbook is structured. A possible solution could be to move to a database and use excel as a Front-End.

Can't you post a reduce version with the major functionality in it?

filippo

filo65 · May 14th 2007

Re: How can I Prevent The User From Scrolling In A Sheet?

maybe hiding extra columns and rows and protecting ????

filippo

Posts by filo65

Formula For Pv Factor

Vlookup On Multiple Tabs

Self Destruct Workbook After Not Logging In Within 30 Days

Dynamic Array Seems Invalid

Convert A String To A "real" String (vba)

Double Loop To Copy And Paste

Double Loop To Copy And Paste

Double Loop To Copy And Paste

Debug.Print Causes Crash

Double Loop To Copy And Paste

Double Loop To Copy And Paste

Link With Variable File Name

Return Matrix as Range From Function

Return Matrix as Range From Function

Extract Dynamic Data From Closed Workbook

Extract Dynamic Data From Closed Workbook

Countif Vba Arrays

Declare Constant

Lowering Memory Usage

How can I Prevent The User From Scrolling In A Sheet?