Why in this macro there is telling Error104 Select methode of range class failed

[jbesclapez]

Hi,

I made this macro and get an error on this line
Worksheets("Temp").Range("A1").SelectI run the macro from the sheet Q-Simple. I dont know why there is this error because if I select the sheet, than that means it is activated... so why this error. It is so basic...

Please give me a clue,

Thanks

Sub GetQSimplifiedinQSImple()




Application.ScreenUpdating = False
Worksheets("Q-Simple").Range("A1").Select
Worksheets("Q-Simple").Columns("I:Q").ClearContents


Worksheets("Q-Simple").Columns("B:F").Copy
Worksheets("Q-Simple").Columns("I:M").PasteSpecial xlPasteValues


'delete duplicates
Worksheets("Q-Simple").Range("I:M").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlYes


    Sheets("Q-Simple").Range("N1").FormulaR1C1 = "countif"
    Sheets("Q-Simple").Range("O1").FormulaR1C1 = "Conca"
    Sheets("Q-Simple").Range("P1").FormulaR1C1 = "Count"
    Sheets("Q-Simple").Range("Q1").FormulaR1C1 = "Formula"
    Sheets("Q-Simple").Range("N2").FormulaR1C1 = "=+COUNTIF(C[-5],RC[-5])"
    Sheets("Q-Simple").Range("O2").FormulaR1C1 = "=+RC[-5]&RC[-4]&RC[-3]&RC[-2]"
     
    
Dim LastRowI As Long
LastRowI = Sheets("Q-Simple").Cells(Rows.Count, 9).End(xlUp).Row


    Range("N2:O2").Copy Destination:=Range("N2:O" & LastRowI)
      
    Sheets("Q-Simple").Range("P2").FormulaR1C1 = "=IF(MOD( ROW() - MATCH(RC[-2],C[-2],0), RC[-2] ) + 1=1,1,IF(MOD( ROW() - MATCH(RC[-2],C[-2],0), RC[-2] ) + 1>10,10,MOD( ROW() - MATCH(RC[-2],C[-2],0), RC[-2] ) + 1))"
       
    
'Sort Data
       
    With ActiveWorkbook.Worksheets("Q-Simple").Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=Range("N2:N" & LastRowI), Order:=xlAscending
    .SortFields.Add2 Key:=Range("I2:I" & LastRowI), Order:=xlAscending
    .SortFields.Add2 Key:=Range("J2:J" & LastRowI), Order:=xlAscending
    .SortFields.Add2 Key:=Range("K2:K" & LastRowI), Order:=xlAscending
    .SortFields.Add2 Key:=Range("L2:L" & LastRowI), Order:=xlAscending
    .SortFields.Add2 Key:=Range("M2:M" & LastRowI), Order:=xlAscending
        
        .SetRange Range("I1:N" & LastRowI)
        .Header = xlYes
        .Apply
        
    End With
    
    Range("Q2").FormulaR1C1 = "=IF(RC[-1]=1,Conc(RC[-3],""--""),R[-1]C)"
    Range("O2:Q2").Copy Destination:=Range("O2:Q" & LastRowI)




    Range("R2").FormulaR1C1 = "=+IF(RC[-4]=1,""ok"",MyVlookup(RC[-1],TabPassage,2))"
    Range("S2").FormulaR1C1 = "=IF(RC[-5]=1,RC[-2],+IF(LEN(RC[-2])>255,MyVlookup(RC[-2],TabPassage,RC[-3]+2),+VLOOKUP(RC[-2],TabPassage,RC[-3]+2,0)))"
    Range("T2").FormulaR1C1 = "=+VLOOKUP(RC[-1],TabTypePassage,2,0)"
    Range("U2").FormulaR1C1 = "=+VLOOKUP(RC[-2],TabTypePassage,3,0)"
    Range("V2").FormulaR1C1 = "=+VLOOKUP(RC[-3],TabTypePassage,4,0)"
    Range("W2").FormulaR1C1 = "=+VLOOKUP(RC[-4],TabTypePassage,5,0)"


Range("R2:W2").Copy Destination:=Range("R2:W" & LastRowI)
Sheets("Q-Simple").Range("T1").FormulaR1C1 = "Type"
Sheets("Q-Simple").Range("U1").FormulaR1C1 = "Catégorie"
Sheets("Q-Simple").Range("V1").FormulaR1C1 = "Autres"
Sheets("Q-Simple").Range("W1").FormulaR1C1 = "Lactation"








Worksheets("Temp").Cells.ClearContents
Worksheets("Q-Simple").Columns("I:J").Copy
Worksheets("Temp").Columns("A:B").PasteSpecial xlPasteValues
Worksheets("Q-Simple").Columns("T:W").Copy
Worksheets("Temp").Columns("C:F").PasteSpecial xlPasteValues






