สอบถามเรื่อง Macro ครับ

[torofish]

สวัสดีครับ
มีคำถามมาถามอีกแล้วครับ

ตอนนี้เวลาจ่ายเงินพนักงาน จะจ่ายเป็น 2 งวด คืองวดวันที่ 15 และ 31
ทีนี้ในทุก ๆ ปลายเดือน จะต้องนำข้อมูลเงินเดือน, ภาษี, ประกันสังคม ไปส่งให้กับหน่วยงานรัฐ ซึ่งสามารถดูตามรูปได้เลยครับ
COLUMN นั้นจะคงที่ ไม่เปลี่ยนแปลง
ที่เปลี่ยน คือ row คือ การลาออก รับเข้า ย้ายแผนกของพนักงาน

ไฟล์เงินเดือน 2 ไฟล์ อยากให้มารวมที่ไฟล์เดียว ซึ่งข้างในนั้นจะมีข้อมูลที่เอาไว้ใช้สำหรับงานสี่ชนิดอยู่ แล้วก็จะ print จะ copy paste ก็ค่อยว่ากัน

แนวทาง
1.) งวดวันที่ 15 May จะ บัตรประชาชน รหัส ชื่อ มาตรง ๆ
แล้งเขียน vlookup หาเอา

ปัญหา
1.) งวดวันที่ 31 คนออกจะไม่มีปัญหาเพราะว่า vlookup จะไม่เจออะไร แต่คนที่เข้าใหม่จะทำยังไงได้บ้างครับ จะเขียน macro ให้ตรวจสอบได้อย่างไรว่า คนใหม่ให้ copy ID, Employee Code, ชื่อ มาด้วย

2.) ที่ผมคิดไว้คือ จะเป็นไปได้มั้ย ถ้ามีปุ่มให้เลือก 2 ปุ่ม
ปุ่ม งวดวันที่ 15
ปุ่ม งวดวันที่ 31

พอกดแล้ว ก็มีหน้าเปิดมาให้เลือกว่า source file: 15 May อยุ่ที่ไหน
แล้วพอเลือก macro ก็จะ copy แล้วก็ใส่สูตรให้โดยอัตโนมัติ

ส่วนปุ่มวันที่ 31 ก็เลือก source file; 31 May
macro ก็จะใส่ vlookup ให้เองเลย
----------------------------------------------
หรือจะให้เปิด file 15 May, 31 May, Data Portal ขึ้นมา
แล้วมีปุ่ม งวดวันที่ 15
งวดวันที่31
พอกด มันก็จะเอาข้อมูลจากงวดวันที่ 15 มาใส่ให้เอง
ปัญหาคือ จะทำให้รู้ได้ไงว่าไฟล์ไหน
------------------------------------------------
หรือ เปิด แค่ 15 May, Data Portal แล้วกด ปุ่ม งวดวันที่ 15

ผมทำตรงหน้าจอให้เลือก source file แบบปุ่ม open ที่เรากดเวลาจะเลือกไฟล์ไม่เป็น
แต่ตรง copy ไม่น่ามีปัญหา

---------------------------
ผมพอจะทำ macro ที่ copy, paste formula ได้
แต่ว่าตรง ปุ่มเปิด, หน้าต่างเลือกไฟล์, ทำให้ macro รู้ได้เองว่า ต้องเอางวด 15 มาใส่งวดแรก ผมทำไม่ได้

รบกวนด้วยนะครับ
หรือถ้ามีแนวทางดี ๆ ก็เชิญเลยนะครับ

[torofish]

ภาพ

[snasui]

ผมไม่เจอว่ามี Code Macro อยู่ทั้่ง 3 ไฟล์ครับ

สำหรับการให้เลือกว่าจะเอาข้อมูลจากไฟล์ไหนมาวางนั้นคิดว่าพอมีทางให้ User เลือกเองได้

ช่วยทำไฟล์ตัวอย่างโดยเป็นตัวแทนของไฟล์ที่ใช้จริงแบบที่มี Code มาด้วย เผื่อว่าพอจะช่วยได้สำหรับการเพิ่มหรือลด Code ที่จะใช้้งานครับ

หรือหากใช้ Code จำนวนมาก อาจจะถามมาเป็นท่อน ๆ เฉพาะที่เป็นปัญหาก็ได้ครับ

[torofish]

