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" <GLT@discussions.microsoft.com> wrote in message
> news:67FEFDD1-CD6D-4BA0-917F-702DC509FBB6@microsoft.com...
> >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
> >
>
>
>

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" <GLT@discussions.microsoft.com> wrote in message
news:04521D11-7170-4303-A1CD-1512BCB5F9F7@microsoft.com...
> 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" <GLT@discussions.microsoft.com> wrote in message
>> news:67FEFDD1-CD6D-4BA0-917F-702DC509FBB6@microsoft.com...
>> >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
>> >
>>
>>
>>

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" <GLT@discussions.microsoft.com> wrote in message
> news:04521D11-7170-4303-A1CD-1512BCB5F9F7@microsoft.com...
> > 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" <GLT@discussions.microsoft.com> wrote in message
> >> news:67FEFDD1-CD6D-4BA0-917F-702DC509FBB6@microsoft.com...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>