"Help Checking Out Macro"

[Don]

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

 

[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]

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

 

[Dave Peterson]

The charmed version worked ok for me in xl2003.

What happens when it fails for the user?

 

[Don]

Hi Dave,

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

"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]

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:

 

[Don]

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]

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".

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