Need help with auto selecting cells

[Greegan]

I posted the following in another newsgroup.
I only got so far with answers and its been a few days waiting.
I decided I should try another newsgroup and found this one.
I'm hoping someone could help me out...


I have a changing value in cell K7 which will result in a random number
(already generated - no assistance needed here) with a letter for my BINGO
game. So B1-B15, I16-I30, etc all the way to O75.
I have a range of numbers to match the randomly generated number in cells B1
to P5.

What I would like is when any cell in B1-O75 is equal to the value in K7
then the cursor, cell highlight or whatever, auto selects that matching
cell.

Right now I need to select that matching cell, then select a menu item I
call Next Turn, and then it will generate a new number. But I have to do
this with each and every turn and ... well that's 75 turns to do the same
thing repeatedly (Cell>NextTurn,Cell>NextTurn, and so on).

If someone can give me a script that will auto select the identical cell,
then it only be Button, Button, Button.

Thank you in advance

G

From: ?

Does it need to be selected for you, or just highlighted? If the latter, you
could use conditional formatting.

Answer: It needs to highlight the cell that matches K7



From: Don

right click sheet tab>view code>insert this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
Set myrange = Range("b1:b4")
If Target.Address <> "$D$1" Then Exit Sub
If Intersect(Target, myrange) Is Nothing Then
myrange.Find(Target).Activate
End If
End Sub


Answer:
I see what this does and I actually understand it, however its selecting the
cell the range is being compared to.
I need it to select the cell in the range that matches what currently is my
target.
Is there something we can change here to make that work?

Thanks again,

G

New Updated Question:

I'm going to approach my BINGO question from another angle here...
If I know what cell will have B1 and which will have B-I-N-G-O through to
O75...
Then could I not do a statement or script or something where it says
something like If K7=B1 then select B1 but if K7 = P5 then select P5 ?
I know I would probably need to do this for all of them... and you guys are
great with the help, but what I've been given isn't working the way I need
it to...

Thanks in advance

G


I never did get an answer for this.
Can someone help me out?

Thank you

G

 

[Rowan Drummond]

It shouldn't be too difficult to set the activecell based on the value
in K7 BUT we would need to know exactly how the value in K7 is being
changed e.g. is it done directly via a macro linked to a button, is it a
calculation, is the entry typed in etc. If its done via a macro it would
be handy to see that code.

Regards
Rowan

 

[Greegan]

Thank you for your reply. Actually the way I've done it was simplest for me,
but I'm sure there is an easier way...

I have a script given to me as follows

Function RandLotto(Bottom As Integer, Top As Integer, _
Amount As Integer) As String
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer

Application.Volatile

ReDim iArr(Bottom To Top)
For i = Bottom To Top
iArr(i) = i
Next i

For i = Top To Bottom + 1 Step -1
r = Int(Rnd() * (i - Bottom + 1)) + Bottom
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i

For i = Bottom To Bottom + Amount - 1
RandLotto = RandLotto & "," & iArr(i)
Next i

RandLotto = Trim(RandLotto)

End Function

I added the comma so when i use ... =randlotto(1,75,75) I can use text to
column and separate the numbers.
Then are then transposed into a "table" for a vlookup.
=IF(A7="","",VLOOKUP(A7,BingoGameTurns,2,FALSE))

The Vlookup relates the Turn Number with the number rolled.
J7 vlookups the turn number in A7, and K7 then does a lookup of another
"table" to determine the B-I-N-G-O combination with the numbers 1 through
75.

Its not done very clean but it was the best I could do with the
understanding I have of this stuff.

The formula in K7 is
=IF(J7="","",VLOOKUP(J7,BINGOCode,2,FALSE))

One more thing... So that it changes with each turn I have the following
macro that tells A8 to add 1 to A7 for the next turn...

Sub NextBingoTurn()
'
' NextBingoTurn Macro
' Macro recorded 10/30/2005 by PartyLite
'
' next turn
Selection.Interior.ColorIndex = xlNone
Range("A8").Select
Selection.Copy
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub


If you need anything else then let me know

 

[Rowan Drummond]

I was still a little confused by the fact that it seemed you wanted to
search the range B1:O75 for the value in cell K7 every time it changes.
The problem being that K7 is in the range B1:O75 so you would always end
up finding K7 if nothing else.

Reading your other posts I have now decided the range you are wanting to
search is just columns B,G,I,N and O. Am I even close?? If so try this:

Private Sub Worksheet_Calculate()

Dim BRange As Range
Dim fndRange As Range

On Error GoTo exit_event
Application.EnableEvents = False

Set BRange = Union(Range("B1:B75"), Range("I1:I75"), _
Range("N1:N75"), Range("G1:G75"), Range("O1:O75"))
With BRange
Set fndRange = .Find(Range("K7").Value)
End With
If Not fndRange Is Nothing Then
fndRange.Select
End If

exit_event:
Application.EnableEvents = True
End Sub

This is sheet event code. Right click the sheet tab, select view code
and paste the code in there. This will run every time the sheet
calculates regardless of whether that recalculation has changed the
value of K7.

Regards
Rowan

 

[Greegan]

Rowan thank you for your help.
The easiest way to describe is
column A 1 through 5 is B, I, N, G, O
The BINGO numbers (or the range) are located in B15
B1 is ... B1, B2 is... I1, P1 is B15
Just like a BINGO screen it shows B is 1-15, I is 16-30 and so on up to O75
in cell P5

Sorry for the confusion.

 

[Greegan]

Thanks Rowan,
I changed the ranged cells to match what I described in my last post. It
works perfectly. Thanks.

G

 

[Rowan Drummond]

You're welcome.

Thanks Rowan,
I changed the ranged cells to match what I described in my last post. It
works perfectly. Thanks.

G