VBA Excel : Compile Error when using .activate on a sheet

Question

I encounter the following error within my Excel macro when I tried to execute it : Compilation Error : Function or Variable expected.

Here is my code :

            'On split le libellé afin de récupérer la chaîne de caractère source1 à rechercher dans la colonne B du PSR
            TabSrcDoubleEntree = Split(PtoutPar(j).Nom, "/")
            ' on récupère la ligne du PSR (colonne B) dans laquelle la chaîne source 1 (=TabSrcDoubleEntree(0)) est renseignée
            Set sheet = Workbooks(PFile).Sheets(ParMap(j).Parametrage.SrcFeuille)
            **With sheet.Activate**
                Set celluleRowRange = .Range("B", .Range("B").End(xlUp))
                Set celluleFind = celluleRowRange.Find(TabSrcDoubleEntree(0), LookIn:=xlValues, lookat:=xlWhole)
                celluleRowFind = celluleFind.Row
            End With
            'on initialise la ligne à partir de laquelle on fait la recherche à "c.Row"
            ThisWorkbook.Sheets(PFeuilleDest).Cells(iDest, ParMap(j).Parametrage.DestCol).Value = Workbooks(PFile).Sheets(ParMap(j).Parametrage.SrcFeuille).Cells(celluleFind.Row + ParMap(j).Parametrage.SrcPremiereLigne, ParMap(j).Parametrage.SrcCol).Value

=> Does anyone know why the instruction sheet.Activate is not correct ??

THanks a lot Simon

You don't need `.Activate` [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Dec 20 '13 at 12:32
Have you [activated the workbook](http://stackoverflow.com/a/17410705/43846)? — stuartd, Dec 20 '13 at 12:34
replace `With sheet.Activate` with two lines: `sheet.Activate` and `With ActiveSheet`. — Peter Albert, Dec 20 '13 at 12:48

score 0 · Answer 1 · answered Feb 22 '14 at 01:44

The With "this" statement is used to perform the following block from the context of "this". In your case you are performing the block from the context of the Activate function, rather than the sheet itself, which is throwing a compile error. I would recommend changing it to the following

' Perform the following from the context of sheet    
With sheet
    .Activate
    Set celluleRowRange = .Range("B", .Range("B").End(xlUp))
    ' ...
End With

VBA Excel : Compile Error when using .activate on a sheet

Here is my code :

1 Answers1