| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Equation Editor & Excel | Excel Worksheet Functions | |||
| Copying functions without changing most variables in it | Excel Discussion (Misc queries) | |||
| How do I get Equation Editor on the Toolbar in Excel? | Setting up and Configuration of Excel | |||
| Excel 2007 : Equation editor | Excel Discussion (Misc queries) | |||
| Libraries In Excel VB Editor | Excel Programming | |||
Hybrid Mode
