I want to search my spreadsheet for specific values and replace them. As my
sheet is huge I want to start my search in row 1 of a specific column. The
problem is I may add columns in front of the one I am currently using to
start my search and replace. I want to be able to find the column heading
titled "New Deals" and then go to the cell directly below it and begin my
search and replace macro. If the column heading "New Deals" moves from the
5th to the 6th column I still want to be able to use the same macro without
having to go in and change it everytime.

HELP!

Try this

Public Sub MyFindAndReplace()
Dim r As Range
Dim vSearchFor, vReplaceWith
Dim lLastRow&, lLastCol&

With Sheet1
Set r = .Rows(1).Find(What:="New Deals", LookIn:=xlValues,
LookAt:=xlWhole, MatchByte:=True)
If Not r Is Nothing Then
vSearchFor = InputBox("Enter what you want to search for: ",
"Search For")
If vSearchFor < "" Then
vReplaceWith = InputBox("Enter what you want to replace
with: ", "Replace With")
lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
lLastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
Set r = r.Resize(lLastRow - r.Row, lLastCol - r.Column + 1)
r.Replace What:=vSearchFor, Replacement:=vReplaceWith,
MatchCase:=False
End If
End If
End With
End Sub


"Teresa K." wrote:

I want to search my spreadsheet for specific values and replace them. As my
sheet is huge I want to start my search in row 1 of a specific column. The
problem is I may add columns in front of the one I am currently using to
start my search and replace. I want to be able to find the column heading
titled "New Deals" and then go to the cell directly below it and begin my
search and replace macro. If the column heading "New Deals" moves from the
5th to the 6th column I still want to be able to use the same macro without
having to go in and change it everytime.

HELP!

Thank you both so much for all your help! You have been totally awesome!

"Alok" wrote:

Try this

Public Sub MyFindAndReplace()
Dim r As Range
Dim vSearchFor, vReplaceWith
Dim lLastRow&, lLastCol&

With Sheet1
Set r = .Rows(1).Find(What:="New Deals", LookIn:=xlValues,
LookAt:=xlWhole, MatchByte:=True)
If Not r Is Nothing Then
vSearchFor = InputBox("Enter what you want to search for: ",
"Search For")
If vSearchFor < "" Then
vReplaceWith = InputBox("Enter what you want to replace
with: ", "Replace With")
lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
lLastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
Set r = r.Resize(lLastRow - r.Row, lLastCol - r.Column + 1)
r.Replace What:=vSearchFor, Replacement:=vReplaceWith,
MatchCase:=False
End If
End If
End With
End Sub


"Teresa K." wrote:

I want to search my spreadsheet for specific values and replace them. As my
sheet is huge I want to start my search in row 1 of a specific column. The
problem is I may add columns in front of the one I am currently using to
start my search and replace. I want to be able to find the column heading
titled "New Deals" and then go to the cell directly below it and begin my
search and replace macro. If the column heading "New Deals" moves from the
5th to the 6th column I still want to be able to use the same macro without
having to go in and change it everytime.

HELP!

Sub SearchByColumnHeader()
Dim ColHeader As Range
Dim R As Range
Set ColHeader = Cells.Find(What:="New Deals", _
After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
If ColHeader Is Nothing Then Exit Sub
Set R = Cells.Find(What:="Next Search Item", _
After:=ColHeader.Offset(1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=True)
R = "Replace Text"
End Sub

That should get you pretty close.
--
Charles Chickering

"A good example is twice the value of good advice."


"Teresa K." wrote:

I want to search my spreadsheet for specific values and replace them. As my
sheet is huge I want to start my search in row 1 of a specific column. The
problem is I may add columns in front of the one I am currently using to
start my search and replace. I want to be able to find the column heading
titled "New Deals" and then go to the cell directly below it and begin my
search and replace macro. If the column heading "New Deals" moves from the
5th to the 6th column I still want to be able to use the same macro without
having to go in and change it everytime.

HELP!