Forum Discussion
Bootstrap
Try this:
Sub Resample() Dim NumSamples As Long Dim LastRow As Long NumSamples = Val(InputBox("How many samples do you need (31 - 500)?")) If NumSamples < 31 Or NumSamples > 500 Then MsgBox "Invalid input. Must be between 31 and 500!", vbExclamation Exit Sub End If Application.ScreenUpdating = False LastRow = Range("A" & Rows.Count).End(xlUp).Row Range("AG26:SH" & LastRow).ClearContents With Range(Cells(26, 33), Cells(LastRow, NumSamples + 2)) .FormulaR1C1 = "=INDEX(RC3:RC32,RANDBETWEEN(1,30))" .Value = .Value End With Application.ScreenUpdating = True End Sub
Thank you for your reply! So how do I fix it so I can control the resampling process? I'd like to have a better control of the resampling because I will be editing in the sheet, and I don't to continue to resample.
Regards,
Sam
In the attached version, the formulas have been replaced with values.
I added a command button that runs a macro to resample the values.
You can view the code in the Visual Basic Editor.
Hi Hans,
Thank you so much for your help. In the attached sheet that I sent you earlier, I created a tab "Bootstrap (2)". The original samples are in rows numbered(1-3), but each sample row is a separate or independent row. So, I numbered them into rows number 1-3 (red, green, blue). But in reality I have 6000 independent rows in my lab test data Excel sheet. How do you make VBA code so it does the resampling for each row independently for the three rows? I can fix the VBA code once i see how you do it?
Regards,
SamHi Hans,
I attached two documents. Let me try again. In my lab tests, i have 30 gas releases in the wind tunnel. For each gas release the the measurement of concentration vs time goes up Excel row 6025. i'd like to generate 500 resamples for each row. Each row represents one time, but we did 30 releases( experiments). Please let me know if you have any questions.
Thanks,
Sam