X = ActiveSheet.Range("A611").Value
Y = ActiveSheet.Range("A612").Value
Z = ActiveSheet.Range("A613").Value - 1

myRNG = Cells(28, X).Address
myRNG1 = Cells(Y, 71).Address
myRNG2 = Cells(Y, Z).Address

If ActiveSheet.Range("A614").Value = 4 Then
Set myLOC = Range(myRNG & ", BY28, BS127:BY127, BS226:BY226, BS325:BY325")

ElseIf ActiveSheet.Range("A614").Value = 5 Then
Set myLOC = Range(myRNG & ",BY28, BS127:BY127, BS226:BY226, BS325:BY325,
BS424," & myRNG2)

ElseIf ActiveSheet.Range("A614").Value = 6 Then
Set myLOC = Range(myRNG & ", BY28, BS127:BY127, BS226:BY226,
BS325:BY325, BS424:BY424, BS523," & myRNG2)

End If
For Each cell In myLOC

a = 1

Next cell

"jnf40" wrote:

How could I write the following code to get it to work? When I run it now I
get the
Method 'Range' of object '_Global' failed message

X = ActiveSheet.Range("A611").Value
Y = ActiveSheet.Range("A612").Value
Z = ActiveSheet.Range("A613").Value - 1

myRNG = Cells(28, X).Address
myRNG1 = Cells(Y, 71).Address
myRNG2 = Cells(Y, Z).Address

If ActiveSheet.Range("A614").Value = 4 Then
myLOC = "Excel.Range" & "(""" & myRNG & ":BY28, BS127:BY127,
BS226:BY226, BS325:BY325" & """)"

ElseIf ActiveSheet.Range("A614").Value = 5 Then
myLOC = "Excel.Range" & "(""" & myRNG & ":BY28, BS127:BY127,
BS226:BY226, BS325:BY325, BS424:" & myRNG2 & """)"

ElseIf ActiveSheet.Range("A614").Value = 6 Then
myLOC = "Excel.Range" & "(""" & myRNG & ":BY28, BS127:BY127,
BS226:BY226, BS325:BY325, BS424:BY424, BS523:" & myRNG2 & """)"

End If

For Each cell In Range(myLOC)