VBA Code to automate Text To Columns for Row Entries w/ Comma as Delimiter

[flipland]

Hi. Could use your help in figuring out proper VBA code to use on a per entry basis, row by row.
Entry is from left to right along the row from “Date” to “Receipt #”.

When it gets to the end of the row, that VBA code should return to the “Description” cell for that row and then execute “Text to Columns” with the text then fiiling in the “Type” and “Category” cells on the same row.

Here's an image link for the above-mentioned row:
https://drive.google.com/file/d/1aKkdGyhK9LUip4-DIbyV4VkpCFqUQboF/view?usp=sharing

The Code is below. I’m getting stuck with the “Destination:=Range(“I41”). I tried to change to Range(“I41”) to “Destination:=ActiveCell". Is that even correct?! I am stuck going nowhere.
Would appreciate your help. Thanks.

  [I][B][COLOR=#008080]ActiveCell(0, -7).Select[/COLOR][/B]
Selection.TextToColumns [COLOR=#008080][B]Destination:=Range("I41")[/B][/COLOR], DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True [/I]

 

[DanteAmor]

Try this
To do so with the entire column H from row 2 and to the last row with data.
Change 2 and H if they are other data.

Sub Macro4()
  Range("[B][COLOR=#ff0000]H2:H[/COLOR][/B]" & Range("[COLOR=#ff0000][B]H[/B][/COLOR]" & Rows.Count).End(xlUp).Row).TextToColumns _
    Destination:=Range("[COLOR=#ff0000][B]H2[/B][/COLOR]"), DataType:=xlDelimited, _
    TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
End Sub

 

[flipland]

Hi Dante,

Thank you for this. Still getting stuck though. I am trying to make it run on a per row basis on a per entry basis, not for the entire column. When I reach the end of the row at Column P, I click on a Save Entry button that recognizes that cells at Column P as the active cell and offsett -7 on the same row to Column I. At that cell on the same row at Column I, it will perform Text to Columns (only for that row) and the information from Column I is then filled out in Columns J and K. The file is saved and the code ends.

The code you shared, when executed, overwrites all the the info in the previous rows for Columns I, J and K and is peformed only on the previous rows, but not in the rows with the new entiries. The link to the images is below.

https://drive.google.com/file/d/1TY8LLgiai-2xN8NUA3OJEZHpOKWT7tGE/view?usp=sharing

Is it possible to make this work on a row-by-row basis?

 

[DanteAmor]

Check if this helps you

  ActiveCell(0, -7).Select
Selection.TextToColumns Destination:=Range([COLOR=#0000ff]activecell.address[/COLOR]), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True