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

.