| Descricao: |
 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)
|