VBA Loop if then

everblazing

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

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,119
Office Version
2007
Platform
Windows
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
 

everblazing

Board Regular
Joined
Sep 18, 2015
Messages
154
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,119
Office Version
2007
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,081
Messages
5,484,562
Members
407,454
Latest member
moomen1999

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top