Posts by mikerickson

    You first Find for "lp." then you Find for "Razem" FindNext is now looking for Razem, and LpDoZnalezienia will be set based on "Razem", not on "lp."

    Could you attach a workbook with your lay-out.
    What kind of drop-downs? Forms controls or Validtaion or something else.
    Where in the destination sheet do you want the row of data to be moved?
    When you change one of the dropdowns in column A, do you want the data removed from the previously selected sheet?
    Do you want the data to remain on Active sheet after the dropdown is selected?

    Do the win percentages average to .5? i.e. are these percentages derived only from between the players on the list or are outside events included in the calculation of win percentage?


    Creating two teams with equal win percentage totals might not be possible, how much variation between the resulting teams is allowed?

    Re: Put data from generated textboxes to specific cells


    When you make the text box, put the address of the cell it refers to in the .Tag property.


    Code
    1. With tBox
    2. .Width = 60
    3. .Height = 20
    4. .Left = 10
    5. .Top = (Ndx - 0.8) * (30 + dDistHoriz) + 30
    6. .Text = Range("A1").Offset(0, Ndx - 1).Value
    7. .Tag = Range("A1").Offset(0, Ndx - 1).Address(,,,True)
    8. End With


    Then when you write to the cell


    Code
    1. With someTextBox
    2. Range(.Tag).Value = .Text
    3. End With

    Re: Copying Option Button 3 and Option Button 2 into excel sheet column C


    I think this code will do what you want

    Re: Reference Worksheet Codename using a string variable?


    Quote from bettvett;800155

    I just think I got what you meant by keeping the advantage of the codename, you're saying that on the case of someone messing up with the index, you would still find the correct sheet ?


    Let's break down the function

    Code
    1. With wb
    2. Set SheetFromCodeName = .Sheets(.VBProject.VBComponents(aName).Properties("Index"))
    3. End With


    VBProject.VBComponents(aName) is the VB component that has the codename that was passed to the function.


    .VBProject.VBComponents(aName).Properties("Index") is the current index of that component (sheet)


    .Sheets(.VBProject.VBComponents(aName).Properties("Index")) is the worksheet object that currently has that index.


    So, when the function is run, it returns a worksheet object that has the given code name.


    If the user re-orders the sheets between you running the function and using the results, it doesn't matter. The Worksheet object is still the same. the user changing the index hasn't changed the object, its only changed its location. (Your car is your car, no matter where you park it)

    Re: VLookup Not Working for Change Event Sub


    If IDs(i) is not there, then the code will error.


    Try using Application.VLookup rather than Application.WorksheetFunction.VLookup.


    Also commentText should be data type Variant, so it can become the error value that will be returned if IDs(i) is not found.

    Re: Running Userforms in functions.


    Lets simplify things so the userform has two TextBoxes an OK button and a cancel button. The code for your Userform would look like


    Code
    1. Private Sub butOK_Click()
    2. Me.Tag = "OK"
    3. Me.Hide
    4. End Sub
    5. Private Sub butCancel_Click()
    6. Unload Me
    7. End Sub


    The code for your function would look like

    Re: VBA Code - Generate a list of all combinations possibles


    I would hesitate doing this with a button, simply because one button push could easily result in millions of results.
    But if you need to you could calcualte the number of rows needed and have you macro put the UDF into the appropriate rows and then copy/Paste Values.

    Re: VBA Code - Generate a list of all combinations possibles


    This uses a UDF. Given a one combination (like result 2,4,7,9) it will return the next combination (3,4,7,9).


    The ThisCombination argument of NextCombination is range of the current combination.
    The Digits argument is the initial range of variables.


    In the attached, H1:K1 is blank. H2:K2 holds the array formula {=NextCombination(H1:K1, $A$1:$D$3)}
    (do not enter the { }, that is only there to indicate an array formula. they are not typed in.)
    This returns the first combination 1,4,7,9
    Drag the formula down to row 3 and the formula becomes {=NextCombination(H1:K1, $A$1:$D$3)} and the result 2,4,7,9
    Drag further down and more combinations are returned.


    Using a NextCombination approach, rather than listing all of them, has the advantage that one can start in the middle of the sequence and not go any further than one needs.


    As the number of digits increases and the number of possible "letters" per digit increase, "all combinations" can quickly become a large number. This allows the user to work with high numbers of input factors, but not have to generate the entire list of combinations. Although every combination is accessible through this route.



    Note that in the attached file, Sheet2 has a 5 digit data set (and a 5 digit result) and sets the optional LeftToRight argument to False.

    Files

    Re: Allow Input Into Formula Cell While Retaining Formula


    I wrote the CellEntry function in the link.
    I've just reviewed it and it used un-documented features of the .Validation object. Those features are no longer present. (One can no longer manipulate the Validation.InputMessage of a cell unless there is validation on that cell).


    I'm at work right now and can't address a workaround or different approach.

    Re: Userform textbox to a specific sheet code


    What I would do is create a function in the user form module.



    Code
    1. Function DirectedWorksheet() as Worksheet
    2. On Error Resume Next
    3. Set DirectedWorksheet = ThisWorkbook.Sheets(Me.TextBox1.Text)
    4. On Error Goto 0
    5. End Function


    Then, all of your write to sheet code could reference that function



    Code
    1. With DirectedWorksheet
    2. .Range("A1") = Me.TextBox2.Text
    3. .Range("B1") = Me.ComboBox1.List(Me.ComboBox1.ListIndex)
    4. ' etc
    5. End With

    Re: Copy ranges one workbook to another


    Try