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.

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.

Hi again Rocky,

I just realized that if you use the Cancel to stop and then start the macro
again later it will not know where your original start position is. (It will
think it is where you re-started). If this is going to cause a problem then
let me know what the cell address of the original start position is and I can
hard code it in and the macro can test for it. You will probably know by the
row numbers that it has looped around to the start again anyway.

--
Regards,

OssieMac


"OssieMac" wrote:

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.

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


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

On Sat, 1 Mar 2008 17:43:01 -0800, Don wrote:

However, this is case sensitive and I've played with it for some time and
can't get it to be case insensitive,

Simple way to make the comparison case insensitive is to precede your macro
with

Option Compare Text


So the start of your macro would look like:

Option Compare Text
Option Explicit
Sub Macro4()

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

Thanks Ron......That is simple....I learn something new everytime I read this
forumn. Hope this helps the original op out.

Don

"Ron Rosenfeld" wrote:

On Sat, 1 Mar 2008 17:43:01 -0800, Don wrote:

However, this is case sensitive and I've played with it for some time and
can't get it to be case insensitive,

Simple way to make the comparison case insensitive is to precede your macro
with

Option Compare Text


So the start of your macro would look like:

Option Compare Text
Option Explicit
Sub Macro4()

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

Again my thanks Ron....added your Option, tested the macro...works like a
champ.

Don

"Ron Rosenfeld" wrote:

On Sat, 1 Mar 2008 17:43:01 -0800, Don wrote:

However, this is case sensitive and I've played with it for some time and
can't get it to be case insensitive,

Simple way to make the comparison case insensitive is to precede your macro
with

Option Compare Text


So the start of your macro would look like:

Option Compare Text
Option Explicit
Sub Macro4()

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

On Sat, 1 Mar 2008 19:00:00 -0800, Don wrote:

Again my thanks Ron....added your Option, tested the macro...works like a
champ.

Don

You're welcome. Glad to help.
--ron

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

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

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

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

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

Rocky,

If you're still reading this thread, I think the problem has been resolved.
Dave Peterson pointed out an error in my Dim statements. It only creates an
error when you're dealing with many thousands of rows, that's why I wasn't
catching the error and you were. I only checked down to 7000 rows. But his
fix appears to have fixed the macro so that it will work for you. Below is
the revised code:

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Range
Dim MySearchValue As 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

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

My thanks to Dave Peterson for steering me in the right direction on this,
and explaining what was happening.

Hope this works for you now....

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

Sort of an afterthought but might be applicable to your application....if the
search string that you enter is not the full content of a cell, this macro
won't pick it up. A step would have to be designed to test each cell for the
contents of the search string, i.e......if you entered "ABC" as a search
string and one or more of the cells had "ABC...and some other text or data"
this macro would pass it.

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

Hello,

Can somebody help me with the following?

I'm using Excel (office 2007)

I have to manage pricelists containing each abt.5000 rows
Unfortunately, the information in the cells of the Column C appears in the wrong order

for example, the existing format is:

A B C
BRAND Item BRAND Description ITEM

I would like to invert the content of the cells in the column C into:

A B C
BRAND Item BRAND ITEM Description

I already did a try with a macro clearing partly the text equal to the text found in the colomn A and B, but I haven’t found a way to insert it back into the cells of the column C in the right order
Is there a possibility to have it done with a macro?

Thanks in advance for your help

Hi

I don't find it clear what you desire. Can you give us an example of your
data, and a description in words of what your want.

Post the macro you have got so far, so that we don't have to start from
scratch.

I'm sure it can be done.

Regards,
Per

"Daniel Thuriaux" skrev i meddelelsen
...
Hello,

Can somebody help me with the following?

I'm using Excel (office 2007)

I have to manage pricelists containing each abt.5000 rows
Unfortunately, the information in the cells of the Column C appears in the
wrong order

for example, the existing format is:

A B C
BRAND Item BRAND Description ITEM

I would like to invert the content of the cells in the column C into:

A B C
BRAND Item BRAND ITEM Description

I already did a try with a macro clearing partly the text equal to the
text found in the colomn A and B, but I haven't found a way to insert it
back into the cells of the column C in the right order
Is there a possibility to have it done with a macro?

Thanks in advance for your help

Sub RevWords()
Dim lastRow As Long
Dim i As Long
Dim s As String
Dim sCol As String
Dim arrSplit
Dim rng As Range, cel As Range

sCol = "C" ' << Change to suit

With ActiveSheet
s = .Range(sCol & .Rows.Count).Address
lastRow = .Range(sCol & .Rows.Count).End(xlUp).Row
End With

Set rng = Range(sCol & "1:" & sCol & lastRow)

For Each cel In rng
With cel
If InStr(2, .Value, " ") Then
arrSplit = Split(.Value, " ")

s = ""
For i = UBound(arrSplit) To 0 Step -1
s = s & arrSplit(i)
If i 0 Then s = s & " "
Next

.Value = s
End If
End With
Next

End Sub

Regards,
Peter T

<Daniel Thuriaux wrote in message
...
Hello,

Can somebody help me with the following?

I'm using Excel (office 2007)

I have to manage pricelists containing each abt.5000 rows
Unfortunately, the information in the cells of the Column C appears in the
wrong order

for example, the existing format is:

A B C
BRAND Item BRAND Description ITEM

I would like to invert the content of the cells in the column C into:

A B C
BRAND Item BRAND ITEM Description

I already did a try with a macro clearing partly the text equal to the
text found in the colomn A and B, but I haven’t found a way to insert it
back into the cells of the column C in the right order
Is there a possibility to have it done with a macro?

Thanks in advance for your help

On Sun, 28 Sep 2008 01:48:11 -0700, Daniel Thuriaux wrote:

Hello,

Can somebody help me with the following?

I'm using Excel (office 2007)

I have to manage pricelists containing each abt.5000 rows
Unfortunately, the information in the cells of the Column C appears in the wrong order

for example, the existing format is:

A B C
BRAND Item BRAND Description ITEM

I would like to invert the content of the cells in the column C into:

A B C
BRAND Item BRAND ITEM Description

I already did a try with a macro clearing partly the text equal to the text found in the colomn A and B, but I haven’t found a way to insert it back into the cells of the column C in the right order
Is there a possibility to have it done with a macro?

Thanks in advance for your help

Perhaps this will work. Be sure to read the comments within the Sub:

===========================================
Option Explicit
Sub ChangeOrder()
Dim c As Range
Dim rg As Range
Dim sRes(0 To 2) As String
Dim sDesc As String
Dim i As Long

Set rg = Range("A1:A5000") 'set to range to be processed
' first column only
For Each c In rg
sRes(0) = Trim(c.Value) 'BRAND
sRes(1) = Trim(c.Offset(0, 1).Value) 'Item
sRes(2) = Trim(c.Offset(0, 2).Value) 'original
'remove BRAND
sRes(2) = Replace(sRes(2), sRes(0), "", 1, 1, vbTextCompare)
'check for duplicate of Item value
i = Len(sRes(2)) - Len(Replace(sRes(2), sRes(1), "", , , vbTextCompare))
'replace "last" item value
sRes(2) = Replace(sRes(2), sRes(1), "", 1, i, vbTextCompare)
'column offset set to 3 for debugging. When satisfied, set it to 2
c.Offset(0, 3).Value = Join(sRes)
Next c
End Sub
====================================
--ron