extract cells content to multiple columns


hi  experts  

I  try extracting  digits & letters from sheet1 in column A   into  headers for multiple  columns   to sheet2  into  multiple  columns   . so  I  put   the  right  result  in sheet2 sheet  should  be  .  the   code  gives error "subsript out of  range in this  line 

  ArrSubData = Split(ArrData(3), ",")

this  is  entire  code 

Sub ExtractData()

Dim n As Long
Dim ArrData() As String, ArrSubData() As String
Dim cell As Range, rngData As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")
Set rngData = ws1.Range("A1", ws1.Cells(Rows.Count, "A").End(xlUp))

n = 2
For Each cell In rngData
    ArrData = Split(cell, Chr(10))
    ArrSubData = Split(ArrData(3), ",")
    ws2.Range("A" & n) = n - 1
    ws2.Range("B" & n) = Split(ArrData(4), "COMPANY: ")(1)
    ws2.Range("C" & n) = Split(ArrData(5), "PHONE: ")(1)
    ws2.Range("D" & n) = Split(ArrData(0), "TIRES SIZE  ")(1)
    ws2.Range("E" & n) = Split(ArrData(1), "& TYPE OF ")(1)
    ws2.Range("F" & n) = Split(ArrData(2), "ORIGIN is ")(1)
    ws2.Range("G" & n) = Replace(Split(ArrSubData(0), "OREDER: ")(1), " TIRES", "")
    ws2.Range("H" & n) = Split(ArrSubData(1), " AVAILABLE IS ")(1)
    n = n + 1

End Sub

any  help  to  fix  this  error,please?



Selected Answer

Hi Leopard

You have a blank cell in A1 of Sheet1 (set as ws1) so when you split it, you don't get several elements in the array  (and index 3 does not exist so VBA fails at that line)..

You just need to change one line (with the bit in bold):

Set rngData = ws1.Range("A2", ws1.Cells(Rows.Count, "A").End(xlUp))
It might be a good idea to check that a looped "cell" actually contains text before you attempt to split it (in case there are blank cells midway down the column).

Hope this fixes it for you.



OMG!!   my bad !
thanks  for  this  notice 
leopard (rep: 64) Nov 25, '21 at 4:39 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login