Solved

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

Posted on 2013-06-07
16
304 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
ID: 39229174
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
ID: 39229393
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
ID: 39229596
No problem when u attach it I will hv a look and revert.
gowflow
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:upobDaPlaya
ID: 39230778
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
ID: 39231475
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
ID: 39232470
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
ID: 39232644
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
ID: 39233390
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
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39233455

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
ID: 39233763
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
ID: 39234187
ok no problem let me know if you need further help.
gowflow
0
 

Author Comment

by:upobDaPlaya
ID: 39236589
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
ID: 39236800
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
ID: 39245296
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
ID: 39246607
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
ID: 39246675
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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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