Excel 2010 Macro Needed for Search, Compare & Change

**BlueMoonNJ** · 05-04-2012, 04:08 PM

[reposting with clearer title]

Is there a way to create a macro that will do the following (I'm stating it as a person would do it):

- look down column A and find any two rows that match in column A only (if other columns differ across the rows that's okay)
Say it finds that row 10 and row 11 in column A match, then
- look across these two matching rows to column J and determine if row 10/column J differs from row 11/column J
- and if yes, they do differ, change row 10/column J to the contents of row 11/column J
- lastly, after making that change, continue on down column A to find additional changes needed until the whole spreadsheet is checked

If this is possible, please advise and maybe even provide the instructions or formula to use?

Thanks,
Cindi

**Pichingualas** · 05-04-2012, 04:16 PM

Would you mind if I sorted your sheet alphabetically by column A? I mean, is the order of your records important or can it be changed? Sorting them would make the macro run faster because it would only need to compare with the next row...

**bstubbs** · 05-04-2012, 04:20 PM

If sort is important, use a blank column and range fill with numbers. Then upon completion, you've got the info you need to restore the sort.

**bstubbs** · 05-04-2012, 04:34 PM

As I understand what you're asking, I've done that type of thing before. I added a column to the sheet that I could empty and then use as a marker that I had already processed the row - marked with an "ok" if it is either the first or subsequent "copies" of the record. The cells all start out blank and are marked as evaluated. Then I do an activeCell scan (with isblank(activeCell.offset(0,10) (for instance - whatever your "ok" column is) type of comparisons to ensure that the record has not already been processed. Start the scan at range("a2").select (for example) and after each comparison, use the "activeCell.offset(1,0).select" to go to the next row. Continue the scan until you get to "if isblank(activeCell)" then quit the processing.

For each record that you process, use a .find routine to determine if there are any more rows with that data. If there is, you can adjust cell data in either record as desired by using either activeCell.offset(0,x) and r.offset(0,x) (where R is the range variable used by the .find routine). It sounds complicated, I know, but just step through the code until you've gone through a few comparisons and adjustments and then let it go.

Hope this helps.

**Pichingualas** · 05-04-2012, 04:22 PM

Yeah true, but if it isn't doing that would be unnecessary... So is the order important or not Cindi?

**BlueMoonNJ** · 05-04-2012, 04:42 PM

Actually, my column A is numbers so sorting is fine (as long as all the other row information stays with it). That is what I do now.

If it helps, let me explain my use/reason: I own a small used books store. I export my inventory from my main database into Excel in order to upload it onto my website. Column A is the book's unique Item #; column J is the book's quantity that I have in stock. Periodically, as books are sold in my store and/or new copies come into the store, I need to update the master accordingly--only the quantity (column J mentioned above); the rest of the information across the row remains the same. So here's what I currently do: I maintain one master Excel sheet. I export from the database into Excel and highlight the whole list. Then I copy-n-paste it at the bottom of the master list, and then sort by column A. Thus I end up with one big sheet, every other line is a duplicate of the one above with one white and the duplicate a color. I need to change only the white line in column J. [As I add inventory obviously there will be rows at the bottom which are new and thus don't have a duplicate/don't need changing.] I manually do the above steps to make the changes. When I'm done with all the changes and my original master rows are now all updated, I then re-sort by color and delete the duplicate colored ones. Quite a convoluted process to do manually, and as my inventory grows it's getting phenomenally time consuming as well.

I hope that makes sense, and thanks for helping me.

(PS: In a perfect world I would have an inventory database that would automatically export only the listings which have changed, up or down, so I would know immediately which needed changing at all. But, frustratingly, I do not have such a thing. I aspire to that when I grow up and the budget allows.)

**Pichingualas** · 05-04-2012, 04:49 PM

Option Explicit

Sub CompareAndChange()
    Dim RowCheck As Long
    Dim LastRow As Long
    Dim LastColumn As Integer
    Dim Ws As Worksheet
    Dim DataRng As Range
    
    Application.ScreenUpdating = False
    Set Ws = ActiveSheet
    With Ws
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastColumn = .Cells.SpecialCells(xlCellTypeLastCell).Column
        Set DataRng = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
    End With
    
    With Ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=DataRng, SortOn:=xlSortOnValues _
            , Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange DataRng
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    With Ws
        For RowCheck = 2 To LastRow
            If .Cells(RowCheck, 1).Value = .Cells(RowCheck - 1, 1).Value Then
                .Cells(RowCheck - 1, 10).Value = .Cells(RowCheck, 10).Value
            End If
        Next RowCheck
    End With
    Application.ScreenUpdating = True
End Sub

Try using this code and provide feedback if it isn't working as you want. I haven't put the color check in it which we could use if as you say the line that needs changing is white... Although you'd have to tell me what's white on it, font or background or what?
So as I said, try using the above code, see if it works, and if not we'll see how to make it work.

**BlueMoonNJ** · 05-04-2012, 05:16 PM

Not working, I get a "Run-Time error '1001'... The sort reference is not valid"

Does it matter that I named the macro UpdateQuantity? I changed it in your formula.

I would attach a sample for you to play with but I can't figure out how to do that. Do you want me to email it to you?

**BlueMoonNJ** · 05-04-2012, 05:19 PM

Not working, I get a "Run-Time error '1001'... The sort reference is not valid"

Does it matter that I named the macro UpdateQuantity? I changed it in your formula.

It doesn't matter about the color, does it? I have two colors right now, purple and blue (means different things to me, unrelated to this) -- but of course I could make them all one color for running the macro.

I would attach a sample for you to play with but I can't figure out how to do that. Do you want me to email it to you?

**Pichingualas** · 05-04-2012, 05:21 PM

Sorry there was an error in the code. Here:

Option Explicit

Sub CompareAndChange()
    Dim RowCheck As Long
    Dim LastRow As Long
    Dim LastColumn As Integer
    Dim Ws As Worksheet
    Dim Wb As Workbook
    Dim DataRng As Range
    
    Application.ScreenUpdating = False
    Set Ws = ActiveSheet
    Set Wb = ActiveWorkbook
    With Ws
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastColumn = .Cells.SpecialCells(xlCellTypeLastCell).Column
        Set DataRng = .Range(.Cells(1, 1), .Cells(1, LastColumn))
    End With
    
    DataRng.AutoFilter
    With ActiveWorkbook.Worksheets(Ws.Name).AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Ws.Cells(1, 1), SortOn:=xlSortOnValues _
            , Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    DataRng.AutoFilter
    
    With Ws
        For RowCheck = 2 To LastRow
            If .Cells(RowCheck, 1).Value = .Cells(RowCheck - 1, 1).Value Then
                .Cells(RowCheck - 1, 10).Value = .Cells(RowCheck, 10).Value
            End If
        Next RowCheck
    End With
    Application.ScreenUpdating = True
End Sub

Try this and tell me

**Pichingualas** · 05-04-2012, 05:28 PM

If you want to upload a sample file, you have to click on the "Go Advanced" button instead of "Post Quick Reply" and you will see the typical Cliplboard button which is "Attachments" at the top, or scroll down and click on "Manage Attachments" below "Additional Options". Make sure you don't have sensible information which you wouldn't want going around the web.

Anyway, I was saying about colors in case that somehow the record you need to change was below the one you need to keep... Because that would be possible to prevent if the record that needs changing and the record you want to keep ALWAYS have different colors (for example all you need to keep are in blue and all that need updating are in purple).

**BlueMoonNJ** · 05-04-2012, 05:29 PM

No, now I get a run-time error 5 "invalid procedure call or argument"

**Pichingualas** · 05-04-2012, 05:35 PM

Can you point out what line its stopping at? This is weird because it's now working on my end...

Would you please try to upload a sample file?

**BlueMoonNJ** · 05-04-2012, 05:46 PM

I think it's now attached.

**Pichingualas** · 05-04-2012, 05:48 PM

Here you have a sample of the code working in a file I made to test it. Check it out and tell me if it works for you:

Compare and change test.xlsm

**Pichingualas** · 05-04-2012, 05:55 PM

Its working on your file too on my computer:

macrotest.xlsm

**Pichingualas** · 05-04-2012, 05:49 PM

I'm also looking at your file.

**Pichingualas** · 05-04-2012, 05:56 PM

Does it still not work for you? what line does it stop at? (to see that click on debug upon getting the error message)

**Pichingualas** · 05-04-2012, 06:17 PM

I suppose its fixed...?

**BlueMoonNJ** · 05-05-2012, 10:28 AM

Nope. Maybe I am doing something wrong on my end but I don't know what. Here are the steps I took:

I copied your code from here, and went back to my spreadsheet
Clicked on Macros
Type the macro name CompareAndChange
Click Create
in the next window it shows
Sub CompareAndChange ()

End Sub
with the cursor between the two lines
I pasted your code where the cursor is, and Save (macro enabled workbook)
Now I'm back in my spreadsheet
I click Macros -- I see two entries (one selected) that both say macrotest.xlsm!Module1.CompareAndChange
I click Run
Get the error message "Compile error Ambiguous name detected: CompareAndChange"
and in the box behind the error is the code
first line says Sub CompareAndChange ()
next Option Explicit
space
next Sub CompareAndChange () <---and this line is highlighted

**Pichingualas** · 05-05-2012, 12:02 PM

Yeah that's wrong Cindi. Why don't you do the following steps:

Open VBA screen (ALT+F11)
Right Click on VBAProject(Your file name)
Insert ---> Module
Paste the code there on the module code screen
Close the VBA screen
Try to run the code
Also delete the code you had added before

If you don't want to do that, just erase the line that's being highlighted, it shouldn't be there. And at the bottom of the code, you will also have End Sub repeated, you should erase one of them.

**BlueMoonNJ** · 05-05-2012, 12:32 PM

No, still not working. Sigh. After all this time I guess I could've done it manually, LOL.
I give up, I don't know what else to try.

Thank you, nonetheless. I appreciate your trying to help me with this.

**Pichingualas** · 05-05-2012, 12:44 PM

Try looking at the file I uploaded, that's how you should use it... Maybe you can use that file, just rename it and do what you want, it already has the macro in place...

**BlueMoonNJ** · 05-05-2012, 01:22 PM

Oh, there's an idea. I'll try that. I probably won't get to work on this anymore until Monday. I'll get back to you then.

Have a good weekend.

**yogananda.muthaiah** · 05-05-2012, 03:15 PM

Hi,

there is error coming out in this below mail

Run time errror '5' :
Invalid Procedure call or argument

.Header = xlNo

**Pichingualas** · 05-05-2012, 09:17 PM

It can also be replaced with .Header = 2

**Pichingualas** · 05-05-2012, 09:17 PM

That line should work on Excel 2007 and 2010.

**BlueMoonNJ** · 05-08-2012, 03:04 PM

Okay, I'm finally back to this. No matter what advice from all of you I take, the macro will not work. It won't even work on Pichingualas' own spreadsheet. Is it maybe my version of Excel (I'm using 2010)? is there perhaps some setting within Excel that is not set properly? I do have macros enabled. Is it Windows Vista? I'm grabbing at straws here. It is very strange that the macro works for you but will not for me.

**Pichingualas** · 05-08-2012, 04:38 PM

Cindi did you say your data was already sorted? If so, try using this code instead:

    Dim RowCheck As Long
    Dim LastRow As Long
    Dim Ws As Worksheet
        
    Application.ScreenUpdating = False
    Set Ws = ActiveSheet
    With Ws
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
        
    With Ws
        For RowCheck = 2 To LastRow
            If .Cells(RowCheck, 1).Value = .Cells(RowCheck - 1, 1).Value Then
                .Cells(RowCheck - 1, 10).Value = .Cells(RowCheck, 10).Value
            End If
        Next RowCheck
    End With
    Application.ScreenUpdating = True

Try inserting that code following the steps you had done originally:

I copied your code from here, and went back to my spreadsheet
Clicked on Macros
Type the macro name CompareAndChange
Click Create
in the next window it shows
Sub CompareAndChange ()

End Sub
with the cursor between the two lines
I pasted your code where the cursor is, and Save (macro enabled workbook)

If your sheet is already sorted, that should work... It seems that the error comes from making the macro sort, which shouldn't be happening as it works ok on my computer but idk... maybe this way will work.

Best of lucks and get back if you still have issues please.

If you are going to be doing this on the same workbook you had been working so far, make sure to delete any other version of this macro that's already there please.

Excel 2010 Macro Needed for Search, Compare & Change

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Re: Excel 2010 Macro Needed for Search, Compare & Change

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions