Excel Forum

Code: Select all

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: Select all

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

Code: Select all

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

Code: Select all

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

Code: Select all

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

Code: Select all

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

Code: Select all

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