VBA Need to Loop through Cell range and match in Range from another Sheet.

[Lres81715]

Hello,

I need a little help as I'm out of my depth right now. My existing code is horrible. It does a Double loop and its copying the Cell every time it doesn't find a match.

Ideally, I want it to take the contents of sh3.Range("A" & iRow) and search sh1.Range("A:A"). If it finds a match, move on. If it doesn't find a match, then

Cells(iRow, "A").Copy sh1.Range("A" & LastRowx + 1)

Logic Example of what I'm talking about:
Sheet1 = Master
Sheet3 = New Monthly Data

Sheet3'!A180 = NewClientXYZ​

Sheet1 has 580 rows of existing Clients and NewClientXYZ is not present.​

Add NewClientXYZ to Sheet1'!A581​

Continue the process until all New Clients are added to Master sheet.

Here is what I have so far and it doesn't work.

    Const xlStrtRw  As Long = 9 'Starting row number for the Monthly data. 
    Dim sh1         As Worksheet 'Master Worksheet
    Dim sh3         As Worksheet 'Monthly Report
    Dim LastRow     As Long      
    Dim LastRowx    As Long
    Dim xlRow     As Long   
    Dim iRow        As Long
    Dim i           As Integer

    Set sh1 = Sheets("Master")
    Set sh3 = Sheets("Client Relations Trending Month")
    LastRow = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    xlRow = sh3.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row

    For iRow = xlStrtRw To xlRow Step 1 ' Row 9 to LastRow on Monthly data sheet
        For i = 2 To LastRow 
            If sh1.Range("A" & i) <> sh3.Range("A" & iRow) Then
                LastRowx = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row 'Not sure if this is needed but it resets the RowCount on Master sheet for each new client added if match above not found
                Cells(iRow, "A").Copy sh1.Range("A" & LastRowx + 1)
            End If
        Next i
    Next iRow

End Sub

 

[hiker95]

Lres81715,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

I need a little help as I'm out of my depth right now. My existing code is horrible. It does a Double loop and its copying the Cell every time it doesn't find a match.

There is a way to loop thru the rows of one worksheet, and, use Find to search in the other worksheet.


I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[Lres81715]

Hello Hiker,

1) I'm using Excel 2013 and Office 365,
2) All on PC Windows 7 soon to be Win 10.

I have already done a through search using Loop, Match, Find and while I came up with quite a few results, I was not able to find an solution that fit my needs. Or I'm unfamiliar with the code they used so I by-passed it accidentally (IE not knowing that was the solution).

If needed, I'll attempt to try a dropbox link tomorrow.

 

[hiker95]

I'll attempt to try a dropbox link tomorrow.

Lres81715,

I will be looking for your next reply.

 

[Lres81715]

Here are the links to the two reports

Monthly Report
https://app.box.com/s/9al4r6eg23ws2s0g1m7sipfpyykunk4y

Master File that requires the new clients added at the bottom.
https://app.box.com/s/k3ku36b7v86adtsb2lbok3u6xbqlh3d1

 

[hiker95]

Lres81715,

Thanks for the two workbooks.

1. Are we working with two workbooks?

2. Or, are we working with one workbook, that contains worksheets All Clients Monthly data, and, Client Relations Trending Month?

 

[Lres81715]

Working with Two workbooks,

I've already got code in place that defines each workbook with sheet in an earlier bit of code. It's sh1 for Master and sh3 for Monthly report.

    Dim wb          As Workbook    Dim crwb        As Workbook


    For Each wb In Workbooks
        If wb.Name Like "Client Relations Trending Report-Monthly*" & ".xls" Then
            Set crwb = Workbooks.Open(wb.Path & "\" & wb.Name)
[B]            Set sh3 = crwb.Sheets("Client Relations Trending Month")[/B]
        End If
    Next

[B]Set sh1 = Sheets("All Clients Monthly data")[/B] ' This code is embedded in MasterFile and this sheet is activated in an earlier bit of code

For your purposes, you can just combine them on the same workbook as the process would technically be the same.
The code I provided can already determine sh1(Master + Sheet) and sh3(Monthly + Sheet) so I didn't think it was relevant at the time. Sorry for the confusion.

 

[hiker95]

Working with Two workbooks

Lres81715,

I have not done any coding in a very long time working with multiple workbooks.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[Lres81715]

Bump

 

[skywriter]

I moved both of your worksheets into one workbook for this code.

Option Explicit
Sub Lres81715()
Dim tArr, j
    With Worksheets("All Clients Monthly data").Range("A1").CurrentRegion
    tArr = .Offset(1).Resize(.Rows.Count - 1).Value
               .Offset(1).Resize(.Rows.Count - 1).ClearContents
    End With
        With CreateObject("Scripting.Dictionary")
                For Each j In tArr
                    If Not (j = Empty Or .Exists(j)) Then .Add j, Empty
                Next j
                With Worksheets("Client Relations Trending Month").Range("A8").CurrentRegion
                    tArr = .Offset(1).Resize(.Rows.Count - 1, 1).Value
                End With
                    For Each j In tArr
                    If Not (j = Empty Or .Exists(j)) Then .Add j, Empty
                Next j
                tArr = .keys()
                Worksheets("All Clients Monthly data").Range("A2").Resize _
                 (UBound(tArr) + 1).Value = Application.Transpose(tArr)
        End With
End Sub