Posts by mikerickson

The leading zeros are controlled by the number format of the cell. The posted code won't change that.

Set LpDoZnalezienia = ws1.Range("A:A").Find("lp.", LookAt:=xlPart)
Set RazemDoZnalezienia = ws1.Range("A:A").Find("Razem", LookAt:=xlWhole)  


For a = 1 To 3    


    If (a > 1) Then        
        Set LpDoZnalezienia = ws1.Range("A:A").FindNext(After:=LpDoZnalezienia).Offset(1, 0)        
        Set RazemDoZnalezienia = ws1.Range("A:A").FindNext(After:=RazemDoZnalezienia)    
    End If      
    '....


Next

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

Either

Mulipage1.Value = 1

or

With Mulipage1
    .Value = (.Value + 1) Mod (.Pages.Count)
End With

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?

The formula =ISNUMBER(MATCH("Bob Smith", Sheet1!$A:$A, 0)) will return True if Bob Smith is compliant (i.e. on the first list) and False if he is not.

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.

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

Then when you write to the cell

With someTextBox
    Range(.Tag).Value = .Text
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

With ActiveSheet


    With .Names
        If Me.OptionButton3.Value Then
            .Add Name:="OptionButtonString", RefersTo:="=""InBound"""
        ElseIf Me.OptionButton2.Value Then
            .Add Name:="OptionButtonString", RefersTo:="=""OutBound"""
        Else
            .Add Name:="OptionButtonString", RefersTo:="=""odd"""
        End If
    End With
    
    With Range("C2:C1000000")
        .FormulaR1C1 = "=IF(RC1="""", ""N/A"",OptionButtonString)"
        .Value = .Value
    End With
    
    .Names("OptionButtonString").Delete
End With

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

With wb 
        Set SheetFromCodeName = .Sheets(.VBProject.VBComponents(aName).Properties("Index")) 
    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

Private Sub butOK_Click()
    Me.Tag = "OK"
    Me.Hide
End Sub


Private Sub butCancel_Click()
    Unload Me
End Sub

The code for your function would look like

Function myFtn()
    Dim userformValue as String


    ' ... code


    Userform1.Show
    If Userform1.Tag = "OK" Then
        userformValue = userform1.TextBox1.Text & "," & userform1.TextBox2.Text
    Else
        Rem user canceled
    End If


    Unload Userform1


' ...
End Function

Re: Enabling macros by using VBA code

Note that while you can set your computer to automatically enable macros, you cannot create a workbook that will automatically have macros enabled when run on other's machines.

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.

Function NextCombination(ThisCombination As Range, Digits As Range, Optional LeftToRight As Boolean = True) As Variant
    Dim DoMe As Long, Incriment As Long
    Dim ColCount As Long
    Dim Result As Variant, LetterIndex As Variant
    
    With Digits
        Set Digits = Application.Intersect(.EntireColumn, .CurrentRegion.EntireRow)
    End With
    ColCount = Digits.Columns.Count
    
    Result = ThisCombination.Resize(1, ColCount).Value
    
    If LeftToRight Then
        DoMe = 1
        Incriment = 1
    Else
        DoMe = ColCount
        Incriment = -1
    End If
    
    Do
        With Digits.Columns(DoMe)
            LetterIndex = Application.Match(Result(1, DoMe), .Cells, 0)
            
            If IsNumeric(LetterIndex) Then
                If .Cells(LetterIndex + 1, 1) = vbNullString Then
                    Result(1, DoMe) = .Cells(1, 1).Value
                Else
                    Result(1, DoMe) = .Cells(LetterIndex + 1, 1)
                    Exit Do
                End If
            Else
                Result(1, DoMe) = .Cells(1, 1).Value
            End If
        End With
        
        DoMe = DoMe + Incriment
    Loop Until (DoMe < 1) Or (ColCount < DoMe)
    
    NextCombination = Result
End Function

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.

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.

Function DirectedWorksheet() as Worksheet
    On Error Resume Next
    Set DirectedWorksheet = ThisWorkbook.Sheets(Me.TextBox1.Text)
    On Error Goto 0
End Function

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

With DirectedWorksheet
    .Range("A1") = Me.TextBox2.Text
    .Range("B1") = Me.ComboBox1.List(Me.ComboBox1.ListIndex)
    ' etc
End With

Re: Copy ranges one workbook to another

Try

Dim wb1 As Workbook 
Dim wb2 As Workbook 
Dim ws1 As Worksheet 
Dim ws2 As Worksheet 


Set wb1 = Workbooks("2017.07.09 xxx yyy zzz v18.xlsm") 
Set wb2 = Workbooks("2017.07.09 results output.xlsm")


Set ws1 = wb1.Worksheets("Output Results Sheet") 
Set ws2 = wb2.Worksheets("CityName") 


ws1.Range("O8:P13").Copy ws2.Range("N5:O10")

Re: Code executing twice

The code in the OP has one Sub declaration line and two End Sub lines.

Re: Copy & Paste 1st 6 digits of a cell to another sheet.

It is a basic Excel formula that takes data from a different worksheet. What happened when you tried it?

Re: Copy & Paste 1st 6 digits of a cell to another sheet.

Formulas like =LEFT(Sheet1!A1,6) will get the data to the new sheet.