Find and Replace MACRO Help Please.

serquan

Board Regular
Joined
Jun 4, 2012
Messages
55
Hello guys,

Please help on below simple macro.

MACRO selects columns and changes specific texts with news

Always B39, B40, B41, B42, B43 changes with the Column ID.

ie: Column name is C, so text will be changed to C39, C40, C41, C42, C43.

Since I have lots of columns like starting with C thru DB so I need some macro to make it happen easier?

Any help will be appreciated.



Code:
Sub Macro2()
'
' Macro2 Macro
'

'
    Range("D38:D43").Select
    Selection.Replace What:="B39", Replacement:="D39", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="B40", Replacement:="D40", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="B41", Replacement:="D41", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="B42", Replacement:="D42", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="B43", Replacement:="D43", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("E38:E43").Select
    Selection.Replace What:="B39", Replacement:="E39", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="B40", Replacement:="E40", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="B41", Replacement:="E41", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="B42", Replacement:="E42", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="B43", Replacement:="E43", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Welcome!
How about this!
Code:
Sub doit()
Dim cl As Range
Dim sh As Worksheet
Dim cellName As String
Set sh = ActiveSheet
For Each cl In sh.Range("39:43")
    If (cl.Column <= 2) Then
    Else
        cellName = cl.Address(False, False)
        cl.Replace What:="B" & cl.Row, Replacement:=cellName
    End If
Next cl
 
End Sub
 
Upvote 0
Here's a brute force method. Overwrites contents with actual Column Letter/Row Number.

Code:
Function ColumnLetter(ColumnNumber As Integer) As String
'http://www.freevbcode.com/ShowCode.asp?ID=4303
  If ColumnNumber > 26 Then
    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter
    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')
    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function

Sub ChangeColLetter()
Dim WS As Worksheet
Dim aCol As Range
Dim B As Long
Set WS = ActiveSheet
With WS
For Each aCol In .Range("C:BD").Columns
    For B = 39 To 43
        .Cells(B, aCol.Column) = ColumnLetter(aCol.Column) & B
    Next
Next
End With
End Sub
 
Upvote 0
Thank you guys for replying, I appreciated.

However it does not worked.

There is a code applied to "B" column below. As you see always "B39", "B40", "B41", "B42", "B43" does "*" to specific cells in other sheet. I have copied this formula in all other cells in this sheet (B column thru DB column).

So next "C" column should be changed as "C39", "C40", "C41", "C42", "C43". and so on until "DB" column.

Is there any formula to make this happen?

Thank you so much!

Code:
=(B39*'Mechanical Bldg.'!E7)+(B39*'Mechanical Bldg.'!F7)+(B39*'Mechanical Bldg.'!G7)+(B40*'Mechanical Bldg.'!H7)+(B40*'Mechanical Bldg.'!I7)+(B41*'Mechanical Bldg.'!J7)+(B41*'Mechanical Bldg.'!K7)+(B42*'Mechanical Bldg.'!L7)+(B42*'Mechanical Bldg.'!M7)+(B43*'Mechanical Bldg.'!N7)+(B43*'Mechanical Bldg.'!O7)+(B43*'Mechanical Bldg.'!P7)+(B43*'Mechanical Bldg.'!Q7)+(B43*'Mechanical Bldg.'!R7)+(B43*'Mechanical Bldg.'!S7)+(B43*'Mechanical Bldg.'!T7)+(B43*'Mechanical Bldg.'!U7)+(B43*'Mechanical Bldg.'!V7)+(B43*'Mechanical Bldg.'!W7)
 
Upvote 0
Cell formula references should be changed automatically when you copy from B to C. etc.

To prevent a Column and/or row from changing cell reference, then proceed the Column/Row with a $.

=(B39*'Mechanical Bldg.'!$E$7)+(B39*'Mechanical Bldg.'!$F$7)

When you copy B to C then it should look like this.

=(C39*'Mechanical Bldg.'!$E$7)+(C39*'Mechanical Bldg.'!$F$7)
 
Last edited:
Upvote 0
Cell formula references should be changed automatically when you copy from B to C. etc.

To prevent a Column and/or row from changing cell reference, then proceed the Column/Row with a $.

=(B39*'Mechanical Bldg.'!$E$7)+(B39*'Mechanical Bldg.'!$F$7)

When you copy B to C then it should look like this.

=(C39*'Mechanical Bldg.'!$E$7)+(C39*'Mechanical Bldg.'!$F$7)

David, thank you so much!!!!
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top