ผมต้องการ loop range name ให้สั้นลง ผมต้องปรับโค้ดยังไงครับ

[10idlnw]

1.ผมมี Range Names หลายตัวมาก เลยจะใช้วิธีวนลูปเอา จะได้สั้นลง ผมตั้งปรับโค้ดอย่างไรครับ
2.กรณีวนลูป Range Names สามารถเลือกเอาเฉพาะกลุ่มที่ต้องการได้ไหมครับ เช่น วนเฉพาะกลุ่มที่ขึ้นต้นด้วยตัว A* หรือเฉพาะกลุ่มที่ขึ้นต้นด้วยตัว B* เป็นต้นครับ

Code: Select all

    If Target.Address = Range("AI_Model").Address Then
            msgbox [AI_Model]
    End If
    
    If Target.Address = Range("AI_CarID").Address Then
            msgbox [AI_CarID]
    End If
    
    If Target.Address = Range("BI_CaseIns").Address Then
            msgbox [BI_CaseIns]
    End If
        
    If Target.Address = Range("BI_FC").Address Then
            msgbox [BI_FC]
    End If

[snasui]

ตัวอย่างการใช้ If...Eleif...EndIf ครับ

Code: Select all

If Target.Address = Range("AI_Model").Address Then
     MsgBox [AI_Model]
ElseIf Target.Address = Range("AI_CarID").Address Then
     MsgBox [AI_CarID]
ElseIf Target.Address = Range("BI_CaseIns").Address Then
     MsgBox [BI_CaseIns]
ElseIf Target.Address = Range("BI_FC").Address Then
     MsgBox [BI_FC]
End If

ตัวอย่างการใช้ Select Case...End Select ครับ

Code: Select all

Select Case Target.Address
    Case Range("AI_Model").Address
         MsgBox [AI_Model]
    Case Range("AI_CarID").Address
         MsgBox [AI_CarID]
    Case Range("BI_CaseIns").Address
         MsgBox [BI_CaseIns]
    Case Range("BI_FC").Address
         MsgBox [BI_FC]
    Case Else
        'Last case
End Select

ศึกษาเรื่องเหล่านี้เพิ่มเติมได้จากที่นี่ครับ https://snasui.com/wordpress/if-then-else/, https://snasui.com/wordpress/select-case/

ศึกษาเรื่องอื่น ๆ ที่จำเป็นต้องทราบได้ในโพสต์นี้ตั้งแต่ลำดับรายการที่ 208 เป็นต้นไป https://snasui.com/viewtopic.php?t=411

[10idlnw]

ขอบคุณครับ แล้วถ้าเกิดผมมี Range Names เยอะๆหล่ะครับประมาณเกือบ 30ตัวเลย คิดว่า if else มันก็ยังยาวอยู่ดี ความจริงๆสิ่งที่ผมต้องการคือ ให้แต่ละแถวทำงานตรงตามชื่อของมันเองครับ เช่นดังนี้ครับ
Ex.A

If Target.Address = Range("AI_Model").Address Then
[BI_Model] = [AI_Model]
End If

ผมไปเจอตัวอย่างที่ loop หา Range Name ทั้งหมดมา แต่จะยัด Ex.A ใส่ใน Ex.B ยังไงให้เงื่อนไข(Range(AI_Model))และคำสั่ง[BI_Model] = [AI_Model] ตรงกัน โดยให้โค้ดสั้นที่สุดตามตัวอย่างใน Ex.B ครับ

Ex.B

Code: Select all

Sub NameRange()
Dim TargetNameRange As Name
For Each TargetNameRange In ActiveWorkbook.Names
' เอาใส่ตรงนี้ไหมครับ แล้วถ้าใส่แล้ว ต้องปรับสูตรยังไงให้เงื่อนไขและการทำงานมันตรงกันเหมือนตัวอย่างด้านบน(Ex.A)
If Target.Address = Range("AI_Model").Address Then
            [BI_Model] = [AI_Model] ' <---  ตรงนี้ก็อยากให้มัน(เช่น AI_Model) 
            			'วิ่งหากันเองจนเจอกันบรรทัดก่อนหน้า(Range("AI_Model").Address)
            			'ประมาณนี้ครับ
End If
Next TargetNameRange
End Sub

ต้องแก้อย่างไร ถึงจะลงตัวครับ

[snasui]

กรุณาแนบไฟล์ตัวอย่างมาเสมอจะได้สะดวกในการตอบของเพื่อนสมาชิก ลดเวลาการทำตัวอย่างขึ้นมาเองใหม่ครับ

[10idlnw]

ตัวอย่างตามนี้ครับ

[snasui]

ตัวอย่าง Code ลองประยุกต์ใช้ดูครับ

Code: Select all

'Other code
Dim TargetNameRange As Name
For Each TargetNameRange In Names
    If InStr(TargetNameRange.Name, "AI_") Then
        Range(TargetNameRange.RefersTo).Value = _
            Range(Names("BI_" & VBA.Mid(TargetNameRange.Name, 4)).RefersTo).Value
    End If
Next TargetNameRange
'Other code

[10idlnw]

ขอบคุณครับ ผมทดสอบแล้วทำงานได้ตามปกติเวลารันด้วย sub module ครับ แต่ผมอยากให้มันรันใน worksheets_changed โดยเปลี่ยนแปลงทันทีที่เปลี่ยนข้อความในเซลล์ต้องปรับโค้ดอย่างไรครับ ข้างล่าง ผมลองแทรก Target.Address ดูก็ไม่มีอะไรเกิดขึ้นครับ ต้องทำยังไงครับ

Code: Select all

