รบกวนถามปัญหา offset+countA ครับ

[kamahltew]

ออกตัวก่อนครับ ผมศึกษา(VBA)แบบครูพักลักจำ

ปัญหาจึงออกมาเป็นแบบนี้ครับ
- กด Add model แล้วมัน Error ที่ Application.Goto Reference:="AllData" ครับ

1.ตามข้อมูลที่แนบมา ผมจะ InputData ไปที่ Sheet (AllData)
2.โดยหลังจากที่กดปุ่ม Add Model แล้วให้ข้อมูลทั้งหมดไปโผล่ที่ AllData และ Clearcontents ทั้งหมด
3.หลังจากนั้นทุกครั้งที่กด Add Model แล้วให้ข้อมูลทั้งหมดไปต่อแถวสุดท้ายทุกครั้งครับ
3.ผมได้กำหนดชื่อ Name > Define name ไว้ด้วย

ไม่ทราบแก้ไขอย่างไรดี

[snasui]

เปลี่ยนสูตรที่ให้กับ AllData เป็นตามด้านล่างครับ

=OFFSET(Reference,COUNTA(AllData!$A:$A),0)

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

[kamahltew]

ขอบคุณมากเลยครับงมหาทั้งวันเลย T T

รบกวนอีกสักข้อครับ

สูตรนี้สามารถเพิ่มได้เรื่อยๆเท่ากับจำนวนแถวที่มีคือ 65536 เลยใช่ไหมครับ

[snasui]

สูตรสามารถทำงานได้ตลอดเวลาจนกว่าจะวางข้อมูลจนเต็มบรรทัดสุดท้ายของชีทแล้วครับ

[kamahltew]

ขอบคุณอีกครั้งครับ

ได้เอาไปใช้แล้ว

[kamahltew]

จากลองใช้ไปสักพัก ผมพบปัญหาว่า เมื่อ Add Model แล้ว

ถ้าเป็นข้อมูลซ้ำกันมันก็จะถูกคีย์ลงไป จะหาทางป้องกันอย่างไรดีครับ

ต้องใช้ VBA อีกหรือเปล่า เพราะลองทำตามโดยใช้ Data Validation > Custom > Formular พิมพ์ Countif คลุมช่วง $A$2:$A$800

แต่เข้าใจว่าวิธีด้านบนใช้ไม่ได้เพราะไม่เกี่ยวข้องกันครับ

ปล.
ผมลองที่จะใส่สูตรใน sheet "InputData" เพื่อให้ Countif ข้อมูลที่ sheet "AllData" แต่โปรแกรมปฏิเสธ มันไม่ให้ใส่ข้าม sheet ครับ

[snasui]

กรณีที่ Model แต่ละค่าต้องไม่เหมือนกับที่เคยบันทึกลงใน AllData แล้ว สามารถใช้ Validation มาดักตอนที่กรอกข้อมูลได้ครับ โดย

คลุมข้อมูลที่ B4:B12 > เข้าเมนู Data > Validation > ช่อง Allow เลือก Custom > ช่อง Formula คีย์ =COUNTIF(INDIRECT("Alldata!A:A"),$B14)=0

หากมีค่าในชีท AllData แล้วจะไม่สามารถคีย์ได้ครับ

[kamahltew]

กรณีที่ Model แต่ละค่าต้องไม่เหมือนกับที่เคยบันทึกลงใน AllData แล้ว สามารถใช้ Validation มาดักตอนที่กรอกข้อมูลได้ครับ โดย

คลุมข้อมูลที่ B4:B12 > เข้าเมนู Data > Validation > ช่อง Allow เลือก Custom > ช่อง Formula คีย์ =COUNTIF(INDIRECT("Alldata!A:A"),$B14)=0

หากมีค่าในชีท AllData แล้วจะไม่สามารถคีย์ได้ครับ

ขอบคุณครับ ตอนนี้ได้แล้วครับ

ตอนนี้ที่ผมหาสูตรมาลองใช้เองคือ "ClearEntries" B3:B10 ใน Sheet "InputData"
ซึ่งใช้ได้ครับแต่อยากทราบความหมายมากกว่า

ดังนี้ครับ

Sub ClearEntries()
On Error GoTo noName 'error handler
'you can use the word "numbers" or any other range name in the next line
Application.Goto Reference:="ClearData"
Selection.ClearContents
Range("B3:B10").Select
Exit Sub
noName: 'only fires on an error
MsgBox "The named range you are calling does not exist." & vbCr & _
"You need to name a range as 'numbers' before proceeding.", _
vbOKOnly + vbInformation, "Named range required"
End Sub

ตาม Code ผมคิดว่าบางข้อความมันไม่จำเป็นต้องมีแต่เค้าใส่มาเพื่ออธิบายผู้ใช้มากกว่า

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

