Using RefEdit to return multiple addresses

Topic

New Reply

I want to be able to get results from a series of out put cells on my worksheets when I run a monte carlo simulation.

The starting point is a UserForm that has a RefEdit control. It works fine if I want to process only one out put but I am having trouble if I want more than that. I realise I could add a second RefEdit control etc but the problem with that approach is I do not know how many outputs I want.

I have tried this approach in change, after update and exit on the form.

Code:

Private Sub RefEdit1_AfterUpdate()
Set SelRangeV = Range(RefEdit1)                                 ‘Sets Range to Cell Selected
Response = MsgBox(“Any More Outputs”, vbYesNo)  ‘Any More Outputs
Set SelRangeArr(RngCnt) = SelRangeV                         ‘Sets Cell Address to the Array for Outputs
If Response = “vbyes” Then
RngCnt = RngCnt + 1
End If
ReDim Preserve serangearr(RngCnt)
End Sub

That does not work. I have tried the same code in a Module, and while that does result in the msgbox appearing for the response when I click on yes the user form disappears.

As can be seen I was hoping to use the same RefEdit control for each output.

Any suggestions greatly appreciated.

Thanks

Peter

Reply | Quote

Replies

I am not sure I am understanding what you actually want to do with the output but…

Have you thought of using either a listbox or combobox control to display your output info. You can add an entry for each of the outputs into the same control and then if you need to process that list you can simply iterate through however many entries there are.

I note that Response is a MsgBox. I would expect that Response therefore returns an Integer but your code checks it against a string – this would stop RngCnt from ever incrementing since the if statement would never be true.

Your code appears to not be changing the value in RefEdit1 which makes me think the monte carlo is actually changing the value in this control and that macro kicks off on each run. In short, I don’t really know what that code is trying to do at all.

Reply | Quote

Andrew

First thank you for taking the time to reply to my problem. My post was not from the actual routine I am running, I thought it easier to try and explain my problem with a simple user form.

So as to what I want to do :
A user form collects the cell address output which is a result of calculations in the worksheets. It is the result as you would imagine of calculations based on various random inputs.

It collects the number of times the worksheet is to be calculated. Also a the number of bins for a frequency array.

The routine goes on to produce a histogram and cumulative frequency chart based on the out put results.

This all works for one output, but what I want to do is select a series of outputs and produce the same graphs for each one.

I have used the response vbyesno in a number of other routines and it has worked. In this one i have not managed to get as far as rngcnt so I do not know. On reflection maybe the vbyes does not require quotes.

Had thought about using list box or combo. Think Combo would be better as I think you can populate it as you go but not sure how to do that.

Thanks again, by the way Perth was home for most of my life its great to get help from OZ.

Take care

Peter

Reply | Quote

Peter

In that case, I don’t know why you are trying to populate the data into the userform at all. Any of your subsequent analysis and graphing would require the data somewhere else so you may as well store it there with each pass of the monte carlo. You can put a copy into the userform if you want to see it building but what processing do you hope to achieve there?

I had initially considered collecting the monte carlo data outputs in a recordset and then sort the recordset to simplify categorisation of the data. However since you want to graph the data it might be best to simply write it to another worksheet so you can sort it there for whatever data processing you want to do.

Reply | Quote

Sorry did not explain the issue properly, I am not trying to populate the User Form just collect the address’s of the Out Put Cells. The main part of my code process the worksheet collecting the result of each iteration in an array. The only data I put on a worksheet is the spread of the Bins and the Frequency array. That is a lot faster I have found and I am not limited by the number of vertical cells.

I am nearly there I think. This is code I built in a separate Workbook just to test the process.

Code:

Private Sub CommandButton1_Click()
Dim SelRangeArr(1), SelRangeV As Range, SelRangeVArr(1)
Set SelRangeV = Range(RefEdit1)
SelRangeArr(rngCnt) = RefEdit1                                       ‘Returns the Address of Selected Cell
SelRangeVArr(rngCnt) = SelRangeV.Value                         ‘REturns the Value This for testing Purposes
MsgBox “Address ” & SelRangeArr(rngCnt) & vbCr & “Value ” & SelRangeVArr(rngCnt)
Response = MsgBox(“Any More Output Cells”, vbYesNo)     ‘Process any more OutPut Cells
    If Response = vbYes Then
    CarryMsg = CarryMsg & SelRangeArr(rngCnt) & ” ” & SelRangeVArr(rngCnt) & vbCr
    RefEdit1 = “”                                                               ‘Zeros the RefEdit Control
    RefEdit1.SetFocus                                                       ‘Focus on Refedt control
    Else
    MsgBox “Cells Selected ” & vbCr & CarryMsg & SelRangeArr(rngCnt) & ” ” & SelRangeVArr(rngCnt)   ‘Testing make sure the value and address are being returned
    End If

End Sub

A lot of this is for testing as you can see, all I am after is the address assigned for each output “SelRangeArr(rngCnt)”. The actual array that accumulates the data is two dimensional (or will be when I get it working) one for the calculation and the other for the desired cell. Not sure that will work but I will keep trying.

Happy to post all of the code if that would make it any clearer. However my coding skills are somewhat rusty having started hardwiring IBM 601’s many years ago.

Take care

Peter

Reply | Quote

In your code, there are only two possible values for SelRangeArr(rngCnt). You declared this variant array variable so it that rngCnt can only be 0 or 1.

To build a loop to see each of these values you can do something like
Dim i as integer
For i = LBound(SelRangeArr) to UBound(SelRangeArr)
debug.print SelRangeArr(i)
Next i

Reply | Quote

Hi Andrew

I have started a new thread as I have run into a problem sorting my results and I thought that was the correct thing to do, and wanted to let you know in case you have any ideas. I have spent al day on trying to sort a two dimensional array and got nowhere.

Take care

peter

Reply | Quote

First My thanks for staying with me on this , it has been of great help. I was having trouble as you would have guessed and I had also posted a request on another Forum. The suggestion from that was I use the Ctrl button to allow a number of cells to be selected and the split function to split the result into an array. You were correct of course about the rngcnt that was dumb of me. This code seems to be working at least the test message box is returning the results of a random calculation for each cell on each iteration. I have to transfer that to the main body of my code so we will see. This is the result of your suggestions and those from Dave at MrExcel.

Code:

Private Sub CommandButton2_Click()
Dim SelRangeVarr() As String, SelRangeV, ArrData(), Test
SelRangeVarr = Split(RefEdit2.Value, “,”)
reCalc = TextBox1                                                   ‘No of times the worksheet is to be recalculated
ReDim ArrData(reCalc, UBound(SelRangeVarr))        ‘Redimensions the Data Array to the No of Required Calcuations and the No of OutPut Cells
    For n = 0 To reCalc
    Application.Calculate                                           ‘Calculates the Worksheet
        For i = LBound(SelRangeVarr) To UBound(SelRangeVarr)
            Set SelRangeV = Range(SelRangeVarr(i))      ‘Sets The Range
            ArrData(n, i) = SelRangeV.Value                 ‘Assigns the Value of the Calculation for Each Out Put Cell
            Test = Test & “Test ” & ” Iteration ” & n & ” ” & ArrData(n, i) & vbCr  ‘For Testing Purposes
        Next
    Next n
    MsgBox Test
Unload Me
End Sub

Take care and thank you again.

Peter

Reply | Quote