CODEWITHSUNDEEP
excelvba
seuadr
seuadr

Reputation: 143

Excel custom function - lookup and concatenate matches

i am using a custom function that someone else built Original Source here!

Code:

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

that is working well for my purposes - except now my end user is asking for carriage returns between the results. So, for example, my initial match/concat might look like this:

RM_B220_L1.alm  SCIENCE - THE TEMPERATURE IN ROOM B220
RM_B220_L1.alm  HAS FALLEN 5 DEGREES BELOW SETPOINT.
RM_B220_L1.alm  THIS INDICATES A LEVEL 1 LOW TEMPERATURE ALARM
RM_B220_L1.alm  THIS ROOM CONTAINS ANIMALS AND CRITICAL RESEARCH!!

and thus output:

RM_B220_L1.alm  SCIENCE - THE TEMPERATURE IN ROOM B220 HAS FALLEN 5 DEGREES BELOW SETPOINT.THIS INDICATES A LEVEL 1 LOW TEMPERATURE ALARM THIS ROOM CONTAINS ANIMALS AND CRITICAL RESEARCH!!

when the end user's preference would be:

RM_B220_L1.alm  SCIENCE - THE TEMPERATURE IN ROOM B220 
HAS FALLEN 5 DEGREES BELOW SETPOINT.
THIS INDICATES A LEVEL 1 LOW TEMPERATURE ALARM 
THIS ROOM CONTAINS ANIMALS AND CRITICAL RESEARCH!!

or something similar.

now, if this was something i'd put together myself, i'd figure you could insert a character to do the carriage return between matches, but, i have no idea how to even begin to do that with the above code.

anyone have any ideas?

Upvotes: 0

Views: 248

Answers (1)

Wils Mils
Wils Mils

Reputation: 633

Basing on the link you provided, replace the line:

result = result & " " & x.Offset(0, indexcol - 1)

with this:

result = result & vbCrLf & x.Offset(0, indexcol - 1)

vbCrLf is equivalent to putting carriage returns in vba.

Upvotes: 3

Related Questions