Copying Variables in VB Editor In Excel
 

Hi

Am not hot on VB and am stuck. Have recorded a macro
which uses text written into a box, running the macro to
run a mass replace job over 10 sheets. When I do this,
everything works apart from the macro always replaces
what was originally in the box rather than whatever is
typed. How do I change this constant into a variable
copy/paste? Please keep it simple if you can help!!!

Thank you for any help you can give

Tony

Copying Variables in VB Editor In Excel
 

Tony,

Paste the code for us to see.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tony" wrote in message
...
Hi

Am not hot on VB and am stuck. Have recorded a macro
which uses text written into a box, running the macro to
run a mass replace job over 10 sheets. When I do this,
everything works apart from the macro always replaces
what was originally in the box rather than whatever is
typed. How do I change this constant into a variable
copy/paste? Please keep it simple if you can help!!!

Thank you for any help you can give

Tony

Copying Variables in VB Editor In Excel
 

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("enter item to be replaced")
sStr1 = InputBox("enter replacement")
Application.displayalerts = False
For Each sh In Worksheets(Array("sheet1", "sheet2", . . . , _
"sheet9","sheet10"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next
Application.displayalerts = True
End Sub

replace ... with the list of additional sheet names.

--
Regards,
Tom Ogilvy


Tony wrote in message
...
Hi

Am not hot on VB and am stuck. Have recorded a macro
which uses text written into a box, running the macro to
run a mass replace job over 10 sheets. When I do this,
everything works apart from the macro always replaces
what was originally in the box rather than whatever is
typed. How do I change this constant into a variable
copy/paste? Please keep it simple if you can help!!!

Thank you for any help you can give

Tony

Copying Variables in VB Editor In Excel
 

As asked. This is a small part of what I want to do, as
this needs to be replicated for 30 different names.

Thanks

Tony

Sub Replace()
'
' Macro10 Macro
' Macro recorded 02/11/2003 by TB
'

'
Range("A2").Select
ActiveCell.FormulaR1C1 = "Trish"
Range("G3").Select
Sheets("Week 1").Select
Cells.Replace What:="Blank1", Replacement:="Trish",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Sheets("Week 2").Select
Cells.Replace What:="Blank1", Replacement:="Trish",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Sheets("Week 3").Select
Cells.Replace What:="Blank1", Replacement:="Trish",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Sheets("Week 4").Select
Cells.Replace What:="Blank1", Replacement:="Trish",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
Sheets("Name").Select
End Sub

-----Original Message-----
Tony,

Paste the code for us to see.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tony" wrote in message
...
Hi

Am not hot on VB and am stuck. Have recorded a macro
which uses text written into a box, running the macro
to
run a mass replace job over 10 sheets. When I do this,
everything works apart from the macro always replaces
what was originally in the box rather than whatever is
typed. How do I change this constant into a variable
copy/paste? Please keep it simple if you can help!!!

Thank you for any help you can give

Tony


.

Copying Variables in VB Editor In Excel
 

This is what I have done, obviously wrong, can you show
me forward again please?

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("L2")
sStr1 = InputBox("A2")
Application.DisplayAlerts = False
(Array(("Week1", "Week2", "Week3", Week4"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub



-----Original Message-----

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("enter item to be replaced")
sStr1 = InputBox("enter replacement")
Application.displayalerts = False
For Each sh In Worksheets(Array
("sheet1", "sheet2", . . . , _
"sheet9","sheet10"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.displayalerts = True
End Sub

replace ... with the list of additional sheet names.

--
Regards,
Tom Ogilvy


Tony wrote in message
...
Hi

Am not hot on VB and am stuck. Have recorded a macro
which uses text written into a box, running the macro
to
run a mass replace job over 10 sheets. When I do this,
everything works apart from the macro always replaces
what was originally in the box rather than whatever is
typed. How do I change this constant into a variable
copy/paste? Please keep it simple if you can help!!!

Thank you for any help you can give

Tony


.

Copying Variables in VB Editor In Excel
 

Sub ReplaceItems()
Dim sh As Worksheet
Dim sStr As String
Dim sStr1 As String
' assume your values are in L2 and A2 of the active sheet
sStr Range("L2").Value
sStr1 = Range("A2").Value
Application.DisplayAlerts = False
For Each sh In Worksheets(Array("Week1", _
"Week2", "Week3", "Week4"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub

--
Regards,
Tom Ogilvy

Tony wrote in message
...
This is what I have done, obviously wrong, can you show
me forward again please?

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("L2")
sStr1 = InputBox("A2")
Application.DisplayAlerts = False
(Array(("Week1", "Week2", "Week3", Week4"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub



-----Original Message-----

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("enter item to be replaced")
sStr1 = InputBox("enter replacement")
Application.displayalerts = False
For Each sh In Worksheets(Array
("sheet1", "sheet2", . . . , _
"sheet9","sheet10"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.displayalerts = True
End Sub

replace ... with the list of additional sheet names.

--
Regards,
Tom Ogilvy


Tony wrote in message
...
Hi

Am not hot on VB and am stuck. Have recorded a macro
which uses text written into a box, running the macro
to
run a mass replace job over 10 sheets. When I do this,
everything works apart from the macro always replaces
what was originally in the box rather than whatever is
typed. How do I change this constant into a variable
copy/paste? Please keep it simple if you can help!!!

Thank you for any help you can give

Tony


.

Copying Variables in VB Editor In Excel
 

Coming up with box saying

Complile Error
Expected Sub, Function or Property

in reference to line
sStr Range("L2").Value

when I debug

please advise
-----Original Message-----
Sub ReplaceItems()
Dim sh As Worksheet
Dim sStr As String
Dim sStr1 As String
' assume your values are in L2 and A2 of the active sheet
sStr Range("L2").Value
sStr1 = Range("A2").Value
Application.DisplayAlerts = False
For Each sh In Worksheets(Array("Week1", _
"Week2", "Week3", "Week4"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub

--
Regards,
Tom Ogilvy

Tony wrote in message
...
This is what I have done, obviously wrong, can you show
me forward again please?

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("L2")
sStr1 = InputBox("A2")
Application.DisplayAlerts = False
(Array(("Week1", "Week2", "Week3", Week4"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub



-----Original Message-----

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("enter item to be replaced")
sStr1 = InputBox("enter replacement")
Application.displayalerts = False
For Each sh In Worksheets(Array
("sheet1", "sheet2", . . . , _
"sheet9","sheet10"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.displayalerts = True
End Sub

replace ... with the list of additional sheet names.

--
Regards,
Tom Ogilvy


Tony wrote in message
...
Hi

Am not hot on VB and am stuck. Have recorded a
macro
which uses text written into a box, running the
macro
to
run a mass replace job over 10 sheets. When I do
this,
everything works apart from the macro always
replaces
what was originally in the box rather than whatever
is
typed. How do I change this constant into a
variable
copy/paste? Please keep it simple if you can
help!!!

Thank you for any help you can give

Tony


.



.

Copying Variables in VB Editor In Excel
 

sStr = Range("L2").Value


Tony wrote in message
...
Coming up with box saying

Complile Error
Expected Sub, Function or Property

in reference to line
sStr Range("L2").Value

when I debug

please advise
-----Original Message-----
Sub ReplaceItems()
Dim sh As Worksheet
Dim sStr As String
Dim sStr1 As String
' assume your values are in L2 and A2 of the active sheet
sStr Range("L2").Value
sStr1 = Range("A2").Value
Application.DisplayAlerts = False
For Each sh In Worksheets(Array("Week1", _
"Week2", "Week3", "Week4"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub

--
Regards,
Tom Ogilvy

Tony wrote in message
...
This is what I have done, obviously wrong, can you show
me forward again please?

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("L2")
sStr1 = InputBox("A2")
Application.DisplayAlerts = False
(Array(("Week1", "Week2", "Week3", Week4"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub



-----Original Message-----

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("enter item to be replaced")
sStr1 = InputBox("enter replacement")
Application.displayalerts = False
For Each sh In Worksheets(Array
("sheet1", "sheet2", . . . , _
"sheet9","sheet10"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.displayalerts = True
End Sub

replace ... with the list of additional sheet names.

--
Regards,
Tom Ogilvy


Tony wrote in message
...
Hi

Am not hot on VB and am stuck. Have recorded a
macro
which uses text written into a box, running the
macro
to
run a mass replace job over 10 sheets. When I do
this,
everything works apart from the macro always
replaces
what was originally in the box rather than whatever
is
typed. How do I change this constant into a
variable
copy/paste? Please keep it simple if you can
help!!!

Thank you for any help you can give

Tony


.



.

Copying Variables in VB Editor In Excel
 

I am really so sorry to keep bothering you about this but
it hasn't woked properly. Ran the debug after last
change, seemed to work fine. Changed the text in box A2,
ran the macro and it changed all info on other 4 sheets
to the name I had overwritten. Any suggestions to how I
can change related cells to whatever I type in a2

Really appreciate your help even if this is not possible

Cheers

Tony
-----Original Message-----
sStr = Range("L2").Value


Tony wrote in message
...
Coming up with box saying

Complile Error
Expected Sub, Function or Property

in reference to line
sStr Range("L2").Value

when I debug

please advise
-----Original Message-----
Sub ReplaceItems()
Dim sh As Worksheet
Dim sStr As String
Dim sStr1 As String
' assume your values are in L2 and A2 of the active
sheet
sStr Range("L2").Value
sStr1 = Range("A2").Value
Application.DisplayAlerts = False
For Each sh In Worksheets(Array("Week1", _
"Week2", "Week3", "Week4"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub

--
Regards,
Tom Ogilvy

Tony wrote in message
...
This is what I have done, obviously wrong, can you
show
me forward again please?

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("L2")
sStr1 = InputBox("A2")
Application.DisplayAlerts = False
(Array(("Week1", "Week2", "Week3", Week4"))
sh.Cells.Replace What:=sStr,
Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub



-----Original Message-----

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("enter item to be replaced")
sStr1 = InputBox("enter replacement")
Application.displayalerts = False
For Each sh In Worksheets(Array
("sheet1", "sheet2", . . . , _
"sheet9","sheet10"))
sh.Cells.Replace What:=sStr,
Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.displayalerts = True
End Sub

replace ... with the list of additional sheet
names.

--
Regards,
Tom Ogilvy


Tony wrote in
message
...
Hi

Am not hot on VB and am stuck. Have recorded a
macro
which uses text written into a box, running the
macro
to
run a mass replace job over 10 sheets. When I do
this,
everything works apart from the macro always
replaces
what was originally in the box rather than
whatever
is
typed. How do I change this constant into a
variable
copy/paste? Please keep it simple if you can
help!!!

Thank you for any help you can give

Tony


.



.



.

Copying Variables in VB Editor In Excel
 

Just got it to work, thanks so much mate. Really
appreciate your help


-----Original Message-----
I am really so sorry to keep bothering you about this
but
it hasn't woked properly. Ran the debug after last
change, seemed to work fine. Changed the text in box
A2,
ran the macro and it changed all info on other 4 sheets
to the name I had overwritten. Any suggestions to how I
can change related cells to whatever I type in a2

Really appreciate your help even if this is not possible

Cheers

Tony
-----Original Message-----
sStr = Range("L2").Value


Tony wrote in message
...
Coming up with box saying

Complile Error
Expected Sub, Function or Property

in reference to line
sStr Range("L2").Value

when I debug

please advise
-----Original Message-----
Sub ReplaceItems()
Dim sh As Worksheet
Dim sStr As String
Dim sStr1 As String
' assume your values are in L2 and A2 of the active
sheet
sStr Range("L2").Value
sStr1 = Range("A2").Value
Application.DisplayAlerts = False
For Each sh In Worksheets(Array("Week1", _
"Week2", "Week3", "Week4"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1,
_
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub

--
Regards,
Tom Ogilvy

Tony wrote in
message
...
This is what I have done, obviously wrong, can you
show
me forward again please?

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("L2")
sStr1 = InputBox("A2")
Application.DisplayAlerts = False
(Array(("Week1", "Week2", "Week3", Week4"))
sh.Cells.Replace What:=sStr,
Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub



-----Original Message-----

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("enter item to be replaced")
sStr1 = InputBox("enter replacement")
Application.displayalerts = False
For Each sh In Worksheets(Array
("sheet1", "sheet2", . . . , _
"sheet9","sheet10"))
sh.Cells.Replace What:=sStr,
Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.displayalerts = True
End Sub

replace ... with the list of additional sheet
names.

--
Regards,
Tom Ogilvy


Tony wrote in
message
...
Hi

Am not hot on VB and am stuck. Have recorded a
macro
which uses text written into a box, running the
macro
to
run a mass replace job over 10 sheets. When I
do
this,
everything works apart from the macro always
replaces
what was originally in the box rather than
whatever
is
typed. How do I change this constant into a
variable
copy/paste? Please keep it simple if you can
help!!!

Thank you for any help you can give

Tony


.



.



.

.

Copying Variables in VB Editor In Excel
 

sStr = Range("L2").Value
sStr1 = Range("A2").Value

any value matching the value in L2 is changed to the Value in A2

If that isn't what you want, change it to what you want.

If you want the macro to know what A2 used to be, sorry, but it doesn't work
that way.

--
Regards,
Tom Ogilvy



Tony wrote in message
...
I am really so sorry to keep bothering you about this but
it hasn't woked properly. Ran the debug after last
change, seemed to work fine. Changed the text in box A2,
ran the macro and it changed all info on other 4 sheets
to the name I had overwritten. Any suggestions to how I
can change related cells to whatever I type in a2

Really appreciate your help even if this is not possible

Cheers

Tony
-----Original Message-----
sStr = Range("L2").Value


Tony wrote in message
...
Coming up with box saying

Complile Error
Expected Sub, Function or Property

in reference to line
sStr Range("L2").Value

when I debug

please advise
-----Original Message-----
Sub ReplaceItems()
Dim sh As Worksheet
Dim sStr As String
Dim sStr1 As String
' assume your values are in L2 and A2 of the active
sheet
sStr Range("L2").Value
sStr1 = Range("A2").Value
Application.DisplayAlerts = False
For Each sh In Worksheets(Array("Week1", _
"Week2", "Week3", "Week4"))
sh.Cells.Replace What:=sStr, Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub

--
Regards,
Tom Ogilvy

Tony wrote in message
...
This is what I have done, obviously wrong, can you
show
me forward again please?

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("L2")
sStr1 = InputBox("A2")
Application.DisplayAlerts = False
(Array(("Week1", "Week2", "Week3", Week4"))
sh.Cells.Replace What:=sStr,
Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.DisplayAlerts = True
End Sub



-----Original Message-----

Sub ReplaceItems()
Dim sh As Worksheet
Dim str As String
sStr = InputBox("enter item to be replaced")
sStr1 = InputBox("enter replacement")
Application.displayalerts = False
For Each sh In Worksheets(Array
("sheet1", "sheet2", . . . , _
"sheet9","sheet10"))
sh.Cells.Replace What:=sStr,
Replacement:=sStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
Next
Application.displayalerts = True
End Sub

replace ... with the list of additional sheet
names.

--
Regards,
Tom Ogilvy


Tony wrote in
message
...
Hi

Am not hot on VB and am stuck. Have recorded a
macro
which uses text written into a box, running the
macro
to
run a mass replace job over 10 sheets. When I do
this,
everything works apart from the macro always
replaces
what was originally in the box rather than
whatever
is
typed. How do I change this constant into a
variable
copy/paste? Please keep it simple if you can
help!!!

Thank you for any help you can give

Tony


.



.



.