If you want the cell to contain a *formula*, you missed the line where I showed you have to use
the FORMULA property of the cell, not the VALUE property.

Range("AH9").Formula = ws

NOT

Range("AH9").Value = ws

To see if the formula is valid, step through the code with F8. Stop on the line after the one
where you construct the string (ws = "=....."), before you get the error. Then in the immediate
window, type

? ws

The string will be displayed. Copy that from the immediate window and paste it into the cell
manually? Do you get an error? If so, the formula has syntax errors. You'll have to find those.

I see you are using SEARCHB rather than SEARCH. I don't even see SEARCHB in the function list
when I click on the Fx button. This is for use with Asian character sets, not English. Does the
formula work if you replace SEARCHB with SEARCH?

Another possibility is that in some versions of Excel/VBA the limit on a the length of a string
passed to the worksheet is 255 characters. Are you exceeding that (I didn't count)?

On Fri, 22 Aug 2003 22:55:10 -0700, "Jim Clements" wrote:

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:m m"),'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

.