Finding a matching value & returning another value

I am a novice at this, but I need to search through a set of workbook pages, compare an activity code from one column, then return the number of hours next to it to another location. Ideally I'd like to summarize all of it on a separate page, but I'll settle for each page.

See the attached for my start at setting it up. I've placed a column under each numbered category and was trying to compare the Activity Code from D to the numbers in the column and returning the hours if there is a match.

If there's an easier way, please share. Thanks!

Hi

Does this help?

Sub aaa()
'this puts the hours onto the data sheet
  Dim OutSH As Worksheet
  Set OutSH = Sheets(1)
  Application.ScreenUpdating = False
  For j = 2 To Sheets.Count
    Sheets(j).Activate
    For Each ce In Range("D5:D" & WorksheetFunction.Max(5, Cells(Rows.Count, 4).End(xlUp).Row))
      If Not IsEmpty(ce) Then
        Set findit = Rows("4:4").Find(What:=ce.Value, LookAt:=xlPart, SearchOrder:=xlByRows)
        If Not findit Is Nothing Then
          Cells(ce.Row, findit.Column).Value = ce.Offset(0, 1).Value
        Else
          Cells(ce.Row, 27).Value = ce.Offset(0, 1).Value
        End If
      End If
    Next ce
  Next j
  
  'this puts the hours onto the first sheet
  For j = 2 To Sheets.Count
    Sheets(j).Activate
    For Each ce In Range("D5:D" & WorksheetFunction.Max(5, Cells(Rows.Count, 4).End(xlUp).Row))
      If Not IsEmpty(ce) Then
        Set findit = OutSH.Rows("4:4").Find(What:=ce.Value, LookAt:=xlPart, SearchOrder:=xlByRows)
        If Not findit Is Nothing Then
          OutSH.Cells(5, findit.Column).Value = OutSH.Cells(5, findit.Column).Value + ce.Offset(0, 1).Value
        Else
          OutSH.Cells(5, 27).Value = OutSH.Cells(5, 27).Value + ce.Offset(0, 1).Value
        End If
      End If
    Next ce
  Next j
  Sheets(1).Activate
  Application.ScreenUpdating = True
End Sub

rylo

Maybe what I'm trying to do is easier than I thought.

I've entered this formula, but can only get it to return the false, not the value it's referring to in $E4

=IF($D4='NCARB CODES'!H4:H18,$E4,0)

I've also tried =OR...

Thanks for the help.

Hi

1) There is no 'NCARB CODES' sheet in your example file, so I've got no idea what this is referencing.

2) Have you array entered the formula? You will need to, and then extract the max value from the resulting array result.

If you really want to have a formula result for this, then can you move the post to the Worksheet Functions sub forum. You may get more formula type options there.

rylo

Sorry, I changed hrses in the middle of the stream...


here's the latest.

Hi

Try

=IF(ISNA(MATCH($D4,'NCARB CODES'!H$4:H$18,0)),0,$E4)

rylo