Copying Variables in VB Editor In Excel

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

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
 
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)
 
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.
 
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
 
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
Click to expand...
("sheet1", "sheet2", . . . , _
 
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
 
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
 
sStr = Range("L2").Value


Tony said:
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
Click to expand...
 
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
 
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.
 
Back
Top