Posts by filo65

filo65 · May 26th 2007

Re: Is A Do Loop Efficient

billyj

Code

Sub AVAILABLEBILLETS()
    Dim xlCalc As XlCalculation
    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    xlCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    On Error GoTo CalcBack
    Application.ScreenUpdating = False
    Dim j As Long, i As Long, fillcell As Long
     
    Set sh1 = Sheets("AVAILABLE BILLETS")
    Set sh2 = Sheets("DECK AVAILABLE BILLETS")
    Set sh3 = Sheets("OPS AVAILABLE BILLETS")
     
    sh1.Rows("1:1000").Delete Shift:=xlUp '  delete old data
     
    i = 3
    With sh2
        .Range("A1:G2").Copy sh1.Range("A1")   ' copy  header info
        Do While .Range("F" & i).Value <> ""
            Select Case True
            Case .Range("F" & i) = "ASAP", .Range("F" & i) = "RESEARCH", .Range("F" & i) = "Select me"
                 .Range("A" & i & ":E" & i).Copy sh1.Range("A" & i & ":E" & i)
            Case Else
                 ' do nothing
            End Select
            i = i + 1
        Loop
    End With
     
    With sh3
        .Range("A1:G2").Copy sh1.Range("A" & i + 1) 'copy header info
        j = 3
        Do While .Range("F" & j).Value <> ""
            Select Case True
            Case .Range("F" & j) = "RESEARCH", .Range("F" & j) = "ASAP", .Range("F" & j) = "Select me"
                fillcell = i + j
                .Range("A" & j & ":E" & j).Copy sh1.Range("A" & fillcell & ":E" & fillcell)
            Case Else
                 ' do nothing
            End Select
            j = j + 1
        Loop
    End With
     
     'YOUR CODE
     
    Application.Calculation = xlCalc
     
    Exit Sub
CalcBack:
    Application.Calculation = xlCalc
    Application.ScreenUpdating = True
End Sub

Display More

This code works poerfectly now. The use of True in the Select case staement is to achieve the so call C-type Shortcircuit ( this boost performances )); Excel up to now - maybe the 2007 has chaged - proofs all conditions in a if...then using select case true ( for OR ) or false ( for and ) allows you to break your proofing as soon one TRUE condition is met ( in case of OR ) or a FALSE one ( in case of AND ). BE CAREFULL using it

see: http://www.ozgrid.com/forum/showthread.php?t=41814

HTH

Filippo

filo65 · May 25th 2007

Re: Count Letters From Words

maybe u must use ";" instead of ","

filippo

filo65 · May 25th 2007

Re: Is A Do Loop Efficient

billyj,

I changed a little bit your code, trying to make more efficient your if statements.
it should be much faster now.

Code

Sub AVAILABLEBILLETS()
    Dim xlCalc As XlCalculation
    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    xlCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    On Error GoTo CalcBack
    Application.ScreenUpdating = False
    Dim j As Long, i As Long, fillcell As Long
    
    Set sh1 = Sheets("AVAILABLE BILLETS")
    Set sh2 = Sheets("DECK AVAILABLE BILLETS")
    Set sh3 = Sheets("OPS AVAILABLE BILLETS")
    
    sh1.Rows("1:1000").Delete Shift:=xlUp ' delete old data
    
    i = 3
    With sh2
      .Range("A1:G2").Copy (sh1.Range("A1"))  ' copy header info
      Do While .Range("F" & i).Value <> ""
        Select Case True
          Case .Range("F" & i).Value = "ASAP", .Range("F" & i).Value = "RESEARCH", .Range("F" & i).Value = "Select me"
            sh1.Range("A" & i & ":E" & i) = .Range("A" & i & ":E" & i)
        Case Else
          ' do nothing
        End Select
        i = i + 1
      Loop
    End With
   
    With sh3
      .Range("A1:G2").Copy (sh1.Range("A" & i+1)) 'copy header info
      j = 3
      Do While .Range("F" & j).Value <> ""
                Select Case True
          Case .Range("F" & j).Value = "RESEARCH", .Range("F" & j).Value = "ASAP", .Range("F" & j).Value = "Select me"
fillcell = i + j 
            sh1.Range("A" & fillcell & ":E" & fillcell) = .Range("A" & j & ":E" & j).Value
        Case Else
          ' do nothing
        End Select
        j = j + 1
      Loop
    End With
  
     'YOUR CODE
     
    Application.Calculation = xlCalc
     
    Exit Sub
CalcBack:
    Application.Calculation = xlCalc
    Application.ScreenUpdating = True
End Sub

Display More

filippo

filo65 · May 25th 2007

Re: Deselecting A Range Of Cells

what about:

Code

sub select_first_range()
  Selection(1, 1).Select
end sub

filippo

filo65 · May 25th 2007

Re: Adding A Digit Into Column Of 2 Sheets

So to resume: you have a spreadsheet per workbook?; are the workbooks all open? or stored in the same directory?

filippo

filo65 · May 25th 2007

Re: Double Clicking On Cells Slowing Down Work

can't you play around with Calculation method, setting it to Manual and back to automatic ( if necessary )?

filippo

filo65 · May 25th 2007

