If cell value in column <> certain text then cell value splits with text to columns

1
votes

I am attempting to determine if the cell values in column H (number of rows varies per file) are "Words" or "Words2". The cells that do not contain "Words" or "Words2" are split into separate columns based on a space delimiter while those that do contain "Words" and "Words2" have no change. Currently my code does not separates any cells. I am new to using Dim as Range and InStr and I am not entirely sure if it is most efficient to use them. I have tried multiple variations of the code to no avail, as well. ARGNAME is each cell in column H (or at least that is what I am aiming for).

For example: H1 = "Words" no change, and H2 = "Words I don't care about" splits into H2:L2

Sub WordSplit
    Dim lastRow As Long
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Dim SrchRng As Range, ARGNAME As Range
        Set SrchRng = Range("H1:H" & lastRow)
        For Each ARGNAME In SrchRng
            If InStr(1, ARGNAME.Value, "Words") Or InStr(1, ARGNAME.Value, "Words2") < 0 _
            Then Columns("H:H").TextToColumns Destination:=Range ("H1"), DataType _
                :=xlDelimited, ConsecutiveDelimiter:=True, Space:=True, _
                FieldInfo:=Array(Array(1, 1)),TrailingMinusNumbers:=True
            End If
        Next ARGNAME
End Sub
2
excelvba

2 Answers

0
votes
Option Explicit

Public Sub WordSplit1()
    Dim ws As Worksheet
    Set ws = Sheet1

    Dim lastRow As Long
    lastRow = ws.Range("H" & Rows.Count).End(xlUp).Row

    Dim srchRng As Range, argName As Range
    Set srchRng = ws.Range("H1:H" & lastRow)

    For Each argName In srchRng

        If InStr(1, argName.Value2, "Words") = 0 And _
           InStr(1, argName.Value2, "Words2") = 0 Then

                argName.TextToColumns Destination:=argName, _
                                      DataType:=xlDelimited, _
                                      ConsecutiveDelimiter:=True, _
                                      Space:=True, _
                                      FieldInfo:=Array(Array(1, 1)), _
                                      TrailingMinusNumbers:=True
        End If

    Next
End Sub

Your initial code was on the right track, but I updated the following:

  • Used Option Explicit at the top of the module
  • Fully qualified all ranges with a specific sheet (CodeName: Sheet1)
  • Fixed code indentation to make it easier to read
  • Issues related to InStr:
    • It only returns positive integers ( >= 0 )
    • All InStr expressions must be checked for return values (not just the last one)
    • If the cell doesn't contain "Words" AND it doesn't contain "Words2"
    • Initial If:
    • If InStr(1, ARGNAME.Value, "Words") Or InStr(1, ARGNAME.Value, "Words2") < 0
    • becomes:
    • If InStr(1, argName.Value, "Words") = 0 And InStr(1, argName.Value, "Words2") = 0
  • Next issue:
    • The If statement is separated onto the second line in an invalid location (syntax error)
    • ") < 0 _ Then Columns("H:H")
    • Your next line splits all cells in column H containing multiple words for every cell not containing "Words" or "Words2"

The code implies that there is no data beyond column H

0
votes

Try something like this

Sub WordSplit()
Dim lastRow As Long
Dim r As Long

lastRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count

    For r = 1 To lastRow
        If Cells(r, 8) = "Words" Or Cells(r, 8) = "Words2" Then
        Else
            Cells(r, 8).TextToColumns Destination:=Cells(r, 8), 
            DataType:=xlDelimited,TextQualifier:=xlDoubleQuote, 
            ConsecutiveDelimiter:=True
        End If
    Next r
End Sub