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/