Excel planilha vba jogos bingo criar cartelas numeros aleatorios

Essa macro do Aplicativo Microsoft Excel VBA, cria cartelas de bingo com números aleatórios para jogos.
observem as macros usadas abaixo, importante verificar que o exemplo foi feito atraves de uma macro gravada.

Sub Inserir_Formulas()
' Atalho do teclado: Ctrl+F
Limpar
Centralizar
Range("A1:E20").Select
With Selection.Font
.Name = "Arial Narrow"
.Size = 26
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A1:E20").Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("F1").Select
Rows("1:20").RowHeight = 34.5
Range("A1") = "B"
Range("B1") = "I"
Range("C1") = "N"
Range("D1") = "G"
Range("E1") = "O"
Range("c4") = "LIVRE"

Range("A8") = "B"
Range("B8") = "I"
Range("C8") = "N"
Range("D8") = "G"
Range("E8") = "O"
Range("c11") = "LIVRE"

Range("A15") = "B"
Range("B15") = "I"
Range("C15") = "N"
Range("D15") = "G"
Range("E15") = "O"
Range("c18") = "LIVRE"
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-1),Saber2!R1C2:R15C2,0))"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-1),Saber2!R1C5:R15C5,0))"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-1),Saber2!R1C8:R15C8,0))"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-1),Saber2!R1C11:R15C11,0))"
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-1),Saber2!R1C14:R15C14,0))"
Range("A3").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-1),Saber2!R1C2:R15C2,0))"
Range("B3").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-1),Saber2!R1C5:R15C5,0))"
Range("C3").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-1),Saber2!R1C8:R15C8,0))"
Range("D3").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-1),Saber2!R1C11:R15C11,0))"
Range("E3").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-1),Saber2!R1C14:R15C14,0))"
Range("A4").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-1),Saber2!R1C2:R15C2,0))"
Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-1),Saber2!R1C5:R15C5,0))"
Range("D4").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-1),Saber2!R1C11:R15C11,0))"
Range("E4").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-1),Saber2!R1C14:R15C14,0))"
Range("A5").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-1),Saber2!R1C2:R15C2,0))"
Range("B5").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-1),Saber2!R1C5:R15C5,0))"
Range("C5").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-1),Saber2!R1C8:R15C8,0))"
Range("D5").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-1),Saber2!R1C11:R15C11,0))"
Range("E5").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-1),Saber2!R1C14:R15C14,0))"
Range("A6").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-1),Saber2!R1C2:R15C2,0))"
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-1),Saber2!R1C5:R15C5,0))"
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-1),Saber2!R1C8:R15C8,0))"
Range("D6").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-1),Saber2!R1C11:R15C11,0))"
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-1),Saber2!R1C14:R15C14,0))"
Range("A9").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-3),Saber2!R1C2:R15C2,0))"
Range("B9").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-3),Saber2!R1C5:R15C5,0))"
Range("C9").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-3),Saber2!R1C8:R15C8,0))"
Range("D9").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-3),Saber2!R1C11:R15C11,0))"
Range("E9").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-3),Saber2!R1C14:R15C14,0))"
Range("A10").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-3),Saber2!R1C2:R15C2,0))"
Range("B10").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-3),Saber2!R1C5:R15C5,0))"
Range("C10").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-3),Saber2!R1C8:R15C8,0))"
Range("D10").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-3),Saber2!R1C11:R15C11,0))"
Range("E10").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-3),Saber2!R1C14:R15C14,0))"
Range("A11").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-3),Saber2!R1C2:R15C2,0))"
Range("B11").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-3),Saber2!R1C5:R15C5,0))"
Range("D11").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-3),Saber2!R1C11:R15C11,0))"
Range("E11").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-3),Saber2!R1C14:R15C14,0))"
Range("A12").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-3),Saber2!R1C2:R15C2,0))"
Range("B12").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-3),Saber2!R1C5:R15C5,0))"
Range("C12").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-3),Saber2!R1C8:R15C8,0))"
Range("D12").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-3),Saber2!R1C11:R15C11,0))"
Range("E12").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-3),Saber2!R1C14:R15C14,0))"
Range("A13").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-3),Saber2!R1C2:R15C2,0))"
Range("B13").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-3),Saber2!R1C5:R15C5,0))"
Range("C13").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-3),Saber2!R1C8:R15C8,0))"
Range("D13").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-3),Saber2!R1C11:R15C11,0))"
Range("E13").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-3),Saber2!R1C14:R15C14,0))"
Range("E14").Select
ActiveWindow.SmallScroll Down:=9
Range("A16").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-5),Saber2!R1C2:R15C2,0))"
Range("B16").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-5),Saber2!R1C5:R15C5,0))"
Range("C16").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-5),Saber2!R1C8:R15C8,0))"
Range("D16").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-5),Saber2!R1C11:R15C11,0))"
Range("E16").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-5),Saber2!R1C14:R15C14,0))"
Range("A17").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-5),Saber2!R1C2:R15C2,0))"
Range("B17").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-5),Saber2!R1C5:R15C5,0))"
Range("C17").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-5),Saber2!R1C8:R15C8,0))"
Range("D17").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-5),Saber2!R1C11:R15C11,0))"
Range("E17").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-5),Saber2!R1C14:R15C14,0))"
Range("A18").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-5),Saber2!R1C2:R15C2,0))"
Range("B18").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-5),Saber2!R1C5:R15C5,0))"
Range("D18").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-5),Saber2!R1C11:R15C11,0))"
Range("E18").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-5),Saber2!R1C14:R15C14,0))"
Range("A19").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-5),Saber2!R1C2:R15C2,0))"
Range("B19").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-5),Saber2!R1C5:R15C5,0))"
Range("C19").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-5),Saber2!R1C8:R15C8,0))"
Range("D19").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-5),Saber2!R1C11:R15C11,0))"
Range("E19").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-5),Saber2!R1C14:R15C14,0))"
Range("A20").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C1:R15C1,MATCH(LARGE(Saber2!R1C2:R15C2,ROW()-5),Saber2!R1C2:R15C2,0))"
Range("B20").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C4:R15C4,MATCH(LARGE(Saber2!R1C5:R15C5,ROW()-5),Saber2!R1C5:R15C5,0))"
Range("C20").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C7:R15C7,MATCH(LARGE(Saber2!R1C8:R15C8,ROW()-5),Saber2!R1C8:R15C8,0))"
Range("D20").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C10:R15C10,MATCH(LARGE(Saber2!R1C11:R15C11,ROW()-5),Saber2!R1C11:R15C11,0))"
Range("E20").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Saber2!R1C13:R15C13,MATCH(LARGE(Saber2!R1C14:R15C14,ROW()-5),Saber2!R1C14:R15C14,0))"
Range("E21").Select
[G4] = "pressione a tecla {Delete} para mudar os números"
[G5] = "pressione a tecla {Control + F} para Iniciar"
Application.Goto Reference:=Worksheets("Saber1").Range("A1"), Scroll:=True
Range("F1").Select

End Sub

Sub Limpar()
Application.ScreenUpdating = False
Range("A1:E100").Select
'Selection.ClearFormats
Selection.RowHeight = 12
Selection.ClearContents
Range("F2").Select
Rows("1:20").RowHeight = 34.5
'Range("A1") = "B"
'Range("B1") = "I"
'Range("C1") = "N"
'Range("D1") = "G"
'Range("E1") = "O"
Range("G7").Select
Application.ScreenUpdating = True
End Sub

Sub Centralizar()
Range("A1:E22").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Selection.ColumnWidth = 12.35
Range("G3").Select
Range("g1").Select

End Sub

Sub Desbloquear_ignorar_erro()
Range("A2:E6").Select
Selection.Locked = False
Selection.FormulaHidden = True
Range("F1").Select
End Sub

Aprenda Microsoft Excel VBA (Saberexcel - o site das macros)

Baixe o exemplo de planilha contendo as macros acima
Excel planilha vba jogos bingo criar cartelas numeros aleatorios (37.86 kB)

Tags: