# Search:

Type: Posts; User: anhn

Page 1 of 13 1

1. ## Thread: A Question Concerning The Percentile Rank Function In Excel 2007

by anhn
Replies
6
Views
2,098

### MsOf07Re: A Question Concerning The Percentile Rank Function In Excel 2007

With function PERCENTRANK(array, x, [significance]):
If x < Min(array) or x > Max(array), the function will return #N/A.
In other words, you must have Min(array) <= x <= Max(array).

To deal with...
2. ## Thread: [RESOLVED] Formula Consolidation

by anhn
Replies
24
Views
1,015

### MsOf07Re: Formula Consolidation

This Array Formula excludes columns AA:AM (search only G:Z,AN:CH) and if no blank cell found it will return "All Done!".

=IF(NOT(ISNA(MATCH(0, \$G17:\$Z17 + 0, 0))), OFFSET(\$G\$8, 0, MATCH(0,...
3. ## Thread: [RESOLVED] Formula Consolidation

by anhn
Replies
24
Views
1,015

### MsOf07Re: Formula Consolidation

An Array Formula can do the job without coding. From G to CH are 80 columns (no exclusion).

Enter this formula, then press Ctrl+Shift+Enter:

=OFFSET(\$G\$8, 0, IFERROR(MATCH(0, \$G17:\$CH17 + 0,...
4. ## Thread: Access 2007 VBA - Looking For A Better Way

by anhn
Replies
4
Views
748

### MsOf07Re: Access 2007 VBA - Looking For A Better Way

A single statement can do the job for you in one hit:

DoCmd.RunSQL "UPDATE Table1 SET ZeroCount = " & _
...
5. ## Thread: decimal fraction to hour and minute

by anhn
Replies
6
Views
1,211

### Re: decimal fraction to hour and minute

A simpler way: 26.999 hours = 26:59:56

dh = 26.999
hms = Int(dh) & ":" & Format(dh / 24, "nn:ss")
6. ## Thread: Excel 2007 - With Selected Cells

by anhn
Replies
2
Views
441

### Re: Excel 2007 - With Selected Cells

One line of code and works much faster for a large selection:

Selection.Replace What:=0, Replacement:="", LookAt:=xlWhole, SearchFormat:=False
7. ## Thread: VB Script Help

by anhn
Replies
4
Views
496

### Re: VB Script Help

Try:

Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0), 3)

by anhn
Replies
18
Views
950

### Re: BVA DATE CODE.. help please!

It should be simpler if using DatePart of "y":

Function DayOfYear(Dt As Date) As Long
DayOfYear = DatePart("y", Dt)
End Function
9. ## Thread: Word VBA: Using Arrays to order For Each...Next Loop

by anhn
Replies
11
Views
5,156

### Re: Word VBA: Using Arrays to order For Each...Next Loop

There is no actual sorting of array here. Me.Controls is a collection, it is not an array.
All controls were added one after another into Me.Controls collection when they were created.
By using For...
10. ## Thread: [RESOLVED] Define Name in Excel using VB6

by anhn
Replies
3
Views
730

### Re: Define Name in Excel using VB6

Something like this: (wb is a workbook object)

11. ## Thread: [RESOLVED] Date Alarm

by anhn
Replies
12
Views
795

### Re: [RESOLVED] Date Alarm

A shorttest line of code to set 2 colors:

[A1].Font.ColorIndex = 3 - (Date - [A1] < 5) * 39
Where 39 = 42-3 and assumed that [A1] has a date value. [A1] is a valid notation of Range("A1")....
12. ## Thread: How do I tell if an array is "empty"

by anhn
Replies
7
Views
5,320

### Re: How do I tell if an array is "empty"

Once upon the time, in other threads, I really love the simplicity of "Not Not myarr" but it really a "bizarre caveat".
Other people use CopyMemory() and other API functions.

My new universal...
13. ## Thread: null or empty string

by anhn
Replies
9
Views
771

### Re: null or empty string

A mimic of IFNULL() function that works for both Numeric or String:

Function IfNull(var As Variant, ValueIfNull As Variant) As Variant
If IsNull(var) Then IfNull = ValueIfNull Else IfNull =...
14. ## Thread: Populating an Excel List from Access

by anhn
Replies
13
Views
1,423

### Re: Populating an Excel List from Access

Square brackets are valid for Microsoft-Access such as "[Projects Extended]", but not single straight quotes (').
In some cases, grave-accent chr\$(96) "`" (on the same key with "~") is valid but not...
15. ## Thread: Populating an Excel List from Access

by anhn
Replies
13
Views
1,423

### Re: Populating an Excel List from Access

Tip: Use CopyFromRecordset() method is much quicker and the code is simpler, particularly for large table, and it also keeps data types intact.

oRs.Open "..."
...
16. ## Thread: [RESOLVED] Excel 2003 Pie Chart Labels

by anhn
Replies
2
Views
707

### MsOf03Re: Excel 2003 Pie Chart Labels

I cannot download your file at work (blocked) but I know why: Somehow, last month or before that, you accidently did a "slow double-click" to edit that particular data label that made it becomes a...
17. ## Thread: If Then Statements

by anhn
Replies
32
Views
1,508

### Re: If Then Statements

It is very clear that from the first post, all valid values for "single" have 4 characters:
("SDAC", "SUAC", "SDAV", "SUAV", "0511", "0611", "0721", "0921", "1021", "1121", "1321")
On checking, if...
18. ## Thread: If Then Statements

by anhn
Replies
32
Views
1,508

### Re: If Then Statements

Another way that takes advantage of the lengths of all test values are always 4.
No need to use concatenate; and no worry if the entry contains delimeter with a trick of using Mod.

Const...
19. ## Thread: [RESOLVED] Procedure Too Large

by anhn
Replies
11
Views
3,018

### Re: Procedure Too Large

Why changing my code to a wrong one?
With your line above, the code will not run if user enter data in column F.

by anhn
Replies
13
Views
1,076

### Re: macro error

This is a valid Range:

Range("I:I, N:N, T:T")
and this line is valid:

Set r = Intersect(Target, Range("I:I, N:N, T:T"))
Keep in mind that:
* A Union of 2 or more Ranges is a Range. (A cell...
21. ## Thread: [RESOLVED] Procedure Too Large

by anhn
Replies
11
Views
3,018

### Re: Procedure Too Large

The main problem is "Procedure too Large."

Your teacher will not believe the code below is of your own but he/she can learn from it too.

Private Sub Worksheet_Change(ByVal Target As Range)
...
22. ## Thread: Access - Function to return the Date (Monday & Saturday date of current week

by anhn
Replies
9
Views
2,014

### MsOf03Re: Access - Function to return the Date (Monday & Saturday date of current week

VB Constants: vbSunday = 1, vbMonday = 2, ..., vbSaturday = 7
So, you can use:

Me.txtStartDate = Date - Weekday(Date, 1) + 2 '-- Monday
Me.txtEndDate = Date - Weekday(Date, 1) + 7 '-- Saturday...
23. ## Thread: Access - Function to return the Date (Monday & Saturday date of current week

by anhn
Replies
9
Views
2,014

### MsOf03Re: Access - Function to return the Date (Monday & Saturday date of current week

What you added is incorrect in cases the system FirstDayOfWeek is not Sunday.
You must always use vbSunday in Weekday(Date, vbSunday) for the function.

Your Enum is not need, you can use...
24. ## Thread: Access - Function to return the Date (Monday & Saturday date of current week

by anhn
Replies
9
Views
2,014

### MsOf03Re: Access - Function to return the Date (Monday & Saturday date of current week

Try this:

Function ThisMonday() As Date
ThisMonday = Date + vbMonday - Weekday(Date, vbSunday)
End Function

Replace vbMonday with vbSaturday you will have ThisSaturday and similar for...
25. ## Thread: math, simple logic

by anhn
Replies
10
Views
595

### Re: math, simple logic

That's not true. After the loop, the value of i will go one more extra step beyond the last step.

For i = 0 to 13 step 3
'-- do nothing
Next
Debug.Print i '-- at this point: i = 15

Why...
26. ## Thread: [RESOLVED] Remove Ordinal Suffix

by anhn
Replies
5
Views
2,303

### Re: Remove Ordinal Suffix

Another way with a single complicated search string:

With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([0-9])([snrt][tdh])...

by anhn
Replies
9
Views
1,057

Try this:

Dim i As Long
Dim ch As String
Dim s1 As String

s1 = " " & vbCr & vbLf & vbTab '-- add more if required
28. ## Thread: Find Replace - With wild cards

by anhn
Replies
17
Views
1,118

### MsOf03Re: Find Replace - With wild cards

My logic in post#5 is:

1. Always add a space before "[" and "(" regardless they are preceeded by a space or not.
2. Always add a space after "]" and ")" regardless they are followed by a space or...
29. ## Thread: Find Replace - With wild cards

by anhn
Replies
17
Views
1,118

### MsOf03Re: Find Replace - With wild cards

Try it, it will work: Not just number or alphabet but if not-a-space.
30. ## Thread: Find Replace - With wild cards

by anhn
Replies
17
Views
1,118

### MsOf03Re: Find Replace - With wild cards

To use less brain power, an easy way is to replace
"[" with " ["
"]" with "] "
"(" with " ("
")" with ") "
and then at the end
" " with " "
31. ## Thread: [RESOLVED] Return UNC

by anhn
Replies
3
Views
361

### Re: [RESOLVED] Return UNC

Function ShareName(sDriveLetter As String) As String
'-- returns ShareName or UNCPath of a mapped drive
'-- returns "" if drive is not mapped or does...
32. ## Thread: [RESOLVED] Writing MedianIf in VBA

by anhn
Replies
2
Views
2,169

### MsOf07Re: Writing MedianIf in VBA

A simple way is to use .CountIf() function:

If WorksheetFunction.CountIf(cell, criteria) = 1 Then
My implementation:
After Redim, all elements of array ar() are Empty.
Only need to set the...

by anhn
Replies
4
Views
788

### Re: [RESOLVED] DateAdd Without Seconds

Why use Now then to struggle to remove the Time?

If you want only the date without time,use Date() function instead of Now().

Now = Date + Time

y = DateAdd("d", -daypas + x, Date)
34. ## Thread: [RESOLVED] Cut cell contents to remove { }

by anhn
Replies
9
Views
1,094

### Re: [RESOLVED] Cut cell contents to remove { }

In Excel, (3) denotes negative number -3.
To fix that problem, you can do it in 3 steps:

With Range("K2:K10")
.Replace What:="(", Replacement:="'(", LookAt:=xlPart, _
...
35. ## Thread: [RESOLVED] Need help with "OR"

by anhn
Replies
7
Views
776

### MsOf07Re: Need help with "OR"

Sid's logic is very good, however the formula in post#3 can be simplified if use AND() instead of OR() and use SEARCH() instead of FIND() if that is case-insensitive:

* Return Boolean value TRUE...
36. ## Thread: clear contents on excel

by anhn
Replies
30
Views
3,507

### Re: clear contents on excel

A fast way doing the job without looping:

Sub ClearData()
Dim r As Long

r = Val(InputBox("Enter row number to be cleared:", _
"Clear Data from column D to...

by anhn
Replies
6
Views
574

### Re: Financial year(Urgent)

Try this function:

Function FYear(aDate As Date) As Long
FYear = FYear * 100 + ((FYear + 1) Mod 100)
End Function
38. ## Thread: [RESOLVED] Cut cell contents to remove { }

by anhn
Replies
9
Views
1,094

### Re: Cut cell contents to remove { }

Try this:

Columns("K:K").Replace What:="{*}", Replacement:="", LookAt:=xlPart, _
SearchFormat:=False, ReplaceFormat:=False
39. ## Thread: Type of currency

by anhn
Replies
5
Views
582

### Re: Type of currency

You must use "," as thousand separator in Format() even your local thousand separator is "."

Text2.Text = Format(Text1.Text, "#,##0")
40. ## Thread: Fully close one workbook and leave another workbook open?

by anhn
Replies
6
Views
780

### MsOf07Re: Fully close one workbook and leave another workbook open?

Pete, that is a deadly way that cannot copy codes in modules other than sheet modules or needs to use complicated VBE methods.

Excel provides a simple way to do what you want: use .SaveCopyAs...
Results 1 to 40 of 500
Page 1 of 13 1

Featured