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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,774
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,774
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're welcome & Thanks for follow-up.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,384
Messages
5,635,993
Members
416,891
Latest member
Okomomo

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