Results 1 to 6 of 6

Thread: [RESOLVED]It used to function - now it doesn't!!!

  1. Apr 18th, 2006, 03:49 AM #1
    direktoren
    direktoren is offline

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Posts
    96

    Resolved [RESOLVED]It used to function - now it doesn't!!!

    Hi all

    I've made a module for organizing special types of input at work. after several problems I finally found a way to make it work - Then I made it into a template and it still worked all right.... Know I need to use it again and all of a sudden I'm getting a "range" error on object "worksheet".... WHY???

    My code is very simple, since I'm using processing sheets, thus I'm merely copy/pasting... Code as follows;

    VB Code:
    1. Sub flexvalue()

    2. Dim j As Double

    3. Dim k As Double

    4. Dim iSheet As Worksheet

    5. Dim nSheet As Worksheet

    6. Dim rSheet As Worksheet

    7. Dim nkSheet As Worksheet

    8.  
    9.     Set iSheet = ActiveWorkbook.Sheets("Input")

    10.     Set nSheet = ActiveWorkbook.Sheets("Nedskrivning-Lån")

    11.     Set rSheet = ActiveWorkbook.Sheets("Resultater")

    12.     Set nkSheet = ActiveWorkbook.Sheets("Nedskrivning-Kredit")

    13.     

    14. Application.ScreenUpdating = False

    15.     nSheet.Activate

    16.     

    17.     For j = 2 To 10000

    18.         nSheet.Cells(20, 16).Value = iSheet.Cells(j, 16).Value

    19.             iSheet.Activate

    20.             If iSheet.Range(Cells(j, 16), Cells(j, 16)).Value > 0 Then

    21.                 iSheet.Range(Cells(j, 1), Cells(j, 1)).Copy

    22.                 rSheet.Activate

    23.                 rSheet.Range(Cells(j, 1), Cells(j, 1)).PasteSpecial Paste:=xlValues

    24.                 iSheet.Activate

    25.                 iSheet.Range(Cells(j, 3), Cells(j, 5)).Copy

    26.                 rSheet.Activate

    27.                 rSheet.Range(Cells(j, 2), Cells(j, 4)).PasteSpecial Paste:=xlValues

    and so forth...

    As I said - used to work, now it doesn't?

    /Nick
    Last edited by direktoren; Apr 18th, 2006 at 07:26 AM. Reason: resolved
    Reply With Quote Reply With Quote
  2. Apr 18th, 2006, 05:52 AM #2
    TheBionicOrange
    TheBionicOrange is offline
    Frenzied Member TheBionicOrange's Avatar
    Join Date
    Apr 2001
    Location
    Cardiff, UK
    Posts
    1,818

    Re: It used to function - now it doesn't!!!

    Sounds like you are leaving an Excel process open after you run it for the first time.
    Check in your code that EVERYTHING is assigned to the values you declared.
    The "Range" property is an obvious one to forget. If its not tied down it creates one on the fly. A good test is to do a "CTRL/ALT/DELETE" and check your processes for any called "Excel". If found kill them off manually.
    Then re-run your script and repeat the process and see if any are still showing. If they are they you have got something not assigned properly in your code.

    If that doesn't help then post your full code and I'll take a look at it if you like.
    Reply With Quote Reply With Quote
  3. Apr 18th, 2006, 06:10 AM #3
    direktoren
    direktoren is offline

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Posts
    96

    Re: It used to function - now it doesn't!!!

    Bio
    Not sure I understand you correct, but when I'm browsing through my processes I find one Excel.exe (obviously, since I'm running excel), and if I terminate that process then exce shuts down...

    anyways here is the entire code for the given procedure;

    VB Code:
    1. Sub flexvalue()

    2. Dim j As Double

    3. Dim k As Double

    4. Dim iSheet As Worksheet

    5. Dim nSheet As Worksheet

    6. Dim rSheet As Worksheet

    7. Dim nkSheet As Worksheet

    8.  
    9.     Set iSheet = ActiveWorkbook.Sheets("Input")

    10.     Set nSheet = ActiveWorkbook.Sheets("Nedskrivning-Lån")

    11.     Set rSheet = ActiveWorkbook.Sheets("Resultater")

    12.     Set nkSheet = ActiveWorkbook.Sheets("Nedskrivning-Kredit")

    13.     

    14. Application.ScreenUpdating = False

    15.     nSheet.Activate

    16.     

    17.     For j = 2 To 10000

    18.         nSheet.Cells(20, 16).Value = iSheet.Cells(j, 16).Value

    19.             iSheet.Activate

    20.             If iSheet.Range(Cells(j, 16), Cells(j, 16)).Value > 0 Then

    21.                 iSheet.Range(Cells(j, 1), Cells(j, 1)).Copy

    22.                 rSheet.Activate

    23.                 rSheet.Range(Cells(j, 1), Cells(j, 1)).PasteSpecial Paste:=xlValues

    24.                 iSheet.Activate

    25.                 iSheet.Range(Cells(j, 3), Cells(j, 5)).Copy

    26.                 rSheet.Activate

    27.                 rSheet.Range(Cells(j, 2), Cells(j, 4)).PasteSpecial Paste:=xlValues

    28.                     If iSheet.Cells(j, 15).Value = "Lån" Then

    29.                         iSheet.Activate

    30.                         iSheet.Range(Cells(j, 17), Cells(j, 17)).Copy

    31.                         nSheet.Activate

    32.                         nSheet.Range(Cells(14, 2), Cells(14, 2)).PasteSpecial Paste:=xlValues

    33.                         iSheet.Activate

    34.                         iSheet.Range(Cells(j, 3), Cells(j, 3)).Copy

    35.                         nSheet.Activate

    36.                         nSheet.Range(Cells(20, 6), Cells(20, 6)).PasteSpecial Paste:=xlValues

    37.                         iSheet.Activate

    38.                         iSheet.Range(Cells(j, 5), Cells(j, 5)).Copy

    39.                         nSheet.Activate

    40.                         nSheet.Range(Cells(20, 3), Cells(20, 3)).PasteSpecial Paste:=xlValues

    41.                         iSheet.Activate

    42.                         iSheet.Range(Cells(j, 21), Cells(j, 30)).Copy

    43.                         nSheet.Activate

    44.                         nSheet.Range("$B$20:$B$29").PasteSpecial Transpose:=True, Paste:=xlValues

    45.                         iSheet.Activate

    46.                         iSheet.Range(Cells(j, 31), Cells(j, 40)).Copy

    47.                         nSheet.Activate

    48.                         nSheet.Range("$B$35:$B$44").PasteSpecial Transpose:=True, Paste:=xlValues

    49.                         nSheet.Range(Cells(20, 10), Cells(29, 10)).Copy

    50.                         rSheet.Activate

    51.                         rSheet.Range(Cells(j, 5), Cells(j, 5)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True

    52.                         nSheet.Activate

    53.                         nSheet.Range(Cells(35, 10), Cells(44, 10)).Copy

    54.                         rSheet.Activate

    55.                         rSheet.Range(Cells(j, 15), Cells(j, 15)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True

    56.                         nSheet.Activate

    57.                         nSheet.Range(Cells(31, 12), Cells(31, 12)).Copy

    58.                         rSheet.Activate

    59.                         rSheet.Range(Cells(j, 25), Cells(j, 25)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True

    60.                         nSheet.Activate

    61.                         nSheet.Range(Cells(46, 12), Cells(46, 12)).Copy

    62.                         rSheet.Activate

    63.                         rSheet.Range(Cells(j, 26), Cells(j, 26)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True

    64.                         nSheet.Activate

    65.                         nSheet.Range(Cells(48, 12), Cells(48, 12)).Copy

    66.                         rSheet.Activate

    67.                         rSheet.Range(Cells(j, 27), Cells(j, 27)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True

    68.                         nSheet.Activate

    69.                         nSheet.Range(Cells(48, 12), Cells(48, 12)).Copy

    70.                         iSheet.Activate

    71.                         iSheet.Range(Cells(j, 41), Cells(j, 41)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False

    72.                     Else

    73.                         iSheet.Activate

    74.                         iSheet.Range(Cells(j, 17), Cells(j, 17)).Copy

    75.                         nkSheet.Activate

    76.                         nkSheet.Range(Cells(8, 2), Cells(8, 2)).PasteSpecial Paste:=xlValues

    77.                         iSheet.Activate

    78.                         iSheet.Range(Cells(j, 3), Cells(j, 3)).Copy

    79.                         nkSheet.Activate

    80.                         nkSheet.Range(Cells(14, 6), Cells(14, 6)).PasteSpecial Paste:=xlValues

    81.                         iSheet.Activate

    82.                         iSheet.Range(Cells(j, 5), Cells(j, 5)).Copy

    83.                         nkSheet.Activate

    84.                         nkSheet.Range(Cells(14, 3), Cells(14, 3)).PasteSpecial Paste:=xlValues

    85.                         iSheet.Activate

    86.                         iSheet.Range(Cells(j, 21), Cells(j, 23)).Copy

    87.                         nkSheet.Activate

    88.                         nkSheet.Range("$B$14:$B$16").PasteSpecial Transpose:=True, Paste:=xlValues

    89.                         iSheet.Activate

    90.                         iSheet.Range(Cells(j, 31), Cells(j, 33)).Copy

    91.                         nkSheet.Activate

    92.                         nkSheet.Range("$B$22:$B$24").PasteSpecial Transpose:=True, Paste:=xlValues

    93.                         nkSheet.Range(Cells(14, 10), Cells(16, 10)).Copy

    94.                         rSheet.Activate

    95.                         rSheet.Range(Cells(j, 5), Cells(j, 5)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True

    96.                         nkSheet.Activate

    97.                         nkSheet.Range(Cells(22, 10), Cells(24, 10)).Copy

    98.                         rSheet.Activate

    99.                         rSheet.Range(Cells(j, 15), Cells(j, 15)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True

    100.                         nkSheet.Activate

    101.                         nkSheet.Range(Cells(18, 12), Cells(18, 12)).Copy

    102.                         rSheet.Activate

    103.                         rSheet.Range(Cells(j, 25), Cells(j, 25)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True

    104.                         nkSheet.Activate

    105.                         nkSheet.Range(Cells(26, 12), Cells(26, 12)).Copy

    106.                         rSheet.Activate

    107.                         rSheet.Range(Cells(j, 26), Cells(j, 26)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True

    108.                         nkSheet.Activate

    109.                         nkSheet.Range(Cells(28, 12), Cells(28, 12)).Copy

    110.                         rSheet.Activate

    111.                         rSheet.Range(Cells(j, 27), Cells(j, 27)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True

    112.                         nkSheet.Activate

    113.                         nkSheet.Range(Cells(28, 12), Cells(28, 12)).Copy

    114.                         iSheet.Activate

    115.                         iSheet.Range(Cells(j, 41), Cells(j, 41)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False

    116.                     End If

    117.             End If

    118.     Next j

    119.  
    120. Application.CutCopyMode = False

    121.  
    122.     Set iSheet = Nothing

    123.     Set nSheet = Nothing

    124.     Set rSheet = Nothing

    125.     Set nkSheet = Nothing

    126.         

    127.     Application.ScreenUpdating = True

    128.     

    129. End Sub

    /Nick
    Reply With Quote Reply With Quote
  4. Apr 18th, 2006, 06:13 AM #4
    TheBionicOrange
    TheBionicOrange is offline
    Frenzied Member TheBionicOrange's Avatar
    Join Date
    Apr 2001
    Location
    Cardiff, UK
    Posts
    1,818

    Re: It used to function - now it doesn't!!!

    Try this :

    Code:
    Sub flexvalue()
Dim j As Double
Dim k As Double
Dim iSheet As Worksheet
Dim nSheet As Worksheet
Dim rSheet As Worksheet
Dim nkSheet As Worksheet

    Set iSheet = ActiveWorkbook.Sheets("Input")
    Set nSheet = ActiveWorkbook.Sheets("Nedskrivning-Lån")
    Set rSheet = ActiveWorkbook.Sheets("Resultater")
    Set nkSheet = ActiveWorkbook.Sheets("Nedskrivning-Kredit")
    
Application.ScreenUpdating = False
    nSheet.Activate
    
    For j = 2 To 10000
        nSheet.cells(20, 16).Value = iSheet.cells(j, 16).Value
            iSheet.Activate
            If iSheet.Range(.cells(j, 16), .cells(j, 16)).Value > 0 Then
                iSheet.Range(.cells(j, 1), .cells(j, 1)).Copy
                rSheet.Activate
                rSheet.Range(.cells(j, 1), .cells(j, 1)).PasteSpecial Paste:=xlValues
                iSheet.Activate
                iSheet.Range(.cells(j, 3), .cells(j, 5)).Copy
                rSheet.Activate
                rSheet.Range(.cells(j, 2), .cells(j, 4)).PasteSpecial Paste:=xlValues
                    If iSheet.cells(j, 15).Value = "Lån" Then
                        iSheet.Activate
                        iSheet.Range(.cells(j, 17), .cells(j, 17)).Copy
                        nSheet.Activate
                        nSheet.Range(.cells(14, 2), .cells(14, 2)).PasteSpecial Paste:=xlValues
                        iSheet.Activate
                        iSheet.Range(.cells(j, 3), .cells(j, 3)).Copy
                        nSheet.Activate
                        nSheet.Range(.cells(20, 6), .cells(20, 6)).PasteSpecial Paste:=xlValues
                        iSheet.Activate
                        iSheet.Range(.cells(j, 5), .cells(j, 5)).Copy
                        nSheet.Activate
                        nSheet.Range(.cells(20, 3), .cells(20, 3)).PasteSpecial Paste:=xlValues
                        iSheet.Activate
                        iSheet.Range(.cells(j, 21), .cells(j, 30)).Copy
                        nSheet.Activate
                        nSheet.Range("$B$20:$B$29").PasteSpecial Transpose:=True, Paste:=xlValues
                        iSheet.Activate
                        iSheet.Range(.cells(j, 31), .cells(j, 40)).Copy
                        nSheet.Activate
                        nSheet.Range("$B$35:$B$44").PasteSpecial Transpose:=True, Paste:=xlValues
                        nSheet.Range(.cells(20, 10), .cells(29, 10)).Copy
                        rSheet.Activate
                        rSheet.Range(.cells(j, 5), .cells(j, 5)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True
                        nSheet.Activate
                        nSheet.Range(.cells(35, 10), .cells(44, 10)).Copy
                        rSheet.Activate
                        rSheet.Range(.cells(j, 15), .cells(j, 15)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True
                        nSheet.Activate
                        nSheet.Range(.cells(31, 12), .cells(31, 12)).Copy
                        rSheet.Activate
                        rSheet.Range(.cells(j, 25), .cells(j, 25)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True
                        nSheet.Activate
                        nSheet.Range(.cells(46, 12), .cells(46, 12)).Copy
                        rSheet.Activate
                        rSheet.Range(.cells(j, 26), .cells(j, 26)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True
                        nSheet.Activate
                        nSheet.Range(.cells(48, 12), .cells(48, 12)).Copy
                        rSheet.Activate
                        rSheet.Range(.cells(j, 27), .cells(j, 27)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True
                        nSheet.Activate
                        nSheet.Range(.cells(48, 12), .cells(48, 12)).Copy
                        iSheet.Activate
                        iSheet.Range(.cells(j, 41), .cells(j, 41)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False
                    Else
                        iSheet.Activate
                        iSheet.Range(.cells(j, 17), .cells(j, 17)).Copy
                        nkSheet.Activate
                        nkSheet.Range(.cells(8, 2), .cells(8, 2)).PasteSpecial Paste:=xlValues
                        iSheet.Activate
                        iSheet.Range(.cells(j, 3), .cells(j, 3)).Copy
                        nkSheet.Activate
                        nkSheet.Range(.cells(14, 6), .cells(14, 6)).PasteSpecial Paste:=xlValues
                        iSheet.Activate
                        iSheet.Range(.cells(j, 5), .cells(j, 5)).Copy
                        nkSheet.Activate
                        nkSheet.Range(.cells(14, 3), .cells(14, 3)).PasteSpecial Paste:=xlValues
                        iSheet.Activate
                        iSheet.Range(.cells(j, 21), .cells(j, 23)).Copy
                        nkSheet.Activate
                        nkSheet.Range("$B$14:$B$16").PasteSpecial Transpose:=True, Paste:=xlValues
                        iSheet.Activate
                        iSheet.Range(.cells(j, 31), .cells(j, 33)).Copy
                        nkSheet.Activate
                        nkSheet.Range("$B$22:$B$24").PasteSpecial Transpose:=True, Paste:=xlValues
                        nkSheet.Range(.cells(14, 10), .cells(16, 10)).Copy
                        rSheet.Activate
                        rSheet.Range(.cells(j, 5), .cells(j, 5)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True
                        nkSheet.Activate
                        nkSheet.Range(.cells(22, 10), .cells(24, 10)).Copy
                        rSheet.Activate
                        rSheet.Range(.cells(j, 15), .cells(j, 15)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True
                        nkSheet.Activate
                        nkSheet.Range(.cells(18, 12), .cells(18, 12)).Copy
                        rSheet.Activate
                        rSheet.Range(.cells(j, 25), .cells(j, 25)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True
                        nkSheet.Activate
                        nkSheet.Range(.cells(26, 12), .cells(26, 12)).Copy
                        rSheet.Activate
                        rSheet.Range(.cells(j, 26), .cells(j, 26)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True
                        nkSheet.Activate
                        nkSheet.Range(.cells(28, 12), .cells(28, 12)).Copy
                        rSheet.Activate
                        rSheet.Range(.cells(j, 27), .cells(j, 27)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False, Transpose:=True
                        nkSheet.Activate
                        nkSheet.Range(.cells(28, 12), .cells(28, 12)).Copy
                        iSheet.Activate
                        iSheet.Range(.cells(j, 41), .cells(j, 41)).PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:=False
                    End If
            End If
    Next j

Application.CutCopyMode = False

    Set iSheet = Nothing
    Set nSheet = Nothing
    Set rSheet = Nothing
    Set nkSheet = Nothing
        
    Application.ScreenUpdating = True
    
End Sub
    Reply With Quote Reply With Quote
  6. Apr 18th, 2006, 07:19 AM #5
    direktoren
    direktoren is offline

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Posts
    96

    Re: It used to function - now it doesn't!!!

    See - THIS, is what I love about VB...

    So I copied your (Bio's) code onto mine; Result now VB got mad at me and wouldn't even recognize the sub!

    So then I copied my original code (the one posted here) back on top - and now it's working perfectly again!!

    I love when an easy day at work can still be made frustrating ;-)

    Cheers
    Nick
    Reply With Quote Reply With Quote
  7. Apr 18th, 2006, 07:33 AM #6
    TheBionicOrange
    TheBionicOrange is offline
    Frenzied Member TheBionicOrange's Avatar
    Join Date
    Apr 2001
    Location
    Cardiff, UK
    Posts
    1,818

    Re: [RESOLVED]It used to function - now it doesn't!!!

    ummm ... ha ha ... don't know what to say to that !
    I changed all your "Cell"'s to ".Cells" as I was guessing it was creating a sub process on the fly.

    Guess that wasn't it !

    Good to hear its working ... problem is you now don't know how long for !
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules


Click Here to Expand Forum to Full Width