record sequence of results from button pressing

[jammoca]

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

 

[Snakehips]

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]

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]

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.

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]

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]

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.