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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,478
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?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,208
Office Version
2007
Platform
Windows
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("[COLOR=#ff0000]Sheet1[/COLOR]")
  Set wb2 = Workbooks("[COLOR=#ff0000]client_list[/COLOR].xlsm")
  Set sh2 = wb2.Sheets("[COLOR=#ff0000]List[/COLOR]")
  
  client = sh1.Range("[COLOR=#ff0000]G7[/COLOR]")
  Set f = sh2.Range("[COLOR=#ff0000]A:A[/COLOR]").Find(client, , xlValues, xlWhole)
  If f Is Nothing Then
    sh2.Range("[COLOR=#ff0000]A[/COLOR]" & Rows.Count).End(xlUp)(2) = client
  End If
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,478
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.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,478
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!! :)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,208
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,679
Messages
5,488,226
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top