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:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]Pull-Ups[/TD]
[TD="align: center"]Crunches[/TD]
[TD="align: center"]3-Mile Run[/TD]
[TD="align: center"]PFT Score[/TD]
[/TR]
[TR]
[TD="align: center"]1-Mar-2013[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0:19:10[/TD]
[TD="align: center"]269[/TD]
[/TR]
[TR]
[TD="align: center"]2-Mar-2013[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]0:19:00[/TD]
[TD="align: center"]273[/TD]
[/TR]
[TR]
[TD="align: center"]3-Mar-2013[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]0:18:10[/TD]
[TD="align: center"]297[/TD]
[/TR]
[TR]
[TD="align: center"]4-Mar-2013[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]0:18:09[/TD]
[TD="align: center"]299[/TD]
[/TR]
</tbody>[/TABLE]
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
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]Pull-Ups[/TD]
[TD="align: center"]Crunches[/TD]
[TD="align: center"]3-Mile Run[/TD]
[TD="align: center"]PFT Score[/TD]
[/TR]
[TR]
[TD="align: center"]1-Mar-2013[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0:19:10[/TD]
[TD="align: center"]269[/TD]
[/TR]
[TR]
[TD="align: center"]2-Mar-2013[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]0:19:00[/TD]
[TD="align: center"]273[/TD]
[/TR]
[TR]
[TD="align: center"]3-Mar-2013[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]98[/TD]
[TD="align: center"]0:18:10[/TD]
[TD="align: center"]297[/TD]
[/TR]
[TR]
[TD="align: center"]4-Mar-2013[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]0:18:09[/TD]
[TD="align: center"]299[/TD]
[/TR]
</tbody>[/TABLE]
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