Formula pasting is not working

  • Thread starter Thread starter Majeed
  • Start date Start date
M

Majeed

I have inserted the following formula and I am getting this error:

Runtime error 1004
Application-defined or object-defined error

Range(Cells(EndRow + 1, 7), Cells(EndRow + 1, 4)).FormulaR1C1 =
"=IF(RC[-2]>RC[-1],'W')"


I am trying to paste this on a cell, returning the letter W if the cell
offset by 2 is greater than the cell offset by 1.

In fact I want to add another condition in the same code where I want the
code to return the letter L if the cell offset by 2 is smaller than the
cell offset by 1 plus to return the letter D if the cell offset by 2 equals
the cell offset by 1.

Example

A B C
D E F
1 01-Dec-03 Arsenal Coventry 4 1
W
2 01-Dec-03 Man Utd. Southampton 1 3
L
3 01-Dec-03 Tottenham Chelsea 2 2
D


Thanks
 
This is a better looking table !

A B C D E F
1 01-Dec-03 Arsenal Coventry 4 1 W
2 01-Dec-03 Man Utd. So'ton 1 3 L
3 01-Dec-03 Tottenham Chelsea 2 2 D


Thanks
 
I think you need something like this:

Range(Cells(EndRow + 1, 4), Cells(EndRow + 1, 7)).FormulaR1C1 = _
"=IF(RC[-2]>RC[-1],""W"","""")"

However, this actually puts the formula under the rows.

Maybe what you actually need is something like this:

Dim EndRow As Long
Dim EndCol As Long
EndRow = Range("A65536").End(xlUp).Row
EndCol = Range("IV1").End(xlToLeft).Column
Range(Cells(1, EndCol + 1), Cells(EndRow, EndCol + 1)).FormulaR1C1 = _
"=IF(RC[-2]>RC[-1],""W"","""")"

Hence the final solution would be:

Dim EndRow As Long
Dim EndCol As Long
EndRow = Range("A65536").End(xlUp).Row
EndCol = Range("IV1").End(xlToLeft).Column
Range(Cells(1, EndCol + 1), Cells(EndRow, EndCol + 1)).FormulaR1C1 = _
"=IF(RC[-2]>RC[-1],""W"",IF(RC[-2]<RC[-1],""L"",""D""))"


Regards

Trevor
 
Thanks Trevor, it works perecftly now

Cheers and a happy new year to you.

Majeed



Trevor Shuttleworth said:
I think you need something like this:

Range(Cells(EndRow + 1, 4), Cells(EndRow + 1, 7)).FormulaR1C1 = _
"=IF(RC[-2]>RC[-1],""W"","""")"

However, this actually puts the formula under the rows.

Maybe what you actually need is something like this:

Dim EndRow As Long
Dim EndCol As Long
EndRow = Range("A65536").End(xlUp).Row
EndCol = Range("IV1").End(xlToLeft).Column
Range(Cells(1, EndCol + 1), Cells(EndRow, EndCol + 1)).FormulaR1C1 = _
"=IF(RC[-2]>RC[-1],""W"","""")"

Hence the final solution would be:

Dim EndRow As Long
Dim EndCol As Long
EndRow = Range("A65536").End(xlUp).Row
EndCol = Range("IV1").End(xlToLeft).Column
Range(Cells(1, EndCol + 1), Cells(EndRow, EndCol + 1)).FormulaR1C1 = _
"=IF(RC[-2]>RC[-1],""W"",IF(RC[-2]<RC[-1],""L"",""D""))"


Regards

Trevor


Majeed said:
I have inserted the following formula and I am getting this error:

Runtime error 1004
Application-defined or object-defined error

Range(Cells(EndRow + 1, 7), Cells(EndRow + 1, 4)).FormulaR1C1 =
"=IF(RC[-2]>RC[-1],'W')"


I am trying to paste this on a cell, returning the letter W if the cell
offset by 2 is greater than the cell offset by 1.

In fact I want to add another condition in the same code where I want the
code to return the letter L if the cell offset by 2 is smaller than the
cell offset by 1 plus to return the letter D if the cell offset by 2 equals
the cell offset by 1.

Example

A B C
D E F
1 01-Dec-03 Arsenal Coventry 4 1
W
2 01-Dec-03 Man Utd. Southampton 1 3
L
3 01-Dec-03 Tottenham Chelsea 2 2
D


Thanks
Click to expand...
Click to expand...
 
Majeed

Nice to know that your problem is solved

And all the best for the New Year to you too.

Trevor


Majeed said:
Thanks Trevor, it works perecftly now

Cheers and a happy new year to you.

Majeed



Trevor Shuttleworth said:
I think you need something like this:

Range(Cells(EndRow + 1, 4), Cells(EndRow + 1, 7)).FormulaR1C1 = _
"=IF(RC[-2]>RC[-1],""W"","""")"

However, this actually puts the formula under the rows.

Maybe what you actually need is something like this:

Dim EndRow As Long
Dim EndCol As Long
EndRow = Range("A65536").End(xlUp).Row
EndCol = Range("IV1").End(xlToLeft).Column
Range(Cells(1, EndCol + 1), Cells(EndRow, EndCol + 1)).FormulaR1C1 = _
"=IF(RC[-2]>RC[-1],""W"","""")"

Hence the final solution would be:

Dim EndRow As Long
Dim EndCol As Long
EndRow = Range("A65536").End(xlUp).Row
EndCol = Range("IV1").End(xlToLeft).Column
Range(Cells(1, EndCol + 1), Cells(EndRow, EndCol + 1)).FormulaR1C1 = _
"=IF(RC[-2]>RC[-1],""W"",IF(RC[-2]<RC[-1],""L"",""D""))"


Regards

Trevor


Majeed said:
I have inserted the following formula and I am getting this error:

Runtime error 1004
Application-defined or object-defined error

Range(Cells(EndRow + 1, 7), Cells(EndRow + 1, 4)).FormulaR1C1 =
"=IF(RC[-2]>RC[-1],'W')"


I am trying to paste this on a cell, returning the letter W if the cell
offset by 2 is greater than the cell offset by 1.

In fact I want to add another condition in the same code where I want the
code to return the letter L if the cell offset by 2 is smaller than the
cell offset by 1 plus to return the letter D if the cell offset by 2 equals
the cell offset by 1.

Example

A B C
D E F
1 01-Dec-03 Arsenal Coventry 4 1
W
2 01-Dec-03 Man Utd. Southampton 1 3
L
3 01-Dec-03 Tottenham Chelsea 2 2
D


Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top