Snasui Excel Forum

อยากให้ ค่าที่มีอยู่ใน Sheet2 เอามาใส่ใน Sheet1
โดยที่ตัวแปรจะเปลี่ยนไปเรื่อยๆ ค่าเริ่มต้นก้อเปลี่ยนไปด้วย
และมีหลายเงื่อนไขเข้ามาเกี่ยวข้อง
รบกวนเขียนCode ให้ด้วยค่ะ

Sheet2 คือชีทไหน Sheet1 คือชีทไหนครับ

การอ้างอิงควรให้ตรงกับไฟล์แนบด้วยนะครับ จะได้เข้าึถึงข้อมูลได้โดยง่าย

คงต้องค่อย ๆ ถามตอบไปนะครับ

จากข้อความด้านล่าง

StartlearningVBA wrote:ต้องการให้ ค่าใน Colum "DOH" ไม่ต่ำกว่า3

แต่ข้อมูลเก็บเป็น Text บ้างเก็บเป็นค่าผิดพลาดบ้างเก็บเป็น Number บ้างการตรวจสอบค่าจะเพี้ยนได้นะครับ ทั้งนี้เพราะ "0" > 3 ครับ (ศูนย์ในเครื่องหมายคำพูดเป็นข้อความส่วน 3 เป็นตัวเลข) นี่เป็นแค่ตัวอย่างครับ

Sheet1 คือ sheet"data1" ค่ะ
Sheet2 คือ sheet"box" ค่ะ
ส่วน "DOH" เป็นหัวข้อ ใน Colum"N","R","V" เป็นช่วงๆ ไป ซึ่งถือเป็นเงื่อนไขแรก
และใน colum นี้จะใส่สูตรไว้ค่ะ ถ้าเอาตัวเลข จาก Sheet "box" ใน Colum "G" ที่ Code เดียวกัน
มาใส่ใน Sheet"data1" colum"L"(Del) ตัวเลขใน Colum"N"(DOH) ก็จะเปลี่ยนไปค่ะ
ทำจนกว่า ตัวเลขใน Colum"N","R","V" (DOH) มีค่ามากว่า 3 ค่ะ
เพราะใช้ Code เป็นตัวเชื่อม ระหว่าง 2 Sheet ค่ะ
ไม่แน่ใจว่า จะ งง หรือเปล่าคะ

StartlearningVBA wrote:Sheet1 คือ sheet"data1" ค่ะ
ถ้าเอาตัวเลข จาก Sheet "box" ใน Colum "G" ที่ Code เดียวกัน
มาใส่ใน Sheet"data1" colum"L"(Del) ตัวเลขใน Colum"N"(DOH) ก็จะเปลี่ยนไปค่ะ
ทำจนกว่า ตัวเลขใน Colum"N","R","V" (DOH) มีค่ามากว่า 3 ค่ะ
เพราะใช้ Code เป็นตัวเชื่อม ระหว่าง 2 Sheet ค่ะ
ไม่แน่ใจว่า จะ งง หรือเปล่าคะ

ผมจะเว้นคำอธิบายในไฟล์ไปก่อนนะครับ เดี๋ยวจะสับสน จากที่อธิบายมาตามด้านบน ขอถามว่า ด้วยวิธีการทำงานปกติ นำค่าใน Code เดียวกันมาใส่ด้วยวิธีใดครับ

ต้องขอโทษด้วยค่ะ ที่มาตอบกระทู้ช้าไปหน่อยค่ะ เนื่องจากติดภาระกิจหลายอย่าง
ถ้าทำแบบ manual จะเริ่มจาก การ
1.ใน sheet "data1" เลือก filter ที่แถวที่ 5 แล้ว Sort by A to Z ที่ Colum"n"
จากนั้นให้เลือก ค่าที่น้อยที่สุด ซึ่งบางครั้งอาจมีซ้ำกันมากกว่า1 เลือกมาค่าใดค่าหนึ่ง ปกติจะเลือกแถวบนสุด
2. เลือกเอา Code ใน Colum"B" แถวบนสุดมา สมมุติว่า เป็น "B7" = 96628634 เอาค่านี้ไปหาในขั้นตอนต่อไป

