16372

Excel data checking

Question:

Excel data checking

I have an Excel UserForm which contains the following fields; date, name and work.

In the event that the worksheet contains the same date and name pairing, I want data transfer from the Userform blocked.

Example

A worksheet has one entry:

<ul><li>Column A has the date 1/1/2017 </li> <li>Column B has the name john</li> </ul>

For the worksheet above, the following rules apply:

<ol><li>Information can be passed from the UserForm for 3/1/2017 and john.</li> <li>Information can be passed from the UserForm for 1/1/2017 and jane.</li> <li>Information cannot be passed from the UserForm for 1/1/2017 and john.</li> </ol>

Answer1:

Dim k As Long Dim matched As Boolean matched = False ' Loop over all used rows For k = 1 to ActiveSheet.UsedRange.Rows.Count ' Check if concatenated string of date & name is unique ' e.g. '01/01/17john' If ActiveSheet.Cells(k, "A").Text & ActiveSheet.Cells(k, "B").Text = _ myUserForm.DateField.Text & myUserForm.NameField.Text Then MsgBox "This Name / Date combination is not unique, pick again" matched = True Exit For End If Next k If matched = False Then ' Name/Date combination is unique, send data to sub or whatever... End If

Recommend

  • How to query for all direct subclasses in SPARQL?
  • Parsing title and link from html page
  • PHP making a download counter without leaving the current page
  • Add loop to vba code for excel
  • VBA - Add item to combobox from dynamically created listview
  • In excel have to search for specific string in cell and apply style to that particular string
  • R Split String By Delimiter in a column
  • Replace everything except numbers in a string vb6
  • Is there a shorthand for assigning from $1…$n after matching in ruby
  • Postgresql regexp_replace
  • How to find documents with exactly the same array entries as in a query
  • Does Perl currently (5.8 and 5.10) make any promises about the order alternations will be used?
  • overlapping appointments using the entity framework
  • Is an if-let or a normal if condition better?
  • matching similar elements in between two lists
  • Unix Network Programming Clarification
  • How to validate a year I enter in textbox using jquery rule?
  • Double-click autofill - dynamic based on adjacent cell
  • Excel VBA URLDownloadToFile Error for https Ressource
  • Laravel at least one field is required
  • Creating a Multidimensional, Associative Array in VBScript
  • Django model for a Postgres view
  • Is there some graphical way to create my own configuration file on SonarLint?
  • Conversion from string “a” to type 'Boolean' is not valid
  • Reduction and collapse clauses in OMP have some confusing points
  • Insert new calendar with SyncAdapter- Calendar API Android
  • htaccess add www if not subdomain, if subdomain remove www
  • Groovy: Unexpected token “:”
  • MongoDb aggregation
  • Replace value with Factor in r data.table
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • DirectX11 ClearRenderTargetViewback with transparent buffer?
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • Web-crawler for facebook in python
  • What do the 'size' numbers mean in the windbg !heap output?
  • Transpose CSV data with awk (pivot transformation)
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • WPF Applying a trigger on binding failure