1

So the goal is to make an random team assigner in Excel & VBA. I have 2 Sheets: One with inputs which has 5 columns: Name, RandomNumber (with rand() func), Rank, Divided by (Team) Size and Grouping (Roundup to 0 comma). It looks like this enter image description here

The second sheet should display all Members in a Team via VBA. Before i press update it looks like this: enter image description here

But after Update no entries are shown anymore. enter image description here What did i do? I recorded a macro: Second Sheet has =IF(B$5=Input!$F3;Input!$B3)which basically means, if TeamNumber in sheet 2 is the same as in sheet 1 than print the name of all matching. and than opened VBA where i added the following code:

Option Explicit
Sub Sort_group()
'
' Sort_group Makro
'

'
    Application.ScreenUpdating = "False"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "=IF(R5C=Input!R[-3]C6,Input!R[-3]C2)"
    Range("B6:K6").Select
    Selection.FillRight
    Range("B6:K35").Select
    Selection.FillDown
    
    Call eliminate_false_values
    Range("B4").Select
    Application.ScreenUpdating = "True"
    
End Sub
Sub eliminate_false_values()

Dim Counter As Integer
For Counter = 1 To 300

Dim false_value As Range
Set false_value = Range("B6:K35").Find("False", LookIn:=xlValues)

If Not false_value Is Nothing Then
    false_value.Delete
End If
Next Counter

End Sub

Than via Press on the Button i trigger the Sort_group Macro and suddenly all entries dissapear. Thanks for your help!

OTRAY
  • 115
  • 2
  • 11
  • 1
    Reading and appying [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to your code will probably solve your issue. • Use direct references instead of using `.Select` and `.Activate` also make sure there is no `Range` objelt without a workbook/worksheet specified in your code. Use always complete references: `ThisWorkbook.Worksheets("Sheet1").Range("B6")` otherwise VBA cannot know which sheet you mean and it guesses (which can easily fail). – Pᴇʜ Jul 09 '21 at 09:57
  • @PaulOgilvie That's not correct. The arguments 2 and 3 in the `IF` formula are optional and return `TRUE`/`FALSE` if not specified. – Pᴇʜ Jul 09 '21 at 10:03

0 Answers0