อันนี้เป็นคำอธิบายจากไฟล์ที่แนบมา

StartlearningVBA wrote:แล้วเอาคCODE ใน Colum"B"ทีอยู่ในแถวเดียวกัน ไป find หา
ใน Sheet "box" เพื่อต้องการทราบว่า Code นี้ มีใน Box No ไหนบ้าง
จำนวนเท่าไหร่ เมื่อ Box ถูกใช้แล้ว จะใช้ซ้ำอีกไม่ได้ เอาหมายเลข Box มาเก็บไว้ในSheet "Result" ตามวัน ให้ตรงกับหัวบนคอลัมน์

ที่ชีท Box ไปหาจากคอลัมน์ E ซึ่งเป็น Code เหมือนกันใช่หรือไม่ครับ ชีท Result ที่อ้างถึงน่าจะเป็นชีท BoxList(Result) ตามไฟล์ที่แนบมา

การหาให้หาด้วย Code แต่การนำไปใช้ให้นำหมายเลข Box ไปใช้ และเมื่อนำหมายเลข Box ไปใช้แล้วให้ลบทิ้ง หากมีซ้ำกันหลายหมายเลขก็ลบทิ้งทุกหมายเลข

คำถาม แล้วจะทราบได้อย่างไรว่าหมายเลข Box ที่ได้นั้นต้องวางในคอลัมน์ใดในชีท BoxList(Result)

ใน sheet "data1" แถวที่ 5 จะเป็นหัวข้อ บอกไว้ colum"K" คือ Day1
colum"O" =Day2, colum"S"=Day3 ถ้าเอาจำนวนมาใส่ใน colum"L" นั่นหมายถึง
เป็นช่วง Day1
แล้วมาดูใน Sheet"BoxList(Result)" ก็เอาหมายเลข box มาใส่ให้ตรงหัวข้อวัน เช่น
ใส่วันแรก Day 1 ก็ใส่ใน Colum"B" แล้วก็เรียงต่อไปเรื่อยๆ box ไหนมาก่อน
ก็ให้อยู่บนสุดแล้วต่อลงมาเรื่อยๆ ถ้าเปิดในวันที่ 2 ก็เอามาใส่ใน Colum"C" ค่ะ

เมื่อได้ box มาเปิดแล้ว มาดูรายละเอียดว่า box นั้น มีกี่ Code อะไรบ้าง จำนวนเท่าใด สมมุติว่ามี 10 code (โดยการฟิวเตอร์ที่ box นั้น) เอาทั้ง 10 code นั้นไปเช็คใน sheet "data1"
ให้ยึดที่ sheet "data1" เป็นหลัก ถ้ามี code ไหน ตรงกับใน sheet "data1" ให้เอาปริมาณที่ได้จาก sheet "box" ตาม code เดียวกัน มาบวกเพิ่ม ใน colum"L" ของ sheet "data1" ถ้า code ไหนไม่ตรง ก็ทิ้งไป สมมุติว่าตรงกัน 7 Code ก็ ใช้แค่ตัวเลข ปริมาณบวกเข้าไปใน colum"L" ของ sheet "data1" แค่ 7 code เท่านั้น
แล้วก็ทำอย่างนี้ไปเรื่อยๆ จนกว่า Colum"N" จะได้ค่ามากกว่า 3 แล้วค่อยย้ายไปทำช่วงที่2 (หรือ วันที่2) ในช่วง Colum "P:R" หรือจนกว่า ข้อมูลใน Sheet"box" หมด โดยส่วนตัวถ้าใช้แล้ว Box ไหนแล้ว จะ ลบทิ้งไปเพื่อป้องการการใช้ซ้ำค่ะ

