View original (French)

VBA Method range failed

Solved
Anonymous user -  
 Kiddy -
Hello,
I am currently programming in VBA Excel

I have a counter j that needs to provide me with a row number.

I want to merge cells, so I need to select the cells to merge and apply the formula, but I don’t understand why it says that my Range method has failed; it seems correct to me...
Could you enlighten me on the issue 

Workbooks("etude delai.xls").Sheets("Feuil1").Range("A" & j & " : H" & j).Select


--
95% of errors come from what’s between the keyboard and the chair

Remember to check "resolved" if applicable
Configuration: Windows XP Opera 9.25

8 réponses

Réponse 1 / 8
Kiddy
 
Range("C10:D20").Select
Selection.Copy
Sheets("Feuil2").Select
Range("B7").Select
ActiveSheet.Paste

Do not confuse selected sheet with active sheet...

Either you write:

Range("C10:D20").Select
Selection.Copy
Sheets("Feuil2").Select
Range("B7").Select
Selection.Paste

Or:

Range("C10:D20").Select
Selection.Copy
Sheets("Feuil2").Activate
Range("B7").Activate
ActiveCell.Paste
4
Réponse 2 / 8
joey6 Posted messages 1 Status Membre 2
 
Hi everyone,
I'm starting to do some VBA/Excel programming, and I have a problem that I really can't solve.
I'm encountering the infamous "VBA Method Range failed" problem while creating a macro in Excel, and then I take the code generated by the macro into a procedure.
The generated code is as follows:

Range("C10:D20").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B7").Select
ActiveSheet.Paste

After doing some research, I realized that the sheet change is what causes the problem because if I do the copy-paste on the same sheet, there are no issues.

Has anyone ever had this problem?
Thanks in advance
Joey6
2
Réponse 3 / 8
cythonas Posted messages 618 Status Membre 84
 
("A" & j & " : H" & j).Select tries to set its (you never know^^)
("A" & j & : "H" & j).Select
1
Réponse 4 / 8
Anonymous user
 
A" & j & ": "H" & j).Select cannot work because the method is
range("col1row1: col2row2).select
for example

range("A1:H1").select

--
95% of errors come from what is between the keyboard and the chair

Remember to check "resolved" if applicable
1
Réponse 5 / 8
cythonas Posted messages 618 Status Membre 84
 
Yes, I'm familiar with the "theoretical" structure of the range!
But in practice, VB lets certain things slip through..
In fact, it's the '&' behind the 'j' that bothers me. Why did you put one there??
0
Réponse 6 / 8
Anonymous user
 
No, it's fine, I found it, it's a damn &$*chr&acter that was too much
--
95% of the errors come from what is between the keyboard and the chair

Remember to check "resolved" if applicable
0
Réponse 7 / 8
frrodg Posted messages 6 Status Membre
 
Hi
try a bit to put Activate in place of Select
0
Réponse 8 / 8
Anonymous user
 
It's all good, I found it, thank you. It was a stupid space that messed everything up and I was also missing a Windows("etude delai.xls").Activate higher up. Thanks to both of you for your help.
--
95% of errors come from what is between the keyboard and the chair.

Please remember to check "resolved" if applicable.
0