VBA Loop if then

everblazing

Board Regular
Joined
Sep 18, 2015
Messages
156
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
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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>
 
Upvote 0
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:

Code:
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
 
Upvote 0
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.
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top