Results 1 to 6 of 6

Thread: Looking for a value in a range and acting if it's not found

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Looking for a value in a range and acting if it's not found

    Good morning all!

    As I am learning VBA I normally try to solve a problem before I break it and then come here for help

    For this one though I'm not sure where to start.

    Basically, I have 2 worksheets of data.

    I need to loop through all the rows in one sheet, and find if a specific value exists in a labelled column in the other sheet. If that value is found, then nothing is needed; but if it's not I need certain data copying from one sheet to the other.

    I suspect I need to use some Index, Match, Vlookup process but I've only just started playing in this area (beyond VLOOKUPs) so I'm at a loss.


    I've attached an extract from my workbook so you can see what I need to happen here:

    Invoice match workbook.xlsx

    As I am trying to learn any explanation for the wonderful VBA would be brilliant!

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Looking for a value in a range and acting if it's not found

    Code:
    Sub voucher()
        Dim voucher As Range, v As Variant, counter As Long
        For Each voucher In Sheets("Temp").Range("B2", Sheets("Temp").Range("B" & Rows.Count).End(xlUp))
            With Sheets("2. Final Data")
                v = Application.Match(voucher, .Range("C:C"), 0) 'test for match
                If IsError(v) Then 'if no match then copy data
                    With .Range("B" & Rows.Count).End(xlUp)
                        .Offset(1, 0).Value = voucher.Offset(0, -1).Value
                        .Offset(1, 1).Value = voucher.Value
                        .Offset(1, 2).Value = Abs(voucher.Offset(0, 2).Value)
                        counter = counter + 1
                    End With
                End If
            End With
        Next voucher
        MsgBox counter & " voucheres copied.", vbInformation, "Copy Complete"
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for a value in a range and acting if it's not found

    Hi AlphaFrog,

    This works perfectly in my test sheet. Alas it is never as simple as that!

    The data that comes into the 2. Final Data Sheet is possibly in different orders.

    As such I modified the code slightly, to find the 3 columns that I need to paste in. My new code looks like this:

    Code:
    Sub MoveInvoiceNoIntrastat()
    
        Dim Voucher As Range, v As Variant, Counter As Long
        Dim DateColumn As Long, InvoiceColumn As Long, AdjustedValue As Long
        Dim TextFinder As Range
        
        Set ws1 = Sheets("2. Final Data")
        Set ws2 = Sheets("Temp")
        
        With ws1
            
            Set TextFinder = .Range("A1:Z1").Find("Date")
                DateColumn = TextFinder.Column
                
            Set TextFinder = .Range("A1:Z1").Find("Invoice")
                InvoiceColumn = TextFinder.Column
                
            Set TextFinder = .Range("A1:Z1").Find("Adjusted Value")
                AdjustedValue = TextFinder.Column
        
        End With
        
        For Each Voucher In Sheets("Temp").Range("B2", Sheets("Temp").Range("B" & Rows.Count).End(xlUp))
            With Sheets("2. Final Data")
                v = Application.Match(Voucher, .Range(Columns(DateColumn), Columns(DateColumn)), 0)
                
                If IsError(v) Then
                
                    With .Range(Columns(DateColumn), Rows.Count).End(xlUp)
                        .Offset(1, 0).Value = Voucher.Offset(0, -1).Value
                        .Offset(1, InvoiceColumn - DateColumn).Value = Voucher.Value
                        .Offset(1, AdjustedValue - DateColumn).Value = Abs(Voucher.Offset(0, 2).Value)
                        Counter = Counter + 1
                    End With
                End If
            End With
        Next Voucher
        MsgBox Counter & " vouchers copied.", vbInformation, "Copy Complete"
        
    End Sub
    The bold and underlined line is where it falls over.

    I have stepped through and the number values for the 3 columns are correct.

    I suspect I have an issue with my naming and referring to ranges here. Because I have a number, I thought I didn't use "" to refer to the range?

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Looking for a value in a range and acting if it's not found

    Try it like
    Code:
    Sub MoveInvoiceNoIntrastat()
    
        Dim Voucher As Range, v As Variant, Counter As Long
        Dim DateColumn As Range, InvoiceColumn As Range, AdjustedValue As Range
        Dim NxtRw As Long
        
        Set Ws1 = Sheets("2. Final Data")
        Set Ws2 = Sheets("Temp")
        
        With Ws1
            
            Set DateColumn = .Range("A1:Z1").Find("Date")
                
            Set InvoiceColumn = .Range("A1:Z1").Find("Invoice")
                
            Set AdjustedValue = .Range("A1:Z1").Find("Adjusted Value")
        
        End With
        
        For Each Voucher In Sheets("Temp").Range("B2", Sheets("Temp").Range("B" & Rows.Count).End(xlUp))
            With Sheets("2. Final Data")
                v = Application.Match(Voucher, DateColumn.EntireColumn, 0)
                
                If IsError(v) Then
                   NxtRw = .Cells(Rows.Count, DateColumn.Column).End(xlUp).Offset(1).Row
                   .Cells(NxtRw, DateColumn.Column) = Voucher.Offset(0, -1).Value
                   .Cells(NxtRw, InvoiceColumn.Column).Value = Voucher.Value
                   .Cells(NxtRw, AdjustedValue.Column).Value = Abs(Voucher.Offset(0, 2).Value)
                   Counter = Counter + 1
                End If
            End With
        Next Voucher
        MsgBox Counter & " vouchers copied.", vbInformation, "Copy Complete"
        
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Jul 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for a value in a range and acting if it's not found

    That's working perfectly now, thanks!

    I now need to modify it again so that it pulls the right columns from the Temp folder, I'll give it a go.

    My question though - you changed how the Sub worked a little bit - can I ask why?

    I mean the NxtRw line, rather than the way you changed the variables for the columns, which makes sense to me.
    Last edited by TheRedCardinal; Sep 23rd, 2019 at 09:27 AM.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Looking for a value in a range and acting if it's not found

    I just felt it was easier to do it that way.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •