如何根据VB脚本中的名称和区域计算总小时数 [英] How to calculate total hours according to name and region in VB script

问题描述

REGION	EMPLOYEE  hR	HR	HR	
US	BOB	  1	2	1
UK	BOB	  5	1	0
US	TOM	  7	5	5
UK	TOM	  6	1	1
US	BOB	  1	5	4
UK	TOM	  4	8	2
US	BOB	 10	24	5
UK	TOM	 12	15	2

这是数据。我想根据地区添加每个员工工作的总小时数。我无法根据地区对数据进行排序。 vba脚本新手。 提前致谢



我的尝试：

This is the data. I want to add the total number of hours each employee worked according to region. I am not able to sort data according to region. New to vba script. thanks in advance

What I have tried:

Private Sub CommandButton2_Click()
Dim employee As String, region As String, total As Integer, sheet As Worksheet, i As Integer
total = 0
For Each sheet In Worksheets
employee = InputBox("enter name")
region = InputBox("enter region")

For i = 2 To 9
If sheet.Cells(i, 1).Value = US Then
If sheet.Cells(i, 2).Value = employee Then
    total = total + sheet.Cells(i, 3).Value + sheet.Cells(i, 4).Value + sheet.Cells(i, 5).Value
End If
End If
 Next i
Next sheet
MsgBox "Total Hours of " & employee & " is " & total

End Sub

推荐答案

假设您的数据位于 A1：E9 范围内，您必须结合几种方法才能获得总数适用于特定地区的员工。请参阅：

Assuming that your data are placed in a range "A1:E9", you have to combine few method to get total for employee in specific region. Please, see:

Option Explicit

Private Sub CommandButton2_Click()
Dim sEmployee As String, sRegion As String
Dim sEmployeeColumn As String, sRegionColumn As String, sHrsColumn As String
Dim total As Integer, i As Integer, sMyFormula As String
Dim wsh As Worksheet, rng As Range

total = 0

Do While sEmployee = "" Or sRegion = ""
    sEmployee = UCase(InputBox("Enter name:", "Enter Employee name"))
    sRegion = UCase(InputBox("Enter region:", "Enter Region name"))
    total = total + 1
    If total = 3 Then
        MsgBox "You skipped entering data 3 times.", vbInformation, "Finish..."
        GoTo Exit_CommandButton2_Click
    End If
Loop

total = 0
Set wsh = ThisWorkbook.Worksheets("Arkusz1")
Set rng = wsh.UsedRange 'or wsh.Range("A5:E55")
sEmployeeColumn = rng.Columns("B").Address & "=""" & sEmployee & """"
sRegionColumn = rng.Columns("A").Address & "=""" & sRegion & """"
For i = 2 To 4
    'sum HR columns
    sHrsColumn = rng.Columns("A:A").Offset(0, i).Address
    'formula array
    sMyFormula = "=SUM(IF((" & sRegionColumn & ")*(" & sEmployeeColumn & ")," & sHrsColumn & ",0))"
    'insert formula one row below the range
    rng.End(xlDown).Offset(1, i).Cells(1, 1).FormulaArray = sMyFormula
    'get total
    total = total + WorksheetFunction.Sum(rng.End(xlDown).Offset(1, i).Cells(1, 1))
    'remove temporary data
    rng.End(xlDown).Offset(1, i).Cells(1, 1).Delete xlShiftUp
Next i

MsgBox "Total Hours of '" & sEmployee & "' (region: '" & sRegion & "') is: " & total
 
Exit_CommandButton2_Click:
    'ignore errors
    On Error Resume Next
    'clean up
    Set wsh = Nothing
    Set rng = Nothing
    Exit Sub

Err_CommandButton2_Click:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_CommandButton2_Click
 
End Sub

这篇关于如何根据VB脚本中的名称和区域计算总小时数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！