record sequence of results from button pressing

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
In K5 I have a simple randbetween, concatenation and vlookup formula that results in a grid reference, like G3.

I'd like to add a simple button that when pressed will run a macro that does two things ...

1) changes the grid reference (ie: activates the cell to run the formula again).
2) records in column AB what grid references have appeared in cell K5, ie: from B1 downwards.

My VBA is very poor.

Is this possible ?

Kind regards,

Chris
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,555
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Chris,

Short answer, yes.

Long and more useful answer would require more detail of what you are trying to achieve.

K5 generates a 'grid' (? cell ?) reference?
What are you using to trigger the generation of this reference? Unless you have calculation set to manual the Randbetween will generate new values every time the sheet changes / calculates?

In AK you want to record what references have been generated in K5.
Please explain 'i.e. from B1 downwards'
Do you want to create a list in the order that they are generated or are you ticking items within an existing list?

Add some more flesh to the bones and I'm sure either myself or someone else will be able to help.
 

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
Thanks Tony,

ultimately I'd like a button that, when pressed, will generate a grid reference from somewhere between A1 through to G7 and place it into AB1, when pressed again, it will come up with another grid reference from somewhere between A1 and G7 and place it into AB2, etc etc etc each time it's pressed, it places a new grid reference into the next available cell in column AB.

Each new grid reference cannot be one that already has been 'chosen', ie: it can't already appear anywhere in the cells of Column B above it.

I was originally trying to use a button that involved the use of a formula in cell K5, but if the button code can do all this in one hit, then K5 won't be necessary.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,555
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Chris,

ie: it can't already appear anywhere in the cells of Column B above it.


Assume you mean AB ??


The below code will pick a random as yet un-picked, cell from the range A1:G7 and place it's reference (address) in column AB.
It will do nothing once all 49 cells appear in the list. Clear part or the whole of the list if you wish to draw again.
The cell reference is absolute i.e. with $ signs. These can be removed if necessary.
Code:
Sub Rand_Cell()Dim Rng As Range
Dim LRng As Range
Dim lr As Integer
Set LRng = Range("AB1:AB49")
c = WorksheetFunction.CountA(LRng) + 1
If c = 50 Then
MsgBox "All references drawn."
Exit Sub
End If


Randomize
Set Rng = Range("A1:G7")
Do Until Range("AB" & c) <> ""
x = WorksheetFunction.RandBetween(1, 49)
CelRef = Rng.Item(x).Address
If WorksheetFunction.CountIf(LRng, CelRef) = 0 Then
Range("AB" & c) = CelRef
Range(CelRef) = "XX"
End If
Loop
End Sub

Right click your sheet tab >> View Code >>> paste code into code pane.

I assume that you wish to run this one draw at a time?
Hope that helps.
 

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
Tony, thanks so much for doing that for me.

How would I adjust the coding if I didn't want the actual cells referred to having 'XX' placed in them. I really only need the cell references appearing in column AB.

Chris
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,555
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Chris,

Apologies, I added the "XX" just as an easy way to check that all cells were being selected once only and I should have removed it before posting.

Delete the line Range(CelRef) = "XX" and that will sort it.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,875
Messages
5,834,182
Members
430,263
Latest member
abz54

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top