StartlearningVBA wrote:ใน sheet "data1" แถวที่ 5 จะเป็นหัวข้อ บอกไว้ colum"K" คือ Day1
colum"O" =Day2, colum"S"=Day3 ถ้าเอาจำนวนมาใส่ใน colum"L" นั่นหมายถึง
เป็นช่วง Day1
แล้วมาดูใน Sheet"BoxList(Result)" ก็เอาหมายเลข box มาใส่ให้ตรงหัวข้อวัน เช่น
ใส่วันแรก Day 1 ก็ใส่ใน Colum"B" แล้วก็เรียงต่อไปเรื่อยๆ box ไหนมาก่อน
ก็ให้อยู่บนสุดแล้วต่อลงมาเรื่อยๆ ถ้าเปิดในวันที่ 2 ก็เอามาใส่ใน Colum"C" ค่ะ

StartlearningVBA wrote:เมื่อได้ box มาเปิดแล้ว มาดูรายละเอียดว่า box นั้น มีกี่ Code อะไรบ้าง จำนวนเท่าใด สมมุติว่ามี 10 code (โดยการฟิวเตอร์ที่ box นั้น) เอาทั้ง 10 code นั้นไปเช็คใน sheet "data1"
ให้ยึดที่ sheet "data1" เป็นหลัก ถ้ามี code ไหน ตรงกับใน sheet "data1" ให้เอาปริมาณที่ได้จาก sheet "box" ตาม code เดียวกัน มาบวกเพิ่ม ใน colum"L" ของ sheet "data1" ถ้า code ไหนไม่ตรง ก็ทิ้งไป สมมุติว่าตรงกัน 7 Code ก็ ใช้แค่ตัวเลข ปริมาณบวกเข้าไปใน colum"L" ของ sheet "data1" แค่ 7 code เท่านั้น
แล้วก็ทำอย่างนี้ไปเรื่อยๆ จนกว่า Colum"N" จะได้ค่ามากกว่า 3 แล้วค่อยย้ายไปทำช่วงที่2 (หรือ วันที่2) ในช่วง Colum "P:R" หรือจนกว่า ข้อมูลใน Sheet"box" หมด โดยส่วนตัวถ้าใช้แล้ว Box ไหนแล้ว จะ ลบทิ้งไปเพื่อป้องการการใช้ซ้ำค่ะ

ผมอ่านแล้วยังไม่ Clear คิดว่าน่าจะเปลี่ยนวิธีการในการแก้ปัญหาเพื่อสามารถเดินหน้าไปได้ โดยให้คุณ Record Macro มาทีละขั้นตอนทีละงาน ไม่ควรทำการ Record มารวดเดียวตั้งแต่ต้นจนจบเหมือนที่ทำมา ผมจะได้ทดสอบ Code ที่ได้บันทึกมาดูว่าทำงานอย่างไร มีขั้นตอนใดที่ต้องการ Loop ก็จะได้เขียนเพิ่มกันในภายหลัง สำหรับ Code ที่ได้บันทึกมาแล้วนั้นไม่สามารถติดตามตรวจสอบได้เนื่องจาก Run แล้วติดขัดครับ

ก็ กลัวจะงง อยู่เหมือนกันค่ะ
เดี๋ยวจะ Record ไปให้ดูนะคะ
เพราะว่า หลายขั้นตอนมากเหมือนค่ะ

อันดับแรกจะเพิ่ม Sheet ขึ้นมา 3 Sheet ดังนี้
1. sheet"TempBox" เพื่อพักข้อมูลรายละเอียดใน Box
2. sheet"TempQty" เพื่อพักข้อมูลรายละเอียดของปริมาณ
3. sheet"BoxList" เพื่อใส่ผลลัพท์ที่ต้องการ และใน sheet นี้ ก็จะใส่หัวข้อไว้รอ
ดังรายละเอียดข้างล่างค่ะ

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "TempBox"
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "TempQty"
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "BoxList"
Range("B1").Value = "Box open Day1"
Range("C1").Value = "Box open Day2"
Range("D1").Value = "Box open Day3"

