Data extraction - sheet output 2

[ItIsHardToProgram]

Ok, I really wanted to do this by myself, and strangely I got it to work just fine, but as soon as I put the macro in a button, it has an error, I know exactly wich line it errors on, I have no clue why though...

What my macro does is it goes in a sheet that is ready for upload, it tests if it finds a property and an account (row / column).

Once this is done, it goes catch the data in the according row / column.

It then creates a sheet, if a matching sheet for the specific data sheet does not exist, and adds the data in the newly created sheet.

This works fine, although you will argue my error handling.

Once 10 000 lines have been filtered, it goes to the new sheet, sorts the data, and loops through the next data sheet and so on....

Weirdly, when I have my macro in a Module, it works fine, every behavior has I want it. But when I put my macro in a sheet, and call my macro through a button, it errors on my Cells.select... runtime error saying it failed to select the cells...


Here is my code:

Sub StartUploadMacro()
'
' StartUpload Macro
' Enregistré par Julien-Bono Roy le 29 décembre 2008

Dim CurrDate As String, PostMonth As String, Property As String, GLnumber As String
Dim Amount As Double
Dim iSheet As Integer, SheetTest As Integer
Dim iSheetName As String, SplitSheetName, uploadSheetName As String
Dim i As Integer, j As Integer, iTest As Integer
Dim StartUp As Boolean, SheetExist As Boolean
Dim testSheetName As String
Dim bidon

Dim iLineOffset As Integer

Dim sSplitPMname

On Error GoTo ErrorHandler

PostMonth = Worksheets(1).Cells(1, 6)
CurrDate = Worksheets(1).Cells(2, 6)
iLineOffset = 1

sSplitPMname = Split(PostMonth, "/")

For iSheet = 1 To Worksheets.Count
    'Test si la feuille doit avoir un fichier d'upload ou non.
    iSheetName = Worksheets(iSheet).Name
    SplitSheetName = Split(iSheetName, "_")
    
    StartUp = False
    
    Select Case SplitSheetName(0)
    
            Case "Rdy"
                For iTest = 1 To 20
                    testSheetName = UCase(Worksheets(iSheet).Cells(iTest, 1).FormulaR1C1)
                    'Vérifie si une ligne d'upload est présente
                    If testSheetName = "START-UPLOADMACRO" Then
                    StartUp = True
                    Exit For
                    End If
                Next iTest
                
                If StartUp = True Then
                For i = iTest + 1 To 10000
                    'Identifie une ligne associé à une propriété
                    Property = Worksheets(iSheet).Cells(i, 1).FormulaR1C1
                    'test la validité (très simple, pas très flexible) de ce qui est rentrée)
                    
                    Property = UCase(Property)
                    If Property = "MODIFGL" Then
                        iTest = i
                    End If
                    
                    If Len(Property) = 5 And IsNumeric(Property) Then
                        For j = 1 To 200
                            'Identifie une colone associé à un compte
                            GLnumber = Worksheets(iSheet).Cells(iTest, j).FormulaR1C1
                            'test la validité (très simple, pas très flexible) de ce qui est rentrée)
                                If Len(GLnumber) = 5 And IsNumeric(GLnumber) Then
                                    'Prend le montant dans la ligne et colone identifiée
                                    Amount = Worksheets(iSheet).Cells(i, j).Value
                                    
                                    'vérification si la feuille d'upload existe
                                    For SheetTest = 1 To Worksheets.Count
                                        If Worksheets(SheetTest).Name = ("Up " + SplitSheetName(1) + " " + sSplitPMname(0) + "-" + sSplitPMname(1)) Then
                                            SheetExist = True
                                        End If
                                    Next SheetTest
                                    'Si la feuille existe, annule le test, sinon crée une nouvelle feuille nom selon nom de feuille
                                    If SheetExist = True Then
                                    Else
                                
                                        Worksheets(Worksheets.Count).Select
                                        Sheets.Add
NameLenghtError:
                                        Worksheets(Worksheets.Count - 1).Name = "Up " + SplitSheetName(1) + " " + sSplitPMname(0) + "-" + sSplitPMname(1)
                                    End If
                                    
                                    uploadSheetName = "Up " + SplitSheetName(1) + " " + sSplitPMname(0) + "-" + sSplitPMname(1)
                                    
                                    With Worksheets(uploadSheetName)
                                            .Cells(iLineOffset, 1).FormulaR1C1 = "J"
                                            .Cells(iLineOffset, 5).FormulaR1C1 = CurrDate
                                            .Cells(iLineOffset, 6).FormulaR1C1 = "'" & PostMonth
                                            .Cells(iLineOffset, 9).FormulaR1C1 = Property
                                            .Cells(iLineOffset, 10).FormulaR1C1 = Amount
                                            .Cells(iLineOffset, 11).FormulaR1C1 = GLnumber
                                            .Cells(iLineOffset, 14).FormulaR1C1 = 1
                                            .Cells(iLineOffset, 15).FormulaR1C1 = SplitSheetName(1) + " " + sSplitPMname(0) + "-" + sSplitPMname(1)
                                    End With
                                    
                                    iLineOffset = iLineOffset + 1
                                End If
                        Next j
                    End If
                Next i
                End If
    
    End Select
    
    
    
    If StartUp = True Then
    
        Sheets("Up " + SplitSheetName(1) + " " + sSplitPMname(0) + "-" + sSplitPMname(1)).Activate
        Sheets("Up " + SplitSheetName(1) + " " + sSplitPMname(0) + "-" + sSplitPMname(1)).Select
    
        [highlight]Cells.Select[/highlight]
        
    
        Selection.Sort Key1:=Range("I1"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
            
    End If
    iLineOffset = 1
    
    SheetExist = False
Next iSheet

Exit Sub
ErrorHandler:
    If Err.Number = 1004 And SheetExist = False Then
        bidon = MsgBox("Le nom de feuille qui est dénoté par Rdy_ est trop grand pour créer une nouvelle feuille d'upload. Le nom de feuille sera donc tronqué", vbYesNo, "Nom de feuille trop grand")
        
        If bidon = vbYes Then Exit Sub
        
        SplitSheetName(1) = Left(SplitSheetName(1), 7)
        
        SheetExist = True
        
        Resume NameLenghtError
    End If

        
    MsgBox Error(Err)
    MsgBox Err.Number

End Sub

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Hi,

"But when I put my macro in a sheet"

Your macro is best located in a MODULE and not a sheet. Why did you move the macro to the Sheet Object?

So WHAT SHEET are you doing Sheets("WHAT SHEET").Cells.Select?

I would avoid using the Select and Activate methods and rather use explicit sheet referencing as you mostly do.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

I will get back to you later today, I have to go, Thanks for the quick reply.

Really fast: if I leave it in a module and I do "Call StartUpload" in a button, it dosnt recognize my macro, wich is why I placed it in the sheet, where the button is...

I also tried putting it inside the button, same results.

I understand that ultimate referencing is better, but i splitted it for further test, Theres still not joy, and technicaly once a sheet is selected, its should recognize the fact that it is that sheet thats needs the cells to be selected... Thats how I see it.

Thanks, always a pleasure

Julien -

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

If you call a macro in a module, with the correct name, it can be called from anywhere.

"its should recognize the fact that it is that sheet thats needs the cells to be selected... Thats how I see it."

Wow! As one surgeon said to the other, "Suture self!"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bong]

I think you want to put the button in the sheet and leave the macro in the module. Then in the button's code, call the macro (that is in the module).

_________________
Bob Rashkin

 

[ItIsHardToProgram]

Whoa... Thats a nice sentence... I will add some CLEAR info tomorow.



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[xlbo]

I'm thinking you are using a "Controls" toolbox button and your "Take Focus On Click" property is set to true

Set it to false and try with that

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ItIsHardToProgram]

Ok so let me rephrase.

MY MAIN PROBLEM IS THE FOLLOWING:

my

If StartUp = True Then
    
        Sheets("Up " + SplitSheetName(1) + " " + sSplitPMname(0) + "-" + sSplitPMname(1)).Activate
        Sheets("Up " + SplitSheetName(1) + " " + sSplitPMname(0) + "-" + sSplitPMname(1)).Select
    
        Cells.Select
        
    
        Selection.Sort Key1:=Range("I1"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
            
    End If

Does not work, and errors on Cells.Select

I have tried every type of referencing, and still no joy, maybe some one could try the code in their VB, using the loops and all, because maybe it errors on that line, but the error is due to something else, and then come back to me.

Skip, what I meant is that the referencing should be working, weither its explicit or not.


~

WHEN I locate the macro in a module, it works, but I am UNABLE to fire the specific macro from the module in a SHEET OBJECT, I hope that is clearer.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[combo]

Cells.Select works for active worksheet. Called in standard module acts on any active worksheet. Called in worksheet's module tries to select cells in the same workbook unless you preceed it with other worksheet reference (still has to be active).

combo

 

[SkipVought]

Knock your head against the wall all you want, but with code in a sheet module, ANY reference to another sheet object MUST be fully quallified.

So Cells.Select MUST have the Sheet reference despite the fact that the sheet has been activated.

If it were me, the code would look like this...

With Sheets("Up " + SplitSheetName(1) + " " + sSplitPMname(0) + "-" + sSplitPMname(1))
    [b][red].Range("I1")[/red][/b].Sort _
        Key1:=[b][red].Range("I1")[/red][/b], Order1:=xlAscending, _
        Header:=[b][red]xlYes[/red][/b], _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End With

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

As you insist to play with the Activate and Select methods, what about this ?
Sheets("Up " & SplitSheetName(1) & " " & sSplitPMname(0) & "-" & sSplitPMname(1)).Cells.Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

...and if you must select, you STILL must reference the sheet in Key1...

With Sheets("Up " + SplitSheetName(1) + " " + sSplitPMname(0) + "-" + sSplitPMname(1))
    .Activate
    .Cells.Select
    Selection.Sort _
        Key1:=[red][b].Range("I1")[/b][/red], Order1:=xlAscending, _
        Header:=xlYes, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End With

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

And you should still check that the button is not a controls toolbox one with "take focus on click" set to true...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ItIsHardToProgram]

Sorry Skip, I guess I misunderstood you the first time, so it HAS to be referenced in the SORT key.

I thought you were refering to

Sheets("Up " + SplitSheetName(1) + " " + sSplitPMname(0) + "-" + sSplitPMname(1)).cells.select

Wich I had tried, and the reason why it did not work is that in the sort I don't fully reference.


Thanks, I did not know that, I'll read harder next time.


I can't currently spend time on that project, but I will get back to you as soon as I can, on how everything works out.

Thanks for correcting me and for your help.

(I am a bit stubborn...)



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Sorry Skip, I guess I misunderstood you the first time, so it HAS to be referenced in the SORT key.

ANY range object on ANY other sheet object must have its parent sheet object referenced. Its just a good programming practice to reference ALL range objects with the parent sheet object, regardless.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}