'Filter Data in temp with Effacer value
[COLOR=#ff0000][B]Worksheets("Temp").Range("A1").Select[/B][/COLOR]
Sheets("Temp").Range("A:F").Select
Selection.AutoFilter Field:=3, Criteria1:="effacer"
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
ActiveSheet.AutoFilterMode = False


'delete duplicates
Worksheets("Temp").Range("A:F").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6), Header:=xlYes


'Sort Data
Dim LastRowA As Long
LastRowA = Sheets("Temp").Cells(Rows.Count, 1).End(xlUp).Row


        
    With ActiveWorkbook.Worksheets("Temp").Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=Range("B2:B" & LastRowA), Order:=xlAscending
    .SortFields.Add2 Key:=Range("A2:A" & LastRowA), Order:=xlAscending
    .SortFields.Add2 Key:=Range("C2:C" & LastRowA), Order:=xlAscending
    .SortFields.Add2 Key:=Range("D2:D" & LastRowA), Order:=xlAscending
    .SortFields.Add2 Key:=Range("E2:E" & LastRowA), Order:=xlAscending
    .SortFields.Add2 Key:=Range("F2:F" & LastRowA), Order:=xlAscending
        
        .SetRange Range("A1:F" & LastRowA)
        .Header = xlYes
        .Apply
        
    End With




Worksheets("Q-Simple").Columns("I:W").ClearContents
Worksheets("Temp").Columns("A:F").Copy
Worksheets("Temp").Columns("I:N").PasteSpecial xlPasteValues




Application.ScreenUpdating = True


End Sub

 

[Tim_Excel_]

Re: Noob question : Why in this macro there is telling Error104 Select methode of range class failed

Have you tried

Workbooks("NAME").Worksheets("Temp").Range("A1").Select

Seems to me Excel is looking in the wrong workbook and thus can't find the specified sheet

EDIT: looking at your code, I don't see the purpose of that line... the next line you select another range without having done anything with the previous range!

The same goes for

[COLOR=#333333]Application.ScreenUpdating = False
[/COLOR]Worksheets("Q-Simple").Range("A1").Select 
[COLOR=#333333]Worksheets("Q-Simple").Columns("I:Q").ClearContents[/COLOR]

Try to steer clear from selecting ranges when you can do otherwise. I'd just define the workbook name instead of selecting ranges in the workbook in order to activate said workbook (I think that's what you're attempting to do)

 

[jbesclapez]

Re: Noob question : Why in this macro there is telling Error104 Select methode of range class failed

Have you tried

Workbooks("NAME").Worksheets("Temp").Range("A1").Select

Seems to me Excel is looking in the wrong workbook and thus can't find the specified sheet

EDIT: looking at your code, I don't see the purpose of that line... the next line you select another range without having done anything with the previous range!

The same goes for

[COLOR=#333333]Application.ScreenUpdating = False
[/COLOR]Worksheets("Q-Simple").Range("A1").Select 
[COLOR=#333333]Worksheets("Q-Simple").Columns("I:Q").ClearContents[/COLOR]

Try to steer clear from selecting ranges when you can do otherwise. I'd just define the workbook name instead of selecting ranges in the workbook in order to activate said workbook (I think that's what you're attempting to do)

Wait... you are saying I should hard code the name of the workbook. It makes no sense to me.

You are right regarding the selection. I doubled them for test purpose as I though I needed to have the sheet activated. A good way to activate it is to select it no?
There must be something else wrong in my code.

 

[Tim_Excel_]

Re: Noob question : Why in this macro there is telling Error104 Select methode of range class failed

Without coding the wb name VBA will just look in whatever workbook is activated. You're selecting a range in a worksheet without defining a workbook. This way of working is asking for errors.

So, again, have you tried defining the workbook? Furthermore you could just use the Activate function instead of selecting cells.

 

[Fluff]

Re: Noob question : Why in this macro there is telling Error104 Select methode of range class failed

You can only select a cell on the active worksheet, so I would guess that sheets("Temp") is not the active sheet.

 

[jbesclapez]

Re: Noob question : Why in this macro there is telling Error104 Select methode of range class failed

Thanks you guys.

I did that :
Worksheets("Temp").Activate
and it worked.

I am still puzzled about this problem...

 

[steve the fish]

Re: Noob question : Why in this macro there is telling Error104 Select methode of range class failed

Open a new workbook in excel. Try and select cell A1 of Sheet2 without activating that sheet first. You cant. So you cant in code either.

 

[jbesclapez]

Re: Noob question : Why in this macro there is telling Error104 Select methode of range class failed

Open a new workbook in excel. Try and select cell A1 of Sheet2 without activating that sheet first. You cant. So you cant in code either.

Steve, so it means that it is better to use .activate ... It is really weird because I did many macros and never realised that before... But what yuo are saying totally makes sense...