1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

visual basic excel 2010 need help

Discussion in 'Business Applications' started by beakerbird, Dec 20, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. beakerbird

    beakerbird Thread Starter

    Joined:
    Dec 20, 2011
    Messages:
    4
    I have 2 sheets in the workbook. The first sheet has all the 9 digit social security numbers, all unique, no duplicates. The 2nd sheet has all these same 9 digit numbers, many are duplicates because they show up on each month that they were worked on. This 2nd sheet has many duplicate 9 digit numbers, some showing up once each month or several times each month and so there are months showing chronologically, eg. Jan 2010, Feb 2010 to Nov 2011 with duplicate 9 digit numbers in each month derived from the 1st sheet which has unique 9 digit numbers, no duplicates.
    "So what I have been doing so far is using control find all and replace all one 9 digit number at a time and there are thousands of these.'
    I enter each 9 digit number on the 1st sheet and trying to "control find all" in 2nd sheet to find the duplicates, then I "control replace all" in the same 2nd sheet with the same color, eg. purple to show that they are duplicates. I have to do this for each 9 digit number from the 1st sheet and find it in the 2nd sheet with the duplicates. I have thousands of these 9 digit numbers from the 1st sheet that I have to find duplicates in the 2nd sheet and to show that they are duplicates in the 2nd sheet, I color them with one color, eg. purple. I can replace all the duplicates with a format, eg. color to show that they are duplicates. I noticed when I enter "control find all" a specific set of numbers, eg. 123456789A, and the numbers that I am searching for has 123456789, it will find it. All that I am looking for most important of all is the exact numbers must match, so if I am entering 123456789A, I want to find 123456789 and it does not matter if it has a suffix or prefix, but the reason that I am entering 123456789A is because I am copying from the 1st sheet which has these unique (non duplicate) 9 digit numbers with prefixes and suffixes already there, therefore, I am trying to save time by copying and pasting into "control find all" exactly from the 1st sheet with the prefixes and suffixes already there in each cell.

    Someone created the following visual basic code for my Excel 2010 and I copied it into the VBA editor in Excel and ran it, but I was not too sure if I was doing it right because I saw a hour glass and the whole worksheet became blank, like the code was working or my computer was too slow to handle this. Please review the below to see if it is correct and describe in steps how to paste it and run it. Thank you for your assistance.


    The following code will read down through Sheet 1 Column A and search Sheet 2 for each value.
    If it finds a match, or a partial match, it will fill the cell with ColorIndex # 39, which is Purple in Excel 2003.
    Sub ColorMePurple()'Determine last row with data in Sheet 1 Column A lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row'Loop through Sheet 1 Column A For nxtRw = 1 To lastRw'Look for matching value on Sheet 2'If found, color the cell purple With Sheets(2).Cells Set c = .Find(Sheets(1).Range("A" & nxtRw), lookat:=xlPart) If Not c Is Nothing Then firstAddress = c.Address Do c.Interior.ColorIndex = 39 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With NextEnd Sub
    Tech Support Guy System Info Utility version 1.0.0.2
    OS Version: Microsoft Windows XP Professional, Service Pack 3, 32 bit
    Processor: Intel Pentium III Xeon processor, x86 Family 6 Model 23 Stepping 10
    Processor Count: 2
    RAM: 1977 Mb
    Graphics Card: ConfigMgr Remote Control Driver, 1024 Mb
    Hard Drives: C: Total - 152617 MB, Free - 133212 MB;
    Motherboard: Hewlett-Packard, 3646h
    Antivirus: Symantec Endpoint Protection, Updated: Yes, On-Demand Scanner: Enabled
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, welcome to the forum.

    It's nice that you paste your code but it's unreadable.
    You should use the [ code ] to start, paste the code as it is and then [ / code ] to close
    (no spaces inside the [] brackets. That way you het a textbox with the code (see below)

    Code:
     here the code
    etc etc
    
    Another thing, did you try to record the action you were doing manually? You will also get a macro and then you van compare it to the code you have and see what's going wrong.

    You could search for the securitynumber minus the suffix and if I understood your posting this will return all the values

    Maybe a short sample with just the numbers and will help
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I checked the code and I have it working in the attached sample

    the vab code thers is
    Code:
    Attribute VB_Name = "beakerbirdModule"
    Option Explicit
    
    Sub ColorMePurple()
    'Determine last row with data in Sheet 1 Column A
    Dim lastRw As Long, nxtRw As Long, c As Range, firstAddress As String
    Dim mySearch As Variant
    lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    
    'Loop through Sheet 1 Column A
    For nxtRw = 1 To lastRw
        'Look for matching value on Sheet 2 If found, color the cell purple
        ' mysearch contains the number without the suffix, if the suffix is longer than two characters
        ' then you will have to forsse in a test to do this.
        ' You do not need the suffix in sheet 1 to make them unique but that is our option.
        ' If NO suffix is used then you could use the line below insted of the left(......,....)
        ' mySearch = Sheets(1).Range("A" & nxtRw).Value
        mySearch = Left(Sheets(1).Range("A" & nxtRw).Value, Len(Sheets(1).Range("A" & nxtRw).Value) - 1)
        
        With Sheets(2).Range("A:A")
            Set c = .Find(mySearch, lookat:=xlPart)
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    c.Interior.ColorIndex = 39
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        End With
    Next
    End Sub
    
    
    You will probably have to edit ranges of sheets in your execl sheet but this one works.

    I removed one number in the uniques to show non colored cells
     

    Attached Files:

  4. beakerbird

    beakerbird Thread Starter

    Joined:
    Dec 20, 2011
    Messages:
    4
    Thank you for the code.

    I tried running it and it looked like my computer may not be able to handle this code because I get a blank screen on the excel worksheet where the data was located and an error message later that the program was not responding and had to shut it down. On sheet 1, I have at least 1000 rows and sheet 2 at least 30,000 rows. I had to "control find all" and then "replace all" with the color purple manually and leave one 9 digit number the original color it had, eg. blue and I tried to record it as a macro, but it did not work all the way.

    I have another project which is very overwhelming and maybe I can try this to see if a new code can be set up to reduce the repetitive tasks to a minimum.

    What I have to do is search for a 9 digit number, it may have a suffix or prefix, does not matter if it has a suffix or prefix attached, as long as the number matches exactly and color it blue or leave as is no fill if it was originally a no fill color, one of them and the rest of the duplicates, the color yellow of the same 9 digit number.

    I have 30,000 rows to work from and what I have been doing manually is as I go down each row, copy the 9 digit number with suffix or prefix, paste it into "control find all" and then "replace all" with yellow, then go back and color one set of numbers with a blue color if it had blue to start with or if it had no fill (9 digit number was not worked on), then I have to leave it with no color.

    The reasoning behind this coloring is to find out which 9 digit number was done (colored), and not done (no fill in the cell with the 9 digit number) and then later, I will get rid of the duplicates (in yellow only) manually rows at a time. Can this be set up as a VBA code?
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    You'll have to post something to work on, I can't tell you like this
     
  6. beakerbird

    beakerbird Thread Starter

    Joined:
    Dec 20, 2011
    Messages:
    4
    please see the attached example
     

    Attached Files:

  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    You can probalbly do all this with VBA, you can ceheck what interior color is used and do the actions based on that.

    Your explanation in the test sheet you sent is very long but does not tell me anything.

    I need a simple step by step;

    You just show numbersw tih SUFFIXES and that's it

    Just simple:

    if green then check ... and do this
    if yellow then ....
    if other then ....

    The second sheet which you have not inckued, you say that it's more than one column,

    So be more explicit

    You know and understand what you have but it's very difficult to explain and have somebody else that has no idea of what you are doing to understand that.

    So a real-life sample with fictiious data will help.
    It would be a waste of time to do something and then when you get it it's not what you expected.

    My code works for the sample I snet you, maybe you can use it to work it out, you cannot just put the code in your sheet and expect it to work, I did not even know in which columns your data is.
     
  8. beakerbird

    beakerbird Thread Starter

    Joined:
    Dec 20, 2011
    Messages:
    4
    I have attached another sample,

    From sheet 1, I check the blue colored cells which means they were worked on, I check to see if there is a matching 9 digit number on sheet 2.

    If there is a match, I color it purple to show it is a duplicate and it was done.
    However, if the 9 digit numbered cell has a non filled color on sheet 1, then the duplicate found on sheet 2, will be colored green to show it was a duplicate and it was not worked on.

    In order to make it even more simpler, blue colored cells which has the 9 digit number in sheet 1 will be colored the same color blue for the duplicates in sheet 2.

    If there are 9 digit numbers in sheet 2 which are not shown on sheet 1, one of that cell must be left the same color and the rest yellow to show duplicates of that 9 digit number in sheet 2.

    After I finish with sheet 2, then I delete the yellow duplicates, but first, I have to show the results to my supervisor first, then I can delete all the yellows. The blues mean they were worked on, white means new and not worked on, green means they were found non filled colored cells on sheet 1 and needs to be worked on. Yellows mean duplicates of the 9 digit numbers in sheet 2, regardless of color.

    The 9 digit numbers, regardless of prefixes or suffixes attached do not have to appear on the same column for both sheet 1 and sheet 2 because some of these columns were merged by so many different people working on them, it would be difficult to have the sheets look alike with the same column for the 9 digit numbers.
     

    Attached Files:

  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1032073

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice