Assigning Formula to a Range

[JMay]

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 D650 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

 

[Tom Ogilvy]

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

End With

 

[JMay]

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

 

[Tom Ogilvy]

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

 

[JMay]

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

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

Works Great !!