Sub Tester6()
fndrow = 79
ws = "=IF(AG9=""="",AH8,IF(FIND(" & _
"TEXT(Timemap!$AF9,""hh:mm"")," & _
"'Conf Rm A'!$CZ$" & fndrow & _
")=1,MID('Conf Rm A'!$CZ$" & _
fndrow & ",SEARCHB(TEXT($AF9,""hh:mm"")," & _
"'Conf Rm A'!$CZ$" & _
fndrow & "),SEARCHB(CHAR(10),'Conf Rm A'!$CZ$" & _
fndrow & ",SEARCHB(TEXT($AF9,""hh:mm"")," & _
"'Conf Rm A'!$CZ$" & _
fndrow & "))-SEARCHB(TEXT($AF9,""hh:mm"" )," & _
"'Conf Rm A'!$CZ$" & _
fndrow & ")),""""))"
ActiveCell.Formula = ws
End Sub

Worked for me using my method.

--
Regards,
Tom Ogilvy


Jim Clements wrote in message
...
Tom: This is the valid formula from the cell that I am
recreating in the macro:

=IF(AG9="=",AH8,IF(FIND(TEXT(TimeMap!$AF9,"hh:mm") ,'Conf
Rm A'!$CZ$79)=1,MID('Conf Rm A'!$CZ$79,SEARCHB(TEXT
($AF9,"hh:mm" ),'Conf Rm A'!$CZ$79),SEARCHB(CHAR(10),'Conf
Rm A'!$CZ$79,SEARCHB(TEXT($AF9,"hh:mm" ),'Conf Rm A'!
$CZ$79))-SEARCHB(TEXT($AF9,"hh:mm" ),'Conf Rm A'!
$CZ$79)),""))

The method i use is to copy that from the sheet and then
make the edits in the macro resulting in:

ws = "=IF(AG9=""="",AH8,IF(FIND(TEXT(TimeMap!
$AF9,""hh:mm""),'Conf Rm A'!$CZ$" _
& fndrow & ")=1,MID('Conf Rm A'!$CZ$" & fndrow
& ",SEARCHB(TEXT($AF9,""hh:mm""),'Conf Rm A'!$CZ$" &
fndrow & "),SEARCHB(CHAR(10),'Conf Rm A'!$CZ$" & fndrow &
_
",SEARCHB(TEXT($AF9,""hh:mm""),'Conf Rm A'!$CZ$" & fndrow
& _
"))-SEARCHB(TEXT($AF9,""hh:mm"")),'Conf Rm A'!$CZ$" &
fndrow & ")),""""))"

MsgBox ws // properly displays the resultant formula

then the error occurs with --- Range("AH9").Value = ws

there wil be other cells that will return #Value! error,
but that is acceptable and I later run code to remove the
formulas from those cells to give a clean view of the
sheet when viewed or printed.

Any other thoughts as to the reason i am getting the

runtime error '1004' App-or object defined error when i
step through the macro-code ??

Thanks, JIM

-----Original Message-----
Sure, you probably need to create a formula that is a
valid formula. No way
for anyone to tell with a function like SEARCHB in the
formula as this is
either a UDF or a typo. Why not enter a valid formula
in the target cell,
then use the immediate window to get a string version of
it

? ActiveCell.Formula

you can then go in and alter the returned string to use
fndrow.

--
Regards,
Tom Ogilvy


Jim Clements wrote in message
...
TKX Myrna !!!

the string double quotes appears to make the string
correct as follows :


