For Next loupe in a worksheetformula
 

Good Morning

I can't find the answer to my question.
The macro below selects the cells M2 to M30 to place a
Worksheetformula.
That works all right
But at the same time the formula has to raise the sheetnames in the
next cells from Player 2 to
Player 3, Player 4 and so on.
I can't figure out if this is possible at all and if yes how to do
so.
Sheets("Player 1").Select
For x = 2 To 30
Range("M" & x).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-5]="""","""",IF(AND(RC[-9]=RC[-7],'Player
2'!R6C4='Player 2'!R6C6),1,IF(RC[-9]=RC[-7],2,0)))"
Next x

Looking forward to your answer.

Piet

For Next loupe in a worksheetformula
 

Must be For Next Loop of course

Kweenie schreef:

Good Morning

I can't find the answer to my question.
The macro below selects the cells M2 to M30 to place a
Worksheetformula.
That works all right
But at the same time the formula has to raise the sheetnames in the
next cells from Player 2 to
Player 3, Player 4 and so on.
I can't figure out if this is possible at all and if yes how to do
so.
Sheets("Player 1").Select
For x = 2 To 30
Range("M" & x).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-5]="""","""",IF(AND(RC[-9]=RC[-7],'Player
2'!R6C4='Player 2'!R6C6),1,IF(RC[-9]=RC[-7],2,0)))"
Next x

Looking forward to your answer.

Piet

For Next loupe in a worksheetformula
 

Use this line:
ActiveCell.FormulaR1C1 = _
"=IF(RC[-5]="""","""",IF(AND(RC[-9]=RC[-7],'Player " & x & "'!R6C4='Player "
& x & "'!R6C6),1,IF(RC[-9]=RC[-7],2,0)))"

Regards,
Stefi

"Kweenie" wrote:

Must be For Next Loop of course

Kweenie schreef:

Good Morning

I can't find the answer to my question.
The macro below selects the cells M2 to M30 to place a
Worksheetformula.
That works all right
But at the same time the formula has to raise the sheetnames in the
next cells from Player 2 to
Player 3, Player 4 and so on.
I can't figure out if this is possible at all and if yes how to do
so.
Sheets("Player 1").Select
For x = 2 To 30
Range("M" & x).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-5]="""","""",IF(AND(RC[-9]=RC[-7],'Player
2'!R6C4='Player 2'!R6C6),1,IF(RC[-9]=RC[-7],2,0)))"
Next x

Looking forward to your answer.

Piet

For Next loupe in a worksheetformula
 

Stefi

Thanks a lot for your answer
I tried the same but probably i mistyped something.

Anyway it works.
Thanks again

Piet


Stefi schreef:

Use this line:
ActiveCell.FormulaR1C1 = _
"=IF(RC[-5]="""","""",IF(AND(RC[-9]=RC[-7],'Player " & x & "'!R6C4='Player "
& x & "'!R6C6),1,IF(RC[-9]=RC[-7],2,0)))"

Regards,
Stefi

"Kweenie" wrote:

Must be For Next Loop of course

Kweenie schreef:

Good Morning

I can't find the answer to my question.
The macro below selects the cells M2 to M30 to place a
Worksheetformula.
That works all right
But at the same time the formula has to raise the sheetnames in the
next cells from Player 2 to
Player 3, Player 4 and so on.
I can't figure out if this is possible at all and if yes how to do
so.
Sheets("Player 1").Select
For x = 2 To 30
Range("M" & x).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-5]="""","""",IF(AND(RC[-9]=RC[-7],'Player
2'!R6C4='Player 2'!R6C6),1,IF(RC[-9]=RC[-7],2,0)))"
Next x

Looking forward to your answer.

Piet

For Next loupe in a worksheetformula
 

You are welcome! Thanks for the feedback!
Stefi

"Kweenie" wrote:

Stefi

Thanks a lot for your answer
I tried the same but probably i mistyped something.

Anyway it works.
Thanks again

Piet


Stefi schreef:

Use this line:
ActiveCell.FormulaR1C1 = _
"=IF(RC[-5]="""","""",IF(AND(RC[-9]=RC[-7],'Player " & x & "'!R6C4='Player "
& x & "'!R6C6),1,IF(RC[-9]=RC[-7],2,0)))"

Regards,
Stefi

"Kweenie" wrote:

Must be For Next Loop of course

Kweenie schreef:

Good Morning

I can't find the answer to my question.
The macro below selects the cells M2 to M30 to place a
Worksheetformula.
That works all right
But at the same time the formula has to raise the sheetnames in the
next cells from Player 2 to
Player 3, Player 4 and so on.
I can't figure out if this is possible at all and if yes how to do
so.
Sheets("Player 1").Select
For x = 2 To 30
Range("M" & x).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-5]="""","""",IF(AND(RC[-9]=RC[-7],'Player
2'!R6C4='Player 2'!R6C6),1,IF(RC[-9]=RC[-7],2,0)))"
Next x

Looking forward to your answer.

Piet