Bloggang.com : ปรุงก่อนชิม - การสุ่มรายชื่อ และบันทึกผล รวมทั้งเก็บ log เป็น text file

ตัวอย่างหน้าจอ

Load file (update on 05/02/2014)

load type A

load type B

สูตรที่ต้องเขียนใน sheet

B8 = INDEX(RandomEmployee5_ok.xlsm!EMP_ID,RANDBETWEEN(1,1412))

C8=INDEX(RandomEmployee5_ok.xlsm!Data,MATCH($B$8,RandomEmployee5_ok.xlsm!EMP_ID,0),2)

D8=INDEX(RandomEmployee5_ok.xlsm!Data,MATCH($B$8,RandomEmployee5_ok.xlsm!EMP_ID,0),3)

E8=INDEX(RandomEmployee5_ok.xlsm!Data,MATCH($B$8,RandomEmployee5_ok.xlsm!EMP_ID,0),4)

F8=INDEX(RandomEmployee5_ok.xlsm!Data,MATCH($B$8,RandomEmployee5_ok.xlsm!EMP_ID,0),5)

G8=INDEX(RandomEmployee5_ok.xlsm!Data,MATCH($B$8,RandomEmployee5_ok.xlsm!EMP_ID,0),6)

ตัวอย่าง code

Option Explicit

Dim i, k As Integer

Dim a As Long

Dim j As Integer

Dim bck As Long

Dim backupText As String

Sub randEmployee()

Dim ans As Variant

' show random and take delay

On Error Resume Next

For k = 1 To 6

For a = 1 To 100000000

a = a + 2

Next a

'-----------------------------for action random worksheet

SendKeys "%^{F9}"

DoEvents

'-----------------------------------µÃ¼Ùéâª¤´Õ----------------áÊ´Ç¹¢³ÐÊØèÁ

Cells(5, 3).Value = Cells(8, 2).Value

Cells(5, 4).Value = Cells(8, 3).Value

Cells(5, 5).Value = Cells(8, 4).Value & " -- " & Cells(8, 7).Value & " - " & Cells(8, 6).Value

Next k

Application.Calculation = xlCalculationManual ' stop random and keeping the result

'-----------------------------------µÃ¼Ùéâª¤´Õ----------------áÊ´ËÅÑ¨Ò¡´âªÇìÇ¹Ë¹èÇàÇÅÒáÅéÇä´é¤¹¹ÕéÅèÒÊØ´ (ËÂØ´ÊØèÁáÅéÇ)

Cells(5, 3).Value = Cells(8, 2).Value

Cells(5, 4).Value = Cells(8, 3).Value

Cells(5, 5).Value = Cells(8, 4).Value & " -- " & Cells(8, 7).Value & " - " & Cells(8, 6).Value

'----------------------------------List of lucky persons---------------

i = i + 1

Cells(17 + i, 2).Value = i '----No.

Cells(17 + i, 3).Value = Cells(8, 2).Value '----emp_id.

Cells(17 + i, 4).Value = Cells(8, 3).Value '----name.

Cells(17 + i, 5).Value = Cells(8, 4).Value '----lastname.

'--------------------------------Log back up to text file ---------------------------------

backupText = i & "|" & Cells(8, 2).Value & "|" & Cells(8, 3).Value & "|" & Cells(8, 4).Value & "|" & Now()

Call Module1.backup(backupText)

Sheets("type1").Select

Application.Calculation = xlCalculationAutomatic

'--------------------------------------à¢ÂèÒÑ

Call allrowRandom

Sheets("type1").Select

End Sub

Sub initial_Process()

'clear old data

'initial i=0

If MsgBox("yes ¢éÍÁÙÅ·ÕèÊØèÁäÇé¨ÐÙ¡ÅºËÁ´ ãËé¡´ cancel à¾×èÍä»·Ó¡ÒÃ copy ä»äÇé·ÕèÍ×è¹¡èÍ¹ ", vbYesNoCancel, "¤Óàµ×Í¹") = vbYes Then

i = 0

Range("B18:E2000").Value = ""

Range("C5:H5").Value = ""

ElseIf MsgBox("yes ¢éÍÁÙÅ·ÕèÊØèÁäÇé¨ÐÙ¡ÅºËÁ´ ãËé¡´ cancel à¾×èÍ·Ó¡ÒÃ copy ä»äÇé·ÕèÍ×è¹¡èÍ¹ ", vbYesNoCancel, "¤Óàµ×Í¹") = vbNo Then

Exit Sub

Else

Exit Sub

End If

End Sub

Sub backup(text1 As String) ' ok

'------------------------for back up on note pad

Dim FSO As FileSystemObject

Dim FSOFile As TextStream

Dim FilePath As String

Dim NoOfLoop As Integer

FilePath = "D:backUpRandom.txt" ' create a test.txt file or change this

Set FSO = New FileSystemObject

' opens file in write mode

Set FSOFile = FSOTextFile(FilePath, ForAppending, True)

' write your code here

FSOFile.WriteLine (" lucky man----is " & text1)

FSOFile.Close

End Sub

Sub backup2(text1 As String) ' ok

Sheets("backup").Select

Dim i

i = 1

Do Until Cells(i, 1).Value = ""

i = i + 1

Loop

Cells(i, 1).Value = text1

End Sub

Sub allrowRandom()

Application.Goto Reference:="Data"

ActiveWorkbook.Worksheets("data").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("data").Sort.SortFields.Add Key:=Range("G2:G1412") _

, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("data").Sort

.SetRange Range("A1:G1412")

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub