Search code examples
excelvba

VBA - Text to columns in specific row

I've run into a problem again (I hope I don't ask any more questions). I need to split data from column F (rows will be added every day) into columns G,H,I. The data in column F has the format "XXX YY ZZZ_ZZZZ randomdatahere". I need to split XXX into column G, XX into column H, ZZZ_ZZZZ into column I and let the program ignore the rest (anything after ZZZ_ZZZZ) or if it could not be ignored, let it throw it into column J.

I have a command that works for this (I have modified the format to "XXX-YY-ZZZ_ZZZZ randomdatahere" where I use "-" to define what the command should split it according to. The problem is that I need to make sure that the split is done in that particular line that is marked. Now if I mark specific rows it will overwrite from row G4 (first row from where i need to start), I need to move the target location to that specific marked row(s). I tried to find a solution but I am quite lost in VBA.

Private Sub Generate_Click()
  Dim destRng As Range
    
    If Selection.Columns.Count = 1 Then
    
    Set destRng = Range("G4")
    
    Selection.TextToColumns , Destination:=destRng, _
    DataType:=xlDelimited, Other:=True, OtherChar:="-", Other:=False, OtherChar:="_"
    
    Else
    MsgBox "Blah blah blah"
    End If
    
End Sub

EDIT :

By specific row I mean that after marking the rows for example F15:F25 the columns G,H,I will also be filled in rows G15:G25, H15:H25 etc. If someone in row F14 forgot to mark the row and split that column F so that row F15 will not be entered in cell G14,H14 and I14. I am also sending a picture of how I mean it.Example

Solution

  • This mod. use the selection rows to define the destination.

    Private Sub Generate_Click()
  Dim destRng As Range
    
    If Selection.Columns.Count = 1 Then
    
    'Set destRng = Range("G4")  instead this
    
    Selection.TextToColumns , Destination:=Range("G" & Selection.Row), _   'mod
    DataType:=xlDelimited, Other:=True, OtherChar:="-", Other:=False, OtherChar:="_"
    
    Else
    MsgBox "Blah blah blah"
    End If
    
End Sub