AW: Sprechtafel
28.07.2017 13:44:24
UweD
Hallo nochmal
so?
Sub Num_code()
Dim SP As Integer, ZE As Integer, WE As Integer, K As Integer, LB As Integer
WE = 48 'mit 0 beginnen
Application.ScreenUpdating = False
Range("A3:T16").ClearContents 'reset
For ZE = 4 To 16
For SP = 2 To 14
Cells(ZE, SP) = Chr(WE)
If Not IsNumeric(Chr(WE)) Then K = K + 1 'bei Buchstaben nur bis 4
If K = 4 Then 'nach 4 Buchstaben
M = 1
LB = WE 'Letzer Buchstabe merken
WE = 47 'Zahlen einschieben
K = 0
End If
If WE = 57 Then 'Wenn bei 9 dann zu A
K = 0
If M = 0 Then
WE = 65
Else
WE = LB + 1
End If
ElseIf WE = 90 Then ' bei Z wieder neu beginnen
LB = 64
WE = 48
Else
WE = WE + 1
End If
Next SP
Next ZE
'temporäres Blatt erzeugen
Sheets.Add after:=Sheets(Sheets.Count)
With ActiveSheet
.Range("A1:A26").FormulaR1C1 = "=RAND()+NOW()"
.Range("B1:B26").FormulaR1C1 = "=CHAR(ROW()+64)"
.Range("B1:B26").Value = .Range("B1:B26").Value
.Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With .Sort
.SetRange Range("A1:B26")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Tabelle1").Range("B3:N3").Value = _
WorksheetFunction.Transpose(.Range("B1:B16"))
Sheets("Tabelle1").Range("A4:A16").Value = _
.Range("B14:B26").Value
With .Sort 'neu mischen
.SetRange Range("A1:B26")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Tabelle1").Range("P3:T3").Value = _
WorksheetFunction.Transpose(.Range("B1:B5"))
Sheets("Tabelle1").Range("O4:O16").Value = _
.Range("B14:B26").Value
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
With Range("P4:T16") ' Doppelte möglich
.FormulaR1C1 = "=RANDBETWEEN(10,99)"
.Value = .Value
End With
End Sub
LG UweD