Thread: Macro wont run!
View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default Macro wont run!

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