Assigning Formula to a Range
 

I have a formula in Cell D5 on SheetName Formulas as follows:

=IF(MID(E5,LEN(E5)-1,1)="A","Kel Tech",IF(MID(E5,LEN(E5)-1,1)="B","South
Star",IF(MID(E5,LEN(E5)-1,1)="C","White
Rodgers",IF(MID(E5,LEN(E5)-1,1)="D","GRI",IF(MID(E5,LEN(E5)-1,1)="E","Graybar",IF(MID(E5,LEN(E5)-1,1)="F","Guest
Room",""))))))

It currently on the Formulas WS shows up as #Value! - but it is exactly
what I need to paste into my Cell D5 on SheetName Revised-CashOut
and actually I need to Fill D6:D50 with the relative (row 5) to 6:50

My current code line (which I need help with is (and it is not working):

With Sheets("Revised-CashOut")
..Range("H5:H50).Formula = "=" & Sheets("Formulas").Range("$D$5").value
&"

Can someone assist me in getting the syntax right?

TIA,

Jim May

Assigning Formula to a Range
 

With Sheets("Revised-CashOut")
.Range("H5:H50).Formula = _
Sheets("Formulas").Range("D5").Formula

End With

--
Regards,
Tom Ogilvy



"JMay" wrote:

I have a formula in Cell D5 on SheetName Formulas as follows:

=IF(MID(E5,LEN(E5)-1,1)="A","Kel Tech",IF(MID(E5,LEN(E5)-1,1)="B","South
Star",IF(MID(E5,LEN(E5)-1,1)="C","White
Rodgers",IF(MID(E5,LEN(E5)-1,1)="D","GRI",IF(MID(E5,LEN(E5)-1,1)="E","Graybar",IF(MID(E5,LEN(E5)-1,1)="F","Guest
Room",""))))))

It currently on the Formulas WS shows up as #Value! - but it is exactly
what I need to paste into my Cell D5 on SheetName Revised-CashOut
and actually I need to Fill D6:D50 with the relative (row 5) to 6:50

My current code line (which I need help with is (and it is not working):

With Sheets("Revised-CashOut")
..Range("H5:H50).Formula = "=" & Sheets("Formulas").Range("$D$5").value
&"

Can someone assist me in getting the syntax right?

TIA,

Jim May

Assigning Formula to a Range
 

To Clarify:
The Formula in cell D5 was actually Pasted into D5 from H5 on sheet
"Revised-CashOut" Before copying I placed a " ' " in front of the
formula to transpose into text. My sheet Revised-CashOut looses
this formula with various updating that goes on in the sheet, so
that is why I'm trying to replace the formula after everything
settles down on the page. HTH

"JMay" wrote in message
:

I have a formula in Cell D5 on SheetName Formulas as follows:

=IF(MID(E5,LEN(E5)-1,1)="A","Kel Tech",IF(MID(E5,LEN(E5)-1,1)="B","South
Star",IF(MID(E5,LEN(E5)-1,1)="C","White
Rodgers",IF(MID(E5,LEN(E5)-1,1)="D","GRI",IF(MID(E5,LEN(E5)-1,1)="E","Graybar",IF(MID(E5,LEN(E5)-1,1)="F","Guest
Room",""))))))

It currently on the Formulas WS shows up as #Value! - but it is exactly
what I need to paste into my Cell D5 on SheetName Revised-CashOut
and actually I need to Fill D6:D50 with the relative (row 5) to 6:50

My current code line (which I need help with is (and it is not working):

With Sheets("Revised-CashOut")
.Range("H5:H50).Formula = "=" & Sheets("Formulas").Range("$D$5").value
&"

Can someone assist me in getting the syntax right?

TIA,

Jim May

Assigning Formula to a Range
 

s = Sheets("Formulas").Range("D5").Formula
s = Replace(s,"=","")
With Sheets("Revised-CashOut")
.Range("H5:H50").Formula = _
"=" & s
End With

--
Regards,
Tom Ogilvy


"JMay" wrote:

To Clarify:
The Formula in cell D5 was actually Pasted into D5 from H5 on sheet
"Revised-CashOut" Before copying I placed a " ' " in front of the
formula to transpose into text. My sheet Revised-CashOut looses
this formula with various updating that goes on in the sheet, so
that is why I'm trying to replace the formula after everything
settles down on the page. HTH

"JMay" wrote in message
:

I have a formula in Cell D5 on SheetName Formulas as follows:

=IF(MID(E5,LEN(E5)-1,1)="A","Kel Tech",IF(MID(E5,LEN(E5)-1,1)="B","South
Star",IF(MID(E5,LEN(E5)-1,1)="C","White
Rodgers",IF(MID(E5,LEN(E5)-1,1)="D","GRI",IF(MID(E5,LEN(E5)-1,1)="E","Graybar",IF(MID(E5,LEN(E5)-1,1)="F","Guest
Room",""))))))

It currently on the Formulas WS shows up as #Value! - but it is exactly
what I need to paste into my Cell D5 on SheetName Revised-CashOut
and actually I need to Fill D6:D50 with the relative (row 5) to 6:50

My current code line (which I need help with is (and it is not working):

With Sheets("Revised-CashOut")
.Range("H5:H50).Formula = "=" & Sheets("Formulas").Range("$D$5").value
&"

Can someone assist me in getting the syntax right?

TIA,

Jim May

Assigning Formula to a Range
 

Solved -- Found this Googling - (Thanks Chip Pearson):

Sheets("Revised-CashOut").Range("H5:H50").Formula = _
Sheets("Formulas").Range("D5").Formula

Works Great !!


"JMay" wrote in message
:

I have a formula in Cell D5 on SheetName Formulas as follows:

=IF(MID(E5,LEN(E5)-1,1)="A","Kel Tech",IF(MID(E5,LEN(E5)-1,1)="B","South
Star",IF(MID(E5,LEN(E5)-1,1)="C","White
Rodgers",IF(MID(E5,LEN(E5)-1,1)="D","GRI",IF(MID(E5,LEN(E5)-1,1)="E","Graybar",IF(MID(E5,LEN(E5)-1,1)="F","Guest
Room",""))))))

It currently on the Formulas WS shows up as #Value! - but it is exactly
what I need to paste into my Cell D5 on SheetName Revised-CashOut
and actually I need to Fill D6:D50 with the relative (row 5) to 6:50

My current code line (which I need help with is (and it is not working):

With Sheets("Revised-CashOut")
.Range("H5:H50).Formula = "=" & Sheets("Formulas").Range("$D$5").value
&"

Can someone assist me in getting the syntax right?

TIA,

Jim May

Assigning Formula to a Range
 

Thanks Tom;

Score (measured in # of Responces):
msserver 2
cox.net 0

Again thanks,
Jim



"Tom Ogilvy" wrote:


s = Sheets("Formulas").Range("D5").Formula
s = Replace(s,"=","")
With Sheets("Revised-CashOut")
.Range("H5:H50").Formula = _
"=" & s
End With

--
Regards,
Tom Ogilvy


"JMay" wrote:

To Clarify:
The Formula in cell D5 was actually Pasted into D5 from H5 on sheet
"Revised-CashOut" Before copying I placed a " ' " in front of the
formula to transpose into text. My sheet Revised-CashOut looses
this formula with various updating that goes on in the sheet, so
that is why I'm trying to replace the formula after everything
settles down on the page. HTH

"JMay" wrote in message
:

I have a formula in Cell D5 on SheetName Formulas as follows:

=IF(MID(E5,LEN(E5)-1,1)="A","Kel Tech",IF(MID(E5,LEN(E5)-1,1)="B","South
Star",IF(MID(E5,LEN(E5)-1,1)="C","White
Rodgers",IF(MID(E5,LEN(E5)-1,1)="D","GRI",IF(MID(E5,LEN(E5)-1,1)="E","Graybar",IF(MID(E5,LEN(E5)-1,1)="F","Guest
Room",""))))))

It currently on the Formulas WS shows up as #Value! - but it is exactly
what I need to paste into my Cell D5 on SheetName Revised-CashOut
and actually I need to Fill D6:D50 with the relative (row 5) to 6:50

My current code line (which I need help with is (and it is not working):

With Sheets("Revised-CashOut")
.Range("H5:H50).Formula = "=" & Sheets("Formulas").Range("$D$5").value
&"

Can someone assist me in getting the syntax right?

TIA,

Jim May