Hi,

I'm amazed that I've been able to get as far as I am with the cod
below, but now I am stuck on something. I keep getting a Run time erro
on this line:


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


It states that the Method 'Range' of Object_Worksheet failed. Here i
my complete procedu


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 Su
-------------------


Is there an easy workaround for this problem?

Thanks to anyone out there who can hel

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

you can't refer to rng1 with "rng" & k

you can replace

For k = 1 To 5
WS2.Range("Rng" & k).Value = "NA"
Next

with



Set Rng1 = "NA"
Set Rng2 = "NA"
Set Rng3 = "NA"
Set Rng4 = "NA"
Set Rng5 = "NA"

or

Union(rng1,rng2,rng3,rng4,rng5).Value = "NA"

--
Regards,
Tom Ogilvy





"Excel-erate2004 " wrote in
message ...
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 procedu


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


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

Niether of those options will work in my situation, I dont think I'v
structured my code the correct way. What I want it to do is if thes
two conditions are met:

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

AND

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


Then if those 2 options are met for each then display an NA in the cel
range thats listed

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 tha
are not selected i.e. false place a NA in the Cell ranges as listed.

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

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

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

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

No, your explanation doesn't make much sense.

If you loop over 29 checkboxes and each time you find one that is true, you
loop over the same five checkboxes and change the values on five associated
ranges based on the value of the checkbox, either you will change the same
cells to the same value between 1 and 29 times or not at all. But if that
is what you want to do, then just clean up the pseudo code you show
For i = 1 To 29
If WS2.OLEObjects("CheckBoxSpecies" & i).Object.Value = True Then
IF ws1.OleObjects("CheckBoxBedrockL1") _
.Value = False then rng1 = "NA"
IF ws1.OleObjects("CheckBoxBedrockL2") _
.Value = False then rng2 = "NA"
IF ws1.OleObjects("CheckBoxBedrockL3") _
.Value = False then rng3 = "NA"
IF ws1.OleObjects("CheckBoxBedrockL4") _
.Value = False then rng4 = "NA"
IF ws1.OleObjects("CheckBoxBedrockL5") _
.Value = False then rng5 = "NA"
Next

--
Regards,
Tom Ogilvy