จากนั้น มาดูเงื่อนไขแรก คือ ต้องการเอาค่าที่ติดลบมากที่สุดมาทำก่อน โดยดูที่ Sheet "data1"
ติดฟิวเตอร์ก่อนที่แถวที่ 5 แล้วทำการ Sort by A to Z ใน Colum "N"
เลือก ค่า Code ในช่อง B6 คือแถวแรกเพราะติดลบมากสุด (แต่ตามตัวอย่างขอเลื่อนไป B7 เพราะ เดี๋ยวพอไป sheet"box" จะมองเป็นภาพไม่ชัดเจน)

Sheets("data1").Select
Rows("5:5").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("data1").AutoFilter.Sort.SortFields.Add Key:=Range( _
"N5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("data1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

เมื่อได้ Code มาแล้ว Copy code นั้นไปหา find หาใน sheet"box"

Range("B7").Select
Selection.Copy
Sheets("box").Select
Rows("1:1").Select
ActiveSheet.Range("$A$1:$Y$100").AutoFilter Field:=5, Criteria1:= _
"=96628634", Operator:=xlAnd (อันนี้ฟิวเตอร์หา code ใน colum"e")
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("box").AutoFilter.Sort.SortFields.Clear (แล้วเคลียร์ฟิวเตอร์ออกมาก่อน)
ActiveWorkbook.Worksheets("box").AutoFilter.Sort.SortFields.Add Key:=Range( _
"J1:J100"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("box").AutoFilter.Sort (จากนั้น มาทำการ Sort by A to Zที่ colum"j")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
จากนัั้น เลือก Box ที่อยู่บนสุด คือ "M53" ใน Sheet "box" นี่แหละ
Range("M53").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFilter (จากนั้นทำการ ฟิวเตอร์ เอาเฉพาะ Box no. นี้ มา)
Selection.Copy
ActiveSheet.Range("$A$1:$Y$100").AutoFilter Field:=13, Criteria1:= _
"=FCIU8103005", Operator:=xlAnd
Range("A1").Select (จากนั้น ทำการ Copy ข้อมูที่โชว์ทั้งหมดนี้ ไปวางใน Sheet "TempBox"คือจะได้ code ทั้งหมดที่มีอยู่ใน box นั้นมา)
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

จากนั้น มาที่ Sheet "TempBox" เพื่อวาง ข้อมูลของ Box ที่ Copy มา โดยเลือกวางแบบ PasteSpecial เลือก Values

Sheets("TempBox").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("M2").Select (คือ copy หมายเลข box มา)
Application.CutCopyMode = False
Selection.Copy

แล้วเอาหมายเลข Box นั้นมาวาง ใน Sheet "BoxList" ตรงช่อง B2 (ถ้าได้ box ที่ 2 มา ก็มาวางใน B3 ถัดลงมาเรื่อย)
Sheets("BoxList").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

ช่วยส่งไฟล์ตัวอย่างที่มี Code ล่าสุดอยู่แล้วมาด้วยครับ

ยังไม่หมดนะคะ เหลือต่ออีกนิด พอดีมีงานด่วนเข้ามาค่ะ เลยโพสไม่เสร็จ

แต่ใช้ ไฟล์เดิมที่ส่งไปให้นั่นแหละค่ะ มา Run ต่อค่ะ

ผมเองก็งานมหาศาลเช่นกัน เอาเป็นว่าเสร็จเมื่อไรก็ส่งไฟล์มาก็แล้วกัน ดีกว่าให้ผมมาต่อ code เองครับ

จากนั้น กลับไปที่ Sheet "data1" ทำการแทรกขึ้นมาอีก 2 Colum ต่อจาก colum"L"
จะได้ Colum"M"และ "N" มา เพื่อเอามาไว้ บวก ตัวเลข ในช่องของ Colum "L"
จะได้ code vba ดังนี้ค่ะ

Sheets("data1").Select
Selection.AutoFilter (จะทำการ เคลียร์ filter ออกไปก่อน)
Columns("M:N").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("M6").Select (มาทำการ Vlookup Code ที่เหมือนกันให้เอาตัวเลข ปริมาณมาใส่ โดย Vlookup ที่ Sheet"data1" กับSheet"Tempbox)
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],TempBoxNo!R2C5:R[2]C[-6],3,0)"
Selection.AutoFill Destination:=Range("M6:M50")
Range("M6:M50").Select (เนื่องจากค่าที่ได้เป็นสูตร Vlookup จะบวกลบ กันไม่ได้ จึงต้องทำการ Copy ข้อมูลไปไว้ที่ Sheet"TempQty" โดยเลือกวาง PasteSpecial เลือก Values)
Selection.Copy
Sheets("TempQty").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter (ทำการ Filter เลือกเอาเฉพาะค่าที่ติด #N/A)
ActiveSheet.Range("$A$2:$A$46").AutoFilter Field:=1, Criteria1:="#N/A" ( จากนั้น ทำการเปลี่ยน#N/A ให้เป็น 0 แล้วเครียร์ฟิวเตอร์)
Range("A3").Select
ActiveCell.FormulaR1C1 = "0"
Range("A3").Select
Selection.FillDown
Selection.AutoFilter
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy (ทำการ Copy ข้อมูลทั้งหมดกลับมาวางแทนที่ ค่าเดิมใน Sheet"data1" ใน Colum"M" ตั้งแต่ "M6" ลงไป)
Sheets("data1").Select
Range("M6").Select
ActiveSheet.Paste
Range("N6").Select (จากนั้น มาที่ Colum"N" ที่ Range"N6" ทำการ เอาค่ามาบวกกัน ระหว่างค่าเดิมใน Colum"L" กับค่าใหม่ที่หามาได้ใน "M" )
Range("N6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]" (N6 คือสูตร =L6+M6 ไล่ลงมาเรื่อย)
Range("N6").Select
Range(Selection, Selection.End(xlDown)).Select (เนื่องจากเป็นค่าที่ติดสูตร จึงต้องทำการ copy แล้วPasteSpecial เลือกValues)
Selection.Copy
Range("L6").Select (จากนั้นเอาค่าไปวางแทนที่เดิมใน Colum"L" เพื่อแทนค่าเดิม)
ActiveSheet.Paste
Columns("M:N").Select (จากนั้นทำการ Delete colum"M","N" ออกไปเพราะใช้เสร็จแล้ว และต้องการดูค่า ใน Colum"M" ที่เปลี่ยนไปตามเงื่อนไขแรก)
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

จากนั้น กลับมาที่ Sheet"box" ซึ่งจะยังค้างหน้าจอที่ติด filter ของ box ที่นำไปเปิดอยู่ ในหน้านี้จะ Delete ข้อมูล Code และ Box no. ออก
เพราะป้องกันการใช้ซ้ำ หรือจะ Delete ออกทั้งแถวเลยก็ได้ค่ะ และที่ Sheet"TempBox" และ "TempQty" ก็จะลบข้อมูลออกเหมือนกันเพื่อที่ว่า
เมื่อมาทำการ Run รอบถัดไป ข้อมูลจะได้ไม่ผิด เป็นอันเสร็จไป 1 รอบ แล้วกลับไปเช็คเงื่อนไขแรก อีกรอบ ทำจนกว่า Colum "M" ใน Sheet "data1" จะได้ค่ามากกว่า 3 ค่ะ

Sheets("box").Select
Range("E10:E99").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("M10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("E34").Select
Selection.AutoFilter
Range("E3").Select

ถ้าผิดพลาดตรงส่วนไหนต้องขออภัยด้วยนะคะ ได้ส่ง ไฟล์ พร้อม Code มาให้แล้วค่ะ
ถ้ายังสงสัยตรงส่วนไหน บอกได้นะคะ

การบันทึก Macro ควรบันทึกเป็นท่อน ๆ ครับเพื่อให้ง่ายต่อการปรับปรุง ผมทำตัวอย่างมาให้ดูตามไฟล์แนบ

สำหรับเรื่องการ Loop หากมีเวลาจะช่วยดูให้ครับ