Heather515
New Member
- Joined
- Sep 11, 2021
- Messages
- 22
- Office Version
- 365
- Platform
- Windows
Hi,
I need help to retrieve multiple items from one cell. Data and results are shown below. I want to get multiple keywords from one cell. I created a dictionary. However, it can only retrieve one item from the cell. For example, in B2 and B6, it only shows 300. Thanks.
Function VBA(Rng As Range)
Dim dict As Dictionary
Set dict = New Dictionary
dict("Apple") = "300"
dict("Orange") = "400"
dict("Cat") = "500"
dict("Banana") = "600"
VBA = ""
For Each Key In dict.Keys
If InStr(Rng.Text, Key) Then
VBA = dict(Key)
Exit For
End If
Next
End Function
I need help to retrieve multiple items from one cell. Data and results are shown below. I want to get multiple keywords from one cell. I created a dictionary. However, it can only retrieve one item from the cell. For example, in B2 and B6, it only shows 300. Thanks.
Function VBA(Rng As Range)
Dim dict As Dictionary
Set dict = New Dictionary
dict("Apple") = "300"
dict("Orange") = "400"
dict("Cat") = "500"
dict("Banana") = "600"
VBA = ""
For Each Key In dict.Keys
If InStr(Rng.Text, Key) Then
VBA = dict(Key)
Exit For
End If
Next
End Function