VBA Entering Missing Data Points

[laxcat73]

This is what my data looks like (small sample)

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; WIDTH: 484px; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; HEIGHT: 153px; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 88px"><COL style="WIDTH: 91px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Name</TD><TD>Reference #</TD><TD>First Pay Date</TD><TD>Reason not paid</TD><TD>Second pay date, etc.</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">Smith , Dan</TD><TD style="TEXT-ALIGN: right">00001</TD><TD style="TEXT-ALIGN: right">1/17/11</TD><TD style="TEXT-ALIGN: right">-</TD><TD style="TEXT-ALIGN: right">6/24/11</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">Doe, John</TD><TD style="TEXT-ALIGN: right">00002</TD><TD style="TEXT-ALIGN: right">7/11/11</TD><TD style="TEXT-ALIGN: right">-</TD><TD style="TEXT-ALIGN: right">-</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">Roth, Joe</TD><TD style="TEXT-ALIGN: right">00003</TD><TD style="TEXT-ALIGN: right">-</TD><TD style="TEXT-ALIGN: right">-</TD><TD style="TEXT-ALIGN: right">-</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">Rodriguez, Juan</TD><TD style="TEXT-ALIGN: right">00004</TD><TD style="TEXT-ALIGN: right">-</TD><TD style="TEXT-ALIGN: right">-</TD><TD style="TEXT-ALIGN: right">-</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>

Ruth, Babe

​

</TD><TD>00005

​

</TD><TD>-

​

</TD><TD>-

​

</TD><TD>-

</TD></TR></TBODY></TABLE>




I have created a UserForm with the following functions:

• Enter Reference #
• Enter First Pay Date
• Enter Reason not paid, if applicable

What I want to achieve is to have the macro run a lookup based on reference number and column to come to a point. In this example, I need to fill in First Pay Date for Joe Roth: C4. The function needs to match reference number (row) to First Pay Date (column) for the data entry. A condition must exist to allow a reason be enetered if the employee is not paid. I also need a condition that will give an error if BOTH are filled. Only one of the two (First Pay Date and Reason not paid) can be filled at any time.

Anyone have any ideas? I'm pretty new to VBA so I don't know how to write it effectively yet.

 

[kpark91]

I'll brb. Gonna go have my lunch now;;

 

[laxcat73]

Ok. Let's make sure of couple of things.

1) What format is your column D in? (it must be text)
2) Are you using textboxes where you are inputting the values you want to insert? (if you are, use .Text instead of .Value)
3) When you meant debugging comes up with nothing: what do you mean? Does it skip the If statement as in not enter the code inside at all?

1) Ah. Column D is a formula with a number result. Will formatting it differently save it as text?
2) Yes, text boxes are the input cells.
3) I mean it skipped the whole code you gave me. It goes straight to the "Cancel" command button entry I made.

 

[laxcat73]

1) Ah. Column D is a formula with a number result. Will formatting it differently save it as text?
2) Yes, text boxes are the input cells.
3) I mean it skipped the whole code you gave me. It goes straight to the "Cancel" command button entry I made.

1) I formatted as text without improvement. The formula still works fine but now it's formatted as text.

 

[kpark91]

Oops! Forgot reference the worksheets. :P

Private Sub cmdCreate_Click()
    Application.EnableEvents = False
    Dim LR&, i&
    LR = Worksheets("Master").Range("D" & Rows.Count).End(xlUp).Row
    For i = 4 To LR
        'Case-sensitive, space-sensitive..
        'Make sure column B is in texts if you're not allowed to do that, you can alter the code or ask me.
        If Worksheets("Master").Range("D" & i).Value = Me.txtRef.Value Then
            Worksheets("Master").Range("N" & i).Value = Me.txtFirst.Value
            Worksheets("Master").Range("O" & i).Value = Me.txtPaid.Value
        End If
    Next i
    Application.EnableEvents = True
    
'Clear data
Me.txtRef.Value = ""
Me.txtFirst.Value = ""
Me.txtPaid.Value = ""
Me.txtRef.SetFocus
End Sub
Private Sub cmdCancel_Click()
  Unload Me
End Sub

 

[laxcat73]

Got it working!!! Thank you!!

The only issue I had thought of after this was that .Value needed to be .Text in order to lookup the correct thing, seen here:

 If Worksheets("Master").Range("D" & i).Value = Me.txtRef.Value

So I changed that to Me.txtRef.Text and it works!!

Thank you so much for your help.

 

[kpark91]

Glad it has worked for you.
Thanks for your feedback.