Search:
Type: Posts; User: anhn
Search:
Search took 0.26 seconds.
-
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...
-
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,...
-
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,...
-
A single statement can do the job for you in one hit:
DoCmd.RunSQL "UPDATE Table1 SET ZeroCount = " & _
...
-
A simpler way: 26.999 hours = 26:59:56
dh = 26.999
hms = Int(dh) & ":" & Format(dh / 24, "nn:ss")
-
One line of code and works much faster for a large selection:
Selection.Replace What:=0, Replacement:="", LookAt:=xlWhole, SearchFormat:=False
-
Try:
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0), 3)
-
It should be simpler if using DatePart of "y":
Function DayOfYear(Dt As Date) As Long
DayOfYear = DatePart("y", Dt)
End Function
-
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...
-
Something like this: (wb is a workbook object)
wb.Names.Add Name:="My_Name", RefersTo:=wb.Worksheets("SheetName").Range("A2:A10")
-
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")....
-
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...
-
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 =...
-
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...
-
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 "..."
'-- populate headers
...
-
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...
-
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...
-
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...
-
Why changing my code to a wrong one?
With your line above, the code will not run if user enter data in column F.
-
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...
-
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)
...
-
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...
-
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...
-
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...
-
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...
-
Another way with a single complicated search string:
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([0-9])([snrt][tdh])...
-
Try this:
Sub AddSpacesToHyperlinks()
Dim hy As Hyperlink
Dim i As Long
Dim ch As String
Dim s1 As String
s1 = " " & vbCr & vbLf & vbTab '-- add more if required
-
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...
-
Try it, it will work: Not just number or alphabet but if not-a-space.
-
To use less brain power, an easy way is to replace
"[" with " ["
"]" with "] "
"(" with " ("
")" with ") "
and then at the end
" " with " "
-
How about this simple function:
Function ShareName(sDriveLetter As String) As String
'-- returns ShareName or UNCPath of a mapped drive
'-- returns "" if drive is not mapped or does...
-
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...
-
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)
-
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, _
...
-
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...
-
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...
-
Try this function:
Function FYear(aDate As Date) As Long
FYear = Year(aDate) Mod 100 + (aDate < DateSerial(Year(aDate), 4, 1))
FYear = FYear * 100 + ((FYear + 1) Mod 100)
End Function
-
Try this:
Columns("K:K").Replace What:="{*}", Replacement:="", LookAt:=xlPart, _
SearchFormat:=False, ReplaceFormat:=False
-
You must use "," as thousand separator in Format() even your local thousand separator is "."
Text2.Text = Format(Text1.Text, "#,##0")
-
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...
|
Click Here to Expand Forum to Full Width
|