• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code to add names to bottom of a list

F

fantabulator

Member
Hello all,

I am trying to develop an assignment tracker for my class. The class list is updated by CSV through another mark program we use.

The CSV will be dynamic, especially in the first few weeks of the course as students add the class or drop the class.

I have attached the file to show where I am at this point.

I have figured out how to compare the updated list to the class list that will be used to organize the data.

I need a macro attached to a button that updates the class list with any additional names that may be added by an updated CSV
 

Attachments

  • dynamicupdateproblemb.xlsm
    19.7 KB · Views: 3
Thank you...one question...I had to move the button to a different sheet. Everything in the code works except it does not paste the values into the j column. It says how many names were added, but does not add them to the column. What do I have to change in the code if the button is on a different sheet?
 
Last edited:
Try this code:

Code: 
Sub updateclasslist()
Dim rng As Range
Dim lr As Long
Dim lastR As Long
Dim cntr As Long
Dim msg As String
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1") ' change Sheet1 to your sheet name.
Application.ScreenUpdating = False
lr = ws.Cells(Rows.Count, 8).End(xlUp).Row
lastR = ws.Cells(Rows.Count, 10).End(xlUp).Row
cntr = 0
For Each rng In ws.Range("H2:H" & lr)

If IsError(Application.Match(rng.Value, ws.Range("J2:J" & lastR), 0)) Then
    ws.Range("J" & Cells(Rows.Count, 10).End(xlUp).Row).Offset(1, 0) = rng.Value
    cntr = cntr + 1
End If
Next
msg = "No. of names Added: " & cntr
MsgBox msg
Application.ScreenUpdating = True

End Sub

Go through the comment in the code.

Regards,
 
Ok, tried your code. here is the new sheet with two sheet divisions. I have changed the data in the 2n left column so it is ready to update with the press button macro with new data. If you start with the button on the data sheet, it says 36 names added, yet none show up. If I then press the button on the sheet 1 sheet, names show up. If I press it again, no names are added yet it says 8 names are added.(perhaps these are the blanks at the end of the base comparison column(h).

Thanks again for all the time you've spent looking at this for me.

Solving this is really something that helps tie in all of my effort to make this program more seamless and more automated for me.
 

Attachments

  • dynamicupdateproblemb.xlsm
    28.4 KB · Views: 4
@fantabulator

Yes you said it right, it's the blank spaces which are the problem, can you manager without them or they also need to be tweaked as they are the result of formulas.

Regards,
 
Hi ,

Just a piece of advice ; if you are planning an application , starting from scratch , do not mix up formulae and VBA ; the two should be strictly independent , not depend on each other.

If you need to identify the data which is new , do it using VBA itself ; the code is straightforward and should be robust enough.

Formulae are acceptable if you are dealing with numbers ; to do text processing using formulae is unnecessary when VBA has enough text processing functions available.

Narayan
 
Thanks for the advice Narayan. Excel is slowly becoming a bit of a learning hobby for me. In the last six months I have learned a tonne, but have only begun to scratch the surface. The more I learn, the more I seem to be able to apply Excel to many various tasks. I am currently developing 5 projects that I personally use. It's kind of addicting because the more I learn, the more I tinker with and improve the functionality of my projects.

Learning a basic basis upon which to navigate VBA will be a summer learning project for me 2015. As of now, my knowledge of it is extremely limited. If I have a simple task, I can use the recorder function of excel to code it. If the task is more complex, I have to rely on the advice/knowledge of others.

Somendra, are the spaces causing the code not to work when I use the button on the separate data sheet?

Ideally, the event button would be on the data sheet and the names of any and all updated class lists will appear in the first column of that data sheet as well. I will probably hide this class import information sheet (sheet 1) later on in the development process.

If the main purpose of the code(pasting unique names) worked, but the Msg box was not in alignment with the pasting because it identified spaces, I could always take out the message box as the spaces are at the end and do not really show up. Of course the Msg box adds a certain kind of polish to the process.

Thanks,
 
@fantabulator

I did not got the used of formulas in your sheet, you said you will get new names from some other source as CSV, so ideally you will be importing the CSV in EXCEL, so need of formula. Remove them and than try to run the code as per your requirement of sheets, by making suitable changes in the code for the sheet references and it will work.

Or am I missing something than write back.

Regards,
 
The only formula I have now is the one in column h that concatenates the last and first names that are imported.

I did change some sheet names in the code for the button on the data entry sheet, but it does not seem to work as it does if I press the button when it is on the import sheet.

Maybe I did not change the names correctly. Is it possible for you to please check to see if I did it correctly?

Much thanks,
 
Thanks for your time...Still can't get the button on the first sheet to fill in the column on the second sheet. It counts the right number of names added, but does not add them correctly. It only adds one of them and adds it at the top of the list.

The button works perfectly on the import names data sheet. Perhaps I just need to accept that the button will be on the import data sheet and won't be able to hide that sheet.
 
You must log in or register to reply here.
Back
Top