Home Excel - Dicas Microsoft Excel VBA Excel VBA - Jogos Excel planilha vba jogos bingo criar cartelas numeros aleatorios

Excel planilha vba jogos bingo criar cartelas numeros aleatorios

Qui, 25 de Novembro de 2010 07:38 Expedito Marcondes
E-mail Imprimir PDF

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:
Última atualização em Sáb, 13 de Agosto de 2011 19:15  

Adicionar comentário

"Jamais considere seus estudos como uma obrigação, mas como uma oportunidade invejável para aprender a conhecer a influência libertadora da beleza do reino do espírito, para seu próprio prazer pessoal e para proveito da comunidade." Albert Einstein

Código de segurança
Atualizar

PROMOÇÃO DIDÁTICOS SABEREXCEL



Adquira já o Acesso Imediato
à Area de Membros

Compra Grantida --- Entrega Imediata

Aprenda Excel VBA com Simplicidade de 
códigos e Eficácia, Escrevendo Menos e
Fazendo Mais.

'-------------------------------------'
Entrega Imediata:
+  500 Video Aulas MS Excel VBA
+  35.000 Planilhas Excel e VBA
+  Coleção 25.000 Macros MS Excel VBA
+  141 Planilhas Instruções Loops
+  341 Planilhas WorksheetFunctions(VBA)
+    04 Módulos Como Fazer Excel VBA
+  Curso Completo MS Excel VBA
+  Planilhas Inteligentes

Pesquisa Google SaberExcel

Google

Contatos Equipe Saberexcel

Navegar pelo Site

Outros Assuntos

Publicidade Google

<script type="text/javascript"><!--

google_ad_client = "ca-pub-2317234650173689";

/* retangulo 336 x 280 */

google_ad_slot = "0315083363";

google_ad_width = 336;

google_ad_height = 280;

//-->

</script>

<script type="text/javascript"

src="http://pagead2.googlesyndication.com/pagead/show_ads.js">

</script>

Publicidade

Tags Populares

RSFirewallProtected

19.04.2024

Google Associados

Depoimentos

Adicione Saberexcel Favoritos

 
 

Aprenda tudo sobre o Aplicativo Microsoft Excel VBA

Aprenda tudo sobre o Aplicativo Microsoft Excel VBA(Visual Basic Application), sozinho, com baixo custo, praticando com os produtos didáticos Saberexcel,


   Sobre as WorksheetFunctions Funções de Planilhas que retornam valores do VBA