Method 'Range' of Object_Worksheet failed 1

[Excelerate2004]

Hi,

I'm amazed that I've been able to get as far as I am with the code below, but now 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


Is there an easy workaround for this problem?

thanks to anyone out there who can help

 

[PHV]

Why not using an array of Range objects ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Excelerate2004]

I'm not that strong a programmer for that, arrays make me dizzy lol But seriously I havent used arrays before so I'm not sure how I would set it up that way.

 

[PHV]

Something like this ?
Public Sub CommandButton4_Click()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim Rng(1 To 5) As Range
Dim i As Integer, j As Integer, k As Integer
Set WS1 = Sheets("Step 1")
Set WS2 = Sheets("Step 2")
Set Rng(1) = WS2.Range("C11, J11, C27, J27")
Set Rng(2) = WS2.Range("D11, K11, D27, K27")
Set Rng(3) = WS2.Range("E11, L11, E27, L27")
Set Rng(4) = WS2.Range("F11, M11, F27, M27")
Set Rng(5) = 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
Rng(k).Value = "NA"
Next k
End If
Next j
End If
Next i
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Excelerate2004]

I thought it was going to work! It didn't, it just put the NA's in all the Cells and not just the ones that had met the 2 conditions, those ranges need to be looked at individually I do believe.

See explanation below:

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

OR alternatively:

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

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.

 

[PHV]

Something like this ?
For j = 1 To 5
If WS1.OLEObjects("CheckBoxBedrockL" & j).Object.Value = False Then
Rng(j).Value = "NA"
End If
Next j
But how are the five Rng(j) related to the 29 checkboxes ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Excelerate2004]

Awesome! That worked!! I had one too many For...Next statements.

Its difficult to explain how the five Rng(j) are related to the 29 checkboxes without showing you my 2 sheets in my Excel workbook. But here goes...

Basically on Sheet 2 there are tables for a possible 29 products.

In each of the 29 tables there is a combobox which is populated with the 29 selections.

When a user makes a selection from the combobox a click event is fired that changes the value of the CheckBoxSpecies to true. This is the 1st condition thats met.

The next thing it does is if that CheckBoxSpecies is true then it looks back at sheet1 and the checkboxes that are there. The checkboxes that are there are based on ranges i.e 0, 0-1, 1-2, 2-5, 5-10, 10+. So depending if these are checked or True then it will apply an NA in the Ranges that I have set up ONLY IF the checkbox is not checked for those possible 29 CheckBoxSpecies checkboxes that are set to true or in other words these have had a selection been made by a user from a combobox.

Does that help explain?

Anyhow, I think that temporarily solves this problem.

Thank you for your time and solution.

Much appreciated!

 

[Excelerate2004]

The above code does work however there is additional loop's to be carried out and it gets a little complicated...I was lucky enough to have some major help from a VBA expert in another forum.

But I'm still having trouble with it as it doesn't apply the "NA" tag to my merged range of cells.

Here is the almost perfect code:

Public Sub CommandButton4_Click()

Dim WS1 As Worksheet, WS2 As Worksheet
Dim Rng As Range
Dim varr as Variant
Dim i as long, j as long, k as long
Dim jj as long, baserow as long, baserow1 as long


Set WS1 = Sheets("Step 1")
Set WS2 = Sheets("Step 2")

varr = Array("BedrockL","BoulderL","RubbleL","CobbleL", _
"GravelL","SandL","SiltL","ClayL","MuckL","PelagicL")

for i = 1 to 29
If WS2.OLEObjects("CheckBoxSpecies" & i) _
.Object.Value = True Then
baserow = (i-1)*38 + 11
jj = -1
for j = lbound(varr) to ubound(varr)
jj = jj + 1
baserow1 = baserow + jj
for k = 1 to 5
If WS1.OLEObjects("CheckBox" & _
varr(j) & k).Value = False Then ' <= corrected line
set rng = ws2.Cells(baserow1,2+k)
rng.Value = "NA"
rng.offset(16,0).Value = "NA"
rng.offset(0,7).value = "NA"
rng.offset(16,7).value = "NA"
End If
Next
Next
End if
Next
End Sub

Any suggestions?