VBA Column Formula Question

[zdodson]

Hello all,

I am trying to create a VBA userform that creates a composite score based on the data in this userform. The link is to a viewable google doc that has a screen shot of the VBA userform: https://docs.google.com/file/d/0B3NaddYem8bKN2ZPa1NXVWxIeXc/edit?usp=sharing

The formula is a bit complex. Below is the formula:

=IF(OR($B2="",$C2="",$D2=""),"",SUM(IFERROR(VLOOKUP($B2,$H$2:$K$87,4,FALSE),0),IFERROR(VLOOKUP($C2,$I$2:$K$102,3,FALSE),0),IFERROR(VLOOKUP($D2,$J$2:$K$92,2,TRUE),0)))

Basically, the formula checks to see if all three scores are available before generating a composite score. If all three values are not present, then it will return a blank cell.

Here is what the chart looks like:

Date	Pull-Ups	Crunches	3-Mile Run	PFT Score
1-Mar-2013	15	100	0:19:10	269
2-Mar-2013	16	98	0:19:00	273
3-Mar-2013	20	98	0:18:10	297
4-Mar-2013	20	99	0:18:09	299

<tbody>
</tbody>

Now for the question:

How do I create a formula so that when I hit my "Add Data" command button, it generates that composite score using the aforementioned formula? Basically, I need a VBA code that will find the first empty row in the data set, add the data, and then fill in the composite PFT score.

Thanks for your all's help!

Zack

 

[Andrew Poulsom]

What are the names of your TextBoxes? To get the next empty row:

Dim r As long
r = Range("A" & Rows.Count).End(xlUp).Row + 1

To get the syntax for your formula record a macro while entering it manually (if it's already entered somewhere just select it and press F2 then Enter).

 

[zdodson]

What are the names of your TextBoxes? To get the next empty row:

Dim r As long
r = Range("A" & Rows.Count).End(xlUp).Row + 1

To get the syntax for your formula record a macro while entering it manually (if it's already entered somewhere just select it and press F2 then Enter).

Thanks for your advice already!

The names of the text boxes are simple: txtDate, txtPullUps, txtCrunches, txtMile.

 

[Andrew Poulsom]

Try:

Private Sub cmdAddData_Click()
    Dim r As Long
    r = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & r).Value = DateValue(txtDate.Text)
    Range("B" & r).Value = Val(txtPullUps.Text)
    Range("C" & r).Value = Val(txtCrunches.Text)
    Range("D" & r).Value = TimeValue(txtMile.Text)
    Range("E" & r).FormulaR1C1 = _
        "=IF(OR(RC2="""",RC3="""",RC4=""""),"""",SUM(IFERROR(VLOOKUP(RC2,R2C8:R87C11,4,FALSE),0),IFERROR(VLOOKUP(RC3,R2C9:R102C11,3,FALSE),0),IFERROR(VLOOKUP(RC4,R2C10:R92C11,2,TRUE),0)))"
End Sub

You will probably need to add some error checking, particularly for data and time.

 

[zdodson]

Looked good at first, but it produced a Run-Time Error '13': Type mismatch error.

 

[Andrew Poulsom]

I said that there was no error checking. Which line causes the error?

 

[zdodson]

I said that there was no error checking. Which line causes the error?

I had thought you were referencing code that would check to see if the text boxes were filled or not.

And it is line 6 aka the time value line.

 

[Andrew Poulsom]

So what was in txtMile when you got the error? It needs to be a time like 00:18:10.

 

[zdodson]

So what was in txtMile when you got the error? It needs to be a time like 00:18:10.

That must be it. I originally had the column formatted as h:mm:ss because I assumed that I could complete a 3-mile in under two hours

I'll make the formatting changes and update you on whether I got it to work.

Cheers,

Zack

 

[zdodson]

It's currently not putting the formula in the last row of the data set. Once more, it adds zeros in the subsequent row, but only for the first three columns. Would the lack of incorporation of error checking affect that at all?