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:

    Code
    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:

    Code
    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:

    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.

    Code
    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
    =D


    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:


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

    Re: Find Lowest Value In Vlookup


    Use an array formula like this:


    =MIN(IF(A8=$A$1:$A$6,$B$1:$B$6))


    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".