Excel Forum

snasui wrote: Thu Oct 26, 2023 7:15 pm ที่ปรับมานั้น Code เดิมที่สำคัญหายไปหลายบรรทัดเลยทำงานไม่ได้

Code ที่ทำงานได้จะต้องเป็นตามด้านล่างครับ

Code: Select all

Sub addname()
    Dim sh As Worksheet
    Dim itme As Long
    Dim otme As Long
    Application.DisplayAlerts = False
    For Each sh In Worksheets
        If sh.Index > 1 Then
            With Sheets("Main")
                With sh
                    itme = Application.Match("InTime", .Range("a1:a1000"), 0)
                    otme = Application.Match("OutTime", .Range("a1:a1000"), 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, 1).Value = Application.Sum(sh.Range("aq" & itme & ":aq" & otme))
                    .Offset(0, 3).Value = Application.Sum(sh.Range("aq" & otme & ":aq" & 1000))
                    .Offset(0, 2).Value = Application.Count(sh.Range("aq" & itme & ":aq" & otme))
                    .Offset(0, 4).Value = Application.Count(sh.Range("aq" & otme & ":aq" & 1000))
                End With
            End If
            End With
        End If
    Next sh

   Application.DisplayAlerts = True
   
   Worksheets("Main").Activate
   
End Sub

Sub hlrow()

    Dim sh As Worksheet
    Dim itme As Long
    Dim otme As Long
    
    'Application.DisplayAlerts = False
    
    For Each sh In Worksheets
        If sh.Index > 1 Then  'ÁÒ¡¡ÇèÒ 1 ªÕ·
            With Sheets("Main")
                With sh
                
                    itme = Application.Match("InTime", .Range("a1:a10000"), 0)
                    otme = Application.Match("OutTime", .Range("a1:a10000"), 0)
                    
                    If (sh.Range("aq" & itme & ":aq" & otme)) <= 0 Then .Interior.Color = vbCyan
                    
                End With
                
            End With
        End If
        
    Next sh

   'Application.DisplayAlerts = True
   
   Worksheets("Main").Activate

End Sub

snasui wrote: Thu Oct 26, 2023 7:15 pm ที่ปรับมานั้น Code เดิมที่สำคัญหายไปหลายบรรทัดเลยทำงานไม่ได้

Code ที่ทำงานได้จะต้องเป็นตามด้านล่างครับ

Code: Select all

Sub addname()
    Dim sh As Worksheet
    Dim itme As Long
    Dim otme As Long
    Application.DisplayAlerts = False
    For Each sh In Worksheets
        If sh.Index > 1 Then
            With Sheets("Main")
                With sh
                    itme = Application.Match("InTime", .Range("a1:a1000"), 0)
                    otme = Application.Match("OutTime", .Range("a1:a1000"), 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, 1).Value = Application.Sum(sh.Range("aq" & itme & ":aq" & otme))
                    .Offset(0, 3).Value = Application.Sum(sh.Range("aq" & otme & ":aq" & 1000))
                    .Offset(0, 2).Value = Application.Count(sh.Range("aq" & itme & ":aq" & otme))
                    .Offset(0, 4).Value = Application.Count(sh.Range("aq" & otme & ":aq" & 1000))
                End With
            End If
            End With
        End If
    Next sh

   Application.DisplayAlerts = True
   
   Worksheets("Main").Activate
   
End Sub

snasui wrote: Thu Oct 26, 2023 7:15 pm ที่ปรับมานั้น Code เดิมที่สำคัญหายไปหลายบรรทัดเลยทำงานไม่ได้

Code ที่ทำงานได้จะต้องเป็นตามด้านล่างครับ

Code: Select all

Sub addname()
    Dim sh As Worksheet
    Dim itme As Long
    Dim otme As Long
    Application.DisplayAlerts = False
    For Each sh In Worksheets
        If sh.Index > 1 Then
            With Sheets("Main")
                With sh
                    itme = Application.Match("InTime", .Range("a1:a1000"), 0)
                    otme = Application.Match("OutTime", .Range("a1:a1000"), 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, 1).Value = Application.Sum(sh.Range("aq" & itme & ":aq" & otme))
                    .Offset(0, 3).Value = Application.Sum(sh.Range("aq" & otme & ":aq" & 1000))
                    .Offset(0, 2).Value = Application.Count(sh.Range("aq" & itme & ":aq" & otme))
                    .Offset(0, 4).Value = Application.Count(sh.Range("aq" & otme & ":aq" & 1000))
                End With
            End If
            End With
        End If
    Next sh

   Application.DisplayAlerts = True
   
   Worksheets("Main").Activate
   
End Sub

Sub hlrow()

    Dim sh As Worksheet
    Dim itme As Long
    Dim otme As Long
    
    Application.DisplayAlerts = False
    
For Each sh In Worksheets
    If sh.Index > 1 Then  'มากกว่า 1 ชีท
        With Sheets("Main")
            With sh
            
                itme = Application.Match("InTime", .Range("a1:a10000"), 0)

                For Each r In .Range("aq" & itme & ":aq10000")
                    If r.Value <> "" And r.Value <= 0 Then 'เงื่อนไข ไม่เป็นค่าว่าง และ มีค่าน้อยกว่า 0 หรือเท่ากับ 0

                        r.EntireRow.Interior.Color = vbCyan 'ใส่สีฟ้าเฉพาะแถวที่ตรงกับเงื่อนไข
                        
                    End If
                Next r
            End With
            
        End With
    End If
Next sh

   Application.DisplayAlerts = True
   
   Worksheets("Main").Activate

End Sub