# Search:

Type: Posts; User: Jon von der Heyden; Keyword(s):

Page 1 of 6 1

## Search: Search took 0.39 seconds.

by Jon von der Heyden
Replies
6
Views
308

### Re: compare columns

or:

Public Sub IloveVBA()
Dim rng1 As Excel.Range
Dim rng2 As Excel.Range
Dim rngCell As Excel.Range
Dim strJustynasFormula As...
2. ## Thread: how to change colour of cell if anything is entered in textbox

by Jon von der Heyden
Replies
4
Views
158

### Re: how to change colour of cell if anything is entered in textbox

Howdy

Remove the = ""

If Len(TextBox14.value) = "" Then
3. ## Thread: how to change colour of cell if anything is entered in textbox

by Jon von der Heyden
Replies
4
Views
158

### Re: how to change colour of cell if anything is entered in textbox

You could check that the length of the text in the TextBox is not 0:

If Len(TextBox14.Value) Then
4. ## Thread: DATEVALUE in Excel, handy tips?

by Jon von der Heyden
Replies
9
Views
244

### Re: DATEVALUE in Excel, handy tips?

Hi

What version of Excel are you using?

Assuming I understand your question, both WEEKNUM and WEEKDAY functions support unnatural selections, although I cannot recall when this came in to...
5. ## Thread: Parsing a formula: qualify sheet for all range references

by Jon von der Heyden
Replies
7
Views
197

### Re: Parsing a formula: qualify sheet for all range references

Wonderful - thank you Pedro! Very cunning workaround and it seems to be working a treat!
6. ## Thread: Parsing a formula: qualify sheet for all range references

by Jon von der Heyden
Replies
7
Views
197

### Re: Parsing a formula: qualify sheet for all range references

Hi Marcelo

Thanks though for having a crack. Still gives food for thought because Worksheet.Evaluate could indeed have offered some for of workaround.
7. ## Thread: Parsing a formula: qualify sheet for all range references

by Jon von der Heyden
Replies
7
Views
197

### Parsing a formula: qualify sheet for all range references

Hi All

I have a very large workbook with a large volume of validation rules across multiple sheets. The validation uses custom hence standard formulas that evaluate to either TRUE/FALSE.

I...
8. ## Thread: Rounding to the nearest 0.25

by Jon von der Heyden
Replies
11
Views
397

### Re: Rounding to the nearest 0.25

I was also working to that assumption. Adjusting my formula:

=CEILING(A2-"00:07:30","00:15")*24
9. ## Thread: Rounding to the nearest 0.25

by Jon von der Heyden
Replies
11
Views
397

### Re: Rounding to the nearest 0.25

Example results:

Excel 2010
Row\Col
A
B

1Time SerialRound Up .25
10. ## Thread: Rounding to the nearest 0.25

by Jon von der Heyden
Replies
11
Views
397

### Re: Rounding to the nearest 0.25

If your cell has time serial values, then:

=CEILING(A1,"00:15")*24
11. ## Thread: Rounding to the nearest 0.25

by Jon von der Heyden
Replies
11
Views
397

### Re: Rounding to the nearest 0.25

Hi

To round up you to a significance you can also use CEILING. E.g =CEILING(2.55,0.25)

You can also roundup time values (e.g. 02:55) to a significance of 15 minutes, e.g....
12. ## Thread: Rounding to nearest specified day

by Jon von der Heyden
Replies
2
Views
153

### Re: Rounding to nearest specified day

Simplify:

=CEILING(A1-4,7)+2
13. ## Thread: Rounding to nearest specified day

by Jon von der Heyden
Replies
2
Views
153

### Re: Rounding to nearest specified day

Assuming I understand the brief, try:

=IF(WEEKDAY(A1,1)<5,FLOOR(A1,7),CEILING(A1,7))+2
14. ## Thread: Returning MAX value based on multiple criteria.

by Jon von der Heyden
Replies
3
Views
110

### Re: Returning MAX value based on multiple criteria.

Hi

In C2 copied down:

=IF(A2*B2,MAX(A2:B2),"")
15. ## Thread: List specific number

by Jon von der Heyden
Replies
11
Views
443

### Re: List specific number

Glad it's working for you. Another VBA approach:

Public Sub NotAFormula()
Dim varVals As Variant
Dim lngItem As Long
Dim lngCount As Long
...
16. ## Thread: List specific number

by Jon von der Heyden
Replies
11
Views
443

### Re: List specific number

Hi

Using the second formula that I suggested.

Excel 2010
Row\Col
A
B

1NumsResults
17. ## Thread: Type Mismatch trying to find cell value

by Jon von der Heyden
Replies
4
Views
170

### Re: Type Mismatch trying to find cell value

Hi

You are using Set YourVariable...

Set is for Objects but you are after the Value property. Either lose the Set or replace with Let.
18. ## Thread: List specific number

by Jon von der Heyden
Replies
11
Views
443

### Re: List specific number

Hi

If your values are in A1:A100:

=INDEX(A1:A100,SMALL(IF(RIGHT(A1:A100,1)="8",ROW(A1:A100)),1),1)

And confirm with control + shift + enter

Change the 1 to the nth occurrence that you...
19. ## Thread: Summing up TIME - 2 Tables

by Jon von der Heyden
Replies
2
Views
133

### Re: Summing up TIME - 2 Tables

Or using Excel formulas

Excel 2010
Row\Col
A
B
C
D
E
F
20. ## Thread: AverageifS on a range containing Div/Value/Error...

by Jon von der Heyden
Replies
1
Views
161

### Re: AverageifS on a range containing Div/Value/Error...

You cannot use arrays (which one could use to test for error type) in AVERAGEIFS so I believe you need to proceed with the ">0" condition:

=AVERAGEIFS(A1:A5,A1:A5,">0",B1:B5,"Correct",C1:C5,"Yes")
21. ## Thread: Multiple Drop Down Selection Lists

by Jon von der Heyden
Replies
2
Views
146

### Re: Multiple Drop Down Selection Lists

Hi Jason

Contextures offers a nice tutorial here: https://www.contextures.com/xlDataVal02.html

by Jon von der Heyden
Replies
6
Views
250

### Re: Sumproduct

Although you can simplify if you use avoid using whole column and if your data is in a contiguous range.

=SUMPRODUCT(B1:B100-A1:A100)+SUMPRODUCT(--(A1:A100=B1:B100))

The whole column approach...

by Jon von der Heyden
Replies
6
Views
250

### Re: Sumproduct

Try:

=SUMPRODUCT(B:B-A:A)+SUMPRODUCT(--(A:A=B:B),--(A:A&B:B<>""))

by Jon von der Heyden
Replies
6
Views
250

### Re: Sumproduct

Hi

What does the first formula give you? I think it should work.

by Jon von der Heyden
Replies
4
Views
200

### Re: Formula Issue

Try:

=IF(LEN(C12),TEXT(C12,"# ?/?")&" ","")&IF(LEN(D12),TEXT(D12,"dd/mm/yyyy")&"x ","")&E12&" "&F12
Results 1 to 25 of 150
Page 1 of 6 1