Macro to select columns and replace data

  • Thread starter Thread starter graemeh
  • Start date Start date
G

graemeh

I want to loop through a range of colums and do a replacement like below. Is
there an easy way to do this via a marco?

Columns("AQ:AQ").Select
Selection.Replace What:="ap$", Replacement:="AQ$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("AR:AR").Select
Selection.Replace What:="ap$", Replacement:="Ar$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
 
I want to loop through a range of colums and do a replacement like below.Is
there an easy way to do this via a marco?

    Columns("AQ:AQ").Select
        Selection.Replace What:="ap$", Replacement:="AQ$", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("AR:AR").Select
        Selection.Replace What:="ap$", Replacement:="Ar$", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Click to expand...

This should work for you.
Columns("AQ").Replace What:=" ap$", Replacement:="AQ$",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
 
Ron, I need it to step from column to column and do a replacement for each
coulmn as it goes. Currently I have 37 columns that I need to step do a
replacement command.
 
Ron, I need it to step from column to column and do a replacement for each
coulmn as it goes. Currently I have 37 columns that I need to step do a
replacement command.
 
Try the below..Adjust the to column to suit your requirement....

Sub Macro()
Dim lngCol As Long, strCol As String
For lngCol = Columns("AQ").Column To Columns("BB").Column
strCol = Split(Columns(lngCol).Address(False, False), ":")(0)
Columns(strCol).Replace What:=" ap$", Replacement:=strCol & "$", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next
End Sub
If this post helps click Yes
 
I'm not sure where you're starting or where you're finishing -- or if you're
doing contiguous columns...

And it looks like you may be fixing formulas...

So maybe...

Option Explicit
Sub testme02()

Dim myWhat As String
Dim myWith As String
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim CalcMode As Long

CalcMode = Application.Calculate
Application.Calculation = xlCalculationManual

With ActiveSheet
FirstCol = .Range("b1").Column
LastCol = .Range("iv1").Column

For iCol = FirstCol To LastCol Step 1 'right?
'the previous column
myWhat = .Cells(1, iCol - 1).Address(0, 0) 'like B1
'remove the 1 and add $
myWhat = Left(myWhat, Len(myWhat) - 1) & "$"

'the current column
myWith = .Cells(1, iCol).Address(0, 0) 'like B1
'remove the 1 and add $
myWith = Left(myWith, Len(myWhat) - 1) & "$"

'just for testing
'MsgBox "what:=" & myWhat & vbLf & "with:=" & myWith

.Columns(iCol).Replace what:=myWhat, _
Replacement:=myWith, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next iCol
End With

Application.Calculation = CalcMode
End Sub
 
I read your question wrong.

I thought you were changing the "what" parm in each of the replaces.

Delete these lines:
myWhat = .Cells(1, iCol - 1).Address(0, 0) 'like B1
'remove the 1 and add $
myWhat = Left(myWhat, Len(myWhat) - 1) & "$"
Click to expand...
and replace them with:
myWhat = "AR$"

And if your columns are contiguous, just do a single edit|replace:

.range("aq1").entirecolumn.resize(,37).replace ...
 
Back
Top