# Search:

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

Page 1 of 6 1

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

by Oaktree
Replies
2
Views
148

### 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. ## Thread: Number of periods between two

by Oaktree
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. ## Thread: Date/Time Excel Formula Calculation Help for Intermediate user in Excel 2016

by Oaktree
Replies
2
Views
178

### 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. ## Thread: Number of periods between two

by Oaktree
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. ## Thread: [VBA] SumIf Errors

by Oaktree
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. ## Thread: 'Range' of object '_Global' failed

by Oaktree
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 ...

by Oaktree
Replies
5
Views
214

8. ## Thread: Is there a way to apply Conditional Formatting to JUST a number in a text string?

by Oaktree
Replies
5
Views
214

### 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...

by Oaktree
Replies
1
Views
108

### 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. ## Thread: Move the range to the right

by Oaktree
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. ## Thread: Probability formula needed

by Oaktree
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. ## Thread: Visual Basic closes excel when it is opened

by Oaktree
Replies
3
Views
150

### 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. ## Thread: Countif function with a closed workbook

by Oaktree
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. ## Thread: Assitance with CountIF Formula

by Oaktree
Replies
1
Views
102

### Re: Assitance with CountIF Formula

=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. ## Thread: Alias within Formula

by Oaktree
Replies
9
Views
310

### Re: Alias within Formula

See Chip's write-up here: http://www.cpearson.com/excel/returningarraysfromvba.aspx
16. ## Thread: Alias within Formula

by Oaktree
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. ## Thread: clean none printable unicode chars

by Oaktree
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,...

by Oaktree
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. ## Thread: SUMPRODUCT that is going wrong for some reason

by Oaktree
Replies
4
Views
270

### 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. ## Thread: How do I convert 1,000,000 into 1M?

by Oaktree
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...

by Oaktree
Replies
1
Views
139

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. ## Thread: How can I put values in one array into another without spaces?

by Oaktree
Replies
7
Views
246

### 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. ## Thread: Toggle Change Procedure

by Oaktree
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. ## Thread: Help with formula to calculate target

by Oaktree
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. ## Thread: How can I put values in one array into another without spaces?

by Oaktree
Replies
7
Views
246

### 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