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 wrote:
> 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
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>> 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]

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 wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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 wrote:
>>
>>>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
>
>
>

[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 B1:P5
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.


"Rowan Drummond" <[email protected]> wrote in message
news:[email protected]...
>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 wrote:
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>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 wrote:
>>>
>>>>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
>>
>>

[Greegan]

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

G


"Rowan Drummond" <[email protected]> wrote in message
news:[email protected]...
>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 wrote:
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>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 wrote:
>>>
>>>>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]

You're welcome.

Greegan wrote:
> Thanks Rowan,
> I changed the ranged cells to match what I described in my last post. It
> works perfectly. Thanks.
>
> G
>
>
> "Rowan Drummond" <[email protected]> wrote in message
> news:[email protected]...
>
>>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 wrote:
>>
>>>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" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>
>>>>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 wrote:
>>>>
>>>>
>>>>>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
>>>
>>>
>