| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Assigning Variable Range | Excel Discussion (Misc queries) | |||
| Assigning set of range names | Excel Programming | |||
| Assigning Range to Array | Excel Programming | |||
| Match / Name-assigning To A Range | Excel Programming | |||
| Assigning a Range to | Excel Programming | |||
Linear Mode
