VBA Formula mismatch

Question

Will you please help with this line of code?

I am attempting to achieve the format shown in the image. I can do this just fine, without VBA. I am wanting the code to count how many entries there are in a column from B9 to B500.

For the number of entries, If the value <> "", then set cell in the same row in column L equal to "=LEFT(B "row number", FIND(" - ",B "row number")-1)"

For the number of entries, If the value <> "", then set cell in the same row in column M equal to "=RIGHT(B "row number",LEN(B "row number")-FIND(" - ",B "row number"))"

Answer 1

'Artist - Title' Column?

The Excel formulas should look like this:

' In Cell L9: =IF(ISERROR(FIND(" - ",B9)),"",LEFT(B9,FIND(" - ",B9)-1))
' In Cell M9: =IF(ISERROR(FIND(" - ",B9)),"",RIGHT(B9,LEN(B9)-FIND(" - ",B9)-LEN(" - ")+1))

Carefully read at least the customize sections of the codes to avoid losing data.

A 'Classical' For Next Approach

Sub CellsSplitterForNext()
'Description:
  'Separates the delimited contents of cells in a column to new columns.

'Excel Formulas:
' In Cell L9: =IF(ISERROR(FIND(" - ",B9)),"",LEFT(B9,FIND(" - ",B9)-1))
' In Cell M9: =IF(ISERROR(FIND(" - ",B9)),"",RIGHT(B9,LEN(B9)-FIND(" - ",B9)-LEN(" - ")+1))

'**** Customize BEGIN ******************
  Const cStrSource As String = "B" 'Source Column
  Const cStrTarget1 As String = "L" 'Target Column 1
  Const cStrTarget2 As String = "M" 'Target Column 2
  Const cStrSplitter As String = " - " 'Split String
  Const cLngFirst As Long = 9 'First Row
  Const cLngLast As Long = 500 'Last Row(0 to choose last row of data in column)
'**** Customize END ********************

  Dim lng1 As Long 'Row Counter
  Dim lngLast As Long 'Last Row

  'I would rather the code automatically calculate the last row then be tied up
  'to 500 rows, that is, if there is no data below. The same can be done for
  'the first row if it contains the first data in the column. You have to change
  '"cLngLast as Long = 0" in the customize section for this to work.
  If cLngLast = 0 Then
    lngLast = Cells(Rows.Count, cStrSource).End(xlUp).Row
   Else
    lngLast = cLngLast
  End If

  For lng1 = cLngFirst To lngLast
    If InStr(Cells(lng1, cStrSource), cStrSplitter) <> 0 Then
      Cells(lng1, cStrTarget1) = Split(Cells(lng1, cStrSource), cStrSplitter)(0)
      Cells(lng1, cStrTarget2) = Split(Cells(lng1, cStrSource), cStrSplitter)(1)
     Else
      Cells(lng1, cStrTarget1) = ""
      Cells(lng1, cStrTarget2) = ""
    End If
  Next

End Sub

The Super Fast Array Approach

Sub CellsSplitterArray()
'Description:
  'Separates the delimited contents of cells in a column to new columns.

'Excel Formulas:
' In Cell L9: =IF(ISERROR(FIND(" - ",B9)),"",LEFT(B9,FIND(" - ",B9)-1))
' In Cell M9: =IF(ISERROR(FIND(" - ",B9)),"",RIGHT(B9,LEN(B9)-FIND(" - ",B9)-LEN(" - ")+1))

'**** Customize BEGIN ******************
  Const cStrSource As String = "B" 'Source Column
  Const cStrTarget1 As String = "L" 'Target Column 1
  'Note: In this version Target Column 2 has to be the next adjacent column
  'to Target Column 1
  Const cStrTarget2 As String = "M" 'Target Column 2
  Const cStrSplitter As String = " - " 'Split String
  Const cLngFirst As Long = 9 'First Row
  Const cLngLast As Long = 500 'Last Row(0 to choose last row of data in column)
