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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
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,069
Office Version
  1. 2013
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,069
Office Version
  1. 2013
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,734
Members
414,170
Latest member
Mdm

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