Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a formula in VBA
Can anyone help with this problem:
I am trying to write a macro to change a formula in a cell and place the new formula in the same cell. For instance: Cel L1 in a worksheet contains: ='C:\TEST\[file.xls]data'!J7 By hand I can change this in: = TRIM('C:\TEST\[file10.xls]data'!J7) which removes excess spaces. How must I do this in a macro? Thanks in advance MahrYon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a formula in VBA
Excellent, Dave, this does exactly what I asked for. Thanks!
"Dave Peterson" schreef in bericht ... dim myCell as range set mycell = activesheet.range("L1") if mycell.hasformula then mycell.formula = "=trim(" & mid(mycell.formula,2) & ")" end if Mahr Yon wrote: Can anyone help with this problem: I am trying to write a macro to change a formula in a cell and place the new formula in the same cell. For instance: Cel L1 in a worksheet contains: ='C:\TEST\[file.xls]data'!J7 By hand I can change this in: = TRIM('C:\TEST\[file10.xls]data'!J7) which removes excess spaces. How must I do this in a macro? Thanks in advance MahrYon -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a formula in VBA
Thanks for your kind reaction, Leith.
Dave Peterson helped me already. I intended to change once a formula in a number of cells, by a macro. From now on these cells read out a certain file through that changed formula (readfile + TRIM). Every change in the file is observed, while the macro is not needed anymore. Other functions might be added in the same way. I wonder if it is simple to capitalize the first letter of the read strings. Greetings, Mahr Yon "Leith Ross" schreef in bericht ... Hello Mahr Yon, From your post it looks like you are using the Worksheet Function TRIM to remove the spaces in front of the string and at the end. Do you want to create a VBA macro do to the same thing? Do you want the macro to change the cell's value or it's formula? I ask because they are 2 separate properties and I want to be sure we are changing the right one. Post back with your answers and I'll help you with the macro. sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=478110 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a formula in VBA
dim myCell as range
set mycell = activesheet.range("L1") if mycell.hasformula then mycell.formula = "=trim(" & mid(mycell.formula,2) & ")" end if Mahr Yon wrote: Can anyone help with this problem: I am trying to write a macro to change a formula in a cell and place the new formula in the same cell. For instance: Cel L1 in a worksheet contains: ='C:\TEST\[file.xls]data'!J7 By hand I can change this in: = TRIM('C:\TEST\[file10.xls]data'!J7) which removes excess spaces. How must I do this in a macro? Thanks in advance MahrYon -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a formula in VBA
Hello Mahr Yon, From your post it looks like you are using the Worksheet Function TRIM to remove the spaces in front of the string and at the end. Do you want to create a VBA macro do to the same thing? Do you want the macro to change the cell's value or it's formula? I ask because they are 2 separate properties and I want to be sure we are changing the right one. Post back with your answers and I'll help you with the macro. sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=478110 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change a formula in VBA
It sounds like you want to use Excel's proper function which you can do in
VBA code by using worksheetfunction.proper. I did the following in the Immediate pane: x = "THIS is MY cOUNTRY" ? worksheetfunction.Proper(x) This Is My Country "Mahr Yon" wrote: Thanks for your kind reaction, Leith. Dave Peterson helped me already. I intended to change once a formula in a number of cells, by a macro. From now on these cells read out a certain file through that changed formula (readfile + TRIM). Every change in the file is observed, while the macro is not needed anymore. Other functions might be added in the same way. I wonder if it is simple to capitalize the first letter of the read strings. Greetings, Mahr Yon "Leith Ross" schreef in bericht ... Hello Mahr Yon, From your post it looks like you are using the Worksheet Function TRIM to remove the spaces in front of the string and at the end. Do you want to create a VBA macro do to the same thing? Do you want the macro to change the cell's value or it's formula? I ask because they are 2 separate properties and I want to be sure we are changing the right one. Post back with your answers and I'll help you with the macro. sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=478110 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change formula in a shared worksheet without losing change history | Excel Worksheet Functions | |||
how to change formula in shared sheet without loss of change histo | Excel Worksheet Functions | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
How does Data Validation change with a formula change? | Excel Worksheet Functions |