Team generator

4 Responses to “Team generator”

  1. In a scenario like this and recalculating the setup several times, I see that the Team A is never assigned to #10 or greater. Is this a limitation?

  2. I'm trying to upload the image, but I can't.
    The link would be: http://s24.postimg.org/iy5s86r8l/Sem_t_tulo.png
    However, the setup I did was just 1 member in Team A and 19 members in Team B.

    • Oscar says:

      Felipe Costa Gualberto,

      If there are two teams, the probability is 50% that a team is displayed in a cell, that is why Team B is shown almost always in one of the three or four first cells. This is perhaps not ideal, I am working on a better formula.

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Working with List Boxes (Form Controls)

One Response to “Working with List Boxes (Form Controls)”

  1. Admin says:

    Видео Николая Левашова, Прямые url на файлы видео, поиск по основным словам, ролики youtube. Встречи с соратниками, встречи с участниками Движения, интервью в СМИ.

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Split expenses calculator

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Count contiguous values

4 Responses to “Count contiguous values”

  1. First off, you show your formula starting in cell B3... you need to adjust it so that it starts in cell B2, otherwise you will not show a 1 in cell B2 if cell A2 differs from cell A3.

    Second, here is a much simpler, still array-entered, formula, placed in cell B2 and copied down, that appears to produce the same output as your formula...

    =IF(A3<>A4,ROW()-MAX(IF(A$1:A2<>A3,ROW(A$1:A2))),"")

    • Sorry, I posted the wrong formula, plus it looks like the comment processor ate my less than greater than symbol. Here is the correct array-entered formula, rearrange to eliminate the display problem...

      =IF(A2=A3,"",ROW()-MAX(IF(A$1:A1<>A2,ROW(A$1:A1))))

    • D@MN! I missed that the comment processor ate a second less than, greater than symbols in my formula. Here now is the correct [b]array-entered[/b] formula (rearranged so that there are no less than, greater than symbols at all)..

      =IF(A2=A3,"",ROW()-MAX(IF(A$1:A1=A2,,ROW(A$1:A1))))

    • Oscar says:

      Rick Rothstein (MVP - Excel),

      Your formulas work fine, thanks. So much better and smaller than mine.

      I am sorry for wordpress eating your less/greater than signs, I try to edit your comments as soon as I can.

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

List all hyperlinks in worksheet

3 Responses to “List all hyperlinks in worksheet”

  1. I have not had the occasion to work with Hyperlinks in the past, so I cannot be 100% sure this works perfectly, but in my tests, your code and my code seem to return identical results every time, so I have a lot of confidence in my macro. My code has less active lines of code than yours and I believe it will execute slightly faster as it does not look at every cell in the UsedRange, rather, it first looks directly at hyperlinks only, and then it looks at all formulas on the sheet testing them to see if they start with "=HYPERLINK(" or not. Also, my code works slightly different than yours, it is sort of a combination of your two macros. To use it, you first select the sheets you want to process, so if you want a single sheet, select it... if you want only a few sheets, Control+Click the tabs for the sheets you want... and if you want all the sheets, select the first sheet and then Shift+Click the last sheet. After you have selected the sheets you want to process, then run this macro.

    Sub ListHyperlinksToo()
      Dim N As Long, LastRow As Long, StartCells As Range, Cell As Range
      Dim NewSheet As Worksheet, WS As Worksheet, HypLnk As Hyperlink, SS As Object
      On Error GoTo NoHyperlinks
      Set SS = ActiveWindow.SelectedSheets
      Sheets(1).Select
      Set NewSheet = Sheets.Add
      Set StartCells = NewSheet.Range("A1:C1")
      StartCells = Array("Worksheet", "Address", "Hyperlink")
      StartCells.Font.Bold = True
      For Each WS In SS
        For Each HypLnk In WS.Hyperlinks
          N = N + 1
          StartCells.Offset(N) = Array(WS.Name, HypLnk.Range.Address, HypLnk.Address)
        Next
        N = N + 1
        For Each Cell In WS.Cells.SpecialCells(xlFormulas)
          If Left(Cell.Formula, 11) = "=HYPERLINK(" Then
            StartCells.Offset(N) = Array(WS.Name, Cell.Address, Split(Cell.Formula, """")(1))
            N = N + 1
          End If
        Next
      Next
      LastRow = Cells(Rows.Count, "A").End(xlUp).Row
      StartCells.EntireColumn.Sort Range("A2:A" & LastRow), xlAscending, Range("B2:B" & LastRow), , xlAscending, Header:=xlYes
    NoHyperlinks:
    End Sub
    
    • Sorry, the above code does not correctly handle interim selected sheets that contain no hyperlinks (it ends the macro too soon). This revised macro does work correctly for those situations, so use it instead of what I posted earlier...

      Sub ListHyperlinksToo()
        Dim N As Long, LastRow As Long, StartCells As Range, Cell As Range
        Dim NewSheet As Worksheet, WS As Worksheet, HypLnk As Hyperlink, SS As Object
        Set SS = ActiveWindow.SelectedSheets
        Sheets(1).Select
        Set NewSheet = Sheets.Add
        Set StartCells = NewSheet.Range("A1:C1")
        StartCells = Array("Worksheet", "Address", "Hyperlink")
        StartCells.Font.Bold = True
        On Error GoTo NoHyperlinks
        For Each WS In SS
          For Each HypLnk In WS.Hyperlinks
            N = N + 1
            StartCells.Offset(N) = Array(WS.Name, HypLnk.Range.Address, HypLnk.Address)
          Next
          N = N + 1
          For Each Cell In WS.Cells.SpecialCells(xlFormulas)
            If Left(Cell.Formula, 11) = "=HYPERLINK(" Then
              StartCells.Offset(N) = Array(WS.Name, Cell.Address, Split(Cell.Formula, """")(1))
              N = N + 1
            End If
          Next
      Continue:
        Next
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        StartCells.EntireColumn.Sort Range("A2:A" & LastRow), xlAscending, Range("B2:B" & LastRow), , xlAscending, Header:=xlYes
        Exit Sub
      NoHyperlinks:
        Resume Continue
      End Sub
      
    • Oscar says:

      Rick Rothstein (MVP - Excel)

      Thank you for your contribution. Always interesting to read your comments.

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file