For loop with integer 1

[ajkingy]

Hey I'm stuck on this for loop with RC formatting.

Probably easy?

Dim x As Integer
x = 7
For x = 7 To 379

' This next part the range M x+2 works fine but when I put the x into formula it doesn't work, tried with & and "" but not fully sure on how to write it.

Range("M" & x + 2).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-5]<R[-x]C[1],""BELOW"",IF(RC[-5]>R[-x]C[2],""ABOVE"",""OK""))"

Next x


Cheers in advance

ajkingy

 

[PHV]

=IF(RC[-5]<R[-" & x & "]C[1],""BELOW"",IF(RC[-5]>R[-" & x & "]C[2],""ABOVE"",""OK""))"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

And:

Dim x As Integer
[red]x = 7[/red]
For x = 7 To 379

The RED part of your code is not needed, it doesn't do anything.

Just FYI

Have fun.

---- Andy

 

[ajkingy]

Cheers guys works perfectly!

 

[SkipVought]

hi,

Variables must be concatenated with the string...

Dim x As Integer

For x = 7 To 379

' This next part the range M x+2 works fine but when I put the x into formula it doesn't work, tried with & and "" but not fully sure on how to write it.
  
Range("M" & x + 2).FormulaR1C1 = _
            "=IF(RC[-5]<R[" & -x & "]C[1],""BELOW"",IF(RC[-5]>R[" & -x & "]C[2],""ABOVE"",""OK""))"
Next x

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Also, why are you coding the same formula within a loop?

If your FIRST formula is coded properly, than all you need to is COPY if and PASTE it into the range.

However, your formula does NOT seem to be properly coded with the necessary ABSOLUTE refences to row 2 for N2 & O2. The reference should be N$2 and O$2.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ajkingy]

Well thats just being a complete and utter noob for you.

That works quicker!

Cheers skip

 

[SkipVought]

But the problem is that the formjula is really NOT CORRECT.

A correct formula can be copied and pasted, and the result will yeild correct results.

If I copy your formula in M9 and then PASTE into the remainder of the column, the results will NOT be the same as your result!!!

If I received this sheet from you, in the course of business, I would be 1) extremely annoyed if I had to manipulate the data and 2) have some not very complementary thoughts about the author.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ajkingy]

Your message

Also, why are you coding the same formula within a loop?

If your FIRST formula is coded properly, than all you need to is COPY if and PASTE it into the range.

However, your formula does NOT seem to be properly coded with the necessary ABSOLUTE refences to row 2 for N2 & O2. The reference should be N$2 and O$2.

When I said I was being a noob and that works quicker, I had changed it to the N$2, O$2 reference which as you say can be copied and pasted into the range.

Which I think from your next comment, this makes the formula correct. Let me know if I'm still wrong?