Split values from cell

[nkms143]

Hi. i'm having trouble in running VBA code. Actually, i have to split values from Column F delimited by "-" to adjacent cell which is shown in excel file. For that i have written the below code. The codes works fine splitting values in Column F but throws a "Run Time error'1004". Is there any solution to stop this bug. I wonder why it is happening. Plz Suggest me.

Dim myRng As Object
    Dim rwCount As Long
    Dim i As Long
    Dim vA As Variant
    rwCount = Application.WorksheetFunction.CountA(Sheets("PAY SCALES 1").Range("F1:F" & Rows.Count))
    Set myRng = Worksheets("PAY SCALES 1").Range("F1:F" & rwCount)
   
    ActiveWorkbook.Worksheets("PAY SCALES 1").Select
    Range("G1:BC" & Rows.Count).Clear   ' to clear unncessary content in cells
        With myRng.Select
            For i = 2 To rwCount
                vA = Split(myRng.Resize(1).Offset(i - 1), "-")
                myRng.Offset(i - 1).Resize(1, UBound(vA) + 1).Offset(, 1) = vA
            Next
        End With
      Range("G1").Select

 

[Hui]

Try this slightly modified code
Also clear the cells below F12

Sub splitting_cell()
  Dim rwCount As Long
  Dim i As Long
  Dim vA As Variant
  ActiveWorkbook.Worksheets("PAY SCALES 1").Select
  rwCount = Range("F" & Rows.Count).End(xlUp).Row

  Range("G:BC").Clear  ' to clear unncessary content in cells
  For i = 2 To rwCount
  vA = Split(Cells(i, 6).Text, "-")
  Cells(i, 7).Resize(1, UBound(vA) + 1) = vA
  Next
  Range("G1").Select
End Sub

 

[Deepak]

Hi @nkms143 ,

The error is thrown due to unavailability of data onward F13. Your code will work after cleaning the same.

Here's kind altered code!! It will work without cleaning too.

Sub splitting_cell()
  Dim rwCount As Long, r As Range, vA As Variant
  Application.ScreenUpdating = False
  With ActiveSheet
    rwCount = .Range("F" & .Rows.Count).End(xlUp).Row
    .Range("G2:BC" & .UsedRange.Rows.Count).Clear  ' to clear unncessary content in cells
     
      For Each r In .Range("F2:F" & rwCount)
        If InStr(r.Value, "-") Then
          vA = Split(r.Value, "-")
              r.Offset(, 1).Resize(1, UBound(vA) + 1) = vA
        End If
      Next
     
      .Range("G1").Select
    End With
   
    Application.ScreenUpdating = True

End Sub

 

[NARAYANK991]

Hi ,

The issue is basically with this statement :

rwCount = Application.WorksheetFunction.CountA(Sheets("PAY SCALES 1").Range("F1:F" & Rows.Count))

Your column F does not have appropriate data in rows 13 and beyond , but the problem is that the cells F13 through F245 are not blank.

If you select this range and clear the cells by pressing the DEL key , your existing code will work as it is.

Narayan

 

[nkms143]

Try this slightly modified code
Also clear the cells below F12

Sub splitting_cell()
  Dim rwCount As Long
  Dim i As Long
  Dim vA As Variant
  ActiveWorkbook.Worksheets("PAY SCALES 1").Select
  rwCount = Range("F" & Rows.Count).End(xlUp).Row

  Range("G:BC").Clear  ' to clear unncessary content in cells
  For i = 2 To rwCount
  vA = Split(Cells(i, 6).Text, "-")
  Cells(i, 7).Resize(1, UBound(vA) + 1) = vA
  Next
  Range("G1").Select
End Sub

This code also generates run time error.

 

[nkms143]

Hi ,

The issue is basically with this statement :

rwCount = Application.WorksheetFunction.CountA(Sheets("PAY SCALES 1").Range("F1:F" & Rows.Count))

Your column F does not have appropriate data in rows 14 and beyond , but the problem is that the cells F14 through F245 are not blank.

If you select this range and clear the cells by pressing the DEL key , your existing code will work as it is.

Narayan

@NARAYANK991 sir,
the cells in Column F contains formulas upto F245 which derives the above text. Hence, we cannot clean the formula.

 

[NARAYANK991]

Hi ,

Does your column F contain data or formulae ?

I am not able to see any formulae in the cells F2 through F12. Why is it that there should be formulae in cells F13 through F245 ?

The variable rwcount is supposed to tell you how many rows have valid data ; if this value is wrong , obviously the code will error out ; concealing this error or working around it is not the way code is written ; instead the code should then either look at a different column , or it should use a different method to arrive at a valid value of rwcount.

Anyway , it's your decision.

Narayan

 

[Hui]

My code works for me once I clear the the data below Column F12?

 

[nkms143]

Hi @nkms143 ,

The error is thrown due to unavailability of data onward F13. Your code will work after cleaning the same.

Here's kind altered code!! It will work without cleaning too.

Sub splitting_cell()
  Dim rwCount As Long, r As Range, vA As Variant
  Application.ScreenUpdating = False
  With ActiveSheet
    rwCount = .Range("F" & .Rows.Count).End(xlUp).Row
    .Range("G2:BC" & .UsedRange.Rows.Count).Clear  ' to clear unncessary content in cells
    
      For Each r In .Range("F2:F" & rwCount)
        If InStr(r.Value, "-") Then
          vA = Split(r.Value, "-")
              r.Offset(, 1).Resize(1, UBound(vA) + 1) = vA
        End If
      Next
    
      .Range("G1").Select
    End With
  
    Application.ScreenUpdating = True

End Sub

The Above Code Works like charm with few alterations. thanks Deepak Sir

 

[Deepak]

The Above Code Works like charm with few alterations. thanks Deepak Sir

Glad it worked!!!