OzGrid

How to convert split formula in VBA in their respective columns

Requirement:

The user has a sheet where the user needs to split columns A into 2 distinct columns in B and C that is there reference will be in columns B and the serial no in column C ,.

How to convert the actual formula in VB to last data row as it consists of 15000 rows.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148882-convert-split-formula-in-vba-in-their-respective-columns

Solution:

Option Explicit


Sub Macro1()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("A2:A" & lr).TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub

OR

An alternative method

Sub SplitReference()
    Dim x, y(), i As Long
    
    With Sheets("INVOICE SPLIT")
        x = .Cells(1).CurrentRegion
        For i = 2 To UBound(x, 1)
            ReDim Preserve y(1 To 2, 1 To i - 1)
            y(1, i - 1) = Split(x(i, 1), "/")(0): y(2, i - 1) = Split(x(i, 1), "/")(1)
        Next
        .[b2].Resize(UBound(y, 2), UBound(y, 1)) = Application.Transpose(y)
    End With
    
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by AlanSidman and KjBox.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery