Posts by MikeCheck

    Re: Display Range Value In Formula Not Name

    Yes, I've had this problem before also. Excel doesn't like to mix RC notation with cell/range addresses in the same formula. It's not a great solution, but the only way I could get it to work is to name the range. Like this:

    strdate = "6 June 08"
    Sheets(strdate).Range("A:C").Name = "SearchRange"
    strColumnRange = "SearchRange"
    strResult = "='" & strdate & "'!" & strColumnRange

    Re: Format Phone Number In TextBox

    It's sort of a combination of what you started with, and the format code that Dave provided. You ultimately want to end up with Format(TextBox126.value, "00000 000 000") or Format(TextBox126.value, "##### ### ###")...either one will work fine, I chose to use "#". However, if you simply format all entries that way, I found that it does strange and unwanted things as you're typing in numbers. So, you have to create the format to match the number of digits being entered. In this loop:

    For i = 1 To Len(Replace(TextBox126.Value, " ", ""))
            FormatString = FormatString & "#"
            If i = 5 Or i = 8 Then
                FormatString = FormatString & " "
            End If
        Next i

    for each digit in textbox126.value it will add a "#" to formatstring, with a space entered after digit 5 and digit 8. For example, if "123" is entered the format string will be "###"...and if "123456" is entered the format string will be "##### #".
    The resulting format string is then used in the last line of the code:

    TextBox126.Value = Format(Replace(TextBox126.Value, " ", ""), FormatString)

    The Replace function used throughout the subroutine removes any spaces in textbox.value created by previous formatting or typed by the user.

    At the beginning I added this line so that you could use backspace. It bails out at digits 5 and 8 so that the space isn't added continuously.

    If Len(Replace(TextBox126.Value, " ", "")) = 5 Or Len(Replace(TextBox126.Value, " ", "")) = 8 Then Exit Sub

    Hope this helped.

    Re: Set Workbook To Newly Copied Worksheet

    Re: Format Phone Number In TextBox

    Quote from FRIEL

    thanks dave
    top notch as always

    i didnt realy understand yours mike
    but i used dave's as it was shorter and easier to understand

    thanks guys

    My code is not that complicted. It simply creates the format based on the number of characters in the textbox. That way, the phone numbers are formatted properly as they are being entered. I thought that's what you wanted.

    Re: Form Textbox Code Splitting A Phone Number

    Try this:

    Re: Output To File Produces Extra Spaces

    Re: Delete Row If Cell Text Matches Known String

    Start at the end and work backwards like this:

    For i = 200 To 1 Step -1
        Range("B" & i).Activate
        If Range("B" & i).Value = " Total:" Then
            Selection.Delete shift:=xlUp
        End If
    Next i

    Re: Find Lowest Value In Vlookup

    Use an array formula like this:


    Obviously replace the ranges according to your data. In this example:
    "A8" refers to the cell containing "A"
    "$A$1:$A$6" refers to the range containing "A,B,C,A,B,C"
    "$B$1:$B$6" refers to the range containing "5,7,4,3,6,3"

    The formula can go into any blank cell, but you would probably want it in "B8". Then drag the formula down for "B" and "C".