none
Check if Value Exists RRS feed

  • Question

  • I have a schedule that pulls data from a separate tab called 'All Fields Refresh'

    When the workbook is opened, the data is refreshed in this tab, and the formulas update in the other 2 tabs ('Summary' and 'Current Month Detail'). In the 'All Fields Refresh' tab, in column C is the unique 5 digit number to distinguish which retail store submitted the information. I want to write a VBA code that looks at column C in 'All Fields Refresh' and if there is a value that does not exist, create a new row at the bottom of both 'Summary' and 'Current Month Detail,' insert the new value, and copy the formula down.

    I was thinking of doing a "For each row in rng.rows" loop, but I don't want it to take a few minutes to open because it's looping through 1,000 rows. I'm looking for a more efficient way to lookup.

    Any ideas?

    Thanks

    Friday, February 24, 2012 5:39 PM

Answers

  • You are correct; rather than a double loop (down col C in "All Fields Refresh" and also down col C in "Summary"), we can loop down the first tab and use the worksheet function =COUNTIF() to find the missing.  Something like:

    Sub bneb()
    Dim RC As Long, NA As Long, ID As Long
    Dim NB As Long, NC As Long, N As Long
    RC = Rows.Count
    Dim A As Worksheet, B As Worksheet, C As Worksheet
    Set A = Sheets("All Fields Refresh")
    Set B = Sheets("Summary")
    Set C = Sheets("Current Month Detail")
    NA = A.Cells(RC, "C").End(xlUp).Row
    NB = B.Cells(RC, "C").End(xlUp).Row
    NC = C.Cells(RC, "C").End(xlUp).Row

    For N = 1 To NA
        ID = A.Cells(N, "C")
        If Application.WorksheetFunction.CountIf(B.Range("C:C"), ID) = 0 Then
            B.Cells(NB + 1, "C").Value = ID
            C.Cells(NC + 1, "C").Value = ID
            NB = NB + 1
            NC = NC + 1
        End If
    Next

    End Sub

    You would need to add code to supply other data on the newly added rows.


    gsnu201202

    • Marked as answer by Brett N 13 Friday, February 24, 2012 7:16 PM
    Friday, February 24, 2012 7:01 PM
    Moderator

All replies

  • So if an entry in column C is empty, you want to take action??

    gsnu201202


    Friday, February 24, 2012 5:51 PM
    Moderator
  • If an entry in 'All Fields Refresh' Column C does not exist in 'Summary' and 'Current Month Detail' Column C I want it to take action.
    Friday, February 24, 2012 6:03 PM
  • You are correct; rather than a double loop (down col C in "All Fields Refresh" and also down col C in "Summary"), we can loop down the first tab and use the worksheet function =COUNTIF() to find the missing.  Something like:

    Sub bneb()
    Dim RC As Long, NA As Long, ID As Long
    Dim NB As Long, NC As Long, N As Long
    RC = Rows.Count
    Dim A As Worksheet, B As Worksheet, C As Worksheet
    Set A = Sheets("All Fields Refresh")
    Set B = Sheets("Summary")
    Set C = Sheets("Current Month Detail")
    NA = A.Cells(RC, "C").End(xlUp).Row
    NB = B.Cells(RC, "C").End(xlUp).Row
    NC = C.Cells(RC, "C").End(xlUp).Row

    For N = 1 To NA
        ID = A.Cells(N, "C")
        If Application.WorksheetFunction.CountIf(B.Range("C:C"), ID) = 0 Then
            B.Cells(NB + 1, "C").Value = ID
            C.Cells(NC + 1, "C").Value = ID
            NB = NB + 1
            NC = NC + 1
        End If
    Next

    End Sub

    You would need to add code to supply other data on the newly added rows.


    gsnu201202

    • Marked as answer by Brett N 13 Friday, February 24, 2012 7:16 PM
    Friday, February 24, 2012 7:01 PM
    Moderator
  • This looks like what I need! Thanks a bunch!

    Friday, February 24, 2012 7:16 PM
  • O.K.

    Update this post if you need more help on this topic.


    gsnu201202

    Friday, February 24, 2012 7:17 PM
    Moderator