|
I think this is a little problem but then again I am entirely not sure. I'll make a thread out of this if it is more complicated than I think. Basically I have to clean up an Excel file with thousands of cells of data for a cell phone distributor. Here is my process: 1. In my Service Status Column I have to Filter and Delete invalid Statuses. 2. In the Sim Column I have to remove a Character from a string of numbers. 2. In my Service Number, IMEI Number, and SIM Number Column I have to replace blanks with a phrase, replace numbers greater or less than a certain value with a phrase, and replace all numbers that are duplicates with a phrase. What I do is.... By Filtering the Service Status Column I can easily remove the invalid Statuses. And again by Filtering I can mark the Blanks. I insert a separate column and I use the =LEFT(~,~) to output all characters I want from a SIM and not print the Character I would like to be excluded. Then I copy the Values over that and delete the old SIM Column. I insert 3 separate Columns to check the length of my SIM, IEMI, and Service Numbers. Then I filter those that are good which allows me to Flag the improper ones. I use Advance Filter to find the Unique Values of the SIM, IEMI, and Service Numbers and then Color them in a color to distinguish the Duplicates from the Unique. I filter what is not colored (the duplicates) and copy/paste them to another sheet. Then I use the Find and Replace on the copied/pasted numbers to replace all duplicates with the phrase I am suppose to. This whole process takes me about 20-45 minutes to do depending on the size of the spreadsheet and the amount of duplicates that I have to find and replace. My idea was to create a Macro to do the work for me. However, it is not working so well. I am going to retry it a couple of times but maybe you guys have a better idea for me. I have used the web to get me this far but I am struggling at finding a way to reduce my time in this lengthy process. Any suggestions? I really suck at this. >_< Edit: I have been doing more research and figured out that as 1 Macro to complete all these tasks would crash. However, what is working is creating a Macro for each set of Steps and then having a Master Macro run the whole thing. So far its going well. The code is basic but I am no expert. I'll keep you posted in case I have any questions. rekk1ess fucked around with this message at 17:01 on Jul 11, 2009 |
#
¿
Jul 10, 2009 22:07
|
|
|
|
| # ¿ Apr 28, 2024 09:31 |
|
|
I am trying to edit my macro to allow me to fill in the Blank Cells, once filtered, with 05-MM-Blank. I am having issues though. I don't think that when it runs the first cell is blank. Anyway around this. Sub Step_6A() ' ' Step_6A Macro ' Fill Service Number Blanks ' ActiveCell.FormulaR1C1 = "05-MM-Blank" Dim FirstRow As Long FirstRow = [A1].End(xlDown).Row Dim LastRow As Long LastRow = [A65536].End(xlUp).Row Range(Cells(FristRow, 12), Cells(LastRow, 12)).Select Selection.FillDown End Sub If it helps, this is what works: Sub Step_6A() ' ' Step_6A Macro ' Fills Service Number Blanks ' ' ActiveCell.FormulaR1C1 = "05-MM-Blank" Range("L5", "L85").Select Selection.FillDown End Sub However, L5 and L85 might change when I copy a new sheet into this. They might be L6 to L90 for example. I need this to adjust tot hat. Everything else seems to work fine. rekk1ess fucked around with this message at 22:04 on Jul 13, 2009 |
|
#
¿
Jul 13, 2009 14:18
|
|
|
The following somewhat does what I want. Problem is, that it re-labels my 1st Column as 05-MM-Blank. Is there anyway to further this and Offset the FirstRow? Sub Step_6A() ' ' Step_6A Macro ' Fills Service Number Blanks ' ' Dim LastRow As Long LastRow = [A65536].End(xlUp).Row Dim FirstRow As Long FirstRow = [L65536].End(xlUp).Row Range(Cells(FirstRow, 12), Cells(LastRow, 12)).Select ActiveCell.FormulaR1C1 = "05-MM-Blank" Selection.FillDown End Sub
|
|
#
¿
Jul 13, 2009 22:05
|
|