Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.24 seconds.

  1. Re: Protect formulas in workbook - not only sheet?

    No. You could use a macro to loop through the sheets so you wouldn't have to manually go sheet-by-sheet, but the process would still be to protect each sheet independently.
  2. Replies
    3
    Views
    198

    Re: Number of periods between two

    Not sure I follow.

    If D3 = 1/25/2019 and D4 = 12/31/2019 then =DATEDIF(D3-(DAY(D3)>30),D4-(DAY(D4>30)),"m") returns 11.
  3. Re: Date/Time Excel Formula Calculation Help for Intermediate user in Excel 2016

    Welcome to the board.

    You mentioned that you run 3 shifts, but what are the times of those? e.g. when does the first shift start on Monday (or Sunday) and when does the last shift end on Friday...
  4. Replies
    3
    Views
    198

    Re: Number of periods between two

    Perhaps:

    Month calculation =DATEDIF(D3-(DAY(D3)>30),D4-(DAY(D4>30)),"m")

    Day calculation =IF(DAY(D4)>DAY(D3),MIN(30,DAY(D4))-MIN(30,DAY(D3)),30-MIN(30,DAY(D3))+MIN(DAY(D4),30))
  5. Replies
    3
    Views
    169

    Re: [VBA] SumIf Errors

    Welcome to the board.

    Assuming you're searching for the text string "EX20" and not the value from cell EX20, try:


    Sub SumData()
    Dim LastRow As Long
    Dim FirstRow As Long
  6. Replies
    2
    Views
    112

    Re: 'Range' of object '_Global' failed

    The short answer to your question is that you're getting that error because you've declared Lastrow as a variable, but you haven't assigned it a value.

    As such, when your code gets to ...
  7. Re: Is there a way to apply Conditional Formatting to JUST a number in a text string?

    Interesting. I found your write-up here http://www.excelfox.com/forum/showthread.php/2004-Thinking-About-Using-VBA-s-IsNumeric-Function-Read-this-first about the problems currency symbols,...
  8. Re: Is there a way to apply Conditional Formatting to JUST a number in a text string?

    Not with conditional formatting, but you could use a macro for this.

    Select the cells and run this:


    Sub BoldSelectedNumbers()
    Dim my_cell, i
    For Each my_cell In Selection
    For i = 1 To...
  9. Re: Dynamically change header based on worksheet

    Why not put the desired header at the top of the sheet but freeze the panes so that those header rows are off the page (i.e. above the first visible row after freezing panes)? That way, you won't...
  10. Replies
    2
    Views
    167

    Re: Move the range to the right

    You can... but I wouldn't recommend doing this.

    Instead, why not use something like =OFFSET(A2,0,A1,49,12) where A1 has the month number? That way, if you ever want to go back to the previous...
  11. Replies
    1
    Views
    261

    Re: Probability formula needed

    Shouldn't the ^9 be ^10 as there are 10 numbers between 30-39 (endpoints included)?

    Set up A1:B6 to be this table:



    0 20%
    30 15%
    40 10%
    45 7.50%
  12. Re: Visual Basic closes excel when it is opened

    Welcome to the board.

    Sounds like there is a workbook_open event that includes code to close the workbook.

    Try holding down SHIFT while you click "open" from within Excel (as opposed to using...
  13. Replies
    1
    Views
    175

    Re: Countif function with a closed workbook

    SUMPRODUCT works on closed workbooks.

    Instead of =COUNTIF('C:\Excel file\[test.xlsx]Sheet1'!A1:A100,"Look for this"), try =SUMPRODUCT(--('C:\Excel file\[test.xlsx]Sheet1'!A1:A100 = "Look for...
  14. Replies
    1
    Views
    102

    Re: Assitance with CountIF Formula

    I think you've basically answered your own question :)


    =COUNTIF('Deliverables (past due and upco'!V:V,IF($F$4="All","*",$F$4))

    will count all of the values if F4 = "All", and it will count...
  15. Replies
    9
    Views
    310

    Re: Alias within Formula

    See Chip's write-up here: http://www.cpearson.com/excel/returningarraysfromvba.aspx
  16. Replies
    9
    Views
    310

    Re: Alias within Formula

    The GETV and SETV functions that are part of the morefunc.dll add-in might be what you're looking for.
  17. Replies
    1
    Views
    173

    Re: clean none printable unicode chars

    Answering #2 first, CODE is the inverse of CHAR (e.g. =CODE(CHAR(127)) = 127)

    For #1 , this would be easier with a UDF in VBA (search the board for "return only alphanumeric" or similar)... but,...
  18. Replies
    2
    Views
    266

    Re: Solve this mystery please...

    Can you be more specific on what you mean by "we cannot insert or delete rows"?

    How are you trying to insert/delete? Right clicking the mouse? Keyboard shortcut? Clicking on the ribbon?

    What...
  19. Re: SUMPRODUCT that is going wrong for some reason

    SUMPRODUCT requires that each of the multiplying arrays have the same dimensions.

    Your MAX and MIN calculations are only returning a single value... so, SUMPRODUCT is trying to take array * array...
  20. Replies
    5
    Views
    272

    Re: How do I convert 1,000,000 into 1M?

    Use custom number formats (press CTRL + 1 to bring up the format cells dialog box, choose the number tab, and click "custom" on the left side). Under "Type" enter one of these:

    #,##0.00,,,"B" for...
  21. Replies
    1
    Views
    139

    Re: Printing spread sheet

    If you want to print only columns A:C, just select columns A:C and go to Page Layout --> Print Area --> Set Print Area.

    And, if you only want to print the first page, you can do that in File -->...
  22. Re: How can I put values in one array into another without spaces?

    Since the new array will never be bigger than the previous array, you can set its ubound = to the ubound of the old array.



    Sub MakeNewArray()

    Dim lngCounter As Long
    Dim i As Long
    Dim myArr
  23. Replies
    2
    Views
    245

    Re: Toggle Change Procedure

    Is this the only VBA event?

    If so, your toggle could be as simple as


    Sub Toggle()
    Application.EnableEvents = Not (Application.EnableEvents)
    [A1] = Application.EnableEvents 'to list...
  24. Replies
    2
    Views
    173

    Re: Help with formula to calculate target

    I'd set up a few columns:

    1) Item cost
    2) Months left
    3) Cumulative Amount Paid
    4) Calculation for remaining monthly payment, defined as =(Cost - cumulative amount paid)/months left

    If...
  25. Re: How can I put values in one array into another without spaces?

    Use a counter and loop through your array.


    Sub MakeNewArray()

    Dim lngCounter As Long
    Dim i As Long

    lngCounter = 1
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4