สวัสดีครับ
ตอนนี้พบปัญหาสองจุดนะครับ

ในทุก ๆ เดือนชื่อ File มันจะเปลี่ยนไปเช่น
15 พฤษภา 2553, 15 มิถุนายน 2553

ปัญหาข้อ 1
File ที่เปิดอยุ่ตอนนี้มี Data Portal, 15 May 2010

Data Portal.XLS --> Module 1 --> Sub CopyInfo()
Windows("Data Portal.xls").Activate

ตรงนี้เราสามารถทำให้ excel รุ้เองได้มั้ยครับว่า ให้เลือกไฟล์ที่เปิดอีกไฟล์นึง
เช่น ActiveSheet = 15 May 2010
แสดงว่าก็ต้องเลือกอีกไฟล์นึงที่ไม่ใช่ตัวมัน ซึ่งก็คือ Data Portal.xls

ปัญหาข้อที่ 2
Data Portal.xls --> Module 1 --> Sub PasteFormula()

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'[vFile]Sheet1'!R2C1:R15C8,4,FALSE)"

ผมพบปัญหาตรงใส่ Vlookup จะทำยังไงให้ excel เปลี่ยนจากตัวแปร vFile --> กลายไปเป็นที่อยู่จริง ๆ เช่น
vlookup(a1,C:\Info\15 May 2010.xls,4,false)
ผมลองทำดู ปรากฎว่า ข้อมูลสามารถ ลิงค์ได้ก็จริ
แต่พอปิดแล้วเปิดใหม่ จะมีปัญหา Can't Find Source File
ปัญหาอีกข้อคือ สมมุติว่าทำหลาย ๆ เดือน
เรามี vFile ตัวแปรเดียว แบบนี้ข้อมูล งวดแรกMay
งวดแรกJune
ก็จะเปลี่ยนไปเรื่อย ๆ ตามไฟล์ที่ใช้ล่าสุด

[snasui]

ลองทดสอบ Code ข้างล่างนี้ ผมไม่ได้นำ Procedure ตัวที่สองมาด้วย เนื่องจากไม่ได้ปรับ Code ครับ

สำหรับ Procedure ตัวที่ 1 และ 3 ที่ปรับคือย้ายการประกาศ vFile มาไว้ด้านบน และ นำตัวแปรไปใ้ช้ในสูตร Vlookup

Code: Select all

Option Explicit
Dim vFile As Variant

Sub OpenMacro()
'    Dim vFile As Variant
    vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
    "*.xl*", 1, "Select Excel File", "Open", False)
    If TypeName(vFile) = "Boolean" Then
        Exit Sub
    End If
       Workbooks.Open vFile
    Call CopyInfo
    Call PasteFormula
    
End Sub

Sub PasteFormula()
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'" & vFile & "'!R2C1:R15C8,4,FALSE)"
    Range("E4").Select
End Sub

[torofish]

ขอบคุณครับ

มีอีกเรื่องนึงครับ
คนที่มาใหม่ของงวดหลัง
ถ้าคนที่มีชื่อในงวดต้นเดือนแล้ว ก็ให้ใส่ vlookup ได้เลย
แต่ถ้าคนที่มาใหม่ จะทำยังไงให้ Excel คัดลอกเลขบัตรประชาชนของคน ๆ นั้น
ไปต่อท้ายคนล่าสุดแล้วก็ใส่ vlookup

ที่ผมคิดไว้คือ
Copy คนใหม่มาไว้ที่ D Column
แล้วใส่ If เข้าไป
ถ้ามีคนซ้ำก็ Y ไม่ก็ N
แล้วค่อยใช้ Macro ให้ Copy เฉพาะคนที่มี N ไปวางไว้ข้างหลัง

1.) คุณ snasui พอจะมีวิธีที่ดีกว่าแนะนำมั้ยครับ

2.) ถ้าจะคงวิธีนี้ไว้ ผมมีปัญหาตรงสูตร vba เรื่อง Copy เป็น Range
Data Portal.xls --> Module 2 --> SUB TRYTESTING
มันดันไม่ Copy เป็น Range แต่จะ copy เฉพาะคน ๆ เดียว

-----------------------------

