Updating varibales in a worksheet

[GLT]

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

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

[GLT]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
>
>
>

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>> >
>>
>>
>>

[GLT]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>