# Check if Value Exists

• ### 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

• 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 Friday, February 24, 2012 7:16 PM
Friday, February 24, 2012 7:01 PM

### All replies

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

gsnu201202

Friday, February 24, 2012 5:51 PM
• 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 Friday, February 24, 2012 7:16 PM
Friday, February 24, 2012 7:01 PM
• 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