VBA Loop if then
Results 1 to 5 of 5

Thread: VBA Loop if then
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2015
    Location
    Australia
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Loop if then

    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 by everblazing; Jul 19th, 2019 at 02:00 AM.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,743
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA Loop if then

    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.


    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,711
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Loop if then

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


    Raw
     AB
    1VALUEDATA
    2raw1anyone
    3raw2whatever
    4raw3forever
    5raw4nothing


    Map
     ABC
    1VALUE DATA
    2raw1 anyone
    3raw2 zero
    4raw3 always
    5raw4 nothing



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

    Exception
     AB
    1VALUEDATA
    2raw2whatever
    3raw3forever


    -------
    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
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Sep 2015
    Location
    Australia
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Loop if then

    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.

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,711
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: VBA Loop if then

    Quote Originally Posted by everblazing View Post
    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
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •