VBA Find Text String before and use this text to Vlookup

[Boondog]

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.

 

[maabadi]

Welcome to MrExcel Message board.
this code find both 3 character before (Bf) & After (Af) Plus sign.

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]

Welcome to MrExcel Message board.
this code find both 3 character before (Bf) & After (Af) Plus sign.

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. Best wishes, Frank

 

[maabadi]

You're welcome & Thanks for follow-up.