[snasui]

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

ส่วนคำอธิบายผู้ใช้จริง ๆ ปกติจะไม่เขียนใน Code หากจะมีก็จะเป็นพวกกล่องโต้ตอบต่าง ๆ เช่น MsgBox

ตาม Code ด้านล่างผมแนบคำแปลภาษาไทยเข้าไปด้วย

Code: Select all

Sub ClearEntries()
On Error GoTo noName 'error handler (การจัดการกับ Error)
'you can use the word "numbers" or any other range name in the next line
'(ท่านสามารถใช้คำว่า "numbers" หรือช่วงเซลล์อื่น ๆ ในบรรทัดถัดไป)
Application.Goto Reference:="ClearData" 'ไปยังข้อมูลที่ชื่อ ClearData
Selection.ClearContents ' ทำการ Clear เฉพาะค่าภายในตำแหน่งที่เลือก
Range("B3:B10").Select 'เลือกช่วงเซลล์ B3:B10
Exit Sub 'ออกจากการ Run code
noName: 'only fires on an error (กรณีไฟล์เกิดค่าผิดพลาดเท่านั้น)
MsgBox "The named range you are calling does not exist." & vbCr & _
You need to name a range as 'numbers' before proceeding., _
vbOKOnly + vbInformation, "Named range required" 'แสดงข้อความเตือนว่า
' "The named range you are calling does not exist."
' "You need to name a range as 'numbers' before proceeding."
End Sub

หากจะให้เหลือเฉพาะ Code จริง ๆ ก็เหลือตามด้านล่างครับ

Code: Select all

Sub ClearEntries()
On Error GoTo noName
Application.Goto Reference:="ClearData"
Range("B3:B10").ClearContents
Exit Sub
noName:
MsgBox "The named range you are calling does not exist." & vbCr & _
"You need to name a range as 'numbers' before proceeding.", _
vbOKOnly + vbInformation, "Named range required"
End Sub

[kamahltew]

ขอบคุณมากครับ เริ่มเข้าใจอะไรหลายๆอย่างแล้ว

สุดท้ายแล้วครับเกี่ยวกับไฟล์นี้ (ยังไม่รู้จะถามอะไรเพิ่มแล้วครับ)

ตาม Code "CopyTAllData"

Sub CopyToAllData()
Application.Goto Reference:="AddModel"
Selection.Copy
Application.Goto Reference:="AllData"
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select <== ทำไมต้องเป็น Cell "A1" ครับเป็นอย่างอื่นได้หรือไม่เพราะเห็นหลายๆแหล่งที่เห็นก็จะเป็นแบบนี้ครับ
Sheets("InputData").Select
Range("B14:G27").Select
Selection.ClearContents
Range("B14").Select
Application.ScreenUpdating = True
End Sub

[snasui]

จำเป็นหรือไม่อยู่ที่ว่าต้องการให้ผู้ใช้งานทำอะไรต่อครับ

ปกติการเลือก A1 เพราะว่าส่วนใหญ่จะสร้าง Form หรือพื้นที่การทำงานให้เริ่มจากมุมซ้ายบนของ Worksheet ต่าง ๆ เสมอ เมื่อทำงานตามคำสั่งด้วย VBA แล้วก็กำหนดให้เลือก A1 เอาไว้เพื่อพร้อมสำหรับเริ่มงานในรอบต่อไปได้เลย

ทั้งนี้เนื่องจากการทำงานตาม Code อื่น ๆ Cursor จะย้ายไปที่อื่นตามคำสั่งต่าง ๆ เมื่อต้องการให้เริ่มใหม่ก็ระบุให้มาเลือก A1

แต่สำหรับ Code ที่ส่งมาถามนี้ ไม่จำเป็นต้องเลือก A1 เพราะเลือกแล้วก็ไม่ได้ทำอะไรกับ A1 แต่อย่างใด สามารถใส่เครื่องหมาย ' เพื่อให้เป็น Comment แล้วทดสอบ Run Code เพื่อดูผลได้ครับ

[kamahltew]

จำเป็นหรือไม่อยู่ที่ว่าต้องการให้ผู้ใช้งานทำอะไรต่อครับ

ปกติการเลือก A1 เพราะว่าส่วนใหญ่จะสร้าง Form หรือพื้นที่การทำงานให้เริ่มจากมุมซ้ายบนของ Worksheet ต่าง ๆ เสมอ เมื่อทำงานตามคำสั่งด้วย VBA แล้วก็กำหนดให้เลือก A1 เอาไว้เพื่อพร้อมสำหรับเริ่มงานในรอบต่อไปได้เลย

ทั้งนี้เนื่องจากการทำงานตาม Code อื่น ๆ Cursor จะย้ายไปที่อื่นตามคำสั่งต่าง ๆ เมื่อต้องการให้เริ่มใหม่ก็ระบุให้มาเลือก A1

