VBA extract text string from another cell formula problem

[ETMB]

I’m currently trying to include a VBA code into a larger Macro however one particular line (in Part B) is throwing out an error with the FIND function. The objective of the code is to insert two formulas into two diiferrent cells that extract text string from other cell (this formula is copied down accordingly). Below is what I have to date

' Part A
x = Range("A" & Rows.Count).End(xlUp).Row
sConc = "=B2&"" - ""&XLOOKUP(B2,Table4[Code], Table4[Description])"
Range("J2").Resize(x - 1).Formula = sConc


' Part B
y = Range("B" & Rows.Count).End(xlUp).Row
sConc = (LEFT(G1,FIND(" - ",G1)+1)) ' THIS LINE
Range("K2").Resize(y - 1).Formula = sConc

I gather the FIND function will not work within VBA and I need to use the INSTR function however I cannot figure out how to extract the required text ( before the " - ") from the cell using the INSTR function.

Any help on this would be greatly appreciated

Thanks

[TMS]

Try:

Please Login or Register  to view this content.

[TMS]

This works:

Please Login or Register  to view this content.

Though I suspect the reference should be to cell G2

Please Login or Register  to view this content.

[chocolate_kitten]

Hi ETMB,

Instead of using Find(), you may try Excel.WorksheetFunction.Find(). Also, G1 must be changed to Range("G1") when doing the code in Part B.
Therefore, sConc = (LEFT(G1,FIND(" - ",G1)+1)) will be changed to :

Please Login or Register  to view this content.

Note that I changed FIND("-", ..) +1 to be FIND("-", ..) -1 this is to extract the text before "-".
For example, if the text in G1 is Test-Formula, FIND("-", G1) would return 5 (the position of "-"). So, if the required text is "Test", then we should use Left(G1, 4) not Left(G1, 6).