asked on

Compare & Copy Specific Text Values

I have the following code that I would like to add critieria too, however I can not get it work based on what I added

Sub aaa()
  Dim ChkSH As Worksheet
  Set ChkSH = Sheets("LM")

    ce = "Local Market"
   
Sheets("Variance").Activate
  For Each ce In Range("a4:a" & Cells(Rows.Count, 1).End(xlUp).Row)
    Set findit = ChkSH.Range("a4:a3000").Find(what:=ce, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    If findit Is Nothing Then Range("A" & ce.Row).Copy _
        ChkSH.Range("a65000").End(xlUp).Offset(1, 0)

  Next ce
End Sub

Open in new window

From the Variance worksheet I want to only compare and copy over the rows where Column B text = Local Market. As it is written I am returning all values in Column B.

Joe Howard

Try this:

Sub aaa()
    Dim ChkSH As Worksheet
    Dim r As Row
    Dim ce As String

    Set ChkSH = Sheets("LM")
    ce = "Local Market"

    Sheets("Variance").Activate
    For Each r In Sheets("Variance").Range("a4:a" & Cells(Rows.Count, 1).End(xlUp).Row)
        Set findit = ChkSH.Range("a4:a3000").Find(what:=ce, LookIn:=xlValues, _
                                                  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                  MatchCase:=False, SearchFormat:=False)

        If findit Is Nothing Then Range("A" & ce.Row).Copy _
           ChkSH.Range("a65000").End(xlUp).Offset(1, 0)

    Next
End Sub

Open in new window

jmac001

ASKER

Hi,

Receiving User-defined type not defined compile error (Dim r As Row). Attaching a sample of the workbook vba in Module 4.

Thanks
EE-Timeline-Data-2014.04.29.xlsm

Joe Howard

Sorry my bad.

Sub aaa()
    Dim ChkSH As Worksheet
    Dim r As Range
    Dim ce As String

    Set ChkSH = Sheets("LM")
    ce = "Local Market"

    Sheets("Variance").Activate
    For Each r In Sheets("Variance").Range("a4:a" & Cells(Rows.Count, 1).End(xlUp).Row).Rows
        Set findit = ChkSH.Range("a4:a3000").Find(what:=ce, LookIn:=xlValues, _
                                                  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                  MatchCase:=False, SearchFormat:=False)

        If findit Is Nothing Then Range("A" & r.Row).Copy _
           ChkSH.Range("a65000").End(xlUp).Offset(1, 0)

    Next
End Sub

Open in new window

jmac001

ASKER

Still have a problem all of the records are still being copied over

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmac001

ASKER

This does workk. In your opinion, I have to consistanly update the report, it better to copy and repaste the values or do a comparison and add only the new values?

Joe Howard

It will be much more efficient and a whole lot quicker to copy and paste all the the records.

jmac001

ASKER

Thanks for you help and the alternative solution

Compare &amp; Copy Specific Text Values

Compare & Copy Specific Text Values