asked on

Insert row to main spreadsheet if record not found in sub workbook

I have a main workbook where I have all the unique salespeople . On a daily basis I receive an updated workbook which I call the sub workbook that has the most current salespeople. What I want to be able to do is via vba scan the sub workbook for all unique salespeople and compare to my main workbook. If the main workbook does not contain the salesperson found in the subworkbook then I would like to add this new salesperson to the main workbook. This new record would be added to the first empty row found in the main workbook.

Note each of my salespeople are unique by the field called social security number which is found in both the main and sub workbooks.

I have researched my need and it seems like I need to use the find function in order to do the compare of social security numbers in the main vs sub workbooks, but I am lost in terms of if I do not find the record how I find the last empty row in the main workbook and add the record found in the sub workbook.

I am on the train and have been unable to attach my 2 workbooks, but I assume this will not be a handicap in providing me with some guidance ?

Jacques Geday

Hello,
Could you pls post a sample workbook changing sensitive data so I can give you a script that would work ?
gowflow

upobDaPlaya

ASKER

Yes...I can send this evening..apologies that I was not able to attach earlier as I know that is a significant omission..it provides a solid advantage when trying to get a solution...

Jacques Geday

No problem when u attach it I will hv a look and revert.
gowflow

upobDaPlaya

ASKER

Here are the spreadsheets...EE-Sub is the spreadsheet I will receive daily. EE-Main is the spreadsheet I will need to update daily if a new record is found on EE-Sub
EE-Sub.xlsx
EE-Main.xlsx

Jacques Geday

ok here it is.

1) Load the file posted and make sure you activate macros.
2) Press on the button Import New SSN
3) follow the question and point to location of the file you want to import sub.xlsx or any other file the default location is the same where this workbook is located you can change that.
4) check out the results.
5) After the first run run it again and see the results.

Let me know
gowflow
EE-Main.xlsm

upobDaPlaya

ASKER

Thanks gowflow. This looks great ! 3 questions.
1-In the find part of the code I can't figure out how you are referencing the Main workbook. To me it seems like the lines of code that has the FIND code in it is only referencing the SUB workbook.
2-The code only adds the SSN. How do I also add the first and last name found in the SUB ?
3-What does DOEvents do ?

Thanks again for the assist/tutoring

Jacques Geday

ok here are answers to your questions:

1) We do not refrence the main workbook as there is no need for that. We only refrence the Main worksheet and this is the instruction that refers to that:
Set ThisWS = ActiveSheet
Notice all along when you have the prefix 'ThisWS' we are referring to (this worksheet or Main as the active sheet is the sheet that has the button we started the macro with and therefore is the Activesheet.

Moreover, you are partly correct the Find part refrence the Sub but it also refrence Main worksheet
Set cCell = ThisWS.Range("A2:A" & ThisMaxRow).Find(what:=WS.Cells(I, "A"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

Notice 'ThisWS' refers to Main (this worksheet) and also 'WS' refers to the Sub worksheet where we are looking for its value picked.

2) To add first Name that is in Col B and Last Name in Col C you do this in this block of code

If cCell Is Nothing Then
        ThisWS.Cells(ThisMaxRow, "A") = WS.Cells(I, "A")
        SSNCount = SSNCount + 1
        ThisMaxRow = ThisWS.Range("A:A").End(xlDown).Row + 1
        DoEvents
    End If

Open in new window

After the instruction
ThisWS.Cells(ThisMaxRow, "A") = WS.Cells(I, "A")

we add the following ones
ThisWS.Cells(ThisMaxRow, "B") = WS.Cells(I, "B")
ThisWS.Cells(ThisMaxRow, "C") = WS.Cells(I, "C")

so you will end-up with the following code

If cCell Is Nothing Then
        ThisWS.Cells(ThisMaxRow, "A") = WS.Cells(I, "A")
        ThisWS.Cells(ThisMaxRow, "B") = WS.Cells(I, "B")
        ThisWS.Cells(ThisMaxRow, "C") = WS.Cells(I, "C")
        SSNCount = SSNCount + 1
        ThisMaxRow = ThisWS.Range("A:A").End(xlDown).Row + 1
        DoEvents
    End If

Open in new window

3) DoEvents in this case is not necessary but because I disabled screenupdating in the beginning of the code I want to make sure each instruction is processed before moving to the next one. Usually we use doevents in situations where you have backgroung processing while your current sub is executing a loop or something this will ensure the system wait a millisecond to make sure all the other previous instructions has been processed to make sure your logic gives the expected results.

Please feel free to ask more if not clear.

This new version includes the First and Last Name.
gowflow
EE-Main.xlsm

upobDaPlaya

ASKER

Wow...thanks for the detailed explanation. That was great as I am in this to learn. For your response to 1..I am still struggling with how the find only knows how to look in the sub workbook as we have not referenced to search the entire workbook or current worksheet ?

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

upobDaPlaya

ASKER

Thanks gowflow...I will re-reviewing line my line tomorrow to make sure I understand..So far so good thanks to your help...

Jacques Geday

ok no problem let me know if you need further help.
gowflow

upobDaPlaya

ASKER

Hi gowflow...I had a chance today and I think I am pretty good now based on your help.. Some final questions below...

For the below line of code I understand everthing to the right of Find. My question is
to the left of Find..
a-is all this is doing is setting the range of where to do the Find ? In this case the Main spreadsheet from A2:A7 (ThisMaxRow in my example is row 7)...Thus the Find will limit its search from A2:A7 ?
b-Is Find a property of the Range Object ?

Set cCell = ThisWS.Range("A2:A" & ThisMaxRow).Find(what:=WS.Cells(I, "A"), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

Open in new window

c- For the below line of code when you do not specify a row for the range ("A:A") I assume it will look down the entire column of A until it finds the last non blank row and then adds 1 ?

ThisMaxRow = ThisWS.Range("A:A").End(xlDown).Row + 1

Open in new window

Thanks again !

Jacques Geday

a) Exactly true
b) Exactly true
c) Exactly true
now for c if you have several columns in you workbook and say the last row for
Col A is 20
Col B is 16
Col C is 25
Col F is 52

and you want to get the last row of the entire sheet then you are better off doing this

ThisMaxRow = ThisWS.UsedRange.Row + 1

This will look at the furthest row used and add 1 in this case it will return 53.

gowflow

Jacques Geday

If your done with this question and satisfied maybe you can close it and if need more help do not hesitate to let me know.
gowflow

upobDaPlaya

ASKER

Thanks goflow I have finished and I have a strong competency on my topic. Your responses were the best I have ever received at ee in terms of the depth, clarity, and commitment. I have received a lot of great responses at ee so it says a lot that I have such high esteem on your response. Many thanks again.

Jacques Geday

thank you for your nice appreciation and glad I could help you out. Please feel free to post here any other issue/new question you may need help with I will keep this question opened in case u need other help.
Regards
gowflow