แต่สำหรับ Code ที่ส่งมาถามนี้ ไม่จำเป็นต้องเลือก A1 เพราะเลือกแล้วก็ไม่ได้ทำอะไรกับ A1 แต่อย่างใด สามารถใส่เครื่องหมาย ' เพื่อให้เป็น Comment แล้วทดสอบ Run Code เพื่อดูผลได้ครับ

ขอบคุณมมากครับ Clear แล้วครับ

[kamahltew]

ขอโทษที่กลับมาถามอีกรอบครับ

คือปัญหามันมีในไฟล์ครับ ผมใส่กรอบอธิบายไว้แล้ว

ปล.ผมไม่สามารถแนบไฟล์ได้ครับเพราะขนาดมันใหญ่กว่า300 kbไม่ทราบเป็นเพราะอะไร
ทั้งๆที่ไม่มีรูป มีแต่ข้อความอย่างเดียว หรือว่าเป็นเพราะ VBA

[snasui]

กรณีขนาดไฟล์เกิน 300 โดยที่ไม่มีข้อมูลอื่นใด ลองคลิกแต่ละชีทแล้วกดแป้น Ctrl+End เพื่อดูว่าเซลล์สุดท้ายอยู่ที่ใด หากอยู่ในตำแหน่งที่ไม่มีข้อมูลก่อนหน้าทั้งทางบรรทัดและทางคอลัมน์ ให้ทำการลบบรรทัดและคอลัมน์ว่าง ๆ เหล่านั้นทิ้ง โดยเริ่มลบทั้งบรรทัด (เลือกหัวบรรทัดแล้วคลิกขวา > Delete) ตั้งแต่บรรทัดหลังจากมีข้อมูลจนถึงบรรทัดสุดท้าย ทางคอลัมน์ก็เช่นกันครับ จากนั้นกดแป้น Ctrl+Home แล้ว Save แล้วลองดูขนาดไฟล์ใหม่อีกรอบ

กรณีตอบแบบไม่เห็นไฟล์ที่เป็นปัญหาก็วินิจฉัยลำบาก อาจจะไม่ตรงประเด็นครับ

[kamahltew]

ได้แล้วครับ แนบไฟล์ให้ดูด้วย

ปัญหาคือผมได้ คีย์ข้อมูลใส่ sheet "InputData" (B14:B22) ผมใช้ Validation > Custom >ไว้ครับ
จากนั้นมันจะฟ้องตลอด
ว่ามีการคีย์ซ้ำ ทั้งๆที่ ไม่มีข้อมูลชุดนั้นอยู่ใน sheet "AllData"

[snasui]

เครื่องหมายเท่ากับ (=) ใน Validation หายไป จะต้องเป็นสูตรตามด้านล่างครับ

=COUNTIF(INDIRECT("Alldata!A:A"),$B14)=0

[Bafnet]

สวัสดีครับ
ได้ทดสอบไฟล์แล้วครับใช้ได้ครับและได้ปรับสูตร
ในการค้นหาข้อมูลให้ดูสวยงามขึ้นไม่ให้โชว์ค่า Err

ที่บอกว่าใส่ค่าอะไรไปแล้วมันไม่ผ่านให้อาจเป็นเพราะ

กรณีที่ Model แต่ละค่าต้องไม่เหมือนกับที่เคยบันทึกลงใน AllData แล้ว สามารถใช้ Validation มาดักตอนที่กรอกข้อมูลได้ครับ โดย

คลุมข้อมูลที่ B4:B12 > เข้าเมนู Data > Validation > ช่อง Allow เลือก Custom > ช่อง Formula คีย์ =COUNTIF(INDIRECT("Alldata!A:A"),$B14)=0

ลองเปลี่ยนไปคลุมที่ B14:B22 แล้วทำตามขั้นตอนที่อาจารย์แนะนำนะครับ
ลองดูไฟล์แนบนะครับ

Maxload Data.xls

อืม..คำสั่งในmodul Debug นะครับ

Code: Select all

Sub CopyToAllData()
    Application.Goto Reference:="AddModel"
     Selection.Copy
    Application.Goto Reference:="AllData"
    ActiveSheet.Paste
    Application.CutCopyMode = False
     Range("A1").Select
    Sheets("Report").Select
     Range("B14:G27").Select
    Selection.ClearContents
     Range("B14").Select
    Application.ScreenUpdating = True
End Sub

เปลี่ยน Sheets("Report").Select
เป็นSheets("InputData").Select

[kamahltew]

ขอบคุณพี่ๆทุกคนมากครับที่สละเวลา

ตอนนี้ใช้ได้เหมือนเดิมแล้วครับ