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

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

and so forth...

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

/Nick

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.

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:

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

/Nick

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

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

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 !