| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating varibales in a worksheet
I have a list of items that are done on a four week basis.
When my spreadsheet opens, it asks the user which week the items is for (1, 2, 3 or 4) via a INPUTBOX. Within each cell on the worksheet, there is a list of tasks which look similar to the following example: 1) Update MAFGHI for Week( ) 2) Print updates for Week( ) I would like to automatically insert the varible from the macro into Week( ), so that it automatically updates it to Week(4) without having to type it manually through the document. The above example would look like this, after the user has input which weeek: 1) Update MAFGHI for Week(4) 2) Print updates for Week(4) I understand how to set a cell value from a variable, but how do I update just one character from a variable within a cell that contains mutiple characters? Thanks, GLT |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating varibales in a worksheet
Hi GLT,
Try something like: '============= Public Sub Tester001() Dim weekNo As Long weekNo = InputBox("Enter week number") Columns("A:A").Replace _ What:="week(?)", _ Replacement:="Week(" & weekNo & ")", _ LookAt:=xlPart, _ SearchOrder:=xlByRows Columns("A:A").Replace _ What:="week(??)", _ Replacement:="Week(" & weekNo & ")", _ LookAt:=xlPart, _ SearchOrder:=xlByRows End Sub '<<============= --- Regards, Norman "GLT" wrote in message ... I have a list of items that are done on a four week basis. When my spreadsheet opens, it asks the user which week the items is for (1, 2, 3 or 4) via a INPUTBOX. Within each cell on the worksheet, there is a list of tasks which look similar to the following example: 1) Update MAFGHI for Week( ) 2) Print updates for Week( ) I would like to automatically insert the varible from the macro into Week( ), so that it automatically updates it to Week(4) without having to type it manually through the document. The above example would look like this, after the user has input which weeek: 1) Update MAFGHI for Week(4) 2) Print updates for Week(4) I understand how to set a cell value from a variable, but how do I update just one character from a variable within a cell that contains mutiple characters? Thanks, GLT |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating varibales in a worksheet
Hi Norman,
Thanks for your response - your solution works great except my example I gave was a simplified example. In my case, I'm actually dealing with several different sets of weeks (ie. set 1 is 'week 1 to 4', set 2 is 'week 1 to 12', set 3 is 'week 1 to 16'). If I use the find and replace, trying to update the set 1 week only will update all of the other sets as well. Unfortuantely I am stuck with them all having the same names as they are tied to systems that we use at work. I tried using the following in a cell: ='2) Print updates for Week('& weekNo &')' But it did not work either.... Thanks, GLT "Norman Jones" wrote: Hi GLT, Try something like: '============= Public Sub Tester001() Dim weekNo As Long weekNo = InputBox("Enter week number") Columns("A:A").Replace _ What:="week(?)", _ Replacement:="Week(" & weekNo & ")", _ LookAt:=xlPart, _ SearchOrder:=xlByRows Columns("A:A").Replace _ What:="week(??)", _ Replacement:="Week(" & weekNo & ")", _ LookAt:=xlPart, _ SearchOrder:=xlByRows End Sub '<<============= --- Regards, Norman "GLT" wrote in message ... I have a list of items that are done on a four week basis. When my spreadsheet opens, it asks the user which week the items is for (1, 2, 3 or 4) via a INPUTBOX. Within each cell on the worksheet, there is a list of tasks which look similar to the following example: 1) Update MAFGHI for Week( ) 2) Print updates for Week( ) I would like to automatically insert the varible from the macro into Week( ), so that it automatically updates it to Week(4) without having to type it manually through the document. The above example would look like this, after the user has input which weeek: 1) Update MAFGHI for Week(4) 2) Print updates for Week(4) I understand how to set a cell value from a variable, but how do I update just one character from a variable within a cell that contains mutiple characters? Thanks, GLT |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating varibales in a worksheet
Hi GLT,
Can you not restrict the replacement to the requred range, by changing: Columns("A:A").Replace to: Columns("MyRange").Replace If not, how may the 'correct' week entries be distinguished fom the other week entries? --- Regards, Norman "GLT" wrote in message ... Hi Norman, Thanks for your response - your solution works great except my example I gave was a simplified example. In my case, I'm actually dealing with several different sets of weeks (ie. set 1 is 'week 1 to 4', set 2 is 'week 1 to 12', set 3 is 'week 1 to 16'). If I use the find and replace, trying to update the set 1 week only will update all of the other sets as well. Unfortuantely I am stuck with them all having the same names as they are tied to systems that we use at work. I tried using the following in a cell: ='2) Print updates for Week('& weekNo &')' But it did not work either.... Thanks, GLT "Norman Jones" wrote: Hi GLT, Try something like: '============= Public Sub Tester001() Dim weekNo As Long weekNo = InputBox("Enter week number") Columns("A:A").Replace _ What:="week(?)", _ Replacement:="Week(" & weekNo & ")", _ LookAt:=xlPart, _ SearchOrder:=xlByRows Columns("A:A").Replace _ What:="week(??)", _ Replacement:="Week(" & weekNo & ")", _ LookAt:=xlPart, _ SearchOrder:=xlByRows End Sub '<<============= --- Regards, Norman "GLT" wrote in message ... I have a list of items that are done on a four week basis. When my spreadsheet opens, it asks the user which week the items is for (1, 2, 3 or 4) via a INPUTBOX. Within each cell on the worksheet, there is a list of tasks which look similar to the following example: 1) Update MAFGHI for Week( ) 2) Print updates for Week( ) I would like to automatically insert the varible from the macro into Week( ), so that it automatically updates it to Week(4) without having to type it manually through the document. The above example would look like this, after the user has input which weeek: 1) Update MAFGHI for Week(4) 2) Print updates for Week(4) I understand how to set a cell value from a variable, but how do I update just one character from a variable within a cell that contains mutiple characters? Thanks, GLT |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating varibales in a worksheet
Hi Norman,
Initially, all three sets are labelled uniquly (ie. set 1 is called s1weekNo), but after the find and replace runs, it changes it to look like this: ( 4 ) This is ok, but if you run it a third time, it does not pick up anything because the names have all changed. I thought the varible thing would be better option because everytime the variable is updated, it just displays the latest update... Cheers, Geoff. "Norman Jones" wrote: Hi GLT, Can you not restrict the replacement to the requred range, by changing: Columns("A:A").Replace to: Columns("MyRange").Replace If not, how may the 'correct' week entries be distinguished fom the other week entries? --- Regards, Norman "GLT" wrote in message ... Hi Norman, Thanks for your response - your solution works great except my example I gave was a simplified example. In my case, I'm actually dealing with several different sets of weeks (ie. set 1 is 'week 1 to 4', set 2 is 'week 1 to 12', set 3 is 'week 1 to 16'). If I use the find and replace, trying to update the set 1 week only will update all of the other sets as well. Unfortuantely I am stuck with them all having the same names as they are tied to systems that we use at work. I tried using the following in a cell: ='2) Print updates for Week('& weekNo &')' But it did not work either.... Thanks, GLT "Norman Jones" wrote: Hi GLT, Try something like: '============= Public Sub Tester001() Dim weekNo As Long weekNo = InputBox("Enter week number") Columns("A:A").Replace _ What:="week(?)", _ Replacement:="Week(" & weekNo & ")", _ LookAt:=xlPart, _ SearchOrder:=xlByRows Columns("A:A").Replace _ What:="week(??)", _ Replacement:="Week(" & weekNo & ")", _ LookAt:=xlPart, _ SearchOrder:=xlByRows End Sub '<<============= --- Regards, Norman "GLT" wrote in message ... I have a list of items that are done on a four week basis. When my spreadsheet opens, it asks the user which week the items is for (1, 2, 3 or 4) via a INPUTBOX. Within each cell on the worksheet, there is a list of tasks which look similar to the following example: 1) Update MAFGHI for Week( ) 2) Print updates for Week( ) I would like to automatically insert the varible from the macro into Week( ), so that it automatically updates it to Week(4) without having to type it manually through the document. The above example would look like this, after the user has input which weeek: 1) Update MAFGHI for Week(4) 2) Print updates for Week(4) I understand how to set a cell value from a variable, but how do I update just one character from a variable within a cell that contains mutiple characters? Thanks, GLT |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatic updating of a rollup worksheet when a new worksheet is a | Excel Worksheet Functions | |||
| Updating links from one worksheet to another worksheet | Excel Worksheet Functions | |||
| Updating different worksheet with value on current worksheet | Excel Worksheet Functions | |||
| Updating 1 worksheet with result from another worksheet | Excel Discussion (Misc queries) | |||
| When updating a worksheet, how do I create a link updating the sa. | Excel Worksheet Functions | |||
Linear Mode
