extract cells content to multiple columns

0

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
Next

End Sub

any  help  to  fix  this  error,please?

Answer
Discuss

Answers

0
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.

Discuss

Discussion

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