Solved

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

Posted on 2013-06-07
16
301 Views
Last Modified: 2013-06-13
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 ?
0
Comment
Question by:upobDaPlaya
  • 9
  • 7
16 Comments
 
LVL 29

Expert Comment

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

Author Comment

by:upobDaPlaya
Comment Utility
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...
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
No problem when u attach it I will hv a look and revert.
gowflow
0
 

Author Comment

by:upobDaPlaya
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:upobDaPlaya
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:upobDaPlaya
Comment Utility
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 ?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility

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 ?

I think you got things mixed up.
The logic is as follows I will put it in plain English

1) Set ThisWS to be Main worksheet (meaning this current worksheet that holds the vba code)

2) Open WB workbook = WBFile (this variable has the file name of the file you select when the open dialog box shows up)

3) Once we open WB with the Sub file name automatically it opens on the default worksheet which is the only sheet there which become now the Activesheet

4) Set WS = the active sheet which is the sheet of the Sub file.

5) the variable MaxRow get the last Row of the Col A in the File Sub or WS

6) Now we will Loop from row 2 to MaxRow in the File Sub or the worksheet WS
so we are looping in the sub file for each record in Col A and we will check if that record exist in sheet ThisWS or sheet Main.

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

what this instruction means is Set cCell (a range or cell) equal to:
looking the ThisWS (the sheet Main) in Col A from Row 2 to the last Row = ThisMaxRow) look in that column for the value that you just located in sheet Sub or WS on the index I in Column A like it will loop thru all Social Security numbers in sheet Sub from row 2 and look to see if this item is found in the sheet Main in Col A and return the result in the Rance called cCell.

8) If cCell Is Nothing Then
if the item is not found in sheet Main then .... CREATE it in sheet Main
if it was found then do nothing and continue to loop to the next item in sheet Sub till the last row in sheet Sub.

9) At the end display results.

Hope It is more clear, if not do not hesitate to ask I like people who want to learn that is the way to move forward.
BRAVO !!!

gowflow
0
 

Author Comment

by:upobDaPlaya
Comment Utility
Thanks gowflow...I will re-reviewing line my line tomorrow to make sure I understand..So far so good thanks to your help...
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok no problem let me know if you need further help.
gowflow
0
 

Author Comment

by:upobDaPlaya
Comment Utility
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 !
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Closing Comment

by:upobDaPlaya
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now