Posts by CanisFirebrand

Thanks. I forgot I commented out the one line that would print the values. Substituted your code as well and it worked great. Thanks.

I found out what was happening on my end. There was one sheet that only had two rows of data. I thought I removed it, but I guess I did not. Once I deleted that sheet, the code ran without issue.

Forgive me as I am still learning much of these topics. I modified my code to the following. It appears to work in that it does not give me any runtime errors. But it puts excel in a not responding stated since I probably put it in some loop it does not exit from. I was not sure if this is correct as the dictionary was looking for a key, value pair. When I had the line as .Add x(ii,1) it gave an error about not enough parameters. So I changed that line to below.

.Add x(ii, 1), x(ii, 1)

Here is the whole code as it stands now. Commented out what was there originally to keep it for historical purposes, for now. Basically the code should select 4 random cells from each sheet in the workbook and then output those random cells into the range H2:K2. I've not ventured into this territory in excel to work with arrays and the like.

Sub RandomTickets()
    Dim x, i As Long, ii As Long, ws As Excel.Worksheet
     
    For Each ws In Sheets
        If ws.Name <> ActiveSheet.Name And ws.Name <> "Utilization Data" And ws.Name <> "Current Roster" Then
            With ws
                x = .Cells(1).CurrentRegion.Columns(2)
'                On Error Resume Next
'                With CreateObject("system.collections.arraylist")
                 With CreateObject("Scripting.Dictionary")
                
                    For i = 1 To 4
ReTry:
                        ii = Application.RandBetween(2, UBound(x, 1))
                        If Not .exists(x(ii, 1)) Then
                            .Add x(ii, 1), x(ii, 1)
                        Else
                            GoTo ReTry
                        End If
                    Next
'                    ws.[H2:K2] = .toarray
'                    ws.[H2:K2] = dictionary.items
                End With
            End With
        End If

Original post, Create button to run formula on every sheet in workbook.

This is the code from the module to randomly pull tickets from a list of tickets on each sheet in the workbook.

Sub RandomTickets()

Dim x, i As Long, ii As Long, ws As Excel.Worksheet



For Each ws In Sheets

If ws.Name <> ActiveSheet.Name And ws.Name <> "Utilization Data" And ws.Name <> "Current Roster" Then

With ws

x = .Cells(1).CurrentRegion.Columns(2)

' On Error Resume Next

With CreateObject("system.collections.arraylist")



For i = 1 To 4

ReTry:

ii = Application.RandBetween(2, UBound(x, 1))

If Not .contains(x(ii, 1)) Then

.Add x(ii, 1)

Else

GoTo ReTry

End If

Next

ws.[h2:l2] = .toarray

End With

End With

I have a need to change the code as I cannot use CreateObject("system.collections.arraylist")

I'm working on a company computer and .Net Framework 3.5 isn't installed, which I found is a requirement for the arraylist. I can't install .NET myself, and I'm not sure if the company would push it corporate wide since others want to use this spreadsheet as well. So I need to find an alternative.

I read about using VB arrays or collections as an alternative, but got lost trying to convert the code to use a VB array or collection over the arraylist.

Re: Create button to run formula on every sheet in workbook

Ok.. that works and is pretty slick. Thanks

I ran some tests on it. I added more tickets on one of the worksheets. and reran the button and it did indeed select random tickets that were added.

Now, for the next question, to make sure I understand it.

If the column I was interested changed, I would need to change this line,

x = .Cells(1).CurrentRegion.Columns(5)

changing the (5) to what ever number column that I need? Meaning that if the format changes and columns are added or removed, (5) becomes whatever column I am interested in counted from Column A =1, B=2, C=3, etc?

So if I now was interested in column G, it would be

x = .Cells(1).CurrentRegion.Columns(7)

?

Re: Create button to run formula on every sheet in workbook

I would need 5 unique tickets on each worksheet. I am pretty new to things like this in excel, so I did not look more into how to stop possible duplicates.

So that would be appreciated..

Thanks.

Re: Create button to run formula on every sheet in workbook

Sorry.. Here is a sample workbook that I created.

I created a sample employee worksheet that shows what I am trying to achieve once the button does what I'd like it.

The button and the formula that I used is on a worksheet titled Functions.

Hello.

I have a workbook with multiple sheets that a given a unique worksheet name.

Each worksheet multiple columns of data. Each column on each worksheet has the same formatting and type of data. The number of rows in each worksheet varies. But each worksheet has the same number of colums.

I have a formula that picks a random value from a specific column between row 2 and the last row of data. I have it skipping row 1 from the column as that is the column header row.

I want to create a button to run this formula on each worksheet and put the results in 5 cells in a column that does not have data.

To give an example of the structure of the excel sheet:

Columns A - Z are used. Column E is the column I want to pull random values from. I want to automatically run the formula on each worksheet by pressing a button and have the results pasted in cells AA:2 = AA:6

I can manually do this on each worksheet by pasting the formula in the cells that I referenced, but there are 25 worksheets and it takes quite a bit of time to do this manually each week.

Thanks in advance.