EXCEL 2010, Find & Replace data in a specific column/Row?

hello, i need a macro that helps me to find and replace and data in specific column/Row.
suppose i have the following data
     A  B  C  D
1.  1  2  3  4
2.  4  3  2  1
3.  1  2  3  4
4.  3  2  1  4
5.  2  1  3  4

so if i want to replace all numbers 4 in column D with number 5 the macro gives me the ability to do it without replacing any number 4 in different rows such as in column A2.
also i need it to report if any rows are repeated such as row number 1 and 3.

Thanks
reoromanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

If you fire up the macro recorder, select column D and use find and replace, you will see what code is required. Something along the line of

    Columns("D:D").Replace What:="4", Replacement:="5", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Open in new window


>> also i need it to report if any rows are repeated such as row number 1 and 3.

This is not clear. Please provide more detail. How, where, what?

cheers, teylyn
0
gowflowCommented:
ok here it is

1) Insert the following macro in your module1 of the workbook.

Sub ClassRenumber()
Dim WSTR As Worksheet
Dim MaxRow As Long, MaxCol As Long, I As Long
Dim RngFilter As Range

Set WSTR = Sheets("Training")
MaxRow = WSTR.UsedRange.Rows.Count
MaxCol = WSTR.UsedRange.Columns.Count

Application.EnableEvents = False

For I = 2 To MaxRow
    WSTR.Cells(I, "AP").Value = WSTR.Cells(I, "AP").Value - 1
Next I

Application.EnableEvents = True

MsgBox ("Renumbering Class completed successfully for " & MaxRow - 1 & " records.")
End Sub

Open in new window



2) SAVE the workbook
3) Replace your sheet 'Main' by the one attached here rename you current Main to Main old then get this sheet into your workbook and then you can scrap the Main old.
4) SAVE and exit the workbook
5) Open it and you will see a new button in sheet Main called Renumber Class activate it ONCE !!!! it will renumber your classes by -1 !!! once done it will let you know.
6) If you activate this an other time you will renumber the existing classes by -1 also !!! I suggest you remove the button after you do it once so you don't inadvertantly renumber.

Let me know
gowflow
Main.xlsm
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Gowflow, are you in the right thread?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

gowflowCommented:
yes this is continuation of previous question !!!!
:)

But for sure you answered the question ... as usuall ... brilliantly !!!
gowflow
0
gowflowCommented:
reoroman what is this ?



also i need it to report if any rows are repeated such as row number 1 and 3.


You have a file that has 43 columns and 500000 records !!! how do you expect to have the duplicates showns ??? Are we in the same question you need help with ???
gowflow
0
reoromanAuthor Commented:
oh.. sorry for late reply.. i was out...  you said

3) Replace your sheet 'Main' by the one attached here rename you current Main to Main old then get this sheet into your workbook and then you can scrap the Main old

i rename it main-old. but i cant load the other sheet you sent me beside it. how can i do it.?

thanks
0
reoromanAuthor Commented:
also i need it to report if any rows are repeated such as row number 1 and 3.

sorry this is something else... what i meant by it.. is there a possible way to remove a redundant record ... say exact same rows ... forget it.. no worries. this is later on i will add it as new question
0
gowflowCommented:
yes as your data is huge there is a betterway to remove duplicates but I prefer to do a backup first then run the macro that will remove duplicates and then save it onto a new workbook !!! last thing you want is for some code to go mess arround in the same live data that you have. It will be too dangerous.

Sure better leave this as seperate issue.

Now for this you do the following you open Main.xlsm and also open your current workbook
you postion yourself in Main (on hte ribbon there is View menu click on it then the button before the last to the right is called Switch Windows) you click on it and choose Main.xlsm

Right click on the tag of the sheet Main at the bottom and choose Move or Copy a window will open Tick on Create a Copy then on top dropdown click on it and you will see both workbooks Main and yours. Click on yours and press OK.

It will make a copy of Main into your workbook. DO THIS after you rename Main to Main old in your workbook.

Now you can safely delete sheet Main old (right click on it choose delete and press ok)

SAVE the workbook
then make a copy of the new workbook as a backup and then run the macro renumber

