Dim varData
varData = RangeToSum.Value
then iterate that array.
Public Function SumVlookups(ByVal Lookupvalue As Single, ByVal RangeToSum As Range) As Single
Dim ArrayStr As Range
Dim Found As Variant
For Each ArrayStr In RangeToSum
Found = Evaluate(ArrayStr.Value)
If Not IsError(Application.VLookup(Lookupvalue, Found, 1, False)) Then SumVlookups = SumVlookups + Application.VLookup(Lookupvalue, Found, 2, False)
Next ArrayStr
End Function
Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double
Dim Found As Variant
Dim varData
Dim varVal
Dim n As Long
varData = RangeToSum.Value
For n = LBound(varData, 1) To UBound(varData, 1)
Found = Evaluate(varData(n, 1))
If Not IsError(Found) Then
varVal = Application.VLookup(Lookupvalue, Found, 2, False)
If Not IsError(varVal) Then
If IsNumeric(varVal) Then SumVlookups = SumVlookups + varVal
End If
End If
Next n
End Function
Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double
Dim ArrayStr As Range
Dim Found As Variant
Dim varData As Variant
varData = RangeToSum.Value
For Each ArrayStr In varData
Found = Evaluate(ArrayStr.Value)
If Not IsError(Application.VLookup(Lookupvalue, Found, 1, False)) Then SumVlookups = SumVlookups + Application.VLookup(Lookupvalue, Found, 2, False)
Next ArrayStr
End Function
Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double
Dim Found As Variant
Dim varData
Dim varVal
Dim varValue
Dim n As Long
varData = RangeToSum.Value
For n = LBound(varData, 1) To UBound(varData, 1)
' load array data
Found = Evaluate(varData(n, 1))
If Not IsError(Found) Then
' check for match on lookup value
varVal = Application.VLookup(Lookupvalue, Found, 1, False)
If Not IsError(varVal) Then
' return relvant value from second column
varValue = Application.VLookup(Lookupvalue, Found, 2, False)
' make sure it's a number
If IsNumeric(varValue) Then
SumVlookups = SumVlookups + varValue
Else
SumVlookups = CVErr(xlErrValue)
Exit For
End If
End If
End If
Next n
End Function
Public Function SumVlookups(ByVal Lookupvalue As Double, ByVal RangeToSum As Range) As Double
Dim Found As Variant
Dim varData
Dim varVal
Dim varValue
Dim n As Long
varData = RangeToSum.Value
For n = LBound(varData, 1) To UBound(varData, 1)
' load array data
If InStr(varData(n, 1), Lookupvalue & ",") > 0 Then
Found = Evaluate(varData(n, 1))
If Not IsError(Found) Then
' check for match on lookup value
varVal = Application.VLookup(Lookupvalue, Found, 1, False)
If Not IsError(varVal) Then
' return relvant value from second column
varValue = Application.VLookup(Lookupvalue, Found, 2, False)
' make sure it's a number
If IsNumeric(varValue) Then
SumVlookups = SumVlookups + varValue
Else
SumVlookups = CVErr(xlErrValue)
Exit For
End If
End If
End If
End If
Next n
End Function
Title | # Comments | Views | Activity |
---|---|---|---|
How to Add / Edit Windows Menu | 4 | 29 | |
VB.Net - ReportViewer Windows Form many reports | 2 | 8 | |
Excel Difference between 2013 and 2016 | 2 | 15 | |
Added a column screws up code | 5 | 17 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
11 Experts available now in Live!