Howdy all,

First off, I'm just a self-taught or rather semi-self-taught user here but I
tried to help out on one of the questions here last week. The OP wanted a
macro that would search down a Col, find a value, stop and let him select
whether to add to that value and put the results in the next Col over same
Row. Here's what I finally came up with and it works great evertime for me
but there may be problems with it as he can't seem to get it to work on his
machine. I'm using XP and Excel2002, the's got Excel2003. If one or more of
you could try the code and see if it does work on other machines I'd be
greatly appreciative. TIA for any help on this issue....Entire code posted
below:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

TIA,

Don

Actually misspoke when I first posted.....the search was for a string (not a
value) and the search string might be only part of a string contained in a
cell....Hope that makes sense...

Don

"Don" wrote:

Howdy all,

First off, I'm just a self-taught or rather semi-self-taught user here but I
tried to help out on one of the questions here last week. The OP wanted a
macro that would search down a Col, find a value, stop and let him select
whether to add to that value and put the results in the next Col over same
Row. Here's what I finally came up with and it works great evertime for me
but there may be problems with it as he can't seem to get it to work on his
machine. I'm using XP and Excel2002, the's got Excel2003. If one or more of
you could try the code and see if it does work on other machines I'd be
greatly appreciative. TIA for any help on this issue....Entire code posted
below:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

TIA,

Don

Dang....third times a charm...the line breaks have the code messed
up...here's the code with corrections for the line breaks:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

Hope this works...

Tks again, Don

"Don" wrote:

Howdy all,

First off, I'm just a self-taught or rather semi-self-taught user here but I
tried to help out on one of the questions here last week. The OP wanted a
macro that would search down a Col, find a value, stop and let him select
whether to add to that value and put the results in the next Col over same
Row. Here's what I finally came up with and it works great evertime for me
but there may be problems with it as he can't seem to get it to work on his
machine. I'm using XP and Excel2002, the's got Excel2003. If one or more of
you could try the code and see if it does work on other machines I'd be
greatly appreciative. TIA for any help on this issue....Entire code posted
below:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

TIA,

Don

The charmed version worked ok for me in xl2003.

What happens when it fails for the user?


Don wrote:

Dang....third times a charm...the line breaks have the code messed
up...here's the code with corrections for the line breaks:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

Hope this works...

Tks again, Don

"Don" wrote:

Howdy all,

First off, I'm just a self-taught or rather semi-self-taught user here but I
tried to help out on one of the questions here last week. The OP wanted a
macro that would search down a Col, find a value, stop and let him select
whether to add to that value and put the results in the next Col over same
Row. Here's what I finally came up with and it works great evertime for me
but there may be problems with it as he can't seem to get it to work on his
machine. I'm using XP and Excel2002, the's got Excel2003. If one or more of
you could try the code and see if it does work on other machines I'd be
greatly appreciative. TIA for any help on this issue....Entire code posted
below:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

TIA,

Don

--

Dave Peterson

Hi Dave,

Thanks for responding. Here's his statement as to the fairlu

"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 assume he's talking about entering D for the Column although he didn't
clarify that.

I suggested that he eliminate the LastRow step and enter the commands to let
the macro search the entire column he selects (he did state that he was
dealing with several thousand rows, but it'll go through those quite quickly
if it finds no matches). He hasn't responded to that yet but I'd like to
know, if possible, why it fails for him. Good to know that it does work for
someone else.

Thanks Again,

Don


"Dave Peterson" wrote:

The charmed version worked ok for me in xl2003.

What happens when it fails for the user?


Don wrote:

Dang....third times a charm...the line breaks have the code messed
up...here's the code with corrections for the line breaks:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

Hope this works...

Tks again, Don

"Don" wrote:

Howdy all,

First off, I'm just a self-taught or rather semi-self-taught user here but I
tried to help out on one of the questions here last week. The OP wanted a
macro that would search down a Col, find a value, stop and let him select
whether to add to that value and put the results in the next Col over same
Row. Here's what I finally came up with and it works great evertime for me
but there may be problems with it as he can't seem to get it to work on his
machine. I'm using XP and Excel2002, the's got Excel2003. If one or more of
you could try the code and see if it does work on other machines I'd be
greatly appreciative. TIA for any help on this issue....Entire code posted
below:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

TIA,

Don

--

Dave Peterson

Change all your declarations from "As Integer" to "As Long".

Integers can only go up to 32k. If you have more rows than 32k, those integers
will blow up!

I'd use:

Dim c As Range
Dim MySearchValue As String 'from an inputbox, it's a string
Dim MyString As String
Dim ReplaceWith As String
Dim MyCol As String
Dim Rge As Range
Dim i As Long
Dim LastRow As Long


Don wrote:

Hi Dave,

Thanks for responding. Here's his statement as to the fairlu

"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 assume he's talking about entering D for the Column although he didn't
clarify that.

I suggested that he eliminate the LastRow step and enter the commands to let
the macro search the entire column he selects (he did state that he was
dealing with several thousand rows, but it'll go through those quite quickly
if it finds no matches). He hasn't responded to that yet but I'd like to
know, if possible, why it fails for him. Good to know that it does work for
someone else.

Thanks Again,

Don

"Dave Peterson" wrote:

The charmed version worked ok for me in xl2003.

What happens when it fails for the user?


Don wrote:

