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.

 

[laxcat73]

To the top

 

[laxcat73]

Need help!

 

[kpark91]

You haven't even mentioned where we will be looking up to... to get the missing datapoints.

Moreover, it IS possible to check the row and column with formulas using match and indexes with vlookup.

However, VBA will be easier to represent over the keyboards.

 

[laxcat73]

I'm trying to get data from a UserForm and enter it into the unique cells. For example, I enter in reference number and a date and it enters that date into the column, same row as the reference number entered.

For example:

First Pay DatE:
Reference #: 00005
Date Paid: 7/14/11
[Click OK Command Button]

I would then want it to populate cell C6 with [7/14/11] by using [Reference #] as row reference.

 

[kpark91]

This is a pseudo-code.

Dim LR&, i&
LR = Get last row of column B
For i = 1 To LR
    If Range("B" & i).Value = UserformName.txtReference.Text Then
        Range("C" & i).Value = UserForm.txtDatePaid.Text
    End If
Next i

so, now your job is to search for getting last row of the worksheet in column B.

 

[laxcat73]

This is a pseudo-code.

Dim LR&, i&
LR = Get last row of column B
For i = 1 To LR
    If Range("B" & i).Value = UserformName.txtReference.Text Then
        Range("C" & i).Value = UserForm.txtDatePaid.Text
    End If
Next i

so, now your job is to search for getting last row of the worksheet in column B.

I don't quite understand what you mean by now I have to search for the last row. I want the code to work if I have to enter say Reference # 00004 as well, even though there are data points after that one.

Thank you for your help.

EDIT: Ah I see that it searches for reference number and places date in next column. Thanks. I'm not sure what you meant after that though

 

[kpark91]

Instead of asking for answers, why not try to learn the half-answers given?
I'm glad you were able to figure out what the for loop was doing but the question is "will you be able to solve your own problem?"

You've been given enough information in my previous post.

The only piece of code you need to find is

LR = Get last row of column B in a worksheet

Hint: Google it.

I'm willing to explain what the code is doing, if you want.

 

[laxcat73]

Instead of asking for answers, why not try to learn the half-answers given?
I'm glad you were able to figure out what the for loop was doing but the question is "will you be able to solve your own problem?"

You've been given enough information in my previous post.

The only piece of code you need to find is

LR = Get last row of column B in a worksheet

Hint: Google it.

I'm willing to explain what the code is doing, if you want.

If you know of some website that has a list of expressions and explains them then I'd be better off that way. No offense, but you're tone comes off as somewhat condescending and I'd rather someone else help if that's the case. I've tried googling without luck. I would prefer to see examples of code used in certain circumstances as that's the best way I figure out what it does and it's limitations.

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.

 

[kpark91]

If you know of some website that has a list of expressions and explains them then I'd be better off that way. No offense, but you're tone comes off as somewhat condescending and I'd rather someone else help if that's the case. I've tried googling without luck. I would prefer to see examples of code used in certain circumstances as that's the best way I figure out what it does and it's limitations.

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.

I was only meaning to introduce you to VBA furthermore and getting the last row was the way to introduce you to the syntax of VBA, structure of VBA, string concatenation, Range objects and more...
Killing several birds with one stone.

Googling "getting last row of column B in VBA" gave me these results
http://www.google.ca/search?hl=en&s...+in+vba&rlz=1R2ADRA_enCA346&aq=f&aqi=&aql=&oq=

and in the third website
http://en.allexperts.com/q/Excel-1059/2011/3/VBA-code-locate-cell.htm

there is the answer.

Sub ABC()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
End Sub

Sorry if my tone sounded condescending to you.
It seems that C programming forum attitudes are rubbing off on me.
I'll keep that in mind.