Thursday, March 31, 2016

Fast Solution to Error: Adding unique records in Excel using class module, collections and 'key'

Adding unique records in Excel using class module, collections and 'key'

I would like to enter only unique records in an Excel worksheet using Excel VBA, collections and a key with the record.
Example: I would like to add a userid, firstname and last name.
No duplicates.
Thanks.
Sample code:
Sub addEmployee()
Dim colEmployees As New Collection
Dim recEmployee As New clsEmployee
Dim erow As Long
Dim answer As String
answer = "y"
Do While answer = "y"
answer = InputBox("Do you wish to enter a new record?Please enter y or n only!")
If answer = "n" Then Exit Sub
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
recEmployee.ID = InputBox("Enter Employee ID")
Cells(erow, 1) = recEmployee.ID
recEmployee.FirstName = InputBox("Enter First Name")
Cells(erow, 2) = recEmployee.FirstName
recEmployee.LastName = InputBox("Enter Last Name")
Cells(erow, 3) = recEmployee.LastName
------> What code can I add here?
colEmployees.Add recEmployee, recEmployee.ID
End sub
 
Class module:
Public ID As String
Public FirstName As String
Public LastName As String

Solutions to the Problem Adding unique records in Excel using class module, collections and 'key'

Download SmartPCFixer for Free Now

Perhaps:

Sub addEmployee()
    Dim colEmployees     As Collection
    Dim recEmployee      As clsEmployee
    Dim erow             As Long
    Dim answer           As String

    Set colEmployees = New Collection

    answer = "y"
    Do While answer = "y"
        answer = InputBox("Do you wish to enter a new record?Please enter y or n only!")
        If answer = "n" Then Exit Sub
        erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Set recEmployee = New clsEmployee
        recEmployee.ID = InputBox("Enter Employee ID")
        On Error Resume Next
        colEmployees.Add recEmployee, recEmployee.ID
        If Err.Number = 0 Then
            Cells(erow, 1) = recEmployee.ID
            recEmployee.FirstName = InputBox("Enter First Name")
            Cells(erow, 2) = recEmployee.FirstName
            recEmployee.LastName = InputBox("Enter Last Name")
            Cells(erow, 3) = recEmployee.LastName
        Else
            Err.Clear
            MsgBox "Duplicate ID.
Data not entered"
        End If
    Loop
End Sub

Running System Update Readiness Command

On running the System Update readiness Command, it will Detect & Replace Incorrect Registry data with the Correct one. Which may ultimately Solve Adding unique records in Excel using class module, collections and 'key' in Windows.

  1. Open CMD as Administrator by Simply Right Clicking on Start button > Command Prompt (Admin).
  2. Now Type or Copy paste "DISM.exe /Online /Cleanup-image /Scanhealth" and hit Enter,
  3. Now again Type or Copy paste this "DISM.exe /Online /Cleanup-image /Restorehealth" and hit Enter.
  4. Close the Command Prompt Window because we are done.

Try and see if you still get the same error Adding unique records in Excel using class module, collections and 'key' in your Windows.

Note: You may be prompted to enter your administrator's password when you try to execute certain system tasks!

Recommended Method to Repair the Problem: Adding unique records in Excel using class module, collections and 'key':

How to Fix Adding unique records in Excel using class module, collections and 'key' with SmartPCFixer?

1. You can Download Error Fixer here. Install it on your system. When you open SmartPCFixer, it will perform a scan.

2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All.

3. When the Fixing part is finished, your computer has been speeded up and the errors have been removed


Related: How to Fix - 64g ssd with a 500g regular drive?,Allow Unhide Rows in Protected Workbook [Solved],[Solved] Get in Excel 2007 data from Access 2007 out of self-built Queries,[Solution] How can I temporarily disable 'service manager' to install Adobe flashplayer?,[Anwsered] When I try to watch a flash video, I am told occasionally that I don't have Adobe Flash.,Solution to Error: Black screen during boot sequence,[Solved] Can't restore Windows 7 64-bit from external hard drive,How to Fix - IE 11 Enhance Protect Mode reset issue with add-on's?,Solution to Error: Internet Explorer 9 update/install error - Error Code 80092004,Upgrading to IE 8 causes cookies to get deleted when starting IE [Anwsered],Solution to Problem: All programs try to start from windows component
,Troubleshoot:External Hard Drive not listed in Windows 7 backup wizard Error
,How to Fix Error - Getting an error "not connected to the internet" while trying to install Samsung Kies?
,How to Fix - Internet Explorer shuts down and reopens tab when attaching to email or uploading files.?
,Fast Solution to Problem: Sending Error Message
,[Anwsered] Thinkpad 8611 Boot,How to Resolve - Svchost Helper?,Fast Solution to Problem: L30 101 Driver Windows 7,Troubleshooter of Error: Io Device,How to Fix Error - Dell Laptop Code 39?
Read More: Adding saved shortcuts to internet explorer from documents folder [Solved],How Can You Fix - After downloading Business-In-A-Box install updates and it states it is not compatible with Windows 7?,Troubleshooting:Adobe Encore Freezes,Add Power Button to the charms bar (suggestion - reposted). Make Windows 8 shut down in two clicks! [Solved],Solution to Problem: additional monitors not working after 8.1 upgrade,a file called mDNSResponse.exe. is causing bonjour not to operate properly,what should I do?,A QUESTION USING THE "IF'S" Formula.,A continuos flashing window with which title is C:Windows\System32\cmd.exe, and has the following message: The syntax of the command is incorrect.,Acrobat compatibility issue and you tube problems____,ActiveX on IE 9 not loaded

No comments:

Post a Comment