Excel 2003 Macro to add text to front of data in cell

D

Don

OK Rocky.....MSN let me back on finally....here's what I came up
with...Tested it some but would advise you to test and re-test it...use
copies of your WB just in case.

This new code should do what you requested, including some things that will
make it more OP friendly. It will also keep track of where you stopped. The
MsgBox's and InputBox's are pretty self-explanatory.

First: Create a new WS and name it "WrkSht" without the quotes.

Second: On this new WS:
In Cell A1 enter "1"
In Cell A2 enter "A"
In Cell A3 enter "ABC"
In Cell A4 enter "47-"

All of the above without the quotes.

Input the following code into a new Module:

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, LstRowUsed As Long
Dim Response, RowDir, PrevEnt As String

On Error Resume Next
i = 0
PrevEnt = _
MsgBox("DO YOU WANT TO USE PREVIOUS COLUMN USED: COLUMN: " _
& Sheets("WrkSht").Range("A2"), vbYesNoCancel + vbInformation)
If PrevEnt = vbCancel Then Exit Sub
If PrevEnt = vbYes Then
MyCol = Sheets("WrkSht").Range("A2")
End If
If PrevEnt = vbNo Then _
MyCol = InputBox("WHAT COLUMN DO YOU WANT TO SEARCH IN?")
Sheets("WrkSht").Range("A2") = MyCol
If MyCol = "" Then
Exit Sub
End If
With ActiveSheet
LastRow = .Cells(.Rows.Count, MyCol).End(xlUp).Row
End With
PrevEnt = _
MsgBox("WOULD YOU LIKE TO USE THE PREVIOUS SEARCH STRING USED: " _
& Sheets("wrksht").Range("A3"), vbYesNoCancel)
If PrevEnt = vbCancel Then
Exit Sub
End If
If PrevEnt = vbNo Then
MyString = InputBox("WHAT SEARCHSTRING DO YOU WANT TO USE?")
Sheets("WrkSht").Range("A3") = MyString
If MyString = "" Then Exit Sub
Else
MyString = Sheets("WrkSht").Range("A3")
End If
PrevEnt = MsgBox _
("WOULD YOU LIKE TO USE THE PREVIOUS STRING AS THE ADD FACTOR: " _
& Sheets("wrksht").Range("A4"), vbYesNoCancel)
If PrevEnt = vbCancel Then
Exit Sub
End If
If PrevEnt = vbNo Then
ReplaceWith = InputBox("WHAT WOULD YOU LIKE TO ADD?")
Sheets("WrkSht").Range("A4") = ReplaceWith
If MyString = "" Then Exit Sub
Else
ReplaceWith = Sheets("WrkSht").Range("A4")
End If
RowDir = _
MsgBox("WOULD YOU LIKE TO START WHERE YOU LEFT OFF?", _
vbYesNoCancel + vbInformation)
If RowDir = vbCancel Then Exit Sub
If RowDir = vbNo Then
i = InputBox("WHICH ROW WOULD YOU LIKE TO START ON?")
Sheets("WrkSht").Range("A1") = i
If i > 0 Then
GoTo Cont
Exit Sub
End If
Else
i = Sheets("WrkSht").Range("A1")
End If
Cont:
Set Rge = Range(MyCol & i & ":" & MyCol & LastRow)
For Each c In Rge
MySearchValue = InStr(c, MyString)
If (Not IsNull(MySearchValue)) And (MySearchValue > 0) Then
Range(MyCol & i).Select
Response = MsgBox("Is This One To Addend?", vbYesNoCancel _
+ vbInformation)
If Response = vbYes Then GoTo Add

If Response = vbCancel Then
Exit Sub
End If
If Response = vbNo Then GoTo Rep
End If
Rep:
i = i + 1
Sheets("WrkSht").Range("A1") = i
Next c
Exit Sub
Add:
Range(MyCol & i).Offset(0, 1).Value = ReplaceWith & _
Range(MyCol & i).Offset(0, 1).Value
GoTo Rep

End Sub

Make sure you've selected the Sheet that has the data you want to search.
(didn't put this in code as I wasn't sure what the name of your sheet was)

This could probably get cleaned up a bit, but I think it'll do what you've
asked for.

Let me know......

Don
 
R

Rocky Lane

Hi Don,

It works like a charm. I changed your code to use the name of my worksheet
and it worked perfectly. The only improvements I can think of if possible,
without a lot of work are:

1) the column I am searching in contains many cells that have data
containing RCA (i.e. RCACP, Arcade, etc.) so it stops at ever one of those. I
would like it to stop at cells that contain RCA only. With over 44,000 rows,
clicking no for non-RCA only cells is a drag because there are more of them
than just RCA cells.

