Support
Quality
Security
License
Reuse
kandi has reviewed tinker and discovered the below as its top functions. This is intended to give you an instant insight into tinker implemented functionality, and help decide if they suit your requirements.
Get all kandi verified functions for this library.
Get all kandi verified functions for this library.
Tinker is a hot-fix solution library for Android, it supports dex, library and resources update without reinstall apk.
QUESTION
VBA code to return multiple lookup values in one comma separated works but crashes if there's an empty cell
Asked 2022-Mar-08 at 15:05I've found this code online which works like vlookup function but returns multiple data in one cell separated by comma and it works most of the time. But when there's a blank cell in the lookup value it causes a crash. It takes a long time to process too. I tried to tinker with it but I'm completely new to VBA coding. I was wondering if anyone could please help me fix the issue and maybe optimize the code a little so it doesn't crash or take as long. Lookup value Table array
Here's the code
Function MultiVLookUp(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
Dim I As Long
Dim xRet As String
For I = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(I, 1) = LookupValue Then
If xRet = "" Then
xRet = LookupRange.Cells(I, ColumnNumber) & Char
Else
xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
End If
End If
Next
MultiVLookUp = Left(xRet, Len(xRet) - 1)
End Function
Thanks in advance.
ANSWER
Answered 2022-Mar-08 at 12:50Im not really sure the point of this macro considering it just outputs the same value repeatedly but here ya go.
Sub main()
' ws is the worksheet object referencing "Sheet1"
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
' Top Left (r, c) (r, c) Bottom Right
Dim rng As Range: Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(6, 6))
Dim lookupValue As String: lookupValue = ""
Dim outStr As String: outStr = rangeValuesToString(rng, lookupValue)
Debug.Print outStr
End Sub
Function rangeValuesToString(rng As Range, lookupValue As String) As String
Dim topRow As Integer: topRow = rng.Row
Dim botRow As Integer: botRow = rng.Row - 1 + rng.Rows.Count
Dim leftCol As Integer: leftCol = rng.Column
Dim rightCol As Integer: rightCol = rng.Column - 1 + rng.Columns.Count
Dim i As Integer, j As Integer
Dim outStr As String: outStr = ""
' Iterates through each column moving left to right
For i = leftCol To rightCol
For j = topRow To botRow
If rng.Cells(j, i).Value = lookupValue Then
outStr = outStr & rng.Cells(j, i).Value & ", "
End If
Next j
Next i
rangeValuesToString = Left(outStr, Len(outStr) - 2)
End Function
Community Discussions, Code Snippets contain sources that include Stack Exchange Network
No vulnerabilities reported
Find more information at:
Save this library and start creating your kit
See Similar Libraries in
Save this library and start creating your kit
Open Weaver – Develop Applications Faster with Open Source