# Search:

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

Page 1 of 6 1

1. ## Thread: Condition formatting for overnight hours worked

by Yongle
Replies
1
Views
41

### 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. ## Thread: ERRORS in VBA FUnctions "InputBox" & "Select Case"

by Yongle
Replies
5
Views
33

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

...

by Yongle
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","")

by Yongle
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","")

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. ## Thread: List of all Combinations of Row with 10's of Millions of Combinations

by Yongle
Replies
2
Views
48

### 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. ## Thread: Custom number formatting in Excel

by Yongle
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. ## Thread: Custom number formatting in Excel

by Yongle
Replies
4
Views
97

### Re: Custom number formatting in Excel

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

Ignore this - I missed something !!
9. ## Thread: Find and show the strings

by Yongle
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. ## Thread: Search Multiple Columns

by Yongle
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. ## Thread: Address of the First Five Consecutive rows where the cell value is >=X

by Yongle
Replies
5
Views
138

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

by Yongle
Replies
1
Views
53

13. ## Thread: Finding the last value less than 24 hours

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

by Yongle
Replies
3
Views
51

:beerchug:
15. ## Thread: Worksheet Change event / Calculate cells changed by formulas

by Yongle
Replies
3
Views
51

### 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. ## Thread: Apply formula to cells without relative cell referencing in VBA

by Yongle
Replies
4
Views
31

### 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. ## Thread: Apply formula to cells without relative cell referencing in VBA

by Yongle
Replies
4
Views
31

### 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. ## Thread: Make it mark "V" if found mark "X"

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

by Yongle
Replies
1
Views
61

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. ## Thread: VBA code- insert comment box

by Yongle
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. ## Thread: Make it mark "V" if found mark "X"

by Yongle
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. ## Thread: Return Unique Value in Excel Using Formula

by Yongle
Replies
4
Views
72

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

by Yongle
Replies
4
Views
63

### Re: IF Formula

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

by Yongle
Replies
4
Views
72

### Re: Return Unique Value in Excel Using Formula

Try this:

Excel 2016 (Windows) 32 bit

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

by Yongle
Replies
2
Views
79

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