snasui.com ยินดีต้อนรับ ยินดีต้อนรับสู่กระดานถามตอบ Excel and VBA และอื่น ๆ ที่เป็นมิตรกับทุกท่าน มีไฟล์แนบมหาศาล ช่วยให้ท่านค้นหาและติดตามศึกษาได้โดยง่าย สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ ระบุ Version ของ Excel
Sub paidpro()
Dim sh As Worksheet
Dim itme As Long
Dim otme As Long
Application.DisplayAlerts = False
For Each sh In Worksheets
If sh.Index > 3 Then
With Sheets("Main")
With sh
cp = Application.Match("central", .Range("a1:a50000"), 0)
op = Application.Match("country", .Range("a5:a50000"), 0)
End With
If Application.CountIfs(.Range("b6:b30"), sh.Name) = 0 Then
With .Range("b30").End(xlUp).Offset(1, 0)
.Value = sh.Name
.Offset(0, 2).Value = Application.VLookup(sh.Range("d" & cp & ":d" & op), 2, 0)
End With
End If
End With
End If
Next sh
Application.DisplayAlerts = True
End Sub
ผมพยายามเอา code จากงานตัวเก่ามาปรับแต่ยังไม่ได้ พอรัน vb ข้อมูลจะไปแสดงที่ชีท main และค่าที่แสดงไม่ตรงกับที่ต้องการ
ขอความอนุเคราะห์ชี้แนะด้วย ขอบพระคุณครับ
You do not have the required permissions to view the files attached to this post.
Sub paidpro()
Dim sh As Worksheet
Dim r As Range
Application.DisplayAlerts = False
On Error Resume Next
For Each sh In Worksheets
If sh.Index > 3 Then
With Sheets("province")
For Each r In sh.Range("d8:d99")
r.Value = Application.IfError(Application. _
VLookup(r.Text, Sheets("province").Range("a2:b79"), 2, 0), "Not foud")
Next r
End With
End If
Next sh
Application.DisplayAlerts = True
End Sub
Sub paidpro()
Dim sh As Worksheet
Dim r As Range
Application.DisplayAlerts = False
On Error Resume Next
For Each sh In Worksheets
If sh.Index > 3 Then
With Sheets("province")
For Each r In sh.Range("d8:d99")
r.Value = Application.IfError(Application. _
VLookup(r.Text, Sheets("province").Range("a2:b79"), 2, 0), "Not foud")
Next r
End With
End If
Next sh
Application.DisplayAlerts = True
End Sub
Sub paidpro()
Dim sh As Worksheet
Dim r As Range
Application.DisplayAlerts = False
On Error Resume Next
For Each sh In Worksheets
If sh.Index > 3 Then
With Sheets("province")
For Each r In sh.Range("d8:d10000")
If Len(r.Text) < 3 Then
r.Value = Application.IfError(Application. _
VLookup(r.Text, Sheets("province").Range("a2:b79"), 2, 0), " ")
End If
Next r
End With
End If
Sub paidpro()
Dim sh As Worksheet
Dim r As Range
Application.DisplayAlerts = False
On Error Resume Next
For Each sh In Worksheets
If sh.Index > 3 Then
With Sheets("province")
For Each r In sh.Range("d8:d10000")
If Len(r.Text) < 3 Then
r.Value = Application.IfError(Application. _
VLookup(r.Text, Sheets("province").Range("a2:b79"), 2, 0), " ")
End If
Next r
End With
End If
Sub paidpro()
Dim sh As Worksheet
Dim r As Range
Application.DisplayAlerts = False
On Error Resume Next
For Each sh In Worksheets
If sh.Index > 3 Then
With Sheets("province")
For Each r In sh.Range("d8:d10000")
If Len(r.Text) < 3 Then
r.Value = Application.IfError(Application. _
VLookup(r.Text, Sheets("province").Range("a2:b79"), 2, 0), " ")
End If
Next r
End With
End If
End Sub
You do not have the required permissions to view the files attached to this post.
Sub paidpro()
Dim sh As Worksheet
Dim r As Range
Application.DisplayAlerts = False
On Error Resume Next
For Each sh In Worksheets
If sh.Index > 3 Then
With Sheets("province")
For Each r In sh.Range("d8:d10000")
If Len(r.Text) < 3 Then
r.Value = Application.IfError(Application. _
VLookup(r.Text, Sheets("province").Range("a2:b79"), 2, 0), " ")
End If
Next r
End With
End If
Sub paidpro()
Dim sh As Worksheet
Dim r As Range
Application.DisplayAlerts = False
On Error Resume Next
For Each sh In Worksheets
If sh.Index > 3 Then
With Sheets("province")
For Each r In sh.Range("d8:d10000")
If Len(r.Text) < 3 Then
r.Value = Application.IfError(Application. _
VLookup(r.Text, Sheets("province").Range("a2:b79"), 2, 0), " ")
End If
Next r
End With
End If
End Sub
Sub paidpro()
Dim sh As Worksheet
Dim r As Range
Application.DisplayAlerts = False
On Error Resume Next
For Each sh In Worksheets
If sh.Index > 3 Then
With Sheets("province")
For Each r In sh.Range("d8:d10000")
If Len(r.Text) < 3 Then
r.Value = Application.IfError(Application. _
VLookup(r.Text, Sheets("province").Range("a2:b79"), 2, 0), " ")
End If
Next r
End With
End If
Next sh
Application.DisplayAlerts = True
End Sub
Sub paidname()
Dim sh As Worksheet
Dim r As Range
Application.DisplayAlerts = False
On Error Resume Next
For Each sh In Worksheets
If sh.Index > 3 Then
With Sheets("hoscode")
For Each r In sh.Range("q10:q10000")
If Len(r.Text) < 3 Then
r.Value = Application.IfError(Application. _
VLookup(r.Text, Sheets("hoscode").Range("a2:b17553"), 2, 0), "ไม่พบข้อมูล")
End If
Next r
End With
End If
Next sh
Application.DisplayAlerts = True
End Sub
You do not have the required permissions to view the files attached to this post.
Sub paidname()
Dim sh As Worksheet
Dim r As Range
Application.DisplayAlerts = False
On Error Resume Next
For Each sh In Worksheets
If sh.Index > 2 Then
With Sheets("hoscode")
For Each r In sh.Range("q10:q10000")
If Len(r.Text) = 5 Then
r.Value = Application.IfError(Application. _
VLookup(r.Text, Sheets("hoscode").Range("a2:b17553"), 2, 0), "ไม่พบข้อมูล")
End If
Next r
End With
End If
Next sh
Application.DisplayAlerts = True
End Sub
Sub paidname()
Dim sh As Worksheet
Dim r As Range
Application.DisplayAlerts = False
On Error Resume Next
For Each sh In Worksheets
If sh.Index > 2 Then
With Sheets("hoscode")
For Each r In sh.Range("q10:q10000")
If Len(r.Text) = 5 Then
r.Value = Application.IfError(Application. _
VLookup(r.Text, Sheets("hoscode").Range("a2:b17553"), 2, 0), "ไม่พบข้อมูล")
End If
Next r
End With
End If
Next sh
Application.DisplayAlerts = True
End Sub
snasui.com ยินดีต้อนรับ 
สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ 
โปรดทราบ 
