Posts by CanisFirebrand

    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.

    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.

    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)



    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.