Adding a name to a list if it doesn't exist
Results 1 to 5 of 5

Thread: Adding a name to a list if it doesn't exist

  1. #1
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    859
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Adding a name to a list if it doesn't exist

    I have a spreadsheet that is used to generate quotes for clients.


    • The worksheet (npss_quote_sheet) is used to generate 1 quote for one client at a time.
    • There is a table (npss_quote) that lists the services in the quote for the one client.
    • Once all services have been entered, the quote is copied to another sheet (Costing_tool), to another table (tblCosting) for additional data entry


    Within the process that copies from the table npss_quote to the table tblCosting, I need code that will take the client name that is stored in a merged cell of G7:H7 and add it (if it doesn't already exist) to a table called ChildYP, that is stored on the sheet List that is in the file client_list.xlsm that is stored in the same folder as the spreadsheet file.

    Can someone help me with the vba code please?

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

    Default Re: Adding a name to a list if it doesn't exist

    Try this.

    I recommend you to open the book "client_list"
    Adjust the data in red for your information.

    Code:
    Sub Adding_name()
      Dim wb1 As Workbook, wb2 As Workbook
      Dim sh1 As Worksheet, sh2 As Worksheet
      Dim f As Range, client As Variant
      
      Set wb1 = ThisWorkbook
      Set sh1 = wb1.Sheets("Sheet1")
      Set wb2 = Workbooks("client_list.xlsm")
      Set sh2 = wb2.Sheets("List")
      
      client = sh1.Range("G7")
      Set f = sh2.Range("A:A").Find(client, , xlValues, xlWhole)
      If f Is Nothing Then
        sh2.Range("A" & Rows.Count).End(xlUp)(2) = client
      End If
    End Sub
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    859
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding a name to a list if it doesn't exist

    Thanks for that Dante, it pasted the name in the cell below where the table is, not in the table. Is it better to use a range for this or a table?

    I did modify my spreadsheet to work with just a range.

  4. #4
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    859
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding a name to a list if it doesn't exist

    I remember now that I made the table when i couldn't get the range working so I had no reference to the new table in the original post. I will just keep it as a range so it is working perfectly thankyou!!

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

    Default Re: Adding a name to a list if it doesn't exist

    Quote Originally Posted by dpaton05 View Post
    I remember now that I made the table when i couldn't get the range working so I had no reference to the new table in the original post. I will just keep it as a range so it is working perfectly thankyou!!
    I'm glad to help you. Thanks for the feedback.
    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
  •