Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Search For:

Type: Posts; User: Kieran

Page 1 of 3 1 2 3

Search For: Search took 0.01 seconds.

  1. Re: Using RangeName instead of hard coded time in TimeValue Vba Macro

    in the immediate window, this worked for me

    print range(activeworkbook.names("thetime")).Value

    it returned a value of 5.78703703703704E-05

    it did fail however if "00:00:05" was entered as...
  2. Re: Insert and Delete Rows with formula and formating in a table using VBA

    I am wondering why you are using VBA at all.

    Select your data and press Crtl-T. This will create an excel table.
    One of the proprieties of an excel table is that it will allow you to insert a...
  3. Re: Using RangeName instead of hard coded time in TimeValue Vba Macro

    Try playing wit the follwoing

    range(activeworkbook.names("timevalue")).Value to retrieve the value of the cell referred to by the name "timevalue" on the active sheet
  4. Replies
    3
    Views
    105

    Re: Picking up a range in a formula

    My mistake - I forgot to test properly.,
    See the attached workbook with the adjusted lookup range.
  5. Re: Creating a copy of a Worksheet for each Autofilters checked

    An easy way to do this is to create a summary pivot table of your data.
    One of the default behaviors of a pivot table is that when you double click a value in the pivot table, it will open up a new...
  6. Replies
    3
    Views
    105

    Re: Picking up a range in a formula

    Highlight the range H1 to H9 and enter the formula =OFFSET(A2,0,MATCH(G1,$A$1:$E$1,0),9,1) and the press Crtl-Shift-Enter (instead of just enter, to create an array formula) to populate the range...
  7. Re: getting the sum of filtered data without the use of formulas

    Considered using the SUBTOTAL function?

    It can be placed anywhere on the spreadsheet, is automatic, and faster than any custom function.

    Or, if needed, it can be used as the basis for any VBA...
  8. Replies
    1
    Views
    148

    Re: VBA Find not working!

    The following code is UNTESTED, but may help.


    Dim sh As Worksheet
    Dim iLoop As Integer
    For iLoop = 7 To 1719

    ' this is where the invoices are in an excel sheet

    ...
  9. Replies
    3
    Views
    154

    Re: Conditional format using IF formula

    Hi Lanky,

    you say "7 different cells that can contain either a "O" "X" or "N/A"." - I cannot see any reference to the "X' in your formula.
    If any of the referenced cells contain an "X", the...
  10. Replies
    1
    Views
    3,316

    Re: Autofilter Hide Column

    Does the following help?


    Function AutoFilter_Criteria(Header As Range) As String
    'On Error GoTo em
    Dim strCri1 As String, strCri2 As String

    Application.Volatile

    ...
  11. Replies
    8
    Views
    3,808

    Re: Filterable Dynamic Chart Range

    cmorgan,
    Doesn't seem to be a way to do it. Refer to this.
  12. Replies
    8
    Views
    3,808

    Re: Filterable Dynamic Chart Range

    The no calculation in the chart series seems to be the case. But I am not 100% sure.

    You could however have the series refer to another named range that is the result of the calculation.
    See this...
  13. Replies
    4
    Views
    15,621

    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...
  14. Re: String Search Function That Doesn't Return #value Error?

    Kadence,

    Try the following formula.

    =IF(ISERROR(SEARCH("z","word")),0,SEARCH("z","word") )
  15. Replies
    4
    Views
    1,161

    Re: MsgBox popping up on delete

    Brians12,
    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").
    ...
  16. Thread: Sounds in 2002

    by Kieran
    Replies
    3
    Views
    1,123

    Re: Sounds in 2002

    Oops,

    I should have been a bit more explicit.

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


    Private Sub CommandButton1_Click()
    Beep
    End Sub
  17. Thread: Sounds in 2002

    by Kieran
    Replies
    3
    Views
    1,123

    Re: Sounds in 2002

    Try this

    Private Sub CommandButton1_Click()
    Beep
    End Sub
  18. 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...
  19. Re: Array value seems to blow up to infinity possibly a memory overflow issue?

    Rick,

    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...
  20. Re: Loop through folder and print all files in folder

    Ladybug,

    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...
  21. Replies
    3
    Views
    1,543

    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.

    Sub TestFile()
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim cell As Range

    ...
  22. Replies
    3
    Views
    1,543

    Re: e-mail for every people only one row

    Try the tips at this site http://www.rondebruin.nl/sendmail.htm
  23. Replies
    3
    Views
    5,194

    Re: Date/Time Calculations in hours and tenths

    like this ?

    =INT(((A2+B2)-(A1+B1))*24) & " Hours " & ROUND(MOD(((A2+B2)-(A1+B1))*24,1),1) & " tenths"
  24. Replies
    4
    Views
    1,438

    Re: check box and change date

    Lisa,

    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.
  25. Replies
    1
    Views
    3,607

    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...
Results 1 to 25 of 75
Page 1 of 3 1 2 3
porno