"Excel-erate2004 " wrote in
message ...
Niether of those options will work in my situation, 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 those 2 options are met for each then display an NA in the cell
range thats listed

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 a 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")
IF CheckBoxBedrockL2=false Then enter an NA in these Cells("D11, K11,
D27, K27")
IF CheckBoxBedrockL3=true Then do nothing to these Cells ("E11, L11,
E27, L27")
IF CheckBoxBedrockL4=false Then enter an NA in these Cells("F11, M11,
F27, M27")
IF CheckBoxBedrockL5=false Then enter an NA in these Cells("G11, N11,
G27, N27")

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


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

I think I'll have to come up with an alternate explanation thats mor
concise and clearer. I'll repost at a later time when I can ge
something together.

Thanks for your help

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

I've attached a file with code that takes the long way to do what I wa
trying to do with Loops.

I hope this clarifies things.

Thank

Attachment filename: explanation.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=66303
--
Message posted from http://www.ExcelForum.com

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(i) & k).Value = False Then
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

--
Regards,
Tom Ogilvy

"Excel-erate2004 " wrote in
message ...
I've attached a file with code that takes the long way to do what I was
trying to do with Loops.

I hope this clarifies things.

Thanks

Attachment filename: explanation.txt
Download attachment:
http://www.excelforum.com/attachment.php?postid=663035
---
Message posted from http://www.ExcelForum.com/

Tom,

I'm having trouble with this line of your suggested code:

If WS1.OLEObjects("CheckBox" & _
varr(i) & k).Value = False Then

and when I use the debugger the value of:

varr(i) = boulderL
K = 1
Value = False


I get a Run Time error '438' Object doesnt support this property or
method.

Any ideas? Thanks for all your help


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

WS1.OLEObjects("CheckBox" & _
varr(i) & k).
should resolve to

WS1.OLWObjects("CheckBoxboulderL1")

but it looks like you control is named CheckBoxBoulderL1

so change the words in the array so they will match your checkbox names.
(capitalize the first letter, as an example in this case).

--
Regards,
Tom Ogilvy

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

I'm having trouble with this line of your suggested code:

If WS1.OLEObjects("CheckBox" & _
varr(i) & k).Value = False Then

and when I use the debugger the value of:

varr(i) = boulderL
K = 1
Value = False


I get a Run Time error '438' Object doesnt support this property or
method.

Any ideas? Thanks for all your help


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

Any other ideas, I checked the array and the control, both spellings ar
consistent. What else could it be

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

If WS1.OLEObjects("CheckBox" & _
varr(i) & k).Value = False Then

change to

If WS1.OLEObjects("CheckBox" & _
varr(i) & k).Object.Value = False Then

add Object

--
Regards,
Tom Ogilvy


"Excel-erate2004 " wrote in
message ...
Any other ideas, I checked the array and the control, both spellings are
consistent. What else could it be?


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

Hi Tom,

Your last suggestion solved the minor problem that I was having, but
unfortunately the code didn't quite solve the overall problem although
its very close.

It applied the "NA" tag to a range of cells instead of specific
individual cells.

I realize its extremely difficult to try and solve this in such a
manner not really knowing the full extent of my project but I greatly
appreciate your time, patience and energy. Thanks very much.

Not sure where I can go from here.

Cheers


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

I tested that part of it and it appeared to work for me.

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"

rng is a single cell.
the code then sets four cells. for example as your sample indicated
Set Rng1 = WS2.Range("C11, J11, C27, J27")

so if rng is C11, it does C11, next it does C27, J11 and then J27

It does what your sample code represented to me - at least in my tests. I
don't see anywhere it works on a range.

--
Regards,
Tom Ogilvy





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

Your last suggestion solved the minor problem that I was having, but
unfortunately the code didn't quite solve the overall problem although
its very close.

It applied the "NA" tag to a range of cells instead of specific
individual cells.

I realize its extremely difficult to try and solve this in such a
manner not really knowing the full extent of my project but I greatly
appreciate your time, patience and energy. Thanks very much.

Not sure where I can go from here.

Cheers


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

Hi again Tom,

From my testing of your suggestion, for whatever reason what the cod
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 rang
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
better idea on how to fix it as it seems like its almost there. Soo
close...

Thank

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

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/

Hi Tom,

Your most recent suggestion seems to be working!! I'll have to d
further tesing on it for various situations, But I'm very hopeful
thanks all due to your help!

I have yet to take a long hard look at your posted Test code as I'
still testing with the main project, but that will be a big asset as
try and figure out just whats going on there.

One minor thing that I have to figure out is, an "NA" tag is not bein
placed in a group of merged cells. These merged cells are at th
bottom of the tables and I just choose to represent it by a single cel
in the ranges that I had given in a text file. Its th
CheckboxPelagicL item in the array.

Its strange but it works for the 1st and 3rd range but not the 2nd an
4th.

Where in the code would I go to adjust this so that these cells ar
encompassed and the NA tag is applied as required by the checkboxes i
the array.

Thanks again for your amazing help!

P.S. I hope this is the last time I have to bother you with
question

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

the value for a merged cell is stored in the upper left corner of the merged
area. If (for example), C11 were in a merged area B11:D11, then the value
would need to be written to B11. I can only guess, but

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"

would be
at the top add
Dim rngArr(1 to 4) as Range
Dim LL as Long

then replace the above with

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


to demo from the immediate window

B9 is not merged
? Range("B9").MergeArea(1).Address
$B$9

Now merge the area A9 to C10 (included B9)
range("A9:C10").Merge

Now test with B9 in a merge area
? Range("B9").MergeArea(1).Address
$A$9

so the data would be written to A9 in this case which should work if this is
the nature of the problem.

--
Regards,
Tom Ogilvy


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

Your most recent suggestion seems to be working!! I'll have to do
further tesing on it for various situations, But I'm very hopeful,
thanks all due to your help!

I have yet to take a long hard look at your posted Test code as I'm
still testing with the main project, but that will be a big asset as I
try and figure out just whats going on there.

One minor thing that I have to figure out is, an "NA" tag is not being
placed in a group of merged cells. These merged cells are at the
bottom of the tables and I just choose to represent it by a single cell
in the ranges that I had given in a text file. Its the
CheckboxPelagicL item in the array.

Its strange but it works for the 1st and 3rd range but not the 2nd and
4th.

Where in the code would I go to adjust this so that these cells are
encompassed and the NA tag is applied as required by the checkboxes in
the array.

Thanks again for your amazing help!

P.S. I hope this is the last time I have to bother you with a
question.


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

Back again,

Hopefully I can get this solved so I can leave you alone lol,

I'm not sure where in fact the problem is coming from in regards to th
Merged cells at the bottom of my tables, if it is in fact that.

I dont have VS2000 so the Immediate Window is not an option for me t
look at.

The problem again is that in 2 of 4 merged cell areas the "NA" tag i
applied when the checkbox is set to false, as it should be.

However, in the other 2 merged areas the tag is not applied at al
when the checkbox is set to false.


I'm not sure if this makes any sense at all, but diagramtically I hav
a large Table with 4 mini tables within it [See attached image]

Table1 Table2
Merged area 1 Merged area 2
Works fine NA applied Does not work NA not applied
(C20:G20) (J20:N20)


Table3 Table4
Merged area 3 Merged area 4
Works fine NA applied Does not work NA not applied
(C36:G36) (J36:N36)



I also tried your suggestion but on clicking it didnt do anything.

Do I have it set up properly?




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

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"

End If

Next
Next
End If
Next


-------------------



I'm so close thanks to all your help but its just this last little sna
that I hope I can get thru.

Thank

Attachment filename: tables.gif
Download attachment: http://www.excelforum.com/attachment.php?postid=66416
--
Message posted from http://www.ExcelForum.com

if you have excel, you have an immediate window

in the view menu in the VBE, select immediate window

for other types, you loop 1 to 5, so for the given row, you walk across the
row

c d e f g j k l m n

and the same for 26 rows down - but you have now merged all those cells, so
not sure what the code is supposed to do. Should it not loop 1 to 5 if the
type is PelagicL?

It looks like you have it set up correctly - so with that change, looping 1
to 5, if any evaluate to false it should put NA in C20, J20, C36, J36



--
Regards,
Tom Ogilvy


"Excel-erate2004 " wrote in
message ...
Back again,

Hopefully I can get this solved so I can leave you alone lol,

I'm not sure where in fact the problem is coming from in regards to the
Merged cells at the bottom of my tables, if it is in fact that.

I dont have VS2000 so the Immediate Window is not an option for me to
look at.

The problem again is that in 2 of 4 merged cell areas the "NA" tag is
applied when the checkbox is set to false, as it should be.

However, in the other 2 merged areas the tag is not applied at all
when the checkbox is set to false.


I'm not sure if this makes any sense at all, but diagramtically I have
a large Table with 4 mini tables within it [See attached image]

Table1 Table2
Merged area 1 Merged area 2
Works fine NA applied Does not work NA not applied
(C20:G20) (J20:N20)


Table3 Table4
Merged area 3 Merged area 4
Works fine NA applied Does not work NA not applied
(C36:G36) (J36:N36)



I also tried your suggestion but on clicking it didnt do anything.

Do I have it set up properly?




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

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"

End If

Next
Next
End If
Next


--------------------



I'm so close thanks to all your help but its just this last little snag
that I hope I can get thru.

Thanks

Attachment filename: tables.gif
Download attachment:
http://www.excelforum.com/attachment.php?postid=664161
---
Message posted from http://www.ExcelForum.com/

I'm still stuck I cant get the FOR NEXT and END IF statements to matc
up at the end of the code sample so the code won't execute.

How do you know where to place them??

Note I'm using my last posted code, but still no luck..

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

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

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

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

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

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
-------------------


Cheers

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

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

--
Regards,
Tom Ogilvy

"Excel-erate2004 " wrote in
message ...
Disregard the past couple of posts...I think I was starting to lose
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 i
--------------------


Cheers!


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