| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a cell that will search multiple sheets for entered string. | Excel Discussion (Misc queries) | |||
| Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
| Property Let: assign return value of Double when passing String | Excel Discussion (Misc queries) | |||
| to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
| How can I assign a number to a string? | Excel Discussion (Misc queries) | |||
![Myrna Larson[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
