VBA Using LOOP and FIND to search Column. If no Results, Do This

[Lres81715]

I posted this problem earlier but didn't get an answer so I'm resubmitting. Hopefully I'll explain it better.

I have two worksheets. One is the Master Data (sh1) and the other is Monthly Re-occuring sales (sh3). Column A on both sheets have Client names. Master has ALL clients from the beginning of time (A1:A1000). Monthly Re-occuring may have (A1:A80) with just the clients doing sales in said month. This also includes NEW CLIENTS.

I want to be able to LOOP through the Monthly Re-occuring (A1:A80) and .FIND/search Master (A1:A1000). If no Match found, Then take that Cell with no match and sh3.Cell(i, "A").copy destination:=sh1.Cell(1001,"A")

Here is the Workbook to test the code on.

Visually, this is what it should look like

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Master Worksheet (sh1)[/TD]
[TD][/TD]
[TD]Monthly Re-occuring (sh3)[/TD]
[TD][/TD]
[TD]Master (sh1) EndResult[/TD]
[/TR]
[TR]
[TD]123 Company[/TD]
[TD][/TD]
[TD]123 Company[/TD]
[TD][/TD]
[TD]123 Company[/TD]
[/TR]
[TR]
[TD]ABC Corp[/TD]
[TD][/TD]
[TD]ABC Corp[/TD]
[TD][/TD]
[TD]ABC Corp[/TD]
[/TR]
[TR]
[TD]Basic Company LLC[/TD]
[TD][/TD]
[TD]Corp Co USA[/TD]
[TD][/TD]
[TD]Basic Company LLC[/TD]
[/TR]
[TR]
[TD]Corp Co USA[/TD]
[TD][/TD]
[TD]Elegant Company[/TD]
[TD][/TD]
[TD]Corp Co USA[/TD]
[/TR]
[TR]
[TD]Day Z Corp[/TD]
[TD][/TD]
[TD]FANCY NEW CLIENT[/TD]
[TD][/TD]
[TD]Day Z Corp[/TD]
[/TR]
[TR]
[TD]Elegant Company[/TD]
[TD][/TD]
[TD]Giant Corp trust[/TD]
[TD][/TD]
[TD]Elegant Company[/TD]
[/TR]
[TR]
[TD]Giant Corp trust[/TD]
[TD][/TD]
[TD]Havana Company[/TD]
[TD][/TD]
[TD]Giant Corp trust[/TD]
[/TR]
[TR]
[TD]Havana Company[/TD]
[TD][/TD]
[TD]ZZ Company[/TD]
[TD][/TD]
[TD]Havana Company[/TD]
[/TR]
[TR]
[TD]Y Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y Corp[/TD]
[/TR]
[TR]
[TD]ZZ Company[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ZZ Company[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FANCY NEW CLIENT[/TD]
[/TR]
</tbody>[/TABLE]


I'm completely unfamiliar with .find and how to use it properly in a Loop. Let alone get it to do something IF a match isn't found.

Sub LoopThroughClientList()
' This copies the Client Relationship Trending Report Data from Reports server over to the Master Data Report.
' Details of what this Macro does
' 1 This will add new clients at the bottom of the All Clients list


    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("All Clients Monthly data")
    Set sh3 = Sheets("Client Relations Trending Month")

'===Using LastRow to for both Sheets==
    LastRow = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    xlRow = sh3.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
'===


[B]    For iRow = xlStrtRw To xlRow Step 1 ' Row 9 to LastRow on Monthly data sheet[/B]
[B]        For i = 2 To LastRow [/B]
[B]            If sh1.Range("A" & i) <> sh3.Range("A" & iRow) Then[/B]
[B]                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[/B]
[B]                Cells(iRow, "A").Copy Destination:=sh1.Range("A" & LastRowx + 1)[/B]
[B]            End If[/B]
[B]        Next i[/B]
[B]    Next iRow[/B][COLOR=#333333]

[/COLOR]End Sub

Note: this was my horrendous failed attempt. Everything in BOLD is the problem code where I need to do the find/search and the loop

Thanks in advance!

 

[michaelsmith559]

Save your workbook and then try this macro. Let me know if it works.

Sub Import_New_Clients_to_MasterSheet()
    Dim FindString As String, r As Long
    Dim Rng As Range, i As Long, j As Integer
    lastRow = Sheets("Sheet3").Range("A1").End(xlDown).Row
    j = 1
    
    For r = 1 To lastRow
    FindString = Sheets("Sheet3").Range(j & r).Value
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
            Else
            Sheets("Sheet1").Range("A1048576").End(xlUp).Offset(1, 0).Value = _
                Sheets("Sheet3").Range(j & r).Value
            End If
        End With
    End If
    Next r
End Sub

 

[Gingertrees]

Hmm... I don't have Excel at home, so this is untested. See if it works. If it doesn't, tell me and I'll tweak it on a break tomorrow...

Sub LoopDeux()


Dim sh1 as worksheet, sh3 as worksheet 
Dim LastRow as long, LastRowX as long, xlRow as long
Dim iRow as integer  [COLOR=#008000]'//used for monthly (sh3)[/COLOR]
Dim xlStrtRw as integer


Application.ScreenUpdating = False


xlStrtRw = 9 [COLOR=#008000]'//This isn't super-necessary... I didn't actually use it[/COLOR]
Set sh1 = Sheets("All Clients Monthly data") [COLOR=#008000]'//MASTER[/COLOR]
Set sh3 = Sheets("Client Relations Trending Month")[COLOR=#008000] '///mon. only[/COLOR]


[COLOR=#008000]'===Using LastRow to for both Sheets==[/COLOR]
LastRow = sh1.UsedRange.Rows(sh1.UsedRange.Rows.Count).Row
xlRow = sh3.UsedRange.Rows(sh3.UsedRange.Rows.Count).Row
[COLOR=#008000]'//diff. method of finding lastrow[/COLOR]




sh3.select [COLOR=#008000]'//just to select something[/COLOR]
    For iRow = 9 To xlRow Step 1 [COLOR=#008000]' Row 9 to LastRow on Monthly data sheet[/COLOR]
	If Application.WorksheetFunction.Countif(sht1.Range("A2:A" & xlRow),sh3.Range("A" & iRow))=0 Then
	    LastRowX = sh1.UsedRange.Rows(sh1.Usedrange.Rows.Count).Row
	    sh3.Range("A" & iRow).Copy
	    sh1.Range("A" & LastRowX + 1).PasteSpecial xlPasteValues
	    Application.CutCopyMode = False
	End if
    Next iRow


Application.ScreenUpdating = True
End sub

 

[Lres81715]

Save your workbook and then try this macro. Let me know if it works.

Had to do some minor tweeking but otherwise this worked like a charm. VBA didn't like Range(j & r) so I just changed "j" to "A".

Learned a lot from this btw, thanks! Haven't had the chance to use If Not Rng Is Nothing Then before. I will definitely adapt this to better use in the future.

Thanks again!

Gingertrees,
Thanks for replying as well. I saved your version and I'll dissect it at a later date. I immediately understood using Application.WorksheetFunction.Countif and I want to see if I can apply that to another project I have coming up in a few weeks. Good stuff and thanks again!