PDA

View Full Version : VBA range.value and looping issues with selecting a range of cells



V_F
09-21-2018, 09:04 AM
Hello,

I created a macro that I had working when selecting 1 cell at a time in order to perform 1 power analysis test, whether the result was to get sample size (nB) or power (1-beta). I'm trying to update what I had so that I could perform multiple tests by selecting a range of cells for each variable. Since 1 of the fields will be left blank (the one I'm trying to compute), I seem to be having issues on how to set that value in order to run through the loop. I've attached a workbook with example and VBA code. I normally set it up as an add-in so that it can be easily accessed from the toolbar up top. The portion of the macro I'm concerned with is the code behind the 'PowerAnalysisPrompt'.

Ideally, I'd like to be able to select the range of cells for pA (A2:A16), pB (B2:B16), Kappa (C2:C16), and Alpha is optional (if not selected, default is 0.05). The goal of the macro is to either compute nB (Sample Size) or Beta. When computing nB, Beta will be known (as in the example). When computing Beta, nB will be known. Please note, the selected ranges always change (won't always be A2:A16, it could be anything the user selects, but it will be in a column and not a row. Once all necessary fields are selected, the user would select the top cell of the field they're trying to compute for as Output Range (F2 in this example), click Compute and the data would be computed and filled in for F2:F16.

I keep getting errors and am not sure how to proceed.

Any help is appreciated.