Select method of the Range class Solved

Question

Hello everyone,

I have a VBA code that allows me to create a chart based on the number of occurrences of each entry.

Some entries have a zero occurrence, and I would like them not to appear in my chart.
To do this, before creating the chart, I added the following lines of code that allow me to hide the rows for which the occurrence is zero.

 ''''''''''''''''''''''Hiding zero occurrences'''''''''''''''''''''''''''' line2 = 27 Do Until IsEmpty(Cells(line2, "B")) If Cells(line2, "B").Value = 0 Then Rows(line2).Select Selection.EntireRow.Hidden = True line2 = line2 + 1 Else line2 = line2 + 1 End If Loop

This code works perfectly when it's in a separate module, but when I add it to my main code that is activated by clicking a button, it no longer works...

I get a runtime error 1004 that says "the Select method of the Range class failed" ... The debugger indicates that this line is causing an issue:

Rows(line2).Select

I tried replacing

Rows(line2).Select

with

Range(line2:line2).Select

but that doesn't work either...

Any idea why it works in a module and not in the global code?
Thanks in advance!

Configuration: Windows 7 / Chrome 53.0.2785.116

michel_m · Answer

Hello,

without seeing the code or the general part of the code since it's what prevents...

but already you could improve your code
in VBA, we avoid the "select" as much as possible and whatever the if, you increment..

If Cells(ligne2, "B") = 0 Then
 Rows(ligne2).Hidden = True
 End If
 ligne2 = ligne2 + 1

--
Michel

ThauTheme · Answer

Hello thread, hello forum,

Without looking in detail at the code you provided (why the code and not the file?), maybe an explanation.
If you execute code from one tab that refers to another tab, you need to specify the name of the tab each time. For example, I'm creating a button on the Feuil1 tab with the following code:

Sheets("Feuil2").Select Rows(2).Select

It's going to crash!...

I need to write:

Sheets("Feuil2").Select Sheets("Feuil2").Rows(2).Select

for it to work...

--
See you,
ThauTheme

Nashimok · Answer

But if I use

Sheets("Résumé graphique").Activate

at the beginning of my code, I don't need to reference my sheet for each action, do I?

Because right now, I have


Sheets("Résumé graphique").Activate
.
.
.
.
Rows(ligne2).Select

and I thought that would allow me not to have to reference

Sheets("Résumé graphique").method

every time.

jordane45 · Answer

Hello,

There are some issues with your code...
For example, in the FeuillExist function:

 Function FeuilleExiste1(MaFeuille As String) As Boolean 'Returns "TRUE" if the sheet "MaFeuille" exists Dim Feuille As Worksheet FeuilleExiste = False For Each Feuille In Worksheets If (Feuille.Name = "Résumé graphique") Then FeuilleExiste1 = True End If Next Feuille End Function

You have two different variable names...
FeuilleExiste and FeuilleExiste1 ...... No, you should only have the same one (and this variable has the same name as your function name... FeuilleExiste1)

[edit]
I suggest a code that is a bit more suited to your needs:

 Function FExist(NomF As String) As Boolean ' test if the sheet exists Application.ScreenUpdating = False On Error Resume Next FExist = Not Sheets(NomF) Is Nothing Application.ScreenUpdating = True End Function

And to use it:

 NomFeuille = "Résumé graphique" Reponse = FExist(NomFeuille)

Then.... DO NOT USE ACCENTED CHARACTERS as variable names!

Also, try to break down your code into several functions... because right now it is too long in one block... for debugging it is a total hassle!

And finally.... Since at certain points in your code you reference other sheets... it is normal that you have to re-reference your sheet ("Résumé Graphique") so that your macro can find it....

Best regards,
Jordane

ThauTheme · Answer

Re,  If your code is in a standard module, no. But if it is in a tab, yes. Test the two example lines I suggested and you'll see...

Nashimok · Answer

Hi everyone!

You'll find the complete file at this link.
It compiles, so I hope the code isn't too messy ;)

http://www.cjoint.com/c/FIBwCek6M7f

I tried to follow the advice by removing the

.Select

, and I've used

With .... End With

instead.

I also separated the code into different modules. I admit I've learned on the job, without really following any tutorials (not great in terms of syntax...), so regarding the organization of my project, I'm not too sure how it looks.

Basically, it does what I want now, so I'm happy, and I don't have many questions left.
If you have time to look at the code and give me tips on how I can improve it, I'd really appreciate it.

Anyway, thanks for your help!!!

ThauTheme · Answer

Hello thread, hello forum,

Not much time to look at all the codes... But just one remark. In module 5, procedure graph_def_risque, you have, if I counted correctly, 2 sets of 66 variables. I recommend replacing those 66 variables with an array of variables with 66 entries. I used an array with 2 rows and 66 columns because I wasn't sure if you would need the variable names later.
In the first row are stored the names of the variables and in the second the values. I named TBCCO the array of cco variables and TBR the array of R variables. It looks like this:

Dim TBOCC(1, 1 To 66) As Variant Dim TBR(1, 1 To 66) As Variant TBOCC(0, 1) = "occ_101" TBOCC(1, 1) = Application.CountIf(Worksheets("High risks").Range("G:G"), "101") TBOCC(0, 2) = "occ_102" TBOCC(1, 1) = Application.CountIf(Worksheets("High risks").Range("G:G"), "102") '... TBOCC(0, 66) = occ_1605 TBOCC(1, 66) = Application.CountIf(Worksheets("High risks").Range("G:G"), "1605") TBR(0, 1) = "r101" TBR(1, 1) = "slippery ground" TBR(0, 2) = "r102" TBR(1, 1) = "uneven ground" '... TBR(0, 66) = "r605" TBR(1, 66) = "coactivity"

For now, you're not gaining anything, but later you will remove all the lines:

Cells(27, "A") = r101 '... Cells(92, "B") = occ_1605

with a single loop:

For X = 1 To 66 Cells(26 + X, "A") = TBR(1, X) Cells(26 + X, "B") = TBOCC(1, X) Next X

if you don't need the variable names, it simplifies like this:

Dim TBOCC(1 to 66) As Variant Dim TBR(1 To 66) As Variant TBOCC(1) = Application.CountIf(Worksheets("High risks").Range("G:G"), "101") TBR(1) = "slippery ground" '... TBOCC(66) = Application.CountIf(Worksheets("High risks").Range("G:G"), "1605") TBR(66) = "coactivity"

the loop becomes:

For X = 1 To 66 Cells(26 + X, "A") = TBR(X) Cells(26 + X, "B") = TBOCC(X) Next X

See you,
ThauTheme

--
See you,
ThauTheme

Select method of the Range class

7 réponses

Texture issues in enshrouded

Canon mb5350 - cartridge replacement impossible

Football manager (club management game)

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.