-
Mar 10th, 2014, 04:21 AM
#1
Thread Starter
Registered User
-
Mar 10th, 2014, 12:10 PM
#2
Re: VBA for EXCEL: Find specific text in a column of cells with text.
Why is vLookup not working?
If all the items on worksheet 2 are on worksheet 1 as well, and you have them both sorted by item, it should work.
For example, I have a worksheet called "source" that has the data, and am looking to populate the price on my other worksheet, and this works:
Code:
=VLOOKUP(A2,source!A2:C11,2)
cell A2 has the value I'm looking up, and the data in "source" goes from A2 to C11, with the price being in column 2 (or B)
-
Mar 10th, 2014, 03:09 PM
#3
Re: VBA for EXCEL: Find specific text in a column of cells with text.
If I understand correctly, you want a positive match if the lookup table value contains the text in the source.
As you indicate you are new to VBA let us do this by the numbers.
- Open the VBA Editor (Alt-F11 from Excel)
- Insert a Module (Alt-IM - hold Alt key down, type I followed by M)
- Copy the code below to the module.
- Go back to Excel and use the new function.
VBA Code:
Public Function VContains(what As Range, SourceRange As Range, ReturnColumn As Integer) As Variant
Dim strWhat As String
strWhat = CStr(what.Value)
Dim row As Integer
row = 0
Dim found As Boolean
Do While (Not found) And (row <= SourceRange.Columns.Item(1).Cells.Count)
row = row + 1
found = CStr(SourceRange.Columns.Item(1).Cells(row).Value) Like "*" & strWhat & "*"
Loop
If found Then
VContains = SourceRange.Cells(row, ReturnColumn).Value
Else
VContains = CVErr(xlErrNA)
End If
End Function
-
Mar 10th, 2014, 05:38 PM
#4
-
Mar 14th, 2014, 01:25 AM
#5
Re: VBA for EXCEL: Find specific text in a column of cells with text.
Welcome to the forums roybong
Since you are using Partial Matches, Simply using Vlookup/Lookup will not give you correct results. You need to take help of Index/Match and then use VLookup. Here is an example.
Let's say your data looks like this
To find the text which has the partial match, you need to use Index/Match with wildcard characters. For example, Put this formula in cell E1
=INDEX(A:A,MATCH("*"&D1&"*",A:A,0))
This will return the text which contains the partial match.
We can now use this formula in the Vlookup to achieve what we want
=VLOOKUP(INDEX(A:A,MATCH("*"&D1&"*",A:A,0)),A:B,2,0)
HTH
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|