simple macro gives error .. help needed

[Joe]

hi all,

Can someone pls tell me what is wrong with this code? (It just wont
run!...)

I am trying to split data from worksheet "RAW data" into two worksheets
"IR data" and "FLS data", based on the length of the string in column A
of RAW data. The macro is intended to run until entry in column A
becomes empty.

Sub splitdata()
Dim i As Variant
Dim j As Variant
Dim k As Variant
Dim a As Variant
Dim c As Range 'current
Dim n As Range 'next

Set c = Range("A2")

i = 2
j = 2
k = 2

Do While Not IsEmpty(c)
Set n = c.Offset(1, 0)
a = Len(Cells(i, "A"))
If a < 9 Then
IRdata!Range(Cells(j, "A"), Cells(j, "O")) =
RAWdata!Range(Cells(i, "A"), Cells(i, "O"))
j = j + 1
Else
FLSdata!Range(Cells(k, "A"), Cells(k, "O")) =
RAWdata!Range(Cells(i, "A"), Cells(i, "O"))
k = k + 1
End If
i = i + 1
Set c = n
Loop
End Sub

Thnx,

Joe.

 

[Guest]

Joe,

Try this:

Sub splitdata()

Dim i As Long
Dim j As Long
Dim k As Long
Dim lastrow as Long


Set ws1 = Worksheets("RAWdata")
Set ws2 = Worksheets("IRdata")
Set ws3 = Worksheets("FLSdata")


j = 2
k = 2

lastrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row

With ws1
For i = 2 To lastrow
If Len(.Cells(i, 1)) < 9 Then
.Range("a" & i & "" & i).Copy ws2.Range("a" & j)
j = j + 1
Else
.Range("a" & i & "" & i).Copy ws3.Range("a" & k)
k = k + 1
End If

Next i
End With
End Sub

HTH

 

[Joe]

Toppers,

Thanks a lot.

- Joe.