Dang....third times a charm...the line breaks have the code messed
up...here's the code with corrections for the line breaks:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

Hope this works...

Tks again, Don

"Don" wrote:

Howdy all,

First off, I'm just a self-taught or rather semi-self-taught user here but I
tried to help out on one of the questions here last week. The OP wanted a
macro that would search down a Col, find a value, stop and let him select
whether to add to that value and put the results in the next Col over same
Row. Here's what I finally came up with and it works great evertime for me
but there may be problems with it as he can't seem to get it to work on his
machine. I'm using XP and Excel2002, the's got Excel2003. If one or more of
you could try the code and see if it does work on other machines I'd be
greatly appreciative. TIA for any help on this issue....Entire code posted
below:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

TIA,

Don

--

Dave Peterson


--

Dave Peterson

Hi Dave,

This may get posted twice as I got a page too busy back when I posted this
response the first time but here goes....

Thanks for the tip. I did test the macro to 7000 rows but only about 12
cells had anything in them. I'd assume the OP's WS had rows full of data.
I'll make the changes and relay them on to him.

Thanks Again for the Help....

Don

"Dave Peterson" wrote:

Change all your declarations from "As Integer" to "As Long".

Integers can only go up to 32k. If you have more rows than 32k, those integers
will blow up!

I'd use:

Dim c As Range
Dim MySearchValue As String 'from an inputbox, it's a string
Dim MyString As String
Dim ReplaceWith As String
Dim MyCol As String
Dim Rge As Range
Dim i As Long
Dim LastRow As Long


Don wrote:

Hi Dave,

Thanks for responding. Here's his statement as to the fairlu

"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 assume he's talking about entering D for the Column although he didn't
clarify that.

I suggested that he eliminate the LastRow step and enter the commands to let
the macro search the entire column he selects (he did state that he was
dealing with several thousand rows, but it'll go through those quite quickly
if it finds no matches). He hasn't responded to that yet but I'd like to
know, if possible, why it fails for him. Good to know that it does work for
someone else.

Thanks Again,

Don

"Dave Peterson" wrote:

The charmed version worked ok for me in xl2003.

What happens when it fails for the user?


Don wrote:

Dang....third times a charm...the line breaks have the code messed
up...here's the code with corrections for the line breaks:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

Hope this works...

Tks again, Don

"Don" wrote:

Howdy all,

First off, I'm just a self-taught or rather semi-self-taught user here but I
tried to help out on one of the questions here last week. The OP wanted a
macro that would search down a Col, find a value, stop and let him select
whether to add to that value and put the results in the next Col over same
Row. Here's what I finally came up with and it works great evertime for me
but there may be problems with it as he can't seem to get it to work on his
machine. I'm using XP and Excel2002, the's got Excel2003. If one or more of
you could try the code and see if it does work on other machines I'd be
greatly appreciative. TIA for any help on this issue....Entire code posted
below:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

TIA,

Don

--

Dave Peterson


--

Dave Peterson

I tested with about 5 cells of data.

But I don't use "As Integer" or "as Single" anymore. I use "as Long" and "as
double".

Don wrote:

Hi Dave,

This may get posted twice as I got a page too busy back when I posted this
response the first time but here goes....

Thanks for the tip. I did test the macro to 7000 rows but only about 12
cells had anything in them. I'd assume the OP's WS had rows full of data.
I'll make the changes and relay them on to him.

Thanks Again for the Help....

Don

"Dave Peterson" wrote:

Change all your declarations from "As Integer" to "As Long".

Integers can only go up to 32k. If you have more rows than 32k, those integers
will blow up!

I'd use:

Dim c As Range
Dim MySearchValue As String 'from an inputbox, it's a string
Dim MyString As String
Dim ReplaceWith As String
Dim MyCol As String
Dim Rge As Range
Dim i As Long
Dim LastRow As Long


Don wrote:

Hi Dave,

Thanks for responding. Here's his statement as to the fairlu

"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 assume he's talking about entering D for the Column although he didn't
clarify that.

I suggested that he eliminate the LastRow step and enter the commands to let
the macro search the entire column he selects (he did state that he was
dealing with several thousand rows, but it'll go through those quite quickly
if it finds no matches). He hasn't responded to that yet but I'd like to
know, if possible, why it fails for him. Good to know that it does work for
someone else.

Thanks Again,

Don

"Dave Peterson" wrote:

The charmed version worked ok for me in xl2003.

What happens when it fails for the user?


Don wrote:

Dang....third times a charm...the line breaks have the code messed
up...here's the code with corrections for the line breaks:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

Hope this works...

Tks again, Don

"Don" wrote:

Howdy all,

First off, I'm just a self-taught or rather semi-self-taught user here but I
tried to help out on one of the questions here last week. The OP wanted a
macro that would search down a Col, find a value, stop and let him select
whether to add to that value and put the results in the next Col over same
Row. Here's what I finally came up with and it works great evertime for me
but there may be problems with it as he can't seem to get it to work on his
machine. I'm using XP and Excel2002, the's got Excel2003. If one or more of
you could try the code and see if it does work on other machines I'd be
greatly appreciative. TIA for any help on this issue....Entire code posted
below:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Variant
Dim MySearchValue 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
MySearchValue = InStr(c, MyString)
i = i + 1
If (Not IsNull(MySearchValue)) And (MySearchValue 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)
End If
End If
Next c

End Sub

TIA,

Don

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson