When I tested it, I made a correction on the test code but forgot to correct
it on the posted 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","Pelagic L")

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

see if that works any better.

Here is the test code which you would run on a blank workbook with 1 sheet
if you want to examine what it does:

Public Sub TestCode()

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 kk As Long
kk = 1

'Set WS1 = Sheets("Step 1")
'Set WS2 = Sheets("Step 2")
Set WS2 = ActiveSheet
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(i) & k).Value = False Then
Set Rng = WS2.Cells(baserow1, 2 + k)
Set rng1 = Rng
Set rng2 = Rng.Offset(16, 0)
Set rng3 = Rng.Offset(0, 7)
Set rng4 = Rng.Offset(16, 7)
Cells(kk, 1).Value = "CheckBoxesSpecies" & i
Cells(kk, 2).Value = CheckBox & varr(j) & k
Cells(kk, 3).Value = Union(rng1, rng2, rng3, rng4).Address(0,0)
kk = kk + 1
' End If
Next
Next
' End If
Next
End Sub



--
Regards,
Tom Ogilvy

"Excel-erate2004 " wrote in
message ...
Hi again Tom,

From my testing of your suggestion, for whatever reason what the code
does is it skips the first item in the array (BedrockL)
and ignores any selections made in its checkboxes i.e. BedrockL1,
BedrockL2 etc.

For the rest of the items in the array it does apply the NA to say
(C11, J11, C27, J27) but then it ends up applying the NA's to a range
so that it ends up more like this:

C11:C20, J11:J20, C27:C36, J27:J36

Very close but not quite, as I try to understand your code I'll have a
better idea on how to fix it as it seems like its almost there. Sooo
close...

Thanks


---
Message posted from http://www.ExcelForum.com/