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 answers

  1. 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
  2. joey6 Posted messages 1 Status Member 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
  3. cythonas Posted messages 618 Status Member 84
     
    ("A" & j & " : H" & j).Select tries to set its (you never know^^)
    ("A" & j & : "H" & j).Select
    1
  4. 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
  6. cythonas Posted messages 618 Status Member 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
  7. 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
  8. frrodg Posted messages 6 Status Member
     
    Hi
    try a bit to put Activate in place of Select
    0
  9. 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