PDA

View Full Version : VBA Coding help - Multi-page User Form, vlookup, and INTERCEPT



dcalder
09-15-2016, 01:46 PM
Hello VBA Express Community,

This is my first post. I am a newbie to VBA coding and would appreciate some help.

BACKGROUND INFO

I am trying to create a userform that would eventually allow a user to conduct a linear regression analysis based on two variables, take the Y-intercept and subtract it from the known y values if a y value is greater than the y-intercept; finally summing those results.

The known x-values were pulled from a vlookup table on another worksheet, with the lookup value selected from a drop down list.

Here are the formulas I created in excel:

=INTERCEPT(D12:D23,((INDEX('HDD Vlookup (2)'!$B:$M,MATCH('BCA - Main Page'!$C$6,'HDD Vlookup (2)'!$A:$A,FALSE),1)):(INDEX('HDD Vlookup (2)'!$B:$M,MATCH('BCA - Main Page'!$C$6,'HDD Vlookup (2)'!$A:$A,FALSE),12))))

The result of the above formula is on cell G40

Then my desired information was calculated based on the following second formula

=((IF(D12>$G$40,D12-$G$40,0)+(IF(D13>$G$40,D13-$G$40,0)+(IF(D14>$G$40,D14-$G$40,0)+(IF(D15>$G$40,D15-$G$40,0)+(IF(D16>$G$40,D16-$G$40,0)+(IF(D17>$G$40,D17-$G$40,0)+(IF(D18>$G$40,D18-$G$40,0)+(IF(D19>$G$40,D19-$G$40,0)+(IF(D20>$G$40,D20-$G$40,0)+(IF(D21>$G$40,D21-$G$40,0)+(IF(D22>$G$40,D22-$G$40,0)+(IF(D23>$G$40,D23-$G$40,0))))))))))))))


My question is, I want to create a user form that allows a user to manually enter in the known y-values, with the x-values being identified based on the users selection of the lookup value.

How would someone suggest the best way would be to go about generating a userform to do this?

Thanks
Devon