How to convert split formula in vba in their respective columns
Category: [Excel] Demo Available
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.
Solution:
Code:
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
Code:
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:
| How to use VBA - Split Worksheets and Display SaveAs Prompt |
| How to create VBA to split data to their respective columns with character restriction |
| Split row into many rows using trigger in particular row cells |
| How to find last non blank cell & not affect split screen view |
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.