For Each code

J

jnf40

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

Joel

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
 
M

merjet

It's not clear what you are trying to do, but maybe making 'myLOC'
only a string of cell address will avoid the error. For example, make
the consequent of your first ElseIf:

myLOC = myRNG & ":BY28, BS127:BY127,BS226:BY226, BS325:BY325, BS424:"
& myRNG2

Note: myLOC....myRNG2 should be one line in your code.

Similar for the other two lines starting with 'myLOC ='.

Hth,
Merjet
 
Top