tourism12345
New Member
- Joined
- Apr 18, 2019
- Messages
- 1
Please help I need to combine my two VBAs, but I have no idea how to write them I just copy them off the internet. They work great however I need them to work together for my next step
one is MultiVlookup which combines multiple data into one cell (I have multiple names assigned to different data and needed to combine it to have one name with multiple data)
Public Function MultiVLookup(MatchWith As String, TRange As Range, col_index_num As Integer)
MatchWith = LCase$(MatchWith)
If (MatchWith = "") Then
MultiVLookup = ""
Else
For Each cell In TRange
If LCase$(cell.Value) = MatchWith Then
x = x & cell.Offset(0, col_index_num).Value & ", "
End If
Next cell
If (x = "") Then
MultiVLookup = ""
Else
MultiVLookup = Left(x, Len(x) - 2)
End If
End If
End Function
The other is LookupKeepFormat which works like Vlookup but keeps the formatting of the text, in my case I want to keep the bold text:
'Update by Extendoffice 20180706
Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Dim xFindCell As Range
On Error Resume Next
Application.ScreenUpdating = False
Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
If xFindCell Is Nothing Then
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
Else
LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
End If
Application.ScreenUpdating = True
End Function
one is MultiVlookup which combines multiple data into one cell (I have multiple names assigned to different data and needed to combine it to have one name with multiple data)
Public Function MultiVLookup(MatchWith As String, TRange As Range, col_index_num As Integer)
MatchWith = LCase$(MatchWith)
If (MatchWith = "") Then
MultiVLookup = ""
Else
For Each cell In TRange
If LCase$(cell.Value) = MatchWith Then
x = x & cell.Offset(0, col_index_num).Value & ", "
End If
Next cell
If (x = "") Then
MultiVLookup = ""
Else
MultiVLookup = Left(x, Len(x) - 2)
End If
End If
End Function
The other is LookupKeepFormat which works like Vlookup but keeps the formatting of the text, in my case I want to keep the bold text:
'Update by Extendoffice 20180706
Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Dim xFindCell As Range
On Error Resume Next
Application.ScreenUpdating = False
Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
If xFindCell Is Nothing Then
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
Else
LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
End If
Application.ScreenUpdating = True
End Function