Rocky,

Here's a little better code...this one picks up your search string if it's
anywhere in the cell.

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim ret As Integer
Dim MyString As String
Dim ReplaceWith As String
Dim MyCol As String
Dim Rge As Range
Dim i As Integer
Dim LastRow As Integer

On Error Resume Next
i = 0
MyCol = InputBox("What Column Do You Want To Search?")
With ActiveSheet
LastRow = .Cells(.Rows.Count, MyCol).End(xlUp).Row
End With
MyString = InputBox("What String Do You Wish To Search For?")
ReplaceWith = InputBox("What String Do You Wish To Write?")

Set Rge = Range(MyCol & "1:" & MyCol & LastRow)
For Each c In Rge
ret = InStr(c, MyString)
i = i + 1
If (Not IsNull(ret)) And (ret 0) Then
Range(MyCol & i).Select
If MsgBox("Is This One To Addend?", vbYesNo + vbInformation) = vbNo
Then
Range(MyCol & i).Offset(0, 1).Value = ""
Else
Range(MyCol & i).Offset(0, 1).Value = ReplaceWith & _
Range(MyCol & i).Value

End If
End If
Next c

End Sub

This one gives you three input boxes, Column selection, Search string
selection and Replacement entry.

Let me know if it works for you.

Don

"Rocky Lane" wrote:

Hi Don,

I did have the End Sub but just to be sure I deleted the entire macro and
created a new one copying the code you sent. Unfortunetly, I got the same
error. I looked at the formula and thought that maybe an extra space in the
formulamay be the problem so I deleted it but the debugger just put the space
back in and re-highlighted the formula line.

I hope you can find a solution to this error.

Thanks again.

Rocky

"Don" wrote:

Hi Rocky,

Let me try this again...I notice I didn't have End Sub on my orginal post
either. Here's the complete module, copy and paste this, from Option Compare
Text to End Sub, into a completely new module. It works for me in 2003....


Option Compare Text
Option Explicit
Sub Macro4()

Dim LastRow As Integer
Dim i As Integer
Dim x As String
Dim Col As String

Col = InputBox("What Column Do You Wish To Start In?")
With ActiveSheet
LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row
End With
x = InputBox("Search For:")
For i = 1 To LastRow

If Range(Col & i).Value = x Then
Range(Col & i).Select
If MsgBox("Is This One To Addend?", vbYesNo + vbInformation) = vbNo Then
Range(Col & i).Offset(0, 1) = ""
Else
Range(Col & i).Offset(0, 1) = "47-" & Range(Col & i)
End If
End If
Next
End Sub



When done, the two Option statements should appear above the top line of the
macro and all the rest should be below that same line.

If it doesn't work this time, maybe someone can jump in and tell us
why.....HTH

Don

"Rocky Lane" wrote:

Thank you kindly Don for this code. I copied it into a new macro adding the
extra line Don suggested. When I start the macro, your rmessage box pops up
asking for the column to start in. When I enter D or d, a run-time error 6
occurs (overflow). When I select the debug button it opens up the macro
editor with the line starting with LastRow = highlighted.

I am not familiar enough with macros or the editor to understand what it is
trying to tell me or how to correct this error. I would certainly appreciate
any feedback you can give me on this.

Thanks.

Rocky

"Don" wrote:

Rocky,

Here's a simple code that will open up a couple of input boxes that let you
select which column you want to examine and what you want to search for.
However, this is case sensitive and I've played with it for some time and
can't get it to be case insensitive, but I'm sure someone will jump in with
that solution....

Option Explicit
Sub Macro4()

Dim LastRow As Integer
Dim i As Integer
Dim x As String
Dim Col As String

Col = InputBox("What Column Do You Wish To Start In?")
With ActiveSheet
LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row
End With
x = InputBox("Search For:")
For i = 1 To LastRow

If Range(Col & i).Value = x Then
Range(Col & i).Select
If MsgBox("Is This One To Addend?", vbYesNo + vbInformation) = vbNo Then
Range(Col & i).Offset(0, 1) = ""
Else
Range(Col & i).Offset(0, 1) = "47-" & Range(Col & i)
End If
End If
Next

I'm also fairly new to this coding so there most likely is a much simpler
way.....but, HTH...

Don