Issues using "Textsplit" formula in VBA

[Benjo83]

Hi all,

I am hoping someone might be able to give me some pointers as I am really struggling with getting this formula to work with VBA (not sure if it is even possible to use this within VBA as numerous google trawls have drawn a blank).

I am working on a workbook with 52 Active columns, populated with data that changes (sometimes significantly) weekly. The final 8 columns are intended to be utilised by the "Textsplit" formula, splitting the contents of the preceding cell so that individual lookups can be run on the contained text. I have had to use the textsplit as there are common elements of text shared by 2 or more of the string elements and so am unable to use a wildcard search.

My attempt at the code so far is:

Sub Q_IP_Service_Split()

Q_IP_Service_Split Macro

Sheets("Import Prepared").Select
Range("AS4").Select
ActiveCell.FormulaR1C1 = _
"=TEXTSPLIT(RC[-1],"";"")"
Range("AS4").Select
Selection.AutoFill Destination:=Range("AS4:AS" & Range("E" & Rows.Count).End(xlUp).Row)
Range("AS4:AS" & Range("E" & Rows.Count).End(xlUp).Row).Select
End Sub

When I view the output of the code, the formula is applied, but an "@" symbol appears after the "=", essentially rendering the formula useless.

Any help or guidance would be hugely appreciated.

Ben

 

[steve the fish]

Use Formula2 not Formula

 

[Benjo83]

Use Formula2 not Formula

God tier Steve! Savior

Can I ask what the logic is?

 

[MARK858]

Can I ask what the logic is?

The article in the link below might help explain it

Cell Formulas

Office VBA reference topic

learn.microsoft.com