Macro to select columns and replace data
 

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

Macro to select columns and replace data
 

On Jul 8, 4:14*pm, graemeh wrote:
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

This should work for you.
Columns("AQ").Replace What:=" ap$", Replacement:="AQ$",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Macro to select columns and replace data
 

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" wrote:

On Jul 8, 4:14 pm, graemeh wrote:
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

This should work for you.
Columns("AQ").Replace What:=" ap$", Replacement:="AQ$",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Macro to select columns and replace data
 

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" wrote:

On Jul 8, 4:14 pm, graemeh wrote:
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

This should work for you.
Columns("AQ").Replace What:=" ap$", Replacement:="AQ$",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Macro to select columns and replace data
 

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
---------------
Jacob Skaria


"graemeh" wrote:

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" wrote:

On Jul 8, 4:14 pm, graemeh wrote:
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

This should work for you.
Columns("AQ").Replace What:=" ap$", Replacement:="AQ$",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Macro to select columns and replace data
 

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



graemeh wrote:

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" wrote:

On Jul 8, 4:14 pm, graemeh wrote:
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

This should work for you.
Columns("AQ").Replace What:=" ap$", Replacement:="AQ$",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False


--

Dave Peterson

Macro to select columns and replace data
 

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) & "$"
and replace them with:
myWhat = "AR$"

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

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






Dave Peterson wrote:

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

graemeh wrote:

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" wrote:

On Jul 8, 4:14 pm, graemeh wrote:
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

This should work for you.
Columns("AQ").Replace What:=" ap$", Replacement:="AQ$",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False


--

Dave Peterson

--

Dave Peterson