Anthony's VBA Forum Index Anthony's VBA Forum
Where the knowledge is shared
Menu
 Anthony's VBA Forum IndexHome Page
 Anthony's VBA Forum IndexForum Index
FAQFAQ
MemberlistMemberlist
UsergroupsUsergroups
RegisterRegister
ProfileProfile
Log in to check your private messagesMessages
Log inLogin/Out

Quick Search

Advanced Search

Links
Consulting
Products

Who's Online
[ Administrator ]
[ Moderator ]


SalesRepList Command Broken?

 
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA
View previous topic :: View next topic  
Author Message
kwright90



Joined: 23 Apr 2007
Posts: 4

PostPosted: Mon Jun 29, 2009 5:44 pm    Post subject: SalesRepList Command Broken? Reply with quote

Guys/Gals,

It seems I have done something to my VBA program... I did not build it so I am having problems understanding exactly what needs to be adjusted. It's really a long story how I got to this point, but long story short... A co-worker deleted the operational workbook so I had to go dig this one out from when it was first being built and start over.

This group of commands are programmed to pull Sales Rep names from a particular column and create a drop down list in a user form. For some reason I am getting a run-time arror and being promted to this section of my module when I click my "Create List" button.

The command line that the error promt highlights is as follows...
Contacts.cbxContacts.AddItem wsAgree2.Cells(lRow, 1)

I admit that I am not a seasoned VBA programmer. I need you alls help please. Thank you in advance.



Sub SalesRepList()

Set wsAgree = Workbooks("Agreements Tracking - Rebuild.xls").Sheets("Agreements Tracking")
Set wsAgree2 = Workbooks("Agreements Tracking - Rebuild.xls").Sheets("Contacts")
wsAgree2.Cells.ClearContents
lRow = 1

'This counts the number of rows in the worksheet that contain data and
'the number of columns with data
lLastRow = wsAgree.Cells(wsAgree.Rows.Count, "a").End(xlUp).Row
lLastCol = wsAgree.Cells(lRow, wsAgree.Columns.Count).End(xlToLeft).Column

'This creates a unique list of sales reps that will be used to populate a
'combo box. Puts the data in first vacant colum
Range(Cells(3, 6), Cells(lLastRow, 6)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsAgree2.Cells(1, 1), Unique:=True

'This section of code loads the combo box with the name of the sales reps
lRow = 1

lLastRow = wsAgree2.Cells(wsAgree2.Rows.Count, 1).End(xlUp).Row
For lRow = 1 To lLastRow
Contacts.cbxContacts.AddItem wsAgree2.Cells(lRow, 1)
Next lRow

'Causes the UserForm to appear
Contacts.Show

End Sub
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic     Anthony's VBA Forum Index -> General Excel VBA All times are GMT - 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2002 phpBB Group