Dynamically adding values in several sheets with For..Next loop and Application.WorksheetFunction.HLookup

[slayerpblade]

Hi there,

I have this certain code that I need to implement. Basically I have to add values in several sheets based on hlookup. However, the names of the lookup tables in each sheet are worksheet-scoped and similar for scalability, and I have no idea how to implement the hlookup function in vba despite many attempts. The following is the code that I have so far:

Function TOTALSUM(SearchItem, SheetNameSubstring, LookUpTableName, LookUpColumn)
Dim AimRange
Dim temp
For Each Sheet In ThisWorkbook.Sheets
If InStr(1, Sheet.Name, SheetNameSubstring) <> 0 Then
AimRange = "'" & Sheet.Name & "'!" & LookUpTableName
Set temp = Application.WorksheetFunction.HLookup(SearchItem, Range(AimRange), LookUpColumn)
TOTALSUM = TOTALSUM + temp
End If
Next Sheet
End Function

Could anyone kindly point out to me what is wrong with my code? Thanks a lot!

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

Here's one way to write that.
If you are using HLookup instead of VLookup, you'll want to reference the LookUpRow instead of a LookUpColumn.

Function TOTALSUM(SearchItem, SheetNameSubstring, LookUpTableName, LookUpRow)
 Dim rAimRange As Range
 Dim vFound As Variant
 Dim wks As Worksheet
 
 Application.Volatile
 
 For Each wks In ThisWorkbook.Sheets
   If InStr(1, wks.Name, SheetNameSubstring) <> 0 Then
      Set rAimRange = wks.Range(LookUpTableName)
      vFound = Application.HLookup(SearchItem, rAimRange, LookUpRow, True)
      If IsNumeric(vFound) Then
         TOTALSUM = TOTALSUM + CDbl(vFound)
      End If
   End If
 Next wks
 End Function