ตอนนี้ vlookup ใช้ได้แล้ว
แต่ว่าต้องแก้นิดนึง ถ้าทำตามที่คุณ snasui บอกคือ แยก Sub PasteFormula() แล้วไปประกาศตัวแปรข้างบน
vFile มันจะ error ว่าไม่มีค่า

ผมเลยขยับเอา PasteFormula ไปไว้ใน Sub OpenMacro ก็ใช้ได้แล้ว

มันใช้ไม่ได้เพราะว่ามันเกี่ยวกับการ set ค่าตัวแปร เป็น universal ทำนองนั้นหรือเปล่าครับ

[snasui]

ตอนนี้ vlookup ใช้ได้แล้ว
แต่ว่าต้องแก้นิดนึง ถ้าทำตามที่คุณ snasui บอกคือ แยก Sub PasteFormula() แล้วไปประกาศตัวแปรข้างบน
vFile มันจะ error ว่าไม่มีค่า

ผมประกาศตัวแปรไว้ข้างบน แล้วนำตัวแปรไปใช้ใน Procedure อื่น ๆ ที่ต้องใช้ตัวแปรเดียวกัน จะได้ไม่ต้องประกาศซ้ำ
สำหรับ Procedure หนึ่ง ๆ ควรทำงานเดียว เพื่อให้ง่ายต่อการตรวจแก้ไขครับ

ผมได้ทดสอบก่อนหน้านี้และกลับมาทดสอบใหม่ก็ยังไม่พบว่าเป็นปัญหา ซึ่ง Code ที่ให้มาก็เป็นการเรียกใช้ที่ต่อเนื่องกันมาตามลำดับ

ผมเลยขยับเอา PasteFormula ไปไว้ใน Sub OpenMacro ก็ใช้ได้แล้ว

มันใช้ไม่ได้เพราะว่ามันเกี่ยวกับการ set ค่าตัวแปร เป็น universal ทำนองนั้นหรือเปล่าครับ

ณ ตอนนี้ผมคิดว่าไม่น่าจะใช่ครับ เพราะผมทดสอบแล้วสามารถใช้งานได้

ส่วนอื่น ๆ หากว่างแล้วผมจะดูให้อีกทีรอบนะครับ

[snasui]

ผมเขียน Code มาให้ใหม่สำหรับ Copy ค่าจากไฟล์ที่เป็นงวดเดือน มาใส่ไฟล์ Data Portal โดยกำหนดตัวแปรให้กับช่วงข้อมูล

แต่ยังไม่ค่อย Clear สำหรับงวดแรกงวดหลัง เพราะเห็นว่าไฟล์ 15 May 2010 และ ไฟล์ 31 May 2010 ต่างก็มีงวดแรกงวดหลัง

คงต้องอธิบายกำกับพร้อมระบายสีมาให้เห็นชัดเจนว่า ต้องการนำข้อมูลใดมาลงในพื้นที่ใด ด้วยเงื่อนไขใด

ลองทดสอบ Code นี้ดูครับ

Code: Select all

Option Explicit

Sub OpenMacroFirstHalf()
    Dim vFile As Variant
    Dim rSource1 As Range
    Dim rSource2 As Range
    Dim rTarget1 As Range
    Dim rTarget2 As Range
    
    Set rTarget1 = Workbooks("Data Portal.xls").Sheets(1).Range("A65536") _
            .End(xlUp).Offset(1, 0)
    Set rTarget2 = Workbooks("Data Portal.xls").Sheets(1).Range("D65536") _
            .End(xlUp).Offset(1, 0)
            
    vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
    "*.xl*", 1, "Select Excel File", "Open", False)
    If TypeName(vFile) = "Boolean" Then
        Exit Sub
    End If
    
    Workbooks.Open vFile
    
    With activeworkbook.Sheets(1)
               Set rSource1 = .Range(.Range("A2"), .Range("A65536").End(xlUp))
               Set rSource2 = .Range(.Range("D2"), .Range("D65536").End(xlUp))
    End With
    
    rSource1.Copy rTarget1
    rSource2.Copy rTarget2
    
End Sub

ต้องค่อย ๆ ปรับไปเรื่อย ๆ ครับ

[torofish]

ขอบคุณครับ เดี๋ยวผมลองปรับดูครับ

[torofish]

สวัสดีครับ มีเรื่องมารบกวนอีกแล้วครับ

