Search:

Type: Posts; User: Yongle; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.08 seconds.

  1. Re: Condition formatting for overnight hours worked

    a date is an integer formatted to look like a date( where 1 = 1 Jan 1900 and every day after that is the next number in sequence)
    time is a decimal formatted to look like time (0.25 = 6am, 0.5 =...
  2. Re: ERRORS in VBA FUnctions "InputBox" & "Select Case"

    InputBox returns a string
    Application.InputBox returns boolean with value = False



    It is difficult to work out what is going wrong without seeing your code - please post the problem code

    ...
  3. Replies
    5
    Views
    99

    Re: Alert - First Monday of the Month

    another one using sykes method

    =IF(AND(DAY(TODAY())<8,WEEKDAY(TODAY())=2),"Project","")
  4. Replies
    5
    Views
    99

    Re: Alert - First Monday of the Month

    try this formula in E2

    =IF(TODAY()=EOMONTH(TODAY(),-1)+(7-WEEKDAY(EOMONTH(TODAY(),-1),2)+1),"Project","")
  5. Thread: Excel Formula

    by Yongle
    Replies
    1
    Views
    46

    Re: Excel Formula

    Sum should be visible in your status bar
    - status bar is the panel immediately below sheet tabs
    - Sum value in status bar is the sum of all selected cells

    If Sum is not visible on the status...
  6. Re: List of all Combinations of Row with 10's of Millions of Combinations

    Try this and run from sheet containing your list of coordinates in column A
    - code below writes to a new sheet after 500000 rows which can be set to any value you want (up to 1048576)


    Public...
  7. Replies
    4
    Views
    97

    Re: Custom number formatting in Excel

    This works

    1 apply custom number format as in post#1
    Range Number Custom Format: [=0]"N/A";[<1]##.0%;###%

    2 add NEW CF rule (to insert + before positive numbers)
    if value > 0
    Number...
  8. Replies
    4
    Views
    97

    Re: Custom number formatting in Excel

    How about this custom format

    [=0]"N/A";[Red][<0]-##.0%;+###%.

    Ignore this - I missed something !!
  9. Replies
    2
    Views
    65

    Re: Find and show the strings

    To return the value as a DATE

    Format C2 as Number Format \ Custom Format \ "mmm yy"
    Formula in C2 copied down :laugh:

    =IFERROR( DATEVALUE( MID(...
  10. Replies
    2
    Views
    53

    Re: Search Multiple Columns

    It would anyone trying to help to see several typical examples of
    - what to enter as search criteria
    - what is the expected outcome for each search

    Based on the 2 rows of data provided in...
  11. Re: Address of the First Five Consecutive rows where the cell value is >=X

    Try this


    Public Function Get_Address(rng As Range, x As Double) As String
    Dim arr, a As Long
    arr = rng

    For a = 1 To UBound(arr) - 4
    If arr(a, 1) >= x And arr(a + 1,...
  12. Re: Automating Hyperlink List from folder - with each document's HEADER information.

    See link below to see how to read document properties

    https://exceloffthegrid.com/reading-document-properties-vba/
  13. Replies
    13
    Views
    443

    Re: Finding the last value less than 24 hours

    The LOGIC is what happens if you go past midnight

    6pm = 0.75
    noon = 0.5

    6pm TODAY (= 0.75 ) is obviously earler than noon TOMORROW (= 0.5 ) BUT 0.75 is bigger than 0.5

    Without...
  14. Re: Worksheet Change event / Calculate cells changed by formulas

    glad you have a solution
    :beerchug:
  15. Re: Worksheet Change event / Calculate cells changed by formulas

    Your trigger range should be modified
    It needs to be the cell(s) being manually changed (instead of the cell(s) containing the formula
    But (naturally) the resultant change is dependant on whatever...
  16. Re: Apply formula to cells without relative cell referencing in VBA

    In that case


    Sub AutoFill()
    Dim LastRow As Long
    LastRow = Worksheets("data").Cells(Rows.Count, "I").End(xlUp).Row

    With Range("N2")
    .Formula = "=SUMIFS(data!J$2:J$"...
  17. Re: Apply formula to cells without relative cell referencing in VBA

    Try this


    "=SUMIFS(data!J$2:J$" & LastRow & ",data!I$2:I$" & LastRow & ",Sheet1!I$2)"

    - not sure if you need the final $ - that depends on what the formula is supposed to be summing!
  18. Replies
    4
    Views
    41

    Re: Make it mark "V" if found mark "X"

    You can place a formula in cell D3 and say
    IF "X" is found in E3:I6, then D3 is "Y", else D3 is "X")

    BUT you cannot REPLACE the value in a cell in the way you want - the ONLY way to do that would...
  19. Thread: Hyperlink help

    by Yongle
    Replies
    1
    Views
    61

    Re: Hyperlink help

    Your suggestion is a workaround which may cause other issues later
    Eliminate the problem by correcting the (bad) "list of sheet names"
    - use the existing code that is used to make a valid name to...
  20. Replies
    1
    Views
    58

    Re: VBA code- insert comment box

    Your code does not fail with Office 365

    I suspect it is something else in your workbook causing the problem
    To eliminate the possibility that this is an Excel version problem etc, test the code...
  21. Replies
    4
    Views
    41

    Re: Make it mark "V" if found mark "X"

    You could use a helper column

    Excel 2016 (Windows) 32 bit

    D
    E
    F
    G
    H
    I
  22. Re: Return Unique Value in Excel Using Formula

    NORMAL formula in D2 copied down

    <tbody>

    =IFERROR(LOOKUP(2,1/(((COUNTIF($D$1:D1, $B$2:$B$9)=0)*(COUNTIF($B$2:$B$9, $B$2:$B$9)>1))), $B$2:$B$9),"")


    </tbody>
  23. Thread: IF Formula

    by Yongle
    Replies
    4
    Views
    63

    Re: IF Formula

    How about (untested) ....

    =I8*INDEX(Budget!M5:M25,MATCH(H8,Budget!L5:L25,0))
  24. Re: Return Unique Value in Excel Using Formula

    Try this:

    Excel 2016 (Windows) 32 bit

    A
    B
    C
    D
    E
  25. Re: Arrow keys and mouse not working consistently with scroll and panes

    Let's eliminate a systemic Excel/VBA issue first

    Are you able to recreate the issue in a clean workbook ?
    1 Create a new workbook with a similar layout WITHOUT copy/pasting ANY sheets, data or...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4