Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    St. John's, NFLD
    Posts
    18

    Unhappy Unanswered: Method 'Range' of Object_Worksheet failed

    I am stuck on something. I keep getting a Run time error on this line:

    Code:
    WS2.Range("Rng" & k).Value = "NA"

    It states that the Method 'Range' of Object_Worksheet failed. Here is my complete procedure:

    Code:
    Public Sub CommandButton4_Click()

    Dim WS1 As Worksheet, WS2 As Worksheet
    Dim Rng As Range
    i As Integer
    j As Integer
    k As Integer

    Set WS1 = Sheets("Step 1")
    Set WS2 = Sheets("Step 2")
    Set Rng1 = WS2.Range("C11, J11, C27, J27")
    Set Rng2 = WS2.Range("D11, K11, D27, K27")
    Set Rng3 = WS2.Range("E11, L11, E27, L27")
    Set Rng4 = WS2.Range("F11, M11, F27, M27")
    Set Rng5 = WS2.Range("G11, N11, G27, N27")

    For i = 1 To 29
    If WS2.OLEObjects("CheckBoxSpecies" & i).Object.Value = True Then
    For j = 1 To 5
    If WS1.OLEObjects("CheckBoxBedrockL" & j).Object.Value = False _ Then
    For k = 1 To 5
    WS2.Range("Rng" & k).Value = "NA"
    Next
    End If
    Next
    End If
    Next

    End Sub



    I dont think I've structured my code the correct way. What I want it to do is, if these two conditions are met:

    If WS2.OLEObjects("CheckBoxSpecies" & i).Object.Value = True

    AND

    If WS1.OLEObjects("CheckBoxBedrockL" & j).Object.Value =False


    Then IF and only IF those 2 options are met for each, then display an "NA" in the cell range thats listed. The ranges need to be looked at individually.

    OR alternatively:

    Loop thru the 29 checkboxes on sheet2 and those that are selected (i.e. true) then loop thru the 5 checkboxes on sheet1 and those that are not selected (i.e. false) place an "NA" in the Cell ranges as listed.

    It should work out like this, the combination would vary of course dependant upon the 2 conditions listed above, but each defined range is directly correlated with each checkbox, for example:

    IF CheckBoxBedrockL1=true Then do nothing to these Cells("C11, J11, C27, J27") in Range 1
    IF CheckBoxBedrockL2=false Then enter an NA in these Cells("D11, K11, D27, K27") in Range 2
    IF CheckBoxBedrockL3=true Then do nothing to these Cells ("E11, L11, E27, L27") in Range 3
    IF CheckBoxBedrockL4=false Then enter an NA in these Cells("F11, M11, F27, M27") in Range 4
    IF CheckBoxBedrockL5=false Then enter an NA in these Cells("G11, N11, G27, N27") in Range 5

    Still with me??

    I'm not sure if that clarifies what I'm trying to do, I hope so because I'm stuck

  2. #2
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Quote Originally Posted by Excel-erate2004
    I am stuck on something. I keep getting a Run time error on this line:

    Code:
    WS2.Range("Rng" & k).Value = "NA"

    It states that the Method 'Range' of Object_Worksheet failed. Here is my complete procedure:
    This is happening because the Range() property expects as an argument a string that represents an address. However, you're passing a range, not a string. Try changing these lines:

    Code:
    Dim Rng as variant
    
    Rng = Array("C11, J11, C27, J27", "D11, K11, D27, K27", "E11, L11, E27, L27", "F11, M11, F27, M27", "G11, N11, G27, N27")
    
    For k = 0 to 4
    WS2.Range(Rng(k)).value="NA"
    Next k
    This code should run, but I doubt it will return the results you want. As written, it will set all these cells to NA if WS2.OLEObjects("CheckBoxSpecies" & i) is true for ANY i, and WS1.OLEObjects("CheckBoxBedrockL" & j) is false for ANY j.

    Perhaps since j and k both have five elements then you don't really want to use k at all. Maybe you just need to loop through j and take action accordingly.

    Of course looping through 29 values of i will still cause problems. Perhaps this is just a draft of the macro and you intend to define 29 sets of 5 ranges?

    When you were working on this several months ago, you didn't want to post the spreadsheet. That makes it really tough to help you.

  3. #3
    Join Date
    Mar 2004
    Location
    St. John's, NFLD
    Posts
    18

    Unhappy

    You have a long memory Actuary, it was a few months ago that I posted, probably back in April or May. I did get a solution for that problem after and now the current one is a more complicated variation of that.

    However, I do have somewhat of a solution to my current posted problem that I was lucky enough to get help with on another forum thanks to a VBA expert. But there is a problem, it doesn't apply the "NA" tag to my merged cell's. Hopefully I can get that minor issue solved and then I'll be back on track.

    anyhow, attached is the almost perfect solution in a text file.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •