Posts by the Okk

    Re: Type Mismatch 13 Entering A Date


    Code
    On Error Resume Next
    NumberEntry = InputBox("Enter Start Date", "Start Date", "dd/mm/yyyy")
        Do While Not IsDate(NumberEntry)
                    MsgBox "The FROM date is not a valid date."
                    NumberEntry = InputBox("Enter Start Date", "Start Date", "dd/mm/yyyy")
                    'NumberEntry = InputBox("Please enter the date (dd/mm/yyyy) FROM to work with.")
        Loop

    Re: Code To Reveal Chr Codes?


    Code
    Function ClearSymb(ByVal strText As String, ByVal strPattern As String, ByVal strReplacement As String) As String
        Dim RegExp As Object
        Set RegExp = CreateObject("vbscript.regexp")
        With RegExp
            .Pattern = strPattern
            .Global = True
            ClearSymb = .Replace(Trim(strText), strReplacement)
        End With
    End Function


    And then just call this function whenever you will need it. For example:

    Code
    Sub DelAllTabs()
    [A1] = ClearSymb([A1],vbTab," ")
    End sub


    or

    Code
    Sub DelAllSymbs()
    Dim lngCount As Long
    For lngCount = 1 to 10
    [A1] = ClearSymb([A1],Chr(lngCount)," ")
    Next lngCount
    End sub

    Re: Selecting A Worksheet By Relative Reference



    Use INDIRECT function to convert string to address.

    Re: Highlight A Cell If It Has Changed As A Result Of Formula Recalculation


    If someone change C1 manually (or by macros), formula in C1 will disapear. So:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, [c1]) Is Nothing Then
            With [c1]
                If .FormulaR1C1 = "" Then .Interior.ColorIndex = 4
            End With
        End If
    End Sub


    of course, except situations, when user enters new formula in c1.

    Re: Selecting A Worksheet By Relative Reference


    You can't use sheets number in formulas, but you can get sheet's name by number:

    Code
    Function SheetName(num As Long) As String
        Application.Volatile
        SheetName = Worksheets(num).Name
    End Function


    This UDF will return the name of sheet with specified number.

    Re: Cause For Type Mismatch Error


    Quote from ojchase

    I would like the same procedure to be done on any number in a larger range (B13:B37). Does that make more sense?


    You can not compare array of values (B13,B14,B15...B37) and only one value (4). It's impossible. Does that make more sense? :)

    Re: code calculating incrementally


    Quote from rbrhodes

    I don't see it:


    "It's not the same. Try pool > 40 and spent < 0"


    dr


    Well, I'll try to explain.
    Your formula will allways return text (one or another). If I clearly understand daamn's conditions, formula must not return any text if "pool > 40 and spent < 0" or if "pool < 40 and spent > 0", but your formula does.

    Re: Cause For Type Mismatch Error


    Of course!
    Range("B13:H13") - type: Range;
    4 - type: Integer
    You can't compare Range and Integer! :)
    Range("B13") is a Range object too, BUT it has a value that can be compared with 4.

    Re: Add To Code From Textbox Value


    Something like that:
    [vba] Dim Cell As Range
    With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False


    For i = 2 To Sheets.Count
    With Sheets(i)
    For Each Cell In Range(.Cells(2, 3), .Cells(Rows.Count, 3).End(xlUp))
    With Workbooks("Jockeys.xls").Sheets(Cell).Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1)
    .Value = 1
    Select Case Cell.Offset(6)
    Case 1
    .Offset(, 1) = 1
    Case 2, 3
    .Offset(, 2) = 1
    Case Is > 0
    .Offset(, 3) = Cell.Offset(, 18)
    End Select
    If Cell.Offset(, 11) = 1 Or Cell.Offset(, 14) = 1 _
    Then .Offset(, 4) = 1
    End With
    Next Cell
    End With
    Next i
    .Calculation = xlAutomatic
    .ScreenUpdating = True
    End With[/vba]
    You don't need select.

    Re: code calculating incrementally



    It's not the same. Try pool > 40 and spent < 0 :)

    Re: code calculating incrementally


    Quote from daamyour

    Hey, thanks for showing me how to give a UDF arguments. This now gives a new question though.

    Code
    Function PointBuy(pool As Range, spent As Range) As Long
        If pool > 40 And spent > 0 Then
        target = "What is your character expecting?"
        Else
        If spent < 0 And pool < 40 Then
        target = "Too many points spent!"
        End If
        End If
    End Function


    In the above, target is the result if the conditions are true otherwise the result is blank. I'm looking for this result, either way, to be displayed in the cell pointbuy is in. What did I miss?


    Please help?


    First of all you've missed:

    Code
    Dim target As String


    Always declare your variables. It makes yor code run faster.
    Secondary, I don't see any "PointBuy=..."-code-strings. Because of that your formula's result allways will be 0.
    I don't clearly understand, what you want. Here, see this code:

    Code
    Function PointBuy(pool As Range, spent As Range) As String
        Application.Volatile
        If pool > 40 And spent > 0 Then
            PointBuy = "What is your character expecting?"
        Else
            If spent < 0 And pool < 40 Then PointBuy = "Too many points spent!"
        End If
    End Function

    Re: code calculating incrementally


    Some final corrections:

    Code
    Function rux(xp as Range) As Long
    Application.Volatile
    rux = (xp * (xp + 1) / 2) * 1000
    End Function


    NO DIMS! + You can choose xp-cell.

    Re: Replace Command Missing Cells With More Text?


    Now you use Replace method of Range object. It will not work correctly if length of cell > 1024 characters.

    Code
    cl.Replace


    Try to use Replace VBA operator. It's not the same thing:

    Code
    cl = Replace(cl, Chr(10), "<br>")

    Re: Replace Command Missing Cells With More Text?


    Quote from royUK

    AFAIK, Excel will only display the first 1024 characters in any cell, I'm not sure whether this affects your code though.


    Of course it affects ;) 'Familiar' Excel Replace has limitation 1024, but VBA one does'nt.

    Code
    Sub test()
        Dim cl As Range
        For Each cl In Sheets(1).UsedRange.SpecialCells(xlCellTypeConstants)
            cl = Replace(cl, Chr(10), "<br>")
        Next cl
    End Sub

    Re: &quot;set..with&quot; As Global Variables


    Quote from jonny

    I have "set .. with" blocks in some procedure.
    I want to make it global. How I do it?


    It IS global, but it's not Static. You must declare CreateToolbar() as static:

    Code
    STATIC sub CreateToolbar()


    In this case all variables of CreateToolbar() will be stored. Another way is declare variables as static.