?
Solved

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

Posted on 2013-06-07
16
Medium Priority
?
309 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 31

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 31

Expert Comment

by:gowflow
ID: 39229596
No problem when u attach it I will hv a look and revert.
gowflow
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 31

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 31

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 31

Accepted Solution

by:
gowflow earned 2000 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 31

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 31

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 31

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 31

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 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