Excel VBA transposing issues

Question

I'm trying to perform a do while loop that takes 3 cells, splits them, takes the right split and transposes it. Then move down to the next set of 3. Here's what I have

Sub transposer2()
Dim i As Integer

Do While i < 300
    Range(ActiveCell, ActiveCell.Offset(2, 0)).Select
        Selection.TextToColumns Destination:=ActiveCell.Offset(0, 1).Select, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Range(ActiveCell, ActiveCell.Offset(2, 0)).Select
    Selection.Copy
    Range(ActiveCell.Offset(0, 1)).Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, transpose:=True
    Application.CutCopyMode = False
    ActiveCell(9, -3).Select
Loop

End Sub

The selection after the range selection is where I am having trouble.

This is the data I copy from a txt file and ends up being space 9 rows apart

TAPER ANGLE : 6.6297
GAGE POINT DIA : 0.1775
DEPTH OF TAPER : -0.5950

The cells originally contain a string and a set of numbers like "xxxxxxx xxx xxx : #####", i split those and then select the numericals that get split to the right. then transpose. after i get this working i plan to add in a bit of code to delete all but the transposed data — Zach S. Thompson, Apr 22 '16 at 10:19
That is not a reason to use `Select`. You should also set ActiveCell to a variable. Please read [How to avoid using Select in Excel VBA Macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Your code will be less cluttered and easier to debug. Also, please read Help for [How to Provide an Example](http://stackoverflow.com/help/mcve) — Ron Rosenfeld, Apr 22 '16 at 10:25
If all you want to wind up with are the numbers after the colon, there are much simpler (and faster) ways to do this. But without an examples, it's hard to suggest anything more. — Ron Rosenfeld, Apr 22 '16 at 10:34
i don't prefer using macros, but i knew how to split cells and i didn't know how to take anything after a specified string. — Zach S. Thompson, Apr 22 '16 at 10:44
Look at VBA Split function. You could also use Instrrev and Right — Ron Rosenfeld, Apr 22 '16 at 11:21

score 0 · Answer 1 · answered Apr 22 '16 at 12:40

give this a try. It uses the split function rather than text to columns.

Sub Transposer()

Dim MySpl

SplitAgain:
    For x = 0 To 2
        MySpl = Split(ActiveCell.Offset(x, 0).Value, " : ")
        ActiveCell.Offset(0, x + 1).Value = MySpl(1)
    Next
    If ActiveCell.Offset(3, 0).Value <> "" Then
        ActiveCell.Offset(3, 0).Select
        GoTo SplitAgain
    End If

End Sub

Excel VBA transposing issues

1 Answers1