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
Hans,
I am attaching the actual Excel Sheet that shows 30 gas releases data. Each release is an experiment on its own. The gas concentrations in the air is a measured with a device and it reads it at different relative times which is shown in A26-A6025. At each relative time you will see the measurements for each relative time. In this experiment 30 samples per relative time is not enough. i need to generate resamples for each row(relative time) for up to 500. I am not sure if you can have the VBA code ask the question how many resamples i need so i can type the number<=500. if i decide to change the numbers of resamples, have it delete the existing and regenerate new resamples based on the new resampling number. this is sheet has the data from lab experiment.
it starts from A26:AF26 and up to row 6025.
Thanks a lot!
Sam
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- I appreciated it a lot. Thank you so MUCH Hans!
Sam