ผมคงใช้ Code ที่คุณ snasui ช่วยทำไม่ได้น่ะครับ
เพราะว่าอยากให้ข้อมูลมัน เชื่อมต่อกัน
เวลาจุดนึงแก้ อีกจุดนึงจะได้แก้ไปด้วย

ทีนี้ผมทำออกมาเสร็จแล้วครับ
แต่ว่ามีเรื่องอยากรบกวนให้ช่วยดูตรง
Module 4 --> ตรงช่วงของการคัดลอกข้อมูลซ้ำ
ว่ามันจะมีทางให้ปรับปรุงให้เร็วกว่านี้ได้มั้ย

Module 4 --> Sub CopyIfUnique()
คือ ผมลองหาทางตั้งแต่ Data Filter แต่จะเจอปัญหาว่า มันจะไม่รู้ว่าค่าแรกที่ควรคัดลอกอยู่ที่เท่าไหร่
เลยมาลงด้วยการ If, Loop แบบเดิม ๆ

แต่ปัญหามันก็จะอยู่ที่ว่า วิธีที่ผมใช้มันจะแปลก ๆ หน่อย แถมดูจะอ้อมไปอ้อมมา
แล้วก็ไปอ้างอิงกับ Count Variable ซึ่งกะไม่ได้เลยว่ามันควรจะใช้ที่เท่าไหร่ คือก็ทำงานได้ถ้าใส่มันไปเยอะ ๆ แต่ก็ยังไม่ดีที่สุดอยู่ดี

รบกวนช่วยหน่อยนะครับ

ปล ไฟล์ 15 May, 31 May คราวที่แล้วผมทำผิดเองครับ จริง ๆ แล้วแต่ละไฟล์มันต้องมีเงินเดือนแค่อันเดียว

ปล2 พอจะมีคำสั่งที่ทำให้ excel รู้เองมั้ยครับว่า ช่วงข้อมูลบนสุดถึงล่างสุดอยุ่ที่เท่าไหร่ เช่น A1:A30
ตอนนี้เวลาผมใส่ vlookup ผมต้องเผื่อไปเยอะ ๆ เลยเช่น A1:A300

[snasui]

สวัสดีครับ มีเรื่องมารบกวนอีกแล้วครับ

ผมคงใช้ Code ที่คุณ snasui ช่วยทำไม่ได้น่ะครับ
เพราะว่าอยากให้ข้อมูลมัน เชื่อมต่อกัน
เวลาจุดนึงแก้ อีกจุดนึงจะได้แก้ไปด้วย

อันนี้มองไม่ออกครับ การเชื่อมโยงถ้าหมายถึงว่าให้แสดงสูตรจำพวก Lookup เอาไว้นั้น ผมคิดว่าไม่จำเป็นต้องทำเช่นนั้นครับ เราสามารถนำค่าที่ตรงกันมาวางได้โดยไม่ต้องให้แสดงฟังก์ชั่น

ทีนี้ผมทำออกมาเสร็จแล้วครับ
แต่ว่ามีเรื่องอยากรบกวนให้ช่วยดูตรง
Module 4 --> ตรงช่วงของการคัดลอกข้อมูลซ้ำ
ว่ามันจะมีทางให้ปรับปรุงให้เร็วกว่านี้ได้มั้ย

Module 4 --> Sub CopyIfUnique()
คือ ผมลองหาทางตั้งแต่ Data Filter แต่จะเจอปัญหาว่า มันจะไม่รู้ว่าค่าแรกที่ควรคัดลอกอยู่ที่เท่าไหร่
เลยมาลงด้วยการ If, Loop แบบเดิม ๆ

แต่ปัญหามันก็จะอยู่ที่ว่า วิธีที่ผมใช้มันจะแปลก ๆ หน่อย แถมดูจะอ้อมไปอ้อมมา
แล้วก็ไปอ้างอิงกับ Count Variable ซึ่งกะไม่ได้เลยว่ามันควรจะใช้ที่เท่าไหร่ คือก็ทำงานได้ถ้าใส่มันไปเยอะ ๆ แต่ก็ยังไม่ดีที่สุดอยู่ดี

รบกวนช่วยหน่อยนะครับ

ปล ไฟล์ 15 May, 31 May คราวที่แล้วผมทำผิดเองครับ จริง ๆ แล้วแต่ละไฟล์มันต้องมีเงินเดือนแค่อันเดียว

