Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
'apply random values
For r = 0 To lngFreq - 1
If LCase(Range("C11")) = "yes" Then
Cells(r + 13, 3).Value = dtStart + (dblTime * r)
End If
If LCase(Range("D11")) = "yes" Then
Cells(r + 13, 4).Value = WorksheetFunction.RandBetween(intMin, intMax)
End If
Next r
If Not tTimer Then
MsgBox ("Done.")
Else
'RunWhen = Now + TimeSerial(0, 0, Range("C7"))
StartTimer Range("C7")
End If
lngNewRandomNumber = intMin + Int(Rnd * (intMax-intMin+1))
intmin=5
intmax=25
application.calculation=xlCalculationManual
activesheet.range("$O$6:$T$11").formula="=randbetween(" & intMin & "," & intMax & ")"
activesheet.calculate
activesheet.range("$O$6:$T$11")
To this:activesheet.range("YourNamedRangeName")
substituting the actual name of the range for YourNamedRangeName
I want to refer the date/time and changes to the numbers on another sheet and don't know how to do that without using a range name.Maybe I didn't understand this. Do you have named ranges or not?
activesheet.range(activesheet.range("O6"),activesheet.range("O6").Offset(rowcount, colcount))
activesheet.range(activesheet.Cells(15,6),activesheet.Cells(21,19))
set rngUpperLeft = activesheet.range("O6")
set rngLowerRight = activesheet.range("AA19")
activesheet.range(rngUpperLeft, rngLowerRight)
'apply random values
Application.ScreenUpdating = False
For r = 0 To lngFreq - 1
If LCase(Range("C11")) = "yes" Then
Cells(r + 13, 3).Value = dtStart + (dblTime * r)
End If
If LCase(Range("D11")) = "yes" Then
Cells(r + 13, 4).Value = intMin + Int(Rnd * (intMax - intMin + 1))
End If
Next r
Application.ScreenUpdating = True
Populating and pushing an array of data would look something like this:Option Explicit
Dim vData As Variant
Application.ScreenUpdating = False
ReDim vData(0 To lngFreq - 1, 1 To 2)
'apply random values
For r = 0 To lngFreq - 1
If LCase(Range("C11")) = "yes" Then
vData(r, 1) = dtStart + (dblTime * r)
End If
If LCase(Range("D11")) = "yes" Then
vData(r, 2) = intMin + Int(Rnd * (intMax - intMin + 1))
End If
Next r
With Worksheets("Realtime Data")
.Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq)).Value = vData
End With
Application.ScreenUpdating = True
Please note that you should always include an Option Explicit statement in all your modules and forms.
With Worksheets("Realtime Data")
.Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq - 1)).Value = vData
End With
Application.ScreenUpdating = False
lngFreq = Round(Range("D4"), 0)
dtStart = Range("D5") + Range("D6")
intMin = Range("C10").Value
intMax = Range("C9").Value
ReDim vData(0 To lngFreq - 1, 1 To 2)
'apply random values
For r = 0 To lngFreq - 1
If LCase(Range("C11")) = "yes" Then
vData(r, 1) = dtStart + (dblTime * r)
End If
If LCase(Range("D11")) = "yes" Then
vData(r, 2) = intMin + Int(Rnd * (intMax - intMin + 1))
End If
Next r
With Worksheets("Realtime Data")
.Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq - 1)).Value = vData
End With
Sub Generate_Samples()
Dim lngFreq, r As Long
Dim dtStart As Date
Dim dblTime As Double
Dim intMin, intMax As Integer
Application.EnableEvents = False
lngFreq = Round(Range("D4"), 0)
Range("D6") = Range("D6") + TimeSerial(0, 0, Range("C7"))
dtStart = Range("D5") + Range("D6")
intMin = Range("C10").Value
intMax = Range("C9").Value
Select Case Range("C4").Value
Case "Year(s)"
dblTime = 365.04
Case "Month(s)"
dblTime = 30.42
Case "Day(s)"
dblTime = 1
Case "Hour(s)"
dblTime = 1 / 24
Case "Min(s)"
dblTime = 1 / 1440
Case "Sec(s)"
dblTime = 1 / 86400
End Select
'Clear existing data
'Range("C13:D" & Cells.SpecialCells(xlLastCell).Row).ClearContents
'apply random values
Application.ScreenUpdating = False
lngFreq = Round(Range("D4"), 0)
dtStart = Range("D5") + Range("D6")
intMin = Range("C10").Value
intMax = Range("C9").Value
ReDim vData(0 To lngFreq - 1, 1 To 2)
'apply random values
For r = 0 To lngFreq - 1
If LCase(Range("C11")) = "yes" Then
vData(r, 1) = dtStart + (dblTime * r)
End If
If LCase(Range("D11")) = "yes" Then
vData(r, 2) = intMin + Int(Rnd * (intMax - intMin + 1))
End If
Next r
With Worksheets("Realtime Data")
.Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq - 1)).Value = vData
End With
Application.EnableEvents = True
If Not tTimer Then
MsgBox ("Done.")
Else
'RunWhen = Now + TimeSerial(0, 0, Range("C7"))
StartTimer Range("C7")
End If
End Sub
ReDim vData(0 To lngFreq - 1, 1 To 2)
ReDim vData(0 To lngFreq - 1, 1 To 2)
Hint: ReDim is related to Dim. If you understand what this form of Dim statement does, it will go a loooong way to help your understanding of what I'm talking about and the code that I've offered you so far.
* vData is a 2D array, with lngFreq rows and 2 columns.
* vData is populated in the For...Next loop with random data.
* After population, the entire array's data is pushed into the range that starts at C13.
Not sure why or how.You assign values to variables and object properties (range values) with an equals sign. The receiving variable name is on the left of the equals sign and some expression is on the right side. When the expression has been evaluated, its value is assigned. If variables are on the right side of the equals sign, their value does not change during the expression evaluation process.
Sub Generate_Samples_Q_28508262()
Dim lngFreq, r As Long
Dim dtStart As Date
Dim dblTime As Double
Dim intMin, intMax As Integer
Dim vData
Dim vCurrentData
Application.EnableEvents = False
lngFreq = Round(Range("D4"), 0)
Range("D6") = Range("D6") + TimeSerial(0, 0, Range("C7"))
dtStart = Range("D5") + Range("D6")
intMin = Range("C10").Value
intMax = Range("C9").Value
Select Case Range("C4").Value
Case "Year(s)"
dblTime = 365.04
Case "Month(s)"
dblTime = 30.42
Case "Day(s)"
dblTime = 1
Case "Hour(s)"
dblTime = 1 / 24
Case "Min(s)"
dblTime = 1 / 1440
Case "Sec(s)"
dblTime = 1 / 86400
End Select
'Clear existing data
'Range("C13:D" & Cells.SpecialCells(xlLastCell).Row).ClearContents
Application.ScreenUpdating = False
ReDim vData(1 To lngFreq, 1 To 2)
With Worksheets("Realtime Data")
vCurrentData = .Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq - 1)).Value
End With
'apply random values
For r = 1 To lngFreq
If LCase(Range("C11")) = "yes" Then
vData(r, 1) = dtStart + (dblTime * r)
Else
vData(r, 1) = vCurrentData(r, 1)
End If
If LCase(Range("D11")) = "yes" Then
vData(r, 2) = intMin + Int(Rnd * (intMax - intMin + 1))
Else
vData(r, 2) = vCurrentData(r, 2)
End If
Next r
With Worksheets("Realtime Data")
.Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq - 1)).Value = vData
End With
Application.ScreenUpdating = True
If Not tTimer Then
MsgBox ("Done.")
Else
'RunWhen = Now + TimeSerial(0, 0, Range("C7"))
StartTimer Range("C7")
End If
Application.EnableEvents = True
End Sub
D--temp-Realtime-v5.xlsm
Application.Names("MyData").RefersTo = "=$C$13:$D$19"
ActiveWorkbook.Names("MyData").RefersTo = "=$C$13:$D$21"
A bit more useful example uses a range object to get the proper RefersTo string valueDim rng as Range
set rng = Worksheets("Realtime Data").range(Worksheets("Realtime Data").range("C13:D13"),Worksheets("Realtime Data").range("C13:D13").end(xldown))
Application.Names("MyData").RefersTo = "=" & rng.Address
ActiveWorkbook.Names("MyData").RefersTo = "=" & rng.Address
Note: you might also use Activesheet in place of Worksheets("Realtime Data"), since the code is running in a workbook with only one worksheet.
Sub Generate_Samples_Q_28508262()
Dim lngFreq, r As Long
Dim dtStart As Date
Dim dblTime As Double
Dim intMin, intMax As Integer
Dim vData As Variant
Dim vCurrentData
Dim rng As Range
Dim wks As Worksheet
Dim vUpdateFlags As Variant
Application.EnableEvents = False
lngFreq = Round(Range("D4"), 0)
Range("D6") = Range("D6") + TimeSerial(0, 0, Range("C7"))
dtStart = Range("D5") + Range("D6")
intMin = Range("C10").Value
intMax = Range("C9").Value
Select Case Range("C4").Value
Case "Year(s)"
dblTime = 365.04
Case "Month(s)"
dblTime = 30.42
Case "Day(s)"
dblTime = 1
Case "Hour(s)"
dblTime = 1 / 24
Case "Min(s)"
dblTime = 1 / 1440
Case "Sec(s)"
dblTime = 1 / 86400
End Select
'Clear existing data
'Range("C13:D" & Cells.SpecialCells(xlLastCell).Row).ClearContents
Set wks = Worksheets("Realtime Data")
Set rng = wks.Range("C13:D13")
Application.ScreenUpdating = False
ReDim vData(1 To lngFreq, 1 To 2)
vCurrentData = wks.Range(rng, rng.Offset(lngFreq - 1)).Value
vUpdateFlags = rng.Offset(-2).Value
vUpdateFlags(1, 1) = LCase(vUpdateFlags(1, 1))
vUpdateFlags(1, 2) = LCase(vUpdateFlags(1, 2))
'apply random values
For r = 1 To lngFreq
If vUpdateFlags(1, 1) = "yes" Then
vData(r, 1) = dtStart + (dblTime * r)
Else
vData(r, 1) = vCurrentData(r, 1)
End If
If vUpdateFlags(1, 2) = "yes" Then
vData(r, 2) = intMin + Int(Rnd * (intMax - intMin + 1))
Else
vData(r, 2) = vCurrentData(r, 2)
End If
Next r
wks.Range(rng, rng.Offset(lngFreq - 1)).Value = vData
Set rng = wks.Range(rng, rng.End(xlDown))
ActiveWorkbook.Names("MyData").RefersTo = "=" & rng.Address
'or wks.Parent.Names("MyData").RefersTo = "=" & rng.Address
Application.ScreenUpdating = True
If Not tTimer Then
MsgBox ("Done.")
Else
'RunWhen = Now + TimeSerial(0, 0, Range("C7"))
StartTimer Range("C7")
End If
Application.EnableEvents = True
End Sub
vUpdateFlags = rng.Offset(-2).Value
the range offset was for the original configuration of the cells. This is where the yes/no values were placed to indicate to the code whether or not to update the C13:D13 and below cells. They were originally in C11:D11If you insist on a changing named range
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.