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/