Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Frank
 
Posts: n/a
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default
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
Reply With QuoteReply With Quote
  #3   Report Post  
Frank
 
Posts: n/a
Default
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
.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Record Macro Relative does not work? lbbss Excel Discussion (Misc queries) 3 December 13th 04 08:43 PM
Record Macro Relative does not work? lbbss Excel Discussion (Misc queries) 1 December 13th 04 07:55 PM
excel macro inconsistency JM Excel Discussion (Misc queries) 2 December 9th 04 01:13 AM
Macro and If Statement SATB Excel Discussion (Misc queries) 2 December 3rd 04 04:46 PM
Macro help Jeff Garrett Excel Discussion (Misc queries) 11 December 1st 04 08:47 PM


All times are GMT +1. The time now is 11:19 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement