New to VBA and trying to make simple macro

[jman070700]

Hello everyone,

This is my first post and I hope this site can be useful. It's hard to learn VBA on your own. I'm trying to make a macro that will take check to see if any names in a column on the first sheet exist in a column on another sheet. If the name is not in the second sheet, I want it to add the new name to the end of the second column. The columns will vary in lengths and I'm not sure how to program this. Below is what I have so far. It runs but doesn't do what I would like it to do. I think my biggest issue is the language. I'm used to matlab, but I'm rusty at programing in general. Thanks for the help every one!

Sub test()
    Workbooks("Book4").Activate
    Dim count As Long
    Dim count2 As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim pos As Long
    
    Set ws1 = Workbooks("Book4").Worksheets("Current Week")
    Set ws2 = Workbooks("Book4").Worksheets("2013 Winall")
    
    count = ws1.UsedRange.Rows.count
    count2 = ws2.UsedRange.count
    
    ws1.Activate
    
    For i = 3 To count
    Cells(i, 4).Select
        pos = (count2 + (i - 2))
        For j = 1 To count2
            If StrComp(Selection, (ws2.Cells(j, 2))) = 1 And j <> count2 Then
            
            Selection.Copy
            ws2.Activate
            ws2.Cells(pos, 2).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Else
            End If
            Next j
        Next i
    
End Sub

[JOHN H. DAVIS]

Welcome to the Forum jman070700 from New Jersey. Its best to read the Forum Rules before posting. To get more responses you need to comply with Forum Rule No.1 and provide a more descriptive title. Also Forum Rule No3. requires you too use code tags.

[xladept]

Hi JMan,



BTW - You should always tag your code - just highlight it and click on the #

[jman070700]

Thank you for the advice, I will tag my code in the future.

I tried your code but it did not work. I filled the cells with a few random names to test it out. it's not moving the names from the first column to the second. I'm not sure if the strcomp function should be used or not.

[jman070700]

Also, I'm noticing that count2 is = 36 when there are only 4 names. There is nothing below those names either.

I'm not sure why this is happening.

[xladept]

I used your original code:

count2 = ws2.UsedRange.count

and didn't notice that "rows" was omitted. It should be:

count2 = ws2.UsedRange.Rows.count

- but the Used Range is often unreliable.

As to why the code didn't work otherwise?? Can you post a sample spreadsheet? Go to advanced and click on the paper clip

[jman070700]

that fixed count2.

I really appreciate all this help. I'll be sure to return the favor when I'm good enough to be able to.

[xladept]

Hi JMan,

Try this:

Sub JMan(): Dim ws1 As Worksheet, ws2 As Worksheet
Dim count As Long, count2 As Long, pos As Long, i As Long, j As Long

Set ws1 = Workbooks("Book4.xlsm").Worksheets("Current Week")
Set ws2 = Workbooks("Book4.xlsm").Worksheets("2013 Winall")

            count = ws1.UsedRange.Rows.count
        count2 = ws2.UsedRange.Rows.count: pos = count2

    For i = 3 To count: For j = 3 To count2
    If ws1.Cells(i, 4) = ws2.Cells(j, 2) And j <> count2 Then
    GoTo GetNext: End If: Next j
    
    pos = pos + 1: ws1.Cells(i, 4).Copy ws2.Cells(pos, 2)
    Application.CutCopyMode = False
    
GetNext:    Next i: End Sub

[jman070700]

YES, that seemed to work. I'll look over your code to learn how you did this better. Thank you so much!

[xladept]

You're welcome! And - thanks for the rep

[arlu1201]

jman,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Also,

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

[jman070700]

OK, I did't realize I titled my issue wrong. Thank you

[arlu1201]

Your title still needs correction. You can try changing it to "Macro to check if names match across sheets."