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]

So, do you know any websites that have a list of the code with brief explanations? There are terms and expressions which I feel I need to be accustomed to since I have just been introduced to VBA only yesterday.

Ummm. try searching up for "VBA tutorials" and see if that gives you anything.

I think MrExcel tutorials were great as well as ozgrid's..

After you've taken a look at that, look for "Chip Pearson"
He's a beast in VBA/Excel in general.

 

[laxcat73]

Frist, thank you for taking your time to help me. I don't want you to think I'm unappreciative.

Second, I was a bit confused because I figured out that what you gave me is not my desired goal. I am not looking for the last row of a column, merely looking to match the row with the reference number in it.

If I'm being unclear, please ask me to explain better.

Here is a picture of the userform I would like to use:

My objective are as follows:

• Enter "Reference #"
• Enter "First Installment Date" -OR- "Reason Not Paid"

Click create and the macro populates the cell in the corresponding to that specific reference #. Lastly, if there is no "First Installment Date" value, then it would enter "Reason Not Paid" in the next column.

Here is a screenshot of the actual spreadsheet:



Column N/O are the ones which I want to fill based on the reference lookup of column D.

 

[kpark91]

Hi, the code needs to grab the last row of the data in order to loop through column B to find the reference number you have inputted.

Thank you for the screen shots, they definitely help.

In your userform module, try

Note that you must alter the code to fit your objects names.

Private Sub CreateEntry_Click()
    Application.EnableEvents = False
    Dim LR&, i&
    LR = Worksheets("GridView").Range("B" & 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 Range("B" & i).Value = Me.ReferenceNumber.Text Then
            Range("N" & i).Value = Me.PayDate.Text
            Range("O" & i).Value = Me.ReasonNotPaid.Text
        End If
    Next i
    Application.EnableEvents = True
End Sub

 

[laxcat73]

So far, this is the code. While the userform works as I would like, it is not inputting the data into column N/O like I want.

Private Sub cmdCreate_Click()
    Application.EnableEvents = False
    Dim LR&, i&
    LR = Worksheets("Master").Range("B" & 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 Range("D" & i).Value = Me.txtRef.Value Then
            Range("N" & i).Value = Me.txtFirst.Value
            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
 
[COLOR=red]Private Sub Label2_Click()[/COLOR]
[COLOR=red]End Sub[/COLOR]

Also, the highlighted in red keeps coming up as a random sub each time I go back into the code to look at it. It might be something to have to do with your comment about the text. I changed that to be value but that didn't work either.

 

[laxcat73]

I changed the "B" in

LR = Worksheets("Master").Range("B" & Rows.Count).End(xlUp).Row

to D, but I think the code is having it look for an empty cell in column D; is that correct?

 

[laxcat73]

Lastly, the sheet GridView is just a sheet that allows a macro to copy/paste values from Master so people can view/manipulate the information without screwing up any values.

 

[kpark91]

Yes. you have changed B to D correctly.

Could I see the code you have altered?
lastly, is it working as intended?

If it isn't, try debugging the code and stepping through it and see what action it is not doing or it is doing that is wrong.

 

[laxcat73]

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 Range("D" & i).Value = Me.txtRef.Value Then
            Range("N" & i).Value = Me.txtFirst.Value
            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
Private Sub Label2_Click()
End Sub

The UserForm works fine but the data is just not input.

 

[laxcat73]

Debugging comes up with nothing.

 

[kpark91]

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?