+ Reply to Thread
Results 1 to 3 of 3

Check if Cell Value exists... If not copy into a different range

Hybrid View

  1. #1
    Registered User
    Join Date
    07-18-2014
    Location
    Atlanta, GA
    MS-Off Ver
    10
    Posts
    65

    Check if Cell Value exists... If not copy into a different range

    The following will check to see if value exists, if not then add it to the last row in column.
    How I am using it is to find the Prj ID # in Column U on the Planning Tracker and if it does not exist in Column A on the PnL then add it at the end of the column.
    But, I also need to include values from seven additional cells on the same row that correspond to the Prj ID



    Private Sub CommandButton6_Click()
    '**** UPDATES THE PNL REPORT ****
    
    Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range
    
    Set sh1 = Sheets("Planning Tracker")
    Set sh2 = Sheets("PnL")
    lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = sh1.Range("U2:U" & lr)
    For Each c In rng
    If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 Then
    sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row)(2) = c.Value
    
    End If
    
    Next
    
        Application.ScreenUpdating = True
    End Sub
    I found this which will execute the multiple columns of copy paste... it only updates the last row and stops. What I need is to add the last section in Bold/Italics to the above code, but cannot figure out how to do it


    Public Sub CheckData()
    Dim Lastrow As Long
    Dim Newrow As Long
    Dim i As Long
    Dim sh As Worksheet
    
        Application.ScreenUpdating = False
        
        Set sh = Worksheets("Master")
        
        With Worksheets("Reference")
        
            Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = 4 To Lastrow
            
                If IsError(Application.Match(.Cells(i, "A").Value, sh.Columns("B"), 0)) Then
                
                    Newrow = sh.Range("B3").End(xlDown).Row + 1
                    .Cells(i, "A").Copy sh.Cells(Newrow, "B")
                    .Cells(i, "B").Copy sh.Cells(Newrow, "C")
                    .Cells(i, "C").Copy sh.Cells(Newrow, "E")
                End If
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by HJHamm; 10-23-2015 at 03:29 PM. Reason: Forgot to attach file

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Check if Cell Value exists... If not copy into a different range

    Private Sub CommandButton6_Click()
        '**** UPDATES THE PNL REPORT ****
        
        Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, NextRow As Long
        
        Set sh1 = Sheets("Planning Tracker")
        Set sh2 = Sheets("PnL")
        NextRow = sh2.Cells(Rows.Count, 1).End(xlUp).Row
        
        For Each c In sh1.Range("U2", sh1.Cells(Rows.Count, "U").End(xlUp))
            If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 Then
                NextRow = NextRow + 1
                sh2.Range("A" & NextRow).Value = c.Value                        'Copy U to A
                sh2.Range("B" & NextRow).Value = sh1.Cells(c.Row, "A").Value    'Copy A to B
                sh2.Range("C" & NextRow).Resize(, 4).Value = sh1.Cells(c.Row, "C").Resize(, 4).Value   'Copy C:F to C:F
            End If
        Next
        
        Application.ScreenUpdating = True
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    07-18-2014
    Location
    Atlanta, GA
    MS-Off Ver
    10
    Posts
    65

    Re: Check if Cell Value exists... If not copy into a different range

    Perfect! Works like a charm!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Check Range for negative value, and add to value if it already exists
    By ericbartha in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-06-2015, 02:34 PM
  2. Check if a User's Email already exists in a range
    By ericbartha in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-07-2015, 01:22 PM
  3. [SOLVED] check if number in once cell exists in a range of cells and if yes, format another cell
    By beaujolais44 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-16-2013, 07:23 AM
  4. Check if a named range exists
    By Hein in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-14-2010, 09:16 AM
  5. How to check if range exists
    By z0rpia in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-14-2009, 12:15 AM
  6. [SOLVED] Check if named range exists!
    By steve_doc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2006, 07:00 PM
  7. Check if a number exists in a range?
    By gkaste in forum Excel General
    Replies: 2
    Last Post: 07-13-2005, 03:00 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1