ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Macro (https://www.excelbanter.com/excel-discussion-misc-queries/2983-macro.html)

Frank
Macro
 
I would like the Macro to prompt me to enter the "29"
and "32" numbers manually while the macro is running or
better yet, have the macro grab the numbers from specified
locations on the sheet. How can I do this?

Thank you for any help you can give on this matter.

MACRO1

Range("A3:AF3").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Range("B7:AC7").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B11:AF11").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 1
Range("B15:AE15").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B19:AF19").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B23:AE23").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B27:AF27").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
Range("B31:AF31").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=12
Range("B35:AE35").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B39:AF39").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B43:AE43").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B47:AF47").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A47").Select
End Sub

JE McGimpsey
One way:

Public Sub Replacer()
With Range("A3:F3,B11:AF11,B23:AE23,B27:AF27,B31:AF31," & _
"B35:AE35,B39:AF39,B43:AE43,B47:AF47")
.Replace _
What:=Range("A1").Value, _
Replacement:=Range("B1").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
End Sub

Adjust your range values as desired.

Or, to specify manually:

Public Sub ReplacerManual()
Dim vResult1 As Variant
Dim vResult2 As Variant
vResult1 = Application.InputBox( _
Prompt:="Enter number to replace", _
Default:=29, _
Title:="Replacer", _
Type:=1)
If vResult1 = False Then Exit Sub 'user cancelled
vResult2 = Application.InputBox( _
Prompt:="Enter replacement", _
Default:=32, _
Title:="Replacer", _
Type:=1)
If vResult2 = False Then Exit Sub 'user cancelled
With Range("A3:F3,B11:AF11,B23:AE23,B27:AF27,B31:AF31," & _
"B35:AE35,B39:AF39,B43:AE43,B47:AF47")
.Replace _
What:=vResult1, _
Replacement:=vResult2, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
End Sub

In article ,
"Frank" wrote:

I would like the Macro to prompt me to enter the "29"
and "32" numbers manually while the macro is running or
better yet, have the macro grab the numbers from specified
locations on the sheet. How can I do this?

Thank you for any help you can give on this matter.

MACRO1

Range("A3:AF3").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Range("B7:AC7").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1

Frank
Thank you, Works Great
-----Original Message-----
One way:

Public Sub Replacer()
With Range
("A3:F3,B11:AF11,B23:AE23,B27:AF27,B31:AF31," & _
"B35:AE35,B39:AF39,B43:AE43,B4
7:AF47")
.Replace _
What:=Range("A1").Value, _
Replacement:=Range("B1").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
End Sub

Adjust your range values as desired.

Or, to specify manually:

Public Sub ReplacerManual()
Dim vResult1 As Variant
Dim vResult2 As Variant
vResult1 = Application.InputBox( _
Prompt:="Enter number to replace", _
Default:=29, _
Title:="Replacer", _
Type:=1)
If vResult1 = False Then Exit Sub 'user cancelled
vResult2 = Application.InputBox( _
Prompt:="Enter replacement", _
Default:=32, _
Title:="Replacer", _
Type:=1)
If vResult2 = False Then Exit Sub 'user cancelled
With Range
("A3:F3,B11:AF11,B23:AE23,B27:AF27,B31:AF31," & _
"B35:AE35,B39:AF39,B43:AE43,B4
7:AF47")
.Replace _
What:=vResult1, _
Replacement:=vResult2, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
End Sub

In article ,
"Frank" wrote:

I would like the Macro to prompt me to enter the "29"
and "32" numbers manually while the macro is running or
better yet, have the macro grab the numbers from
specified
locations on the sheet. How can I do this?

Thank you for any help you can give on this matter.

MACRO1

Range("A3:AF3").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Range("B7:AC7").Select
Selection.Replace What:="29", Replacement:="32",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
.


All times are GMT +1. The time now is 08:36 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com