Split columns based on matching data in Excel VBA

Question

I have two columns in which I am comparing the text for both of them. Column A contains text 'Hello 2005 A LW Allocate' and the column B has text 'A LW' . I want to split the text in column A such that column C should have 'Hello 2005 Allocate' and D should have 'A LW'. The value in column B can be among a specific list of values {A, A LW, I , J} etc and I want to match the same text to that of column A and split it. I would really appreciate if someone can help. Right now, I have a code which looks something like this:

`Sub Testing()
  Dim DataRange As Range, CheckRange As Range, aCell As Range, bCell As Range
   Dim rightStrng As String
   Dim i As Long
  Set ws = Worksheets("Sheet 1")
  Set CheckRange = ws.Range("C2,C35000") - Column which has data
  Set DataRange = ws.Range("F2,F34") - Column to which I am comparing data
   With Worksheets("Sheet 1")
   For Each aCell In CheckRange.Rows
    Set bCell = DataRange.Find(What:=aCell, LookIn:=xlValues, _
 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  MatchCase:=False, SearchFormat:=False)
Set strng = Split(bCell)
Set rightStrng = ""
 i = UBound(strng)
rightStrng = Application.WorksheetFunction.Trim(rightStrng)
 Set bCell.Offset(, 2) = rightStrng
Set bCell.Offset(, 1) = Left(aCell.Value, IIf(rightStrng <> "", InStrRev(bCell.Value, rightStrng) - 2, Len(bCell.Value)))
Next aCell
End Sub`

Answer 1

Admittedly, this answer is still a little bit of this: https://www.youtube.com/watch?v=2ZO1jauV31U

But I felt an honest effort was given, despite the similar, original post.

Approach:

It looks like you are trying to do some string parsing, returning the position of the spaces in your string and then using Left to return the left half Mid to return the middle, and Right to return the right part. Instead, I might try iterating over your list of possible entries, if I find them, replace them with vbNullString and print what I found in Column B

Instr([Start], [String1], [String2], [Compare]:

The Instr function will return the position of String2 found in String1 or zero, if the string is not found. Start is the position in the string to start from (usually 1 for the beginning). Compare is the only true optional parameter, specifying how to compare the strings.

Debug.Print Instr(1, "abcd", "b") '<-- prints 2 in your immediate window

Replace

The Replace(Expression, Find, Replace) function will return the Expression string with instances of the Find string replaced with the Replace string.

Debug.Print Replace("abcdefg", "abc", "ABC") '<-- Prints "ABCdefg" in the immediate window.

Your application

Some pseudo code for you to consider:

Dim searchVals(3) as String
searchVals(0) = " A "
searchVals(1) = " A LW "
searchVals(2) = " I "
searchVals(3) = " J "    '<-- surrounded with spaces so we don't find all Js

'...some code you already wrote...
For Each myCell in myColumn
    For i = 0 to 3
    'For each search value I expect to find...
        'If this search value is in there...
        If InStr(1, myCell.Value, searchVals(i)) <> 0 Then
            'I found my string!  Replace the searchVal with a null string (i.e. remove it)
            myCell.Offset(,1) = Replace(myCell.Value, searchVals(i), vbNullString)
            'Print the string I found two to the Right
            myCell.Offset(,2) = searchVals(i)
            Exit For '<-- Once I find one, I can stop searching
        End If
    Next i
Next myCell

Errors in your code

I suspect you are getting an error message one this line:

Set strng = Split(bCell)

The Set keyword is reserved for objects in VBA and Split returns an array of strings. So both these lines:

Set strng = Split(bCell)
Set rightStrng = ""

should be:

strng = Split(bCell)
rightStrng = ""

For newbies, this is typically something to memorize, but for more technical stuff on what the Set keyword does: What does the keyword Set actually do in VBA?