Hi Rocky,

Try the following macro. You can either use the Y, N and Esc keys or your
mouse to select from the msgbox.

You indicated that you wanted to select the cell where you want to start so
the macro relies on you doing this before you start it.

The Cancel key is so you can stop at any time and resume later but you will
need to select the cell where you stopped it before you restart the macro.

If you select a cell that meets the find criteria as your first cell, it
will not process that cell until last because Find always finds the next
ocurrence and then loops around to the start again. You will get a changed
message when it gets back to the first cell actually found. (Not the cell you
selected to start from but the first one it finds). If you want it to start
at a particular cell then select the cell above it before starting the macro.
(It does not have to meet the find criteria).

Feel free to get back to me if you have a problem with it.

Sub Find_And_Modify()

Dim strStart As String
Dim bolStart As Boolean
Dim rngColumn As Range
Dim strTofind As String
Dim Response

With ActiveSheet
Set rngColumn = ActiveCell.EntireColumn
End With

strTofind = "RCA"

bolStart = False

Do While Response < vbCancel
rngColumn.Find(What:=strTofind, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False) _
.Activate

If ActiveCell.Address = strStart Then
MsgBox "You are back to the first found cell" _
& Chr(13) & "processing will terminate"
Exit Sub
End If

If bolStart = False Then
strStart = ActiveCell.Address
bolStart = True
End If

Response = MsgBox("Select Yes to Modify adjacent column" _
& Chr(13) & "No to continue search" _
& Chr(13) & "Cancel to exit", vbYesNoCancel)

If Response = vbYes Then
ActiveCell.Offset(0, 1) = "47-" & ActiveCell.Offset(0, 1)
End If

Loop

End Sub




--
Regards,

OssieMac


"Rocky Lane" wrote:

I have an extremely simple task to perform manually. But to change over
10,000 cells - not! Everybody knows Excel allows you to write simple VBA
macros to do things like this.

OK ... I'm a dummy ... I give up. I could have manually done the changes in
the time I've wasted trying to create a working macro solution.

I have a 47,000 + row spreadsheet with 2 columns (not column A & B). After
manually selecting the current cell location, I want to start the macro. In
text cells in the first column, I want to search down the column looking for
the letters RCA. Case is not important. If found, I want to pause the macro
to allow me to decide if what the macro has found is acceptable.
If it is, I want to press the "y" key to add 47- to the front of data in the
cell to the right (second column) of the found cell in the first column. For
example if the second column cell contained 4862, after pressing the "y" key,
the cell would contain 47-4862 and the current cell location would move left
to the first column to continue searching for RCA.
If it is not acceptable, I want to press the "n" key and have the macro
continue searching for RCA from the current cell location down the first
column.

Help!

Thanks.