+ Reply to Thread
Results 1 to 10 of 10

Macro wont run!

  1. #1
    Joe
    Guest

    Macro wont run!

    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.


  2. #2
    Dave O
    Guest

    Re: Macro wont run!

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


  3. #3
    Joe
    Guest

    Re: Macro wont run!

    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.


  4. #4
    Joe
    Guest

    Re: Macro wont run!

    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.


  5. #5
    Dave O
    Guest

    Re: Macro wont run!

    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.


  6. #6
    Joe
    Guest

    Re: Macro wont run!

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

    Joe.


  7. #7
    Dave O
    Guest

    Re: 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


  8. #8
    Joe
    Guest

    Re: Macro wont run!

    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.


  9. #9
    Joe
    Guest

    Re: Macro wont run!

    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


  10. #10
    Joe
    Guest

    Re: Macro wont run!

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1