| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Select dynamic data in 2 columns, and repeat macro to next sheet | Excel Discussion (Misc queries) | |||
| How do I replace/select new fields in pivot table using a macro? | Excel Discussion (Misc queries) | |||
| Macro to select columns and replace data | Excel Programming | |||
| Macro won't select columns | Excel Discussion (Misc queries) | |||
| Select other workbook to select data in a macro. | Excel Programming | |||
Linear Mode