ws = "=IF(AG9=""="",AH8,IF(FIND(TEXT(TimeMap!
$AF9,""hh:mm""),'Conf Rm A'!$CZ$" _
& fndrow & ")=1,MID('Conf Rm A'!$CZ$" & fndrow
& ",SEARCHB(TEXT($AF9,""hh:mm""),'Conf Rm A'!$CZ$" &
fndrow & "),SEARCHB(CHAR(10),'Conf Rm A'!$CZ$" & fndrow
& _
",SEARCHB(TEXT($AF9,""hh:mm""),'Conf Rm A'!$CZ$" &
fndrow
& _
"))-SEARCHB(TEXT($AF9,""hh:mm"")),'Conf Rm A'!$CZ$" &
fndrow & ")),""""))"

BUT i still get a runtime error '1004'
App-or object defined error when : I try to assign the
variable ws into the cell:

Range("Ah9").Value = ws

Do you any idea what I may still be doing wrong ??

Again tkx for the help , Regards Jim


-----Original Message-----
Where you have one quote mark, replace it with 2, i.e.

ws "=IF(AG7=""="",AH6,IF(FIND(TEXT(TimeMap!
$AF7,""hh:mm""),'Conf Rm A'!$CZ$79)=1,MID('Conf Rm
A'!$CZ$79,SEARCHB(TEXT($AF7,""hh:mm"" ),'Conf Rm A'!
$CZ$79),SEARCHB(CHAR(10),'Conf Rm
A'!$CZ$79,SEARCHB(TEXT($AF7,""hh:mm"" ),'Conf Rm A'!
$CZ$79))-SEARCHB(TEXT($AF7,""hh:mm"" ),'Conf
Rm A'!$CZ$79)),""""))

Range("AH9").Formula = ws

Note you assign the string to the FORMULA property, not
the VALUE property.

On Fri, 22 Aug 2003 22:15:17 -0700, "Jim Clements"
wrote:

The following sub describes the problem I am having
trying to create a formula into a string then assign
that
string into a cell.

Can Anyone help with a solution ....

Thanks Jim
==============================================

Sub New_TM3()
'
' New_TM3 Macro
' Macro recorded 8/22/2003 by James Clements
'
Dim ws, errcl, fndcol, fndrow, srchcell As String
Dim fcv As Integer

Range("aa6").Select

srchcell = Range("AL5").Value

Sheets("Conf Rm A").Select

Cells.Find(What:=srchcell, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

fndrow = ActiveCell.Row
fndcol = ActiveCell.Column

' fndcol is the variable that will change from time
to
time to be written into desired cell

Sheets("TimeMap").Select

' the following is an example of the desired formula
that
I want to write into the cell:

=IF(AG7="=",AH6,IF(FIND(TEXT(TimeMap!
$AF7,"hh:mm"),'Conf
Rm A'!$CZ$79)=1,MID('Conf Rm A'!$CZ$79,SEARCHB(TEXT
($AF7,"hh:mm" ),'Conf Rm A'!$CZ$79),SEARCHB(CHAR
(10),'Conf
Rm A'!$CZ$79,SEARCHB(TEXT($AF7,"hh:mm" ),'Conf Rm A'!
$CZ$79))-SEARCHB(TEXT($AF7,"hh:mm" ),'Conf Rm A'!
$CZ$79)),""))

' The double quotes that need to be set in the formula
are GIVIBG me MAJOR TROUBLE !!

' I have tried CHAR(34) but this causes a compile
error
in the subroutine

' Single quotes can be stored in the string and
assigned
to the formula but does not work in the formula

' ws = "=IF(AG9=" & "'='" & ",AH8,IF(FIND(TEXT
(TimeMap!
$AF9,'hh:mm'),'Conf Rm A'!$CZ$" _
' & fndrow & ")=1,MID('Conf Rm A'!$CZ$" & fndrow
& ",SEARCHB(TEXT($AF9,'hh:mm'),'Conf Rm A'!$CZ$" &
fndrow
& "),SEARCHB(CHAR(10),'Conf Rm A'!$CZ$" & fndrow & _
' ",SEARCHB(TEXT($AF9,'hh:mm' ),'Conf Rm A'!$CZ$" &
fndrow
& _
' "))-SEARCHB(TEXT($AF9,'hh:mm')),'Conf Rm A'!$CZ$" &
fndrow & ")),''))"


MsgBox ws


Range("Ah9").Value = ws

end sub

.



.