Method 'Range' of Object_Worksheet Failed

[Excel-erate2004]

I've also did a demo on the Immediate window and everything works as i
should as you had described.

Now if I can just get that code set up properly maybe it will work an
I can have this thing over with.

Either way I've learned a fair bit

 

[Excel-erate2004]

Tom,

I have a question, if I remove the Pelagic value from the array, s
that it no longer has 5 checkboxes to loop thru but now exists as
single checkbox, how can I adjust your suggested code so that it stil
accounts for this one value while still looping thru the 2
CheckboxSpecies objects and applying it to the designated ranges.

The reason for this as I discussed with future users of my project i
that the pelagic value is a range of values and can be represented as
single value in a merged cell rather than 5 individual values, its no
necssary for it to be set up in a range of checkboxes (1-5).

More specifically, the other method is giving me trouble and so thi
might be an easy way out that works for the user! lol

I hope you can give me this last bit of help on this problem thats go
me stuck.

Thank

 

[Excel-erate2004]

Disregard the past couple of posts...I think I was starting to los
it...looking at the code too long.

I made a minor mistake in theplacing of Next and end if statements

This code does exactly what I wanted!

Thanks so much Tom for all your help!!


Code
-------------------

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

Dim rngArr(1 To 4) As Range 'newly added to test
Dim LL As Long ' newly added to test


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).Object.Value = False Then ' <= corrected line

Set Rng = WS2.Cells(baserow1, 2 + k)
Set rngArr(1) = Rng
Set rngArr(2) = Rng.Offset(16, 0)
Set rngArr(3) = Rng.Offset(0, 7)
Set rngArr(4) = Rng.Offset(16, 7)

For LL = 1 To 4
Set Rng = rngArr(LL).MergeArea(1)
Rng.Value = "NA"
Next LL
End If
Next k
Next j
End If
Next

 

[Tom Ogilvy]

Glad you got it working. I missed your other posts, but I guess it was for
the best.