VBA Find Text String before and use this text to Vlookup

Boondog

New Member
Joined
Feb 10, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I am hoping someone can help a complete novice. I am looking for VBA Code to;

Look at cell "BA19" in worksheet "Output"
Find the text before the "+" [this will only ever have 1-3 characters before and 1-3 after the "+"]
Remove any spaces from this found text
Then use the text to vlookup "A4:J34" in worksheet "Tables"
Then return this lookup value to cell "BH19" in worsheet "Output"

Then Repeat this for text after the "+" , with vlookup and output going to "BI20" in worsheet "Output"

Example:

Cell "BA19" contains text LG + CH [Result should be LG]
Use LG [with no spaces] to lookup table and in this example will return 6

LG 6
KN 7
CH 3


Repeat with text after the "+" and lookup table which would return value 3

This is very frustrating as any code found on the web never does what I want and when i try to modify it , there's always an error code which simply doesn't mean anything to me. So any assist would be very much appreciated. Thanks in advance.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,776
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message board.
this code find both 3 character before (Bf) & After (Af) Plus sign.
VBA Code:
Sub FindPlusData()
Dim Sh1 As Worksheet, Sh2 As Worksheet, Lr1 As Long, i As Long, Lr2 As Long
Dim K As Long, Bf As String, Af As String, L As Long
Set Sh1 = Sheets("Tables")
Set Sh2 = Sheets("Output")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
L = Len(Sh2.Range("BA19").Value) - Len(Application.WorksheetFunction.Substitute(Sh2.Range("BA19").Value, "+", ""))
K = 0
On Error Resume Next
For i = 1 To L
K = InStr(K + 1, Sh2.Range("BA19").Value, "+")
Bf = Trim(Mid(Sh2.Range("BA19").Value, K - 3, 3))
Af = Trim(Mid(Sh2.Range("BA19").Value, K + 1, 3))
Sh2.Range("BH" & 18 + i * 2 - 1).Value = Bf
Sh2.Range("BH" & 18 + i * 2).Value = Af
' I supposed you search Vlookup for column 2, if not change it to your column for answer
Sh2.Range("BI" & 18 + i * 2 - 1).Value = Application.WorksheetFunction.VLookup(Bf, Sh1.Range("A4:J" & Lr1), 2, False)
Sh2.Range("BI" & 18 + i * 2).Value = Application.WorksheetFunction.VLookup(Af, Sh1.Range("A4:J" & Lr1), 2, False)
Next i

End Sub
 

Boondog

New Member
Joined
Feb 10, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Welcome to MrExcel Message board.
this code find both 3 character before (Bf) & After (Af) Plus sign.
VBA Code:
Sub FindPlusData()
Dim Sh1 As Worksheet, Sh2 As Worksheet, Lr1 As Long, i As Long, Lr2 As Long
Dim K As Long, Bf As String, Af As String, L As Long
Set Sh1 = Sheets("Tables")
Set Sh2 = Sheets("Output")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
L = Len(Sh2.Range("BA19").Value) - Len(Application.WorksheetFunction.Substitute(Sh2.Range("BA19").Value, "+", ""))
K = 0
On Error Resume Next
For i = 1 To L
K = InStr(K + 1, Sh2.Range("BA19").Value, "+")
Bf = Trim(Mid(Sh2.Range("BA19").Value, K - 3, 3))
Af = Trim(Mid(Sh2.Range("BA19").Value, K + 1, 3))
Sh2.Range("BH" & 18 + i * 2 - 1).Value = Bf
Sh2.Range("BH" & 18 + i * 2).Value = Af
' I supposed you search Vlookup for column 2, if not change it to your column for answer
Sh2.Range("BI" & 18 + i * 2 - 1).Value = Application.WorksheetFunction.VLookup(Bf, Sh1.Range("A4:J" & Lr1), 2, False)
Sh2.Range("BI" & 18 + i * 2).Value = Application.WorksheetFunction.VLookup(Af, Sh1.Range("A4:J" & Lr1), 2, False)
Next i

End Sub
Maabadi, thank you so very much for taking the time to pull this together. (y) Best wishes, Frank
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,776
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're welcome & Thanks for follow-up.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,404
Messages
5,636,088
Members
416,897
Latest member
YAFI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top