ปล2 พอจะมีคำสั่งที่ทำให้ excel รู้เองมั้ยครับว่า ช่วงข้อมูลบนสุดถึงล่างสุดอยุ่ที่เท่าไหร่ เช่น A1:A30
ตอนนี้เวลาผมใส่ vlookup ผมต้องเผื่อไปเยอะ ๆ เลยเช่น A1:A300

การทำเช่นนี้สามารถใช้การบันทึก Macro ได้เ่ช่นกันครับ เริ่มตั้งแต่

1. บันทึก Macro การ Filter
2. กำหนดขอบเขตข้อมูลให้กับเซลล์ เช่น ให้เืลือกตั้งแต่ Sheet1!A1:Mn (n คือค่าสุดท้ายที่มีข้อมูล) สามารถเขียน Code ได้ดังนี้ครับ

Code: Select all

with worksheets("Sheet1")
   .range(.range("A1"),.range("M65536").end(xlup))
end with

3. เขียน Code ให้เลือกเฉพาะค่าที่ได้จากการ Filter สามารถบันทึก Macro โดยการคลุมข้อมูลที่ได้ แล้วกดแป้น F5 > Special > Visible Cells Only
หรือเขียน Code ได้ดังนี้

Code: Select all

.range(.range("A1"),.range("M65536") _
    .end(xlup)).SpecialCells(xlCellTypeVisible)

สามารถดูตัวอย่างที่คล้ายกันได้ที่นี่ครับ http://www.snasui.com/viewtopic.php?f=3 ... 1147#p1147

[torofish]

อันนี้มองไม่ออกครับ การเชื่อมโยงถ้าหมายถึงว่าให้แสดงสูตรจำพวก Lookup เอาไว้นั้น ผมคิดว่าไม่จำเป็นต้องทำเช่นนั้นครับ เราสามารถนำค่าที่ตรงกันมาวางได้โดยไม่ต้องให้แสดงฟังก์ชั่น

แต่แบบนี้ถ้ามีการแก้ไข เช่น ไฟล์ต้นทางเปลี่ยนเงินเดือน
ถ้าเราไม่ได้ใส่ vlookup เอาไว้ แบบนี้ไฟล์ปลายทางก็จะไม่มีการแก้ไขไม่ใช่เหรอครับ

[snasui]

อันนี้มองไม่ออกครับ การเชื่อมโยงถ้าหมายถึงว่าให้แสดงสูตรจำพวก Lookup เอาไว้นั้น ผมคิดว่าไม่จำเป็นต้องทำเช่นนั้นครับ เราสามารถนำค่าที่ตรงกันมาวางได้โดยไม่ต้องให้แสดงฟังก์ชั่น

แต่แบบนี้ถ้ามีการแก้ไข เช่น ไฟล์ต้นทางเปลี่ยนเงินเดือน
ถ้าเราไม่ได้ใส่ vlookup เอาไว้ แบบนี้ไฟล์ปลายทางก็จะไม่มีการแก้ไขไม่ใช่เหรอครับ

ถ้าไฟล์ต้นทางเปลี่ยนเงินเดือน ไฟล์ปลายทางจะไม่เปลี่ยนเงินเดือนเมื่อไม่มีการใส่สูตรนั้น เข้าใจถูกต้องแล้วครับ ถ้าจำเป็นต้องใช้ Vlookup ก็สามารถใช้ได้ครับ

ปกติการใช้ VBA เพื่อทำข้อมูลเป็น Database นั้น หากมีการเปลี่ยนแปลงเพิ่มหรือลดจะมีการนำข้อมูลที่เปลี่ยนแปลงเข้ามาอีกรอบเพื่อปรับกับข้อมูลเดิมครับ เพื่อจะได้ติดตามได้ว่ามีการเปลี่ยแปลงด้วยรายการใด วันที่เท่าไร โดยใคร ฯลฯ ซึ่งก็ขึ้นอยู่กับความจำเป็นของการใช้งาน แต่กรณีนี้ดูเหมือนว่าสามารถแก้ไขข้อมูลต้นแหล่งแล้วปลายทางเปลี่ยนตามไปด้วย ถ้าไม่เขียน Code สำหรับ Update ข้อมูล ก็ต้องใช้ฟังก์ชั่น Lookup ค่าปัจจุบันมาวางครับ