Dim TargetNameRange As Name
For Each TargetNameRange In Names
    If InStr(TargetNameRange.Name, "BI_") Then
        If Target.Address = Range(Names("AI_" & VBA.Mid(TargetNameRange.Name, 4)).RefersTo).Address Then '<----
            Range(TargetNameRange.RefersTo).Value = _
            			Range(Names("AI_" & VBA.Mid(TargetNameRange.Name, 4)).RefersTo).Value
        End If
    End If
Next TargetNameRange

[snasui]

กรุณาแนบไฟล์ล่าสุดพร้อม Code ที่ปรับปรุงมาเองแล้วจะได้ดูต่อไปจากนั้นครับ

สำหรับการใช้ Change Event ไม่ทราบว่าต้องการให้ทำงานลักษณะใด Code ข้างต้นเป็นการเปลี่ยนทุกเซลล์ให้เป็นไปตามต้นทาง แต่หากต้องการเปลี่ยนแบบเซลล์ต่อเซลล์ไม่จำเป็นต้อง Loop ด้วย For เพียงแต่ดักจับว่าต้นทางคือ Named range ตัวใด แล้วไปเปลี่ยนยังปลายทางที่สัมพันธ์กับ Named range ตัวนั้นเท่านั้น กรุณาอธิบายส่วนนี้เพิ่มเติมมาด้วยครับ

[10idlnw]

ไฟล์ตัวอย่างครับ

[snasui]

สำหรับการใช้ Change Event ไม่ทราบว่าต้องการให้ทำงานลักษณะใด Code ข้างต้นเป็นการเปลี่ยนทุกเซลล์ให้เป็นไปตามต้นทาง แต่หากต้องการเปลี่ยนแบบเซลล์ต่อเซลล์ไม่จำเป็นต้อง Loop ด้วย For เพียงแต่ดักจับว่าต้นทางคือ Named range ตัวใด แล้วไปเปลี่ยนยังปลายทางที่สัมพันธ์กับ Named range ตัวนั้นเท่านั้น กรุณาอธิบายส่วนนี้เพิ่มเติมมาด้วยครับ

กรุณาขยายความตามที่ผมถามไปในข้อความด้านบนด้วยครับ

[10idlnw]

ที่ผมต้องการใช้ Change Event คือ ผมต้องการดักจับ Name Range ที่ต้องการ ไม่ว่าจะพิมพ์เล็ก-ใหญ่ ก็ให้เป็นพิมพ์ใหญ่ทันทีที่พิมพ์ใน Name Range นั้นเสร็จ ซึ่งมีหลายสิบตัวในหนึ่งหน้าที่ขึ้นต้นด้วย AI_ เลยอยากใช้ Loop เพื่อให้โค้ดมันสั้นลง เขียนสั้นๆทีเดียวแล้วให้มัน uCase ให้เองน่ะครับอาจารย์

[snasui]

ตัวอย่างการปรับ Code ครับ

Code: Select all

'Other code
Dim nm As String

Application.EnableEvents = False
On Error Resume Next
nm = Target.Name.Name
If nm = "" Then Exit Sub
If InStr(nm, "BI_") Then Exit Sub
Range("BI_" & Mid(nm, 4)).Value = UCase(Target.Value)
On Error GoTo 0
Application.EnableEvents = True
'Other code

[10idlnw]

ขอบคุณมากครับอาจารย์ ตรงกับความต้องการ 100% เต็มเป๊ะเลยครับ

[10idlnw]

ขอสอบถามอาจารย์เพิ่มอีกนิดครับว่า ถ้าต่าง sheets กัน ต้องปรับแก้เพิ่มเติมตรงไหนครับ (สมมติ BI_ อยู่ sheets2 นอกนั้น(AI_)อยู่ sheets1 ครับอาจารย์)

Code: Select all

'Other code
Dim nm As String

Application.EnableEvents = False
On Error Resume Next
nm = Target.Name.Name
If nm = "" Then Exit Sub
If InStr(nm, "BI_") Then Exit Sub
Range("BI_" & Mid(nm, 4)).Value = UCase(Target.Value)
On Error GoTo 0
Application.EnableEvents = True
'Other code

[snasui]

กรุณาปรับปรุงมาเองก่อน ดูตัวอย่าง Code ตามโพสต์ด้านล่างนี้ ติดตรงไหนแนบไฟล์พร้อม Code ล่าสุดที่ปรับปรุงแล้วมาถามกันต่อครับ

ตัวอย่าง Code ลองประยุกต์ใช้ดูครับ

Code: Select all

'Other code
Dim TargetNameRange As Name
For Each TargetNameRange In Names
    If InStr(TargetNameRange.Name, "AI_") Then
        Range(TargetNameRange.RefersTo).Value = _
            Range(Names("BI_" & VBA.Mid(TargetNameRange.Name, 4)).RefersTo).Value
    End If
Next TargetNameRange
'Other code

[10idlnw]

ลองปรับโค้ดนี้แล้ว ปรากฎว่า ใช้ได้แล้วครับ ขอบคุณอาจารย์มากครับ

Code: Select all

Sub test1()
Application.EnableEvents = False
Dim TargetNameRange As Name
For Each TargetNameRange In Names
    If InStr(TargetNameRange.Name, "BI_") Then
        Range(TargetNameRange.RefersTo).Value = _
            UCase(Range(Names("AI_" & VBA.Mid(TargetNameRange.Name, 4)).RefersTo).Value)
        Range(Names("AI_" & VBA.Mid(TargetNameRange.Name, 4)).RefersTo).Value = _
            UCase(Range(TargetNameRange.RefersTo).Value)
    End If
Next TargetNameRange
Application.EnableEvents = True
End Sub