Not sure what's going on Rocky, I copied and pasted the code into a new WB
and it tested it on a column down to 6000 rows and also on a completely empty
column and get no errors. That Last row statement is there to pick up the
last row used so the macro doesn't have to go through the whole column, but
with the number of entries you say you have, maybe it isn't really necessary.
Try this into a new module and see if you get an error.....

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

LastRow = 7000 '<<<change this to last row used in the col


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

What I did was comment out the line that was giving you the error and the
line above and below it and added a value to LastRow.

If that doesn't work for you, I'm not sure what the problem is. It tests ok
down to 7000 rows for me. I think I misspoke up above saying I was using
2002 but my Excel is 2002 with Windows xp, but this should work on 2003 also.

I'm sure there's someone in these forumns that can figure out what's wrong,
but it's above my level...lol

HTH,

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