VBA Loop if then

[everblazing]

Hi all.

Appreciate some help in writing a loop macro to do a task. I am trying to find every position number that doesn't have the correct mapping Manager position number to copy the row from raw data into exception sheet for review.

I have three sheets. 1-Raw 2-Map 3- Exception

for cell value in A2 in sheet Raw, find in sheet Map column A if Sheet Raw Cell B2 is not the same as sheet Map C2 then copy the row of value in A2 sheet Raw to exception sheet.

Thank you

 

[My Aswer Is This]

I'm confused.
You said:

A2 in sheet Raw

if Sheet Raw Cell B2

Why do you say A2 then B2 ?

And are we suppose to repeat this process all the way down to last cell in sheet raw column A with values

I think B2 of sheet raw may be a mistake.

​

_{^{<strike>
</strike>}}​

 

[DanteAmor]

I guess you have the information like this on each sheet:


<b>Raw</b><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >VALUE</td><td >DATA</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >raw1</td><td >anyone</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >raw2</td><td >whatever</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >raw3</td><td >forever</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >raw4</td><td >nothing</td></tr></table>

<b>Map</b><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >VALUE</td><td > </td><td >DATA</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >raw1</td><td > </td><td >anyone</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >raw2</td><td > </td><td >zero</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >raw3</td><td > </td><td >always</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >raw4</td><td > </td><td >nothing</td></tr></table>


-------
Output
-------

<b>Exception</b><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >VALUE</td><td >DATA</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >raw2</td><td >whatever</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >raw3</td><td >forever</td></tr></table>

-------
Try this:

Sub Mapping_Manager()
    Dim sh1 As Worksheet, c As Range, f As Range
    Set sh1 = Sheets("Raw")
    For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
        Set f = Sheets("Map").Range("A:A").Find(c, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            If c.Offset(0, 1).Value <> f.Offset(0, 2).Value Then
                c.EntireRow.Copy Sheets("Exception").Range("A" & Rows.Count).End(xlUp)(2)
            End If
        End If
    Next
    MsgBox "End"
End Sub

 

[everblazing]

Thank you very much Dante, that's exactly what I am after.. love your work.

there's only 1 issue, and that is for example in sheet Map the Value Raw2 appears twice in column A, it looks up against the first value only and ignores the second Raw2 which is a matching value, which is exactly what a Vlookup would do.

Is it possible for the code to look for any Raw2 value in map and if neither of DATA in column C match then copy to exception sheet?

appreciate your help.

 

[DanteAmor]

Thank you very much Dante, that's exactly what I am after.. love your work.

there's only 1 issue, and that is for example in sheet Map the Value Raw2 appears twice in column A, it looks up against the first value only and ignores the second Raw2 which is a matching value, which is exactly what a Vlookup would do.

Is it possible for the code to look for any Raw2 value in map and if neither of DATA in column C match then copy to exception sheet?

appreciate your help.

Try this please:

Sub Mapping_Manager()
    Dim sh1 As Worksheet, c As Range, exists As Boolean
    Dim r As Range, f As Range, cell As String
    Set sh1 = Sheets("Raw")
    For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
        Set r = Sheets("Map").Range("A:A")
        Set f = r.Find(c, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            cell = f.Address
            exists = False
            Do
                If c.Offset(0, 1).Value = f.Offset(0, 2).Value Then
                    exists = True
                    Exit Do
                End If
                Set f = r.FindNext(f)
            Loop While Not f Is Nothing And f.Address <> cell
            If exists = False Then
                c.EntireRow.Copy Sheets("Exception").Range("A" & Rows.Count).End(xlUp)(2)
            End If
        End If
    Next
    MsgBox "End"
End Sub