Posts by Kieran

    Re: Scrambling Data In A Column

    In column B (or other vacant adajcent column) insert the formula =rand() .

    Then copy th formula down for the lenght of column A.
    Then sort by the new column of random numbers, and you will have a randomly sorted list in Column A.

    Re: MsgBox popping up on delete

    It seems to me that the code is searching for a null/empty value after you delete a cell.
    The result of the find will then be the reult of looking for 'nothing' in wSht.Range("Data").

    Try changing If vResult <> "Y" Then... to
    If vResult <> "Y" and vResult is not nothing Then ...

    Untested, so I hope it helps

    Re: Sounds in 2002


    I should have been a bit more explicit.

    For any button 'CommandButton 1' the following code will cause a beep.

    Private Sub CommandButton1_Click()
    End Sub

    Basically the single VBA line of


    does the work, you can include it anywhere in your code for it to work.

    Re: Array value seems to blow up to infinity possibly a memory overflow issue?

    U() defines an array, it can be of type Integer, or Long, or Double, or String or Variant.
    Defining it as a string may cause the formula to have problems. Try Long or Double or Variant. (Variant will accept anything, but is most expensive on memory in comparison).

    Try running the process with a debug statement like
    Debug.Print U(x, y); (U(x, y + 1) + U(x, y - 1) + U(x - 1, y) + U(x + 1, y)) / 4# ; x ; y

    You will see the values listed int the immediate pane in the VB editor.

    Hope it helps

    Re: Array value seems to blow up to infinity possibly a memory overflow issue?


    Is there any reason why U() is defined as a string?

    I am think thing that U(x, y) = (U(x, y + 1) + U(x, y - 1) + U(x - 1, y) + U(x + 1, y)) / 4# may never be true if there is a rounding difference.

    Try changing it to
    ABS(U(x, y) - (U(x, y + 1) + U(x, y - 1) + U(x - 1, y) + U(x + 1, y)) / 4#) = some arbitrary tolerance value

    Re: Loop through folder and print all files in folder


    I open the folder in explorer, select all the files and then choose print from the right click menu of the mouse.
    Note the pritn option only appears when all the files selected support printing form this pop-up menu. If the option does not appear, you have a file selected that does not have a default print method.


    Re: e-mail for every people only one row

    The code below is copied from the site and I think will do what you want.

    Re: check box and change date


    It is possible, but why nbot set up a recurring task in Outlok instead.
    That will achieve the same thing, and provide a much better reminder mechanism.

    Re: Dynamic Array: Multidimensional

    try ...

    Public Sub realScen()

    Dim intPrice As Double
    Dim EBITDA As Double
    Dim ROR As Double
    Dim Price As Double
    Dim newprice As Double
    Dim ArrOutput(13,2) ' resize if the 'for' loop changes

    intPrice = Sheets(1).Range("Price").Value
    Price = intPrice

    Dim j As Integer
    j = 1
    For i = 0.875 To 1.15 Step 0.02
    newprice = Price * i

    Sheets(1).Range("Price").Value = newprice
    EBITDA = Sheets(1).Range("EBITDA").Value
    ROR = Sheets(1).Range("ROR").Value

    Sheets(2).Cells(10 + j, 1).Value = newprice
    ArrOutput(j-1,0) = newprice
    Sheets(2).Cells(10 + j, 2).Value = EBITDA
    ArrOutput(j-1, 1) = newprice
    Sheets(2).Cells(10 + j, 3).Value = ROR
    ArrOutput(j-1, 2) = newprice
    j = j + 1

    Next i
    'ArrOutput now has the values stored, locally to the procedure.
    ' loop through the array to access them or
    ' set a range of the same sixe as the array = ArrOutput() to populate the range
    ' with the values.
    End Sub

    Re: skip a step

    Why not use

    For tiltAngle = 0 To 5*(Pi / 12) Step Pi / 12

    You will still get to the same steps, just the end value is 5/12s of PI, not 6/12's

    Re: Workbook closing

    Without a name - how do you identify it?

    You could use Workbooks(index), but means that you must know the index number of the workbook, not it's name. Is that the case?

    Re: Workbook closing

    Try Activeworkbook.close

    If you do not want to save any cahnges or have any intervening prompts then use

    Activeworkbook.close SaveChanges:=False

    Re: Valid Function - better idea?


    I received an error if I have the sheetname in mixed case, and the macro has it in upper case.

    Try changing
    With Sheets("DATA " & i)
    With Sheets("Data " & i)

    Re: Multipage OptionButton Control

    If you make any selection on the first page, you should disable the options on teh second page and vice versa.

    This si what option button group do by default when enclosed in a group frame. Moving some options to the second page removed thsi functionality so yu will need to code a change event for this.