2) an extra InputBox asking for the worksheet name would be nice as I have
several with different names I need to run this macro on.

Thank you very much for all you have done so far.

Rocky
 
D

Don

Here you go.....hope this works..lol


Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Long
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, LstRowUsed As Long
Dim Response, RowDir, PrevEnt, WSheet As String

On Error Resume Next
i = 0

WSheet = InputBox("NAME OF THE WORKSHEET YOU WISH TO WORK ON!")
Sheets(WSheet).Select
PrevEnt = _
MsgBox("DO YOU WANT TO USE PREVIOUS COLUMN USED: COLUMN: " _
& Sheets("WrkSht").Range("A2"), vbYesNoCancel + vbInformation)
If PrevEnt = vbCancel Then Exit Sub
If PrevEnt = vbYes Then
MyCol = Sheets("WrkSht").Range("A2")
End If
If PrevEnt = vbNo Then _
MyCol = InputBox("WHAT COLUMN DO YOU WANT TO SEARCH IN?")
Sheets("WrkSht").Range("A2") = MyCol
If MyCol = "" Then
Exit Sub
End If
With ActiveSheet
LastRow = .Cells(.Rows.Count, MyCol).End(xlUp).Row
End With
PrevEnt = _
MsgBox("WOULD YOU LIKE TO USE THE PREVIOUS SEARCH STRING USED: " _
& Sheets("wrksht").Range("A3"), vbYesNoCancel)
If PrevEnt = vbCancel Then
Exit Sub
End If
If PrevEnt = vbNo Then
MyString = InputBox("WHAT SEARCHSTRING DO YOU WANT TO USE?")
Sheets("WrkSht").Range("A3") = MyString
If MyString = "" Then Exit Sub
Else
MyString = Sheets("WrkSht").Range("A3")
End If
PrevEnt = MsgBox _
("WOULD YOU LIKE TO USE THE PREVIOUS STRING AS THE ADD FACTOR: " _
& Sheets("wrksht").Range("A4"), vbYesNoCancel)
If PrevEnt = vbCancel Then
Exit Sub
End If
If PrevEnt = vbNo Then
ReplaceWith = InputBox("WHAT WOULD YOU LIKE TO ADD?")
Sheets("WrkSht").Range("A4") = ReplaceWith
If MyString = "" Then Exit Sub
Else
ReplaceWith = Sheets("WrkSht").Range("A4")
End If
RowDir = _
MsgBox("WOULD YOU LIKE TO START WHERE YOU LEFT OFF?", _
vbYesNoCancel + vbInformation)
If RowDir = vbCancel Then Exit Sub
If RowDir = vbNo Then
i = InputBox("WHICH ROW WOULD YOU LIKE TO START ON?")
Sheets("WrkSht").Range("A1") = i
If i > 0 Then
GoTo Cont
Exit Sub
End If
Else
i = Sheets("WrkSht").Range("A1")
End If
Cont:
Set Rge = Range(MyCol & i & ":" & MyCol & LastRow)
For c = i To LastRow
If Range(MyCol & i) = MyString Then
Range(MyCol & i).Select
Response = MsgBox("Is This One To Addend?", vbYesNoCancel _
+ vbInformation)
If Response = vbYes Then GoTo Add
If Response = vbCancel Then
Exit Sub
End If
If Response = vbNo Then GoTo Rep
End If
Rep:
i = i + 1
Sheets("WrkSht").Range("A1") = i
Next c
Exit Sub
Add:
Range(MyCol & i).Offset(0, 1).Value = ReplaceWith & _
Range(MyCol & i).Offset(0, 1).Value
GoTo Rep

End Sub


Didn't test it, but I'm pretty sure it'll do what you want. HTH

Don
 
R

Rocky Lane

Hi Don,

Copied your latest code and when it asked for the name of the worksheet, the
macro stopped after I selected OK. I tried to see if I could debug the line
involved but nothing I did made it go past that first question.

I took the liberty to modified your code as below. It was destroying the
entries in A1 to A4 so I changed it to an empty column L. I chnaged the
InputBox questions some what too.

==================================

Option Compare Text
Option Explicit

Sub FindReplace()

Dim c As Long
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, LstRowUsed As Long
Dim Response, RowDir, PrevEnt, WSheet As String

On Error Resume Next
i = 0

