How my Sub can write N values to a worksheet and redefine a range?

[JenniferMurphy]

I am working on a sub that will generate a list of random numbers of variable length. I want it to write those values to the calling sheet starting at a named cell and then (re)define that range.

For example: I have assigned the name "Hdr" to D5 and "Count" to D4. Count (D4) currently contains "10". I have a button control that will call my Sub named "DoIt". DoIt will read the value in Count (10) and then generate 10 random numbers. Now I want DoIt to save those 10 values in D6:D15 (offset(Hdr,1,0)ffset(Hdr,Count+1,0). Then I want it to assign the name "NewData" to D6:D15.

Can someone help me with the code to do that?

Thanks

 

[StephenCrump]

Try:

'If MyRandoms have dimensions (1 to N)
With Range("Hdr").Offset(1).Resize(N)
    .Value = Application.Transpose(MyRandoms)
    .Name = "NewData"
End With

'If MyRandoms have dimensions(1 to N, 1 to 1)
With Range("Hdr").Offset(1).Resize(N)
    .Value = MyRandoms
    .Name = "NewData"
End With

EDIT: Sorry, it looks like your Hdr is a VBA range variable? In which case, just replace Range("Hdr") with Hdr.

 

[JenniferMurphy]

Try:

'If MyRandoms have dimensions (1 to N)
With Range("Hdr").Offset(1).Resize(N)
    .Value = Application.Transpose(MyRandoms)
    .Name = "NewData"
End With

'If MyRandoms have dimensions(1 to N, 1 to 1)
With Range("Hdr").Offset(1).Resize(N)
    .Value = MyRandoms
    .Name = "NewData"
End With

EDIT: Sorry, it looks like your Hdr is a VBA range variable? In which case, just replace Range("Hdr") with Hdr.

Stephen,

This is really helpful. Thanks.

Here's my code that seems to work. Please let me know if you have any comments or suggestions:

Const rnDataOutHdr As String = "NormDataHdr"
Const rnData As String = "NormData"
Dim N As Long   'Size of dataset
N = InputBox("Next N", "Test", 0)
If N = 0 Then Exit Sub
Dim testdata() As Variant
testdata = Application.RandArray(N, 1, 1, 10, True)
With Range(rnDataOutHdr).Offset(1).Resize(N)
  .Value = testdata
  .Name = rnData
End With

 

[JenniferMurphy]

Try:

'If MyRandoms have dimensions (1 to N)
With Range("Hdr").Offset(1).Resize(N)
    .Value = Application.Transpose(MyRandoms)
    .Name = "NewData"
End With

'If MyRandoms have dimensions(1 to N, 1 to 1)
With Range("Hdr").Offset(1).Resize(N)
    .Value = MyRandoms
    .Name = "NewData"
End With

EDIT: Sorry, it looks like your Hdr is a VBA range variable? In which case, just replace Range("Hdr") with Hdr.

This is working great. Thanks

 

[JenniferMurphy]

I have another problem. The range in the sheet that I am updating (NormData) contains the Y values for a chart. But the chart isn't getting updated until the Sub exits. I added the DoEvents statement to force this to happen and the MsgBox statement so I could see it, but it isn't working. It still does not update the chart until the sub exits, then the chart is updated.

Can someone tell me what I am doing wrong? Thanks

'Declarations
Const rnDataOutHdr As String = "NormDataHdr"  'The cell above the data range
Const rnData As String = "NormData"           'The data range
Dim N As Long                                 'Size of dataset
Dim testdata() As Variant                     'The new data to go in the data range

'Code
Do
  N = InputBox("Next N", "Test", 0)
  If N = 0 Then Exit Sub
  testdata = Application.RandArray(N, 1, 1, 10, True)   'Generate test data
  With Range(rnDataOutHdr).Offset(1).Resize(N)          'Update chart Y values
    .Value = testdata
    .Name = rnData
  End With
  DoEvents                                              'Allow chart to refresh
  Msgbox "See new chart"
Loop

 

[StephenCrump]

Try replacing your DoEvents with a little time loop:

Dim t As Double

'....

t = Timer
Do While Timer < t + 0.1
    DoEvents
Loop

By the way, there is a specific Chart.Refresh method in VBA, which you'd think would be sufficient to update the graph. But it's not, even with a DoEvents added.

 

[JenniferMurphy]

Try replacing your DoEvents with a little time loop:

Dim t As Double

'....

t = Timer
Do While Timer < t + 0.1
    DoEvents
Loop

That seems to be working. Thanks

Do I just leave the previous post as the solution or change that to this one?

By the way, there is a specific Chart.Refresh method in VBA, which you'd think would be sufficient to update the graph. But it's not, even with a DoEvents added.

(sigh) Just one of the millions of things in M$FT code that don't quite work right.

 

[StephenCrump]

That seems to be working. Thanks

Do I just leave the previous post as the solution or change that to this one?

You're welcome.

I'd leave Post #2 as the solution. That answered the question in the thread title.