Create a formula into a String then assign string to a cell
 

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

Create a formula into a String then assign string to a cell
 

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

.

Create a formula into a String then assign string to a cell
 

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

.

Create a formula into a String then assign string to a cell
 

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

.



.

Create Formula inot a string
 

TOM: IT now works... !!!!

I dont know where the typo was but after several cut
pastes and edits into the code .... I eventually got it
write.

Thanks again, JIM


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

.



.

.

Create a formula into a String then assign string to a cell
 

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

.



.

Create a formula into a String then assign string to a cell
 

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

.