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.

This line:
Set c = Range("A2")
.... limits the code to this single cell. Try expanding this range.

Dave, that line
Set c = Range("A2"), along with the lines

Do While Not IsEmpty(c) ,

Set n = c.Offset(1, 0) , and

Set c = n

was supposed to help the macro run until column A runs dry. Since c is
redefined everytime with the offset, how does that limit the code to
the single cell A2?

Joe.

In fact, when I use the "run" option on the BB editor, it points to the
line

FLSdata!Range(Cells(k, "A"), Cells(k, "O")) = RAWdata!Range(Cells(i,
"A"), Cells(i, "O"))

and says "run time error 424: Object required"

What does that mean?

I tried changing that line to:

FLSdata!Range(Cells(k, "A"), Cells(k, "O")).Value =

RAWdata!Range(Cells(i, "A"), Cells(i, "O")).Value

but keep getting the same message. (Please note that the code just
before this line which is exactly similar in logic gives no error).

Thnx,

-Joe.

Sorry, Joe, didn't read the code through and shot my mouth off. Mea
culpa.

When I pasted your code into my VBA editor and commented out all the
lines between Set n = c.Offset(1, 0) and Set c = n, the cell pointer
moved to each cell until a blank cell was encountered. I'll set up my
spreadsheet to emulate yours, and try some more debugging.

Thanks a lot, Dave. I'd really appreciate any help with this.

Joe.

Joe-
There were some very subtle logic flaws, as it turns out. In your
original code, this line
IRdata!Range(Cells(j, "A"), Cells(j, "O")) = RAWdata!Range(Cells(i,
"A"), Cells(i, "O"))
.... was interpreted as a boolean, rather than setting one range to
match another. (The C++ programming language uses a single = for
equivalence, such as A = 1, and a double == for booleans to avoid this
trap.)

I had to make some changes to the code to get it to run for me. I'm
guessing you have (at least) 4 tabs in the workbook: Rawdata, IRData,
FLSData, and another sheet where everything starts from, that cell A2
reference. I set up my code based on that guess- if that's wrong let
me know and I'll amend if you'd like; that sheet in my code is Sheet4.
To get around the logic problem I described earlier, I changed the code
to switch to the relevant sheet (based on the length of the value
Sheet4, cell A2 etc), copy the range, paste it to IRData or FLSData,
then flip back to Sheet4 to continue processing.

By the way, the reason your code errored out, requiring an object, even
tho the line before it with the same logic did *not* error out, is
because the length of the value was greater than 9, thus invoking the
"Else" portion of your original code.

This is the code I wound up with- let me know if it works for you.

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
Sheets("RAWdata").Select
Range(Cells(i, "A"), Cells(i, "O")).Copy
Sheets("IRdata").Select
Range(Cells(j, "A"), Cells(j, "O")).Select
ActiveSheet.Paste
j = j + 1
Sheets("Sheet4").Select
Else
Sheets("RAWdata").Select
Range(Cells(i, "A"), Cells(i, "O")).Copy
Sheets("FLSdata").Select
Range(Cells(k, "A"), Cells(k, "O")).Select
ActiveSheet.Paste
k = k + 1
Sheets("Sheet4").Select
End If
i = i + 1
Set c = n
Loop
End Sub

Hi Dave,

Thanks a lot for the detailed reply. However, I have a couple of
questions:

1. The cell A2 is in the worksheet RAWdata - it is the data in RAWdata
that is being filtered to either IRdata or FLSdata, depending on
whether or not the column A entries in RAWdata is longer than 9
characters.

I guess I should change the line:

Set c = Range("A2")

to

Set c = Worksheets("RAWdata").Range("A2")

Right?

I also included

Sheets("RAWdata").Select

just before

a = Len(Cells(i, "A")) so that the macro measures
the length of the cell .
entry in Column A of
RAWdata.


2. There is a new error message this time:

For the line right after "Else",

ie, for: (Sheets("RAWdata").Select)

it says: "Subscript out of range". Why does this happen?

Thanks,

Joe.

So now the macro reads as:

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 = Worksheets("RAWdata").Range("A2")

i = 2
j = 2
k = 2

Do While Not IsEmpty(c)
Set n = c.Offset(1, 0)
Sheets("RAWdata").Select
a = Len(Cells(i, "A"))

If a < 9 Then
Sheets("RAWdata").Select
Range(Cells(i, "A"), Cells(i,
"O")).Copy

Sheets("IRdata").Select
Range(Cells(j, "A"),
Cells(j, "O")).Select
ActiveSheet.Paste
j = j + 1

Else
Sheets("RAWdata").Select
Range(Cells(i, "A"), Cells(i,
"O")).Copy
Sheets("FLSdata").Select
Range(Cells(k, "A"),
Cells(k, "O")).Select
ActiveSheet.Paste
k = k + 1

End If
i = i + 1
Set c = n
Loop
End Sub

Hey Dave,

Mea Culpa this time. I mean, OOOPS!! ..lol..

Problem solved .. I forgot that the way I had named the worksheets,
there was a space between "RAW" and "data"

Really appreciate your help.

- Joe.