Check if values entered in userform match existing data

[alex2750]

Hi all,

I am building a legislation tracker to follow any bills that I manually enter into the spreadsheet. I have two tabs with data - RawData and CleanData. RawData has each instance of a bill; if a bill has progressed through the legislature, I will enter each update as a separate entry. I built a macro to take each unique state/chamber/bill number combination and copy it over to CleanData.

Because I didn't want to have to re-enter all of the information for updates of bills that I am already tracking, I built a macro to check if the first three columns (A,B,C) match the inputs on the userform (StateComboBox, ChamberComboBox, BillNumberTextBox). If there is a match (and I am already tracking the bill), it will copy columns D:I from the previous entry of the bill. If there is no match, it pulls up another user form to capture the rest of the information.

However, I have an issue when there are multiple bills from the same state. The macro will recognize that the first bill is in the tracker, but treats any other bills from that state as new (e.g. if NY H 1 and NY H 2 are in the tracker, the macro sees NY H 1 as an existing bill but thinks NY H 2 is new).

Dim found As Boolean: found = FalseDim chamb, bill As Variant
Dim statename As Variant
Dim findrow, lastrow As Long


'Make RawData A2 active
Worksheets("RawData").Activate
Worksheets("RawData").Cells(2, 1).Select


'Define findrow
findrow = ActiveCell.Row


'Define LastRow
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row


'Define statename
statename = Range("A" & findrow & ":A" & lastrow).Value


'Loop through the state names of all bills
For Each statename In Range("A" & findrow & ":A" & lastrow)
    
    'Create INDEX(MATCH()) formulas to find chamber and bill number
    chamb = Application.Index(Range("B" & findrow & ":B" & lastrow), Application.Match(statename.Value, Range("A" & findrow & ":A" & lastrow), 0), 1)
    bill = Application.Index(Range("C" & findrow & ":C" & lastrow), Application.Match(statename.Value, Range("A" & findrow & ":A" & lastrow), 0), 1)


    'Determine if bill is already in tracker
    If statename.Value = StateComboBox.Value And chamb = ChamberComboBox.Value And bill = CDbl(BillNumberTextBox.Value) Then
        found = True
        Exit For
    End If
Next statename

I think my problem is that For Each loops don't select each value in the loop; I tried to account for that by selecting A2 before using INDEX(MATCH()) but apparently that'll only return the first match for each state name.

Any advice or guidance would be a lifesaver!

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()
    Dim sh As Worksheet, r As Range, b As Range, cell As String
    Dim found As Boolean
    
    found = False
    Set sh = Sheets("RawData")
    Set r = sh.Columns("A")
    Set b = r.Find(StateComboBox.Value, LookAt:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        cell = b.Address
        Do
            If sh.Cells(b.Row, "B").Value = ChamberComboBox.Value And _
               sh.Cells(b.Row, "C").Value = CDbl(BillNumberTextBox.Value) Then
                found = True
                MsgBox "Found state in row : " & b.Row
                Exit Do
            End If
            Set b = r.FindNext(b)
        Loop While Not b Is Nothing And b.Address <> cell
    End If
End Sub

 

[alex2750]

Thank you so much, it works perfectly now!

My updated code is:

Dim sh As Worksheet, r As Range, b As Range, cell As String, emptyrow As Long, lastrow As Long
    Dim found As Boolean
    
    found = False
    Set sh = Sheets("RawData")
    Set r = sh.Columns("A")
    Set b = r.Find(StateComboBox.Value, LookAt:=xlWhole, LookIn:=xlValues)
    emptyrow = WorksheetFunction.CountA(sh.Range("A:A")) + 1
    lastrow = WorksheetFunction.CountA(sh.Range("A:A"))
    If Not b Is Nothing Then
        cell = b.Address
        Do
            If sh.Cells(b.Row, "B").Value = ChamberComboBox.Value And _
               sh.Cells(b.Row, "C").Value = CDbl(BillNumberTextBox.Value) Then
                found = True
                Exit Do
            End If
            Set b = r.FindNext(b)
        Loop While Not b Is Nothing And b.Address <> cell
    End If




    If found Then
        sh.Cells(emptyrow, 1).Value = StateComboBox.Value
        sh.Cells(emptyrow, 2).Value = ChamberComboBox.Value
        sh.Cells(emptyrow, 3).Value = BillNumberTextBox.Value
        sh.Cells(emptyrow, 4).Value = sh.Cells(b.Row, "D").Value
        sh.Cells(emptyrow, 5).Value = sh.Cells(b.Row, "E").Value
        sh.Cells(emptyrow, 6).Value = sh.Cells(b.Row, "F").Value
        sh.Cells(emptyrow, 7).Value = Date
        sh.Cells(emptyrow, 8).Value = StatusTextBox.Value
        sh.Cells(emptyrow, 9).Value = sh.Cells(b.Row, "I").Value
    Else
        sh.Cells(emptyrow, 1).Value = StateComboBox.Value
        sh.Cells(emptyrow, 2).Value = ChamberComboBox.Value
        sh.Cells(emptyrow, 3).Value = BillNumberTextBox.Value
        sh.Cells(emptyrow, 8).Value = StatusTextBox.Value
        
        'Pull up user form to get additional data
        BillTrackerUserForm.Hide
        BillInfoUserForm.Show
    End If
    
sh.Range("A" & lastrow & ":I" & lastrow).copy
sh.Range("A" & emptyrow & ":I" & emptyrow).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False


Unload Me

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.