check it and let me know
gowflow
0
reoromanAuthor Commented:
hi, thanks for everything... there are an error... please check the attached picture.

i did as you said.. i clicked on the renumber  and i got the error..

can you please dot it and see by yourself.. i also attached the Training-sample work.

also please make another function which allow me to add +1

so i can have both option, -1 and +1

thanks
error1.JPG
Training-sample.xlsm
0
gowflowCommented:
It seems you did not copy the code to module1 in any case here a full workbook with sample data in traning sample sheet. Just keep this workbook and move to it your real data Training and you can delete the sheet Traning Sample.

Test it and let me know. It allow you to chose any value +X or -X it will nenumber based on that value
Example if you current are 1 to 5 and you say -3 you will get from -2 to 2 etc...

gowflow
training-RenumberCalss-sample.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
reoromanAuthor Commented:
please check error2 .. i ran the code you sent me...
error2.JPG
0
gowflowCommented:
press on debug and tell me what line is yellow
gowflow
0
reoromanAuthor Commented:
Set WSTR = Sheets("Training")
0
reoromanAuthor Commented:
ok, i got it... i need to rename the second sheet to training
0
gowflowCommented:
Well for sure it will give you error !!!!! the code is fine but I included a sheet called
Traning Sample !!!!!
you need to remove this sheeet and put instead your sheet (real 500000 data +) and Call it Exactly
Training

then .. run the macro !!!!
Capiche ???


Just saw your comment !!! yes bad student !!!
gowflow
0
gowflowCommented:
run it if it is ok then I have a better code for renumbering it will go much faster than the one I posted !!!
let me know
gowflow
0
reoromanAuthor Commented:
super super super... i like your work body, many thanks..you make life easy
0
gowflowCommented:
did you read my last comment  ? !!! I have a faster macro for Renumbering

here it is replace in Module1 Sub ClassRenumber by this one

Sub ClassRenumber()
Dim WSTR As Worksheet
Dim MaxRow As Long, MaxCol As Long, I As Long
Dim RngFilter As Range
Dim ClassAdj As Integer
Dim Start

Start = Timer
Set WSTR = Sheets("Training")
MaxRow = WSTR.UsedRange.Rows.Count
MaxCol = WSTR.UsedRange.Columns.Count

Do
    On Error Resume Next
    ClassAdj = InputBox("Please Insert Class Adjustment value +X or -X where X is the new Class", "Class Adjustment Value", 0)
Loop Until Err = 0

Application.EnableEvents = False
Application.ScreenUpdating = False

'---> Old way
'For I = 2 To MaxRow
'    WSTR.Cells(I, "AP").Value = WSTR.Cells(I, "AP").Value + ClassAdj
'Next I

'---> New Way
WSTR.Range("AR2:AR" & MaxRow).Formula = "=$AP2 +" & ClassAdj
WSTR.Range("AR2:AR" & MaxRow).Copy
WSTR.Range("AP2:AP" & MaxRow).PasteSpecial xlPasteValues
WSTR.Range("AR2:AR" & MaxRow).ClearContents

Application.EnableEvents = True
Application.ScreenUpdating = True

MsgBox ("Renumbering Class completed successfully for " & MaxRow - 1 & " records in " & Format(Timer - Start, "#,###") & " seconds.")
End Sub

Open in new window


Enjoy tks for the nice appreciation. Pls feel free to post here any link you may need help with.
gowflow
0
reoromanAuthor Commented:
ok.. I am doing that now
0
gowflowCommented:
ok let me know feedback. The nice thing about this is that you can keep changing the class with no fear !!
gowflow
0
reoromanAuthor Commented:
there two bugs...
the first one is when you click on renumber, and then you change your thought and do not want to enter any value, the screen will never go away, no cancel order...

the second it the produce output now does not work.. please check it and send me the new version. Thanks
0
gowflowCommented:
for the first if you change your mind on the first question ... yes you press cancel then it wont activate but if you change your mind on the number your correct will fix that but beside this how can the values are wrong are you sure ????
I tested it with your entire sheet and it is ok.
gowflow
0
gowflowCommented:
Here is the new code.

Sub ClassRenumber()
Dim WSTR As Worksheet
Dim MaxRow As Long, MaxCol As Long, I As Long
Dim RngFilter As Range
Dim ClassAdj As Integer
Dim Start

Start = Timer
Set WSTR = Sheets("Training")
MaxRow = WSTR.UsedRange.Rows.Count
MaxCol = WSTR.UsedRange.Columns.Count

Do
    On Error Resume Next
    ClassAdj = InputBox("Please Insert Class Adjustment value +X or -X where X is the new Class", "Class Adjustment Value", 0)
Loop Until Err = 0


If MsgBox("Final check, Are you ready to update Class in Col AP that will modify existing Class by " & ClassAdj & " ?", vbQuestion + vbYesNo, "Class Renumbering") = vbYes Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    '---> Old way
    'For I = 2 To MaxRow
    '    WSTR.Cells(I, "AP").Value = WSTR.Cells(I, "AP").Value + ClassAdj
    'Next I
    
    '---> New Way
    WSTR.Range("BA2:BA" & MaxRow).Formula = "=$AP2 +" & ClassAdj
    WSTR.Range("BA2:BA" & MaxRow).Copy
    WSTR.Range("AP2:AP" & MaxRow).PasteSpecial xlPasteValues
    WSTR.Range("BA2:BA" & MaxRow).ClearContents
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    MsgBox ("Renumbering Class completed successfully for " & MaxRow - 1 & " records in " & Format(Timer - Start, "#,###") & " seconds.")
Else
    MsgBox ("Renumbering Cancelled by User Request.")
End If
End Sub

Open in new window


gowflow
0
reoromanAuthor Commented:
it works fine with renumbering. but please check the produce output. in the previous version it was working fine. Thanks
0
gowflowCommented:
sorry do not understand your comment. You say it is working fine in both ??
gowflow
0
gowflowCommented:
so do you still have a problem ?
gowflow
0
reoromanAuthor Commented:
i still have problem with produce output after adding your last code. check the attaches pictures.
compile-error.JPG
compile-error-2.JPG
0
gowflowCommented:
ok please follow this:
DO keep both workbooks opened. Once you copied Main to your workbook. then delete sheet Main old from your workbook save your workbook and close ALL workbooks.

Now you can open your training workbook in which there is sheet Main and sheet Training ONLY and thenn run the macro !!!!

What you are doing is running the macro while all workbooks are opened and as there is macro there it is getting confusing.

Pls revert
gowflow
0
reoromanAuthor Commented:
here is the main and the training file, please do it for me and send me the last file.
i did it as you said but it does not work with me. sorry
Main.xlsm
Training.xlsm
0
gowflowCommented:
It is clear you are mixing versions !!!! when you save on your hard disk a file I post put a date so you dont get confused with same names. You posted an older file that is missing a complete routine reason why you are having these problems.

DO THE FOLLOWING
Take the file I posted here and ONLY move your real training sheet in there. Full stop. save it close it then run it.

gowflow
training-RenumberCalss-Jan24-201.xlsm
0
reoromanAuthor Commented:
This is excellent and decent work. I am satisfied. Thanks you.

respect
Roman
0
gowflowCommented:
Your welcome. Pls do not hesitate to let me know in here if you need help in any other question by posting it here.
gowflow
0
reoromanAuthor Commented:
sure thing. I will let you know about my data reduction issue in next question which will be after a week as i will be a way for that time. regards. Roman
0
gowflowCommented:
Your welcome anytime. I will keep this question monitored and just give me a buzz when your ready and before you post the new question.
Rgds/gowflow
0
reoromanAuthor Commented:
okay... sure ... Thanks
0
gowflowCommented:
meantime .. re the duplicates just for me to look at it you wanted to remove all identical rows in training ? like compare all columns from A to AP and if data is the same then remove and keep 1  ? is that what you wanted or something else ? I know it is not for now but just to look into the right direction.
gowflow
0
reoromanAuthor Commented:
Yes. Correct. That is it.
0
reoromanAuthor Commented:
Dear gowflow, i want to publish new question are you available?
0
gowflowCommented:
yes pls go ahead
gowflow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.