Re: Adding A Digit Into Column Of 2 Sheets

Andy,

super solution!

filippo

filo65 · May 25th 2007

Re: Adding A Digit Into Column Of 2 Sheets

assuming you answer yes to both questions, place this code in a module

Code

Sub Add_a_D()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    With ws
      For i = 6 To .Range("B6").End(xlDown).Row Step 1
        .Cells(i, "B") =  "D" & .Cells(i, "B") 
      Next i
    End With
  Next
End Sub

Display More

filippo

filo65 · May 25th 2007

Re: Re-opening A Read-only Workbook

Thank you Zimitry for posting an English version of the Share Workbook form

A german one would have been not a great help.

filippo

filo65 · May 25th 2007

Re: Re-opening A Read-only Workbook

why don't you share the workbook?

filippo

filo65 · May 25th 2007

Re: Dynamic Formula For Reuters Feeds

Robert,

I couldn't find the old way, but you could try this solution

Code

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 2 Then
    Dim findStart&, findEnd&, strFormula$, oldCode$, rng As Range
    Set rng = Range(Target.Offset(0, 1).Address & ":" & Target.Offset(0, 10).Address)
    strFormula = Target.Offset(0, 1).Formula
    findStart = InStr(1, strFormula, "!'") + 2
    findEnd& = InStr(1, strFormula, ",") - findStart
    oldCode = Mid(strFormula, findStart, findEnd)
    
    rng.Replace What:=oldCode, Replacement:=UCase(Target.Value), LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Set rng = Nothing
  End If
End Sub

Display More

Assumptions:
You enter your WKN ( Bond code ) in column B, Formulas related are in the range C:L.
Please adapt it to your needs, if it helps. Still if you can get PowerPlus, RtGet functions will make your live much easier.

filippo

filo65 · May 24th 2007

Re: Vlookup Referencing Cell With "left" Function

willie447,

you have to put Left(...) in a Value(...). "=LEFT" give you back a string which cannot be compared to a "Long".

filippo

filo65 · May 24th 2007

Re: Application Ontime - Stopping It?

if you could post it we could have a look at it

filippo

filo65 · May 24th 2007

Re: Formula For Pv Factor

Reafidy,

-concerning the round is because the data mgiv provided had only 4 digits
-concerning the formula, it depends: up to one/one and a half years, it's normally used the simple actualization, that is = C / (1 + r% * dt ) ( they are normaly deposit rates calculated normally on a ACT/360 day basis ); above the one year mark ( market convention ) one normally switch to a compound actualization = C * (1 + r% )^t, because you assume the capital to be reinvested at same rate for the time t ( day convention: ACT/ACT, 30/360, etc. ) which actually is not the case, but it's easy and fast to calculate ( basically it assumes a flat yield curve ). Normally in financial markets on the base of deposit, futures and swap rates, one calculate a zero curve ( with the bootstrapping method ) and from there a REAL the discount curve, for each maturity.
having that you can calculate the pv any of your investments.

filippo

filo65 · May 24th 2007

Re: Application Ontime - Stopping It?

if I remember well it should be

Code

on error resume next
application.ontime ""
on error goto 0

the "on error" statement is necessary because stopping the ontime call raise an error ( I believe it's some Excel bug )

filippo

filo65 · May 24th 2007

Re: Sub - Calling/returning Values

Thanks Andy,

just mixed-up two calling conventions

Code

call macro (...,...,...)
'and
 macro ...,...,...

filippo

filo65 · May 24th 2007

Re: Sub - Calling/returning Values

use

Code

call FindDataStart lRow, lColumn

HTH

filippo

filo65 · May 24th 2007

Re: Formula That References A Cell Where Workbook Is A Variable

Adam,

I attach two files; please check "Sheet name"; is it "Sheet 1" or "Sheet1" ?

filippo

filo65 · May 24th 2007

Re: Dynamic Formula For Reuters Feeds

RobertWhite,

If you have a computer provided by REUTERS, it is standard install.

I must have somewhere a way to solve your problem using the old way, but I will be able to check only tomorrow.

filippo

filo65 · May 24th 2007

Re: Formula That References A Cell Where Workbook Is A Variable

Adam,

in cell E11 you have a "=" too much instead of "='[Project A.xls]Sheet 1'!" it should be "
'[Project A.xls]Sheet 1'!"

filippo

Posts by filo65

Is A Do Loop Efficient

Count Letters From Words

Is A Do Loop Efficient

Deselecting A Range Of Cells

Adding A Digit Into Column Of 2 Sheets

Double Clicking On Cells Slowing Down Work

Adding A Digit Into Column Of 2 Sheets

Adding A Digit Into Column Of 2 Sheets

Re-opening A Read-only Workbook

Re-opening A Read-only Workbook

Dynamic Formula For Reuters Feeds

Vlookup Referencing Cell With "left" Function

Application Ontime - Stopping It?

Formula For Pv Factor

Application Ontime - Stopping It?

Sub Calling & Returning Values

Sub Calling & Returning Values

Formula That References A Cell Where Workbook Is A Variable

Dynamic Formula For Reuters Feeds

Formula That References A Cell Where Workbook Is A Variable