View Full Version : Solved: Function to Return Offsetted Values

07-22-2008, 08:40 AM
Hello I would like to write a function which takes in a range, finds the max of that range, and then returns all the values 3 columns to the left of the max as one string

In the example the desired returned string is "a,b,c,e,h,j,l"

07-22-2008, 09:15 AM
Function ReturnComps(Data As Range)
Dim cel As Range, Res As String
For Each cel In Data
If cel.Value = Application.Max(Data) Then
Res = Res & cel.Offset(, -3) & ", "
End If
Res = Left(Res, Len(Res) - 2)
ReturnComps = Res
End Function

07-23-2008, 06:22 AM
Thanks a lot mdmackillop!