趣味のエクセルマクロ

ナンバーズ4などの数字選択式宝くじデータ分析用の自作マクロおよびナンバーズ4の各種データリストなどをブログにしています。

2.miniloto



Sub after_32no() '後追い数字  緑色は平均より出ている
Dim i, ii, iii, countre, xa, ya As Integer
Sheets("元データ").Select
countre = Cells(1, 31)
Range("B2:g1500").Select
Selection.Copy
Sheets("後追数字").Select
Range("A1").Select
ActiveSheet.Paste
Range("l4:ap34") = 0
i = 0: ii = 0: iii = 0
   saikeisanoff
For i = 2 To countre
  For ii = 1 To 6
      xa = Cells(i - 1, ii) '最初数字1,1
   For iii = 1 To 6
      ya = Cells(i, iii)  '次回以降数字1,2
      Cells(3 + ya, 11 + xa) = Cells(3 + ya, 11 + xa) + 1
   Next iii
 Next ii
Next i
   Cells(2, 28) = "=max(AB4:AB46)"
saikeisanon
  Cells(1, 9) = countre & "回"
 Range("l4:ap34").Select
Selection.FormatConditions.AddAboveAverage
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).AboveBelow = xlAboveAverage
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.AddAboveAverage
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).AboveBelow = xlBelowAverage
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("i1").Select
End Sub
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
Sub 行挿入()
gyou = ActiveCell.Row
    Rows(gyou).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("リスト2").Select
Rows(gyou).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
---------------------------------
Sub 入力行へ()
gyou = Cells(1, 31) + 2
Range(Cells(gyou, 2), Cells(gyou, 9)).Select
End Sub
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

Sub c_and_s() '昇順にソートする。マクロの自動記録で作成、範囲部を修正
 kaigo = Sheets("元データ").Cells(1, 31) + 1’最終回号
 Sheets("分析 (3)").Select
Range(Cells(2, 1), Cells(kaigo, 9)).Select
Selection.Copy
Sheets("リスト1").Select
  Range("A2").Select
ActiveSheet.Paste
Range(Cells(2, 1), Cells(kaigo, 9)).Select 
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("リスト1").Sort.SortFields.Clear
' ActiveWorkbook.Worksheets("リスト1").Sort.SortFields.Add Key:=Range("B2:B868" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("リスト1").Sort.SortFields.Add Key:=Range(Cells(2, 2), Cells(kaigo, 2) _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("リスト1").Sort.SortFields.Add Key:=Range(Cells(2, 3), Cells(kaigo, 3) _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("リスト1").Sort.SortFields.Add Key:=Range(Cells(2, 4), Cells(kaigo, 4) _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("リスト1").Sort.SortFields.Add Key:=Range(Cells(2, 5), Cells(kaigo, 5) _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("リスト1").Sort.SortFields.Add Key:=Range(Cells(2, 6), Cells(kaigo, 6) _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("リスト1").Sort.SortFields.Add Key:=Range(Cells(2, 7), Cells(kaigo, 7) _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("リスト1").Sort
 .SetRange Range(Cells(2, 1), Cells(kaigo, 9))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

Sub C_and_s2() '昇順にソートする。
kaigo = Sheets("元データ").Cells(1, 31) + 1 ’最終回号
Sheets("分析 (3)").Select
Range(Cells(2, 1), Cells(kaigo, 50)).Select
Selection.Copy
  Sheets("リスト2").Select
Range("A2").Select
ActiveSheet.Paste
Range(Cells(2, 10), Cells(kaigo, 40)).Select
With Selection.Font
     .ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Selection.Font.Underline = xlUnderlineStyleSingle
Selection.Font.Underline = xlUnderlineStyleNone
Range(Cells(2, 1), Cells(kaigo, 50)).Select
Application.CutCopyMode = False
  ActiveWorkbook.Worksheets("リスト2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("リスト2").Sort.SortFields.Add Key:=Range(Cells(2, 2), Cells(kaigo, 2)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("リスト2").Sort.SortFields.Add Key:=Range(Cells(2, 3), Cells(kaigo, 3)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("リスト2").Sort.SortFields.Add Key:=Range(Cells(2, 4), Cells(kaigo, 4)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("リスト2").Sort.SortFields.Add Key:=Range(Cells(2, 5), Cells(kaigo, 5)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("リスト2").Sort.SortFields.Add Key:=Range(Cells(2, 6), Cells(kaigo, 6)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("リスト2").Sort.SortFields.Add Key:=Range(Cells(2, 7), Cells(kaigo, 7)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("リスト2").Sort
'.SetRange Range("A2:AX918")
.SetRange Range(Cells(2, 1), Cells(kaigo, 50))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
Sub B_H()
  With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
End Sub