WSheet = InputBox("Name of Worksheet")
Sheets(WSheet).Select
PrevEnt = _
MsgBox("Do you want to use previous column used: Column: " _
& Sheets("WSheet").Range("L2"), vbYesNoCancel + vbInformation)
If PrevEnt = vbCancel Then Exit Sub
If PrevEnt = vbYes Then
MyCol = Sheets("WSheet").Range("L2")
End If
If PrevEnt = vbNo Then _
MyCol = InputBox("What column do you want to search in?")
Sheets("WSheet").Range("L2") = MyCol
If MyCol = "" Then
Exit Sub
End If
With ActiveSheet
LastRow = .Cells(.Rows.Count, MyCol).End(xlUp).Row
End With
PrevEnt = _
MsgBox("Do you want to use previous search string: String used: " _
& Sheets("WSheet").Range("L3"), vbYesNoCancel)
If PrevEnt = vbCancel Then
Exit Sub
End If
If PrevEnt = vbNo Then
MyString = InputBox("What string do you want to search for?")
Sheets("WSheet").Range("L3") = MyString
If MyString = "" Then Exit Sub
Else
MyString = Sheets("WSheet").Range("L3")
End If
PrevEnt = MsgBox _
("Do you want to use previous pre-fix: " _
& Sheets("WSheet").Range("L4"), vbYesNoCancel)
If PrevEnt = vbCancel Then
Exit Sub
End If
If PrevEnt = vbNo Then
ReplaceWith = InputBox("What pre-fix would you like to add?")
Sheets("WSheet").Range("L4") = ReplaceWith
If MyString = "" Then Exit Sub
Else
ReplaceWith = Sheets("WSheet").Range("L4")
End If
RowDir = _
MsgBox("Do you want to start where you left off?", _
vbYesNoCancel + vbInformation)
If RowDir = vbCancel Then Exit Sub
If RowDir = vbNo Then
i = InputBox("Which row do you want to start in?")
Sheets("WSheet").Range("L1") = i
If i > 0 Then
GoTo Cont
Exit Sub
End If
Else
i = Sheets("WSheet").Range("L1")
End If
Cont:
Set Rge = Range(MyCol & i & ":" & MyCol & LastRow)
For c = i To LastRow
If Range(MyCol & i) = MyString Then
Range(MyCol & i).Select
Response = MsgBox("Pre-fix this cell?", vbYesNoCancel _
+ vbInformation)
If Response = vbYes Then GoTo Add
If Response = vbCancel Then
Exit Sub
End If
If Response = vbNo Then GoTo Rep
End If
Rep:
i = i + 1
Sheets("WSheet").Range("L1") = i
Next c
Exit Sub
Add:
Range(MyCol & i).Offset(0, 1).Value = ReplaceWith & _
Range(MyCol & i).Offset(0, 1).Value
GoTo Rep

End Sub

Rocky
 
D

Don

Rocky,

Works fine here....you do have to enter a correct WS name in that first
inputbox, then click ok....if you don't do that it'll stay on the same page
that was already selected. If you enter an incorrect WS name the macro
shouldn't stop...it doesn't here.

The cells I had you enter in on WrkSht are designed to change as you enter
new requirements into the input boxes.....they don't change if you select the
option to use the previous data. The only exception to this is A1...that has
an automatic counter built in that keeps changing as you progress down the
Column. It will and should change. It can and should be reset after
finishing a Column. You don't have to change it on WrkSht, just change to
the row number you wish to start with...normally on a new Column that would
be 1.

The only time this arrangement will present problems is if you start working
a second Column before finishing the first one. Then you'll have to change,
in the inputbox, the Row you want to start on.

It's working fine here as per the last code I posted....hope this explains
the inner workings a bit better,

Don
 
R

Rocky Lane

Hi Don,

Well I think I've wasted enough of your time on this thing,. I took out the
worksheet name question and hard-coded the name of the worksheet and it works
again. And since I found out that you have to create the macro in each
worksheet anyways, it's easier to just copy the code from one sheet and
change the hard-coded name.

I appreciate all your effort on this. Maybe someday I'll have another need
for a macro and I'll put a question up on the discussion group.

Rocky
 
D

Don

Thanks for the feedback Rocky. And it wasn't a waste of time, I also learned
quite a few things as I played with this code, and that's what I'm here for,
to learn. Hope it works for you and makes your task a little easier.

Have a good day,

Don
 
D

Daniel Thuriaux

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
 
P

Per Jessen

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
news:[email protected]...
 
P

Peter T

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert to Excel Macro 3
Excel 2013 Merge Data 2
Macro for checking data in a cell against another cell andcopying/pasting data 8
Convert to Excel Macro 2
Help needed to writing a macro to put the year in column B of a tableusing Table nomenclature & the 7
Code to do a simple clean of one column by copying and pasting(finding and replacing) phrases in adj 4
conditional if statement and macro 6
Macro that will insert a simple text string in top cell of text file 1

Top