Issues using "Textsplit" formula in VBA

Benjo83

New Member
Joined
Nov 6, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

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
Back
Top