PDA

View Full Version : multiple criteria for sumifs VBA code



babunchakr
06-03-2013, 01:07 AM
i have two sheets one is "input" and the other is "master" Sheet input has three columns WEEK,working hrs, and worker name, sheet master has all unique worker names in column A, and W1,W2......W13 in column b1,c1,d1...and so on

i want a sum ifs loop so that it can give result for workers working hrs in my masters table in their respective weeks(w1 to w13).

i m using a sum ifs formula for this :

Formula = "=SUMIFS(Input!C32,Input!C37,Master!C1,Input!C31,Master!B1)"


Any suggestions how to loop it in VBA

Please help....

SamT
06-03-2013, 09:01 AM
babunchakr,

Welcome to VBA Express.

VBA doesn't work like a formula does. In your case, VBA basically looks at each workers name on the input sheet, then finds the same name on the master sheet, then sets the correct Wkn Hrs column for that worker to the same value as on the Input sheet.

First I must make some assumptions, and you must tell me if they are wrong.

About the Input sheet:

All the lists start on Row 2
Each week, the Values in the Week Column are the same for every worker.
Each Week, the number of workers can be different
Those Values are the same as the Names or Labels of the Weekly Hrs Columns on the Master sheet, (wk1, wk2, wk3...and so on.)Now we can guess what variable names we will use in the code.

For the Input sheet:

WorkersRng for only all the cells that contain workers' names.
Cel to allow us to loop through the WorkersRng Cells
WorkerName for the name of the Worker we are using for this time through the loop.
WeekNum for the value of the week the input sheet is reporting.
HrsOffset used to refer to the respective Hours column from the workers name column.
For the Master sheet:

WorkerRow for the Row the Worker's name is on.
WeekCol for the column number of this week.Option Explicit

Sub SamT()
'Reads the Weekly Worker Hours Input Sheet and
'puts each worker's hours on the Master sheet.
'Assumes WeekNum is the same for all workers.
'
'By SamT @ http://www.vbaexpress.com/forum/showthread.php?t=46444

'Assign the Worksheets to Variables for easier code maintenance.
Dim WklyHrs As Worksheet
Dim MstrHrs As Worksheet
Set WklyHrs = Sheets("Input") 'Change as needed.
Set MstrHrs = Sheets("Master") 'Change as needed.


'For the Input sheet
Dim Cel As Range
Dim WorkerNames As Range
Dim WeekNum As String
Const HrsOffset As Integer = -1 'Change as needed.

'For the Master sheet
Dim WorkerRow As Long
Dim WeekCol As Long

'Set the WorkerNames Range, WeekNum Value, and WeekCol value
Set WorkerNames = WklyHrs.Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp))
WeekNum = WorkerNames.Cells(1).Offset(0, -2) 'All Weeknums the same
WeekCol = MstrHrs.Rows(1).Find(WeekNum).Column 'All Weeknums the same

'Loop through the WorkerNames Range and set the Master Hours values
For Each Cel In WorkerNames
WorkerRow = MstrHrs.Columns(1).Find(Cel.Value).Row
MstrHrs.Cells(WorkerRow, WeekCol) = Cel.Offset(0, HrsOffset).Value
Next Cel

End Sub

Augusto
01-13-2019, 09:35 AM
Good morning,
I replicated your code and got the error "Object variable not set" in the line:
WeekCol = MstrHrs.Rows(1).Find(WeekNum).Column

And I think I will have error in line:
MstrHrs.Cells(WorkerRow, WeekCol) = Cel.Offset(0, HrsOffset).Value

Could you please help me on this?
Thank you
Augusto

I'm very sorry but I just discovered an error in my code.
Your code works fine
Thank you
Augusto