Sample.xlsx
Option Explicit
Sub Randomish()
Dim xMin As Double
Dim xActual As Double
Dim xTarget As Double
Dim i As Long
Sheets("Sheet2").Activate
xTarget = Range("B1")
xMin = 100000000
Application.ScreenUpdating = False
For i = 1 To 100
Range("B6:F105").Formula = "=(RANDBETWEEN($B$3,$B$4)+(RANDBETWEEN(0,9)/100))"
Range("B6:F105").Copy
Range("B6:F105").PasteSpecial xlPasteValues
xActual = Range("H1")
If Abs(xTarget - xActual) / xTarget < 0.01 Then
MsgBox ("Run complete - difference is " & Format(Abs(xTarget - xActual) / xTarget, "0.00%"))
Exit Sub
End If
If Abs(xTarget - xActual) < xMin Then xMin = Abs(xTarget - xActual)
Next
MsgBox ("Sorry - couldn't get within reach. Closest was " & Format(xMin / xTarget, "0.00%") & ". Please try again.")
Application.ScreenUpdating = True
End Sub
Regards,Title | # Comments | Views | Activity |
---|---|---|---|
VB loop to open the file from the network drive | 30 | 67 | |
Excel VBA - Gather data into a single summary sheet from multiple worksheets | 7 | 24 | |
Excel - Increse columns in range by 5 | 6 | 21 | |
Rearrange Macro | 7 | 22 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
23 Experts available now in Live!