Search:

Type: Posts; User: anhn

Page 1 of 13 1 2 3 4

Search: Search took 0.30 seconds.

  1. MsOf07 Re: 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. Replies
    24
    Views
    1,434

    MsOf07 Re: 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. Replies
    24
    Views
    1,434

    MsOf07 Re: 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. Replies
    4
    Views
    853

    MsOf07 Re: 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. Replies
    6
    Views
    1,622

    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. Replies
    2
    Views
    529

    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
    632

    Re: VB Script Help

    Try:

    Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0), 3)
  8. Replies
    18
    Views
    1,356

    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. 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. Replies
    3
    Views
    1,169

    Re: Define Name in Excel using VB6

    Something like this: (wb is a workbook object)


    wb.Names.Add Name:="My_Name", RefersTo:=wb.Worksheets("SheetName").Range("A2:A10")
  11. Replies
    12
    Views
    1,146

    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. Replies
    7
    Views
    16,711

    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. Replies
    9
    Views
    1,763

    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. Replies
    13
    Views
    1,734

    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. Replies
    13
    Views
    1,734

    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 "..."
    '-- populate headers
    ...
  16. Replies
    2
    Views
    863

    MsOf03 Re: 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. Replies
    32
    Views
    2,428

    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. Replies
    32
    Views
    2,428

    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. Replies
    11
    Views
    3,881

    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.
  20. Thread: macro error

    by anhn
    Replies
    13
    Views
    1,284

    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. Replies
    11
    Views
    3,881

    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. MsOf03 Re: 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. MsOf03 Re: 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. MsOf03 Re: 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. Replies
    10
    Views
    800

    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. Replies
    5
    Views
    4,299

    Re: Remove Ordinal Suffix

    Another way with a single complicated search string:


    With Selection.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "([0-9])([snrt][tdh])...
  27. Replies
    9
    Views
    1,274

    MsOf03 Re: Hyperlinks Add Space before & After

    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
  28. Replies
    17
    Views
    1,338

    MsOf03 Re: 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. Replies
    17
    Views
    1,338

    MsOf03 Re: Find Replace - With wild cards

    Try it, it will work: Not just number or alphabet but if not-a-space.
  30. Replies
    17
    Views
    1,338

    MsOf03 Re: 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. Replies
    3
    Views
    615

    Re: [RESOLVED] Return UNC

    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...
  32. Replies
    2
    Views
    5,119

    MsOf07 Re: 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...
  33. Replies
    4
    Views
    986

    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. Replies
    9
    Views
    1,363

    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. Replies
    7
    Views
    916

    MsOf07 Re: 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. Replies
    30
    Views
    4,790

    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...
  37. Replies
    6
    Views
    808

    Re: Financial year(Urgent)

    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
  38. Replies
    9
    Views
    1,363

    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
    791

    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. MsOf07 Re: 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 2 3 4



Click Here to Expand Forum to Full Width