'**** Customize END ********************

  Dim oRng As Range
  Dim arrSource As Variant 'Source Array
  Dim arrTarget As Variant 'Target Array
  Dim int1 As Integer 'Target Array Columns Counter

  Dim lng1 As Long 'Row Counter
  Dim lngLast As Long 'Last Row

  Const c1 As String = "," 'Debug String Column Separator
  Const r1 As String = vbCr 'Debug String Row Separator
  Dim str1 As String 'Debug String Concatenator

  'I would rather the code automatically calculate the last row then be tied up
  'to 500 rows, that is, if there is no data below. The same can be done for
  'the first row if it contains the first data in the column. You have to change
  '"cLngLast as Long = 0" in the customize section for this to work.
  If cLngLast = 0 Then
    lngLast = Cells(Rows.Count, cStrSource).End(xlUp).Row
   Else
    lngLast = cLngLast
  End If

  'Source Range
  Set oRng = Range(Range( _
      Cells(cLngFirst, cStrSource), _
      Cells(lngLast, cStrSource) _
      ).Address)
  'Source Array
  arrSource = oRng

'            str1 = str1 & "*** arrSource Data ***"
'            For lng1 = LBound(arrSource) To UBound(arrSource)
'              str1 = str1 & r1 & arrSource(lng1, 1)
'            Next

  'Target Array
  ReDim arrTarget(LBound(arrSource) To UBound(arrSource), 1 To 2)

  For lng1 = LBound(arrSource) To UBound(arrSource)
    If InStr(arrSource(lng1, 1), cStrSplitter) <> 0 Then
      For int1 = 1 To 2
        arrTarget(lng1, int1) = _
            Split(arrSource(lng1, 1), cStrSplitter)(int1 - 1)
      Next
    End If
  Next

'            str1 = str1 & r1 & "*** arrTarget Data ***"
'            For lng1 = LBound(arrTarget) To UBound(arrTarget)
'              If Not arrTarget(lng1, 1) = "" And Not arrTarget(lng1, 2) = "" Then
'                str1 = str1 & r1 & arrTarget(lng1, 1)
'                str1 = str1 & c1 & arrTarget(lng1, 2)
'               Else
'                str1 = str1 & r1
'              End If
'            Next

  'Target Range
  Set oRng = Range(Range( _
      Cells(cLngFirst, cStrTarget1), _
      Cells(lngLast, cStrTarget2) _
      ).Address)

  oRng = arrTarget

'            Debug.Print str1

End Sub

Answer 2

It should probably be something like this. The key is to create a counter that counts successful items and increment it according to your logical evaluation. After that, you can either use the Offset function or just add it to the row value in your destination address.

dim rwcnt, itemcnt as integer    

itemcnt = 0    '<- This is your counter for each non-blank row
for rwcnt = 9 to 500
    if activesheet.cells(rwcnt,2).value <> "" then
        itemcnt = itemcnt + 1      '<- This increments it BEFORE you start copying information, so if you want to print out how many items there were, etc. 
        activesheet.cells(9,12).offset(itemcnt,0).value = left(activesheet.cells(rwcnt,2).value,instr(1,"-",activesheet.cells(rwcnt,2),vbtextcompare))     '<- This part begins your copying stuff
        activesheet.cells(9,12).offset(itemcnt,1).value = right(activesheet.cells(rwcnt,2).value,len(activesheet.cells(rwcnt,2).value)-instr(1,"-",activesheet.cells(rwcnt,2), vbtextcompare))
    end if
next rwcnt

Answer 3

Use Text-to-Columns split on the hyphen as delimiter.

sub splitHypen()
    with worksheets("sheet1")
        .range(.cells(9, "B"), .cells(9, "B").end(xldown)).TextToColumns _
                Destination:=.cells(9, "L"), 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))
    end with
end sub