Method 'Range' of Object_Worksheet Failed

E

Excel-erate2004

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


Is there an easy workaround for this problem?

Thanks to anyone out there who can hel
 
T

Tom Ogilvy

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

you can replace
For k = 1 To 5
WS2.Range("Rng" & k).Value = "NA"
Next
Click to expand...

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
 
E

Excel-erate2004

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
 
T

Tom Ogilvy

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
 
E

Excel-erate2004

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
 
T

Tom Ogilvy

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

Excel-erate2004

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
 
T

Tom Ogilvy

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).
 
E

Excel-erate2004

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

Tom Ogilvy

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
 
E

Excel-erate2004

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
 
T

Tom Ogilvy

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

Excel-erate2004

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
 
T

Tom Ogilvy

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

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
 
E

Excel-erate2004

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
 
T

Tom Ogilvy

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

Excel-erate2004

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
 
T

Tom Ogilvy

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 > said:
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
Click to expand...
 
E

Excel-erate2004

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy a range data from 1 sheet to multiple selected sheets 1
Copy code failing 2
Code condition 3
Error 1004 - again 3
List unique items 4
Quick Debug Help 3
Fill cell values on Interiorcolor conditions 2
Copy code - JLGWhiz 3

Top