Solved

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

Posted on 2013-01-23
40
507 Views
Last Modified: 2013-02-01
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
0
Comment
Question by:reoroman
  • 20
  • 18
  • 2
40 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 38808914
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38809015
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
 
LVL 50

Expert Comment

by:teylyn
ID: 38809055
Gowflow, are you in the right thread?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38809065
yes this is continuation of previous question !!!!
:)

But for sure you answered the question ... as usuall ... brilliantly !!!
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38809078
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
 

Author Comment

by:reoroman
ID: 38809283
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
 

Author Comment

by:reoroman
ID: 38809287
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38809318
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
 

Author Comment

by:reoroman
ID: 38809673
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
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 38809765
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
 

Author Comment

by:reoroman
ID: 38809925
please check error2 .. i ran the code you sent me...
error2.JPG
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38810006
press on debug and tell me what line is yellow
gowflow
0
 

Author Comment

by:reoroman
ID: 38810094
Set WSTR = Sheets("Training")
0
 

Author Comment

by:reoroman
ID: 38810105
ok, i got it... i need to rename the second sheet to training
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38810107
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38810123
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
 

Author Closing Comment

by:reoroman
ID: 38810124
super super super... i like your work body, many thanks..you make life easy
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38810148
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
 

Author Comment

by:reoroman
ID: 38810162
ok.. I am doing that now
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38810168
ok let me know feedback. The nice thing about this is that you can keep changing the class with no fear !!
gowflow
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:reoroman
ID: 38810189
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38810199
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38810255
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
 

Author Comment

by:reoroman
ID: 38810806
it works fine with renumbering. but please check the produce output. in the previous version it was working fine. Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38811335
sorry do not understand your comment. You say it is working fine in both ??
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38812508
so do you still have a problem ?
gowflow
0
 

Author Comment

by:reoroman
ID: 38812899
i still have problem with produce output after adding your last code. check the attaches pictures.
compile-error.JPG
compile-error-2.JPG
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38813354
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
 

Author Comment

by:reoroman
ID: 38813421
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38813514
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
 

Author Comment

by:reoroman
ID: 38814099
This is excellent and decent work. I am satisfied. Thanks you.

respect
Roman
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38814872
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
 

Author Comment

by:reoroman
ID: 38814980
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38815097
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
 

Author Comment

by:reoroman
ID: 38815169
okay... sure ... Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38815367
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
 

Author Comment

by:reoroman
ID: 38817185
Yes. Correct. That is it.
0
 

Author Comment

by:reoroman
ID: 38843543
Dear gowflow, i want to publish new question are you available?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38843912
yes pls go ahead
gowflow
0
 

Author Comment

by:reoroman
ID: 38844191
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now