Table question

[jannie]

I have a multiplication table that I wrote for my daughter in excel. I want
to be able to create a formula or macro function so that when she wants to
know like example: 6 X 4 that she can just type that in the cell or cells and
the cursor will go to the correct answer on the table. How do I do that?

 

[Mike H]

Hi,

Right click your sheet tab and view code and then paste the code in on the
right. You will have to modify this line of code to tell Excel where the
table is

Set MyTable = Range("B1:G4") ' Change to suitThe input cell is A1 so change
that to suit as well

To make it work enter (say) 8*2 in a1



Private Sub Worksheet_Change(ByVal Target As Range)
Set MyTable = Range("B1:G4") ' Change to suit
If Target.Address = "$A$1" Then
Application.EnableEvents = False
TheAnswer = Evaluate(Range("A1").Value)
For Each c In MyTable
If c.Value = TheAnswer Then
c.Select
Application.EnableEvents = True
Exit Sub
End If
Next
Application.EnableEvents = True
End If
End Sub

Mike

 

[Mike H]

Here's an enhancement that gives the answe. Change MyName to your daughters
name

Private Sub Worksheet_Change(ByVal Target As Range)
MyName = "Daughtersname" ' Cahnge to suit
Set MyTable = Range("B1:G4") ' Change to suit
If Target.Address = "$A$1" Then
Application.EnableEvents = False
TheAnswer = Evaluate(Range("A1").Value)
For Each c In MyTable
If c.Value = TheAnswer Then
c.Select
MsgBox MyName & " The correct answer is " & c.Value
Application.EnableEvents = True
Exit Sub
End If
Next
Application.EnableEvents = True
End If
End Sub

Mike

 

[Mike H]

and some error trapping

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo getmeout
MyName = "Daughtersname" ' Cahnge to suit
Set MyTable = Range("B1:G4") ' Change to suit
If Target.Address = "$A$1" Then
Application.EnableEvents = False
TheAnswer = Evaluate(Range("A1").Value)
For Each c In MyTable
If c.Value = TheAnswer Then
c.Select
MsgBox MyName & " The correct answer is " & c.Value
Application.EnableEvents = True
Exit Sub
End If
Next
End If
getmeout:
Application.EnableEvents = True
MsgBox "Sorry " & MyDaughter & " The answer to that isn't in the table"
End Sub


Mike

 

[jannie]

Well I've got the code in like you said and my table is A4:K14. cell A1 is
blank, cell A2is blank, cell A3 is 1, cell A4 is A2*A3. Cell B4 is 1 thru K4
which is 10. Cell A5 is 1 thru A14 which is 10. Then I go to cell A4 and
block A4 thru K 14, Data, Table, Row input cell: A2, Colum input cell: A3 and
the table fills in.

If I go to A1 and type 8*2 nothing happens other than it puts 8*2 in that
cell. The cursor doesn't go find 16 on the table which is the answer.

Jannie

 

[Mike H]

Jannie,

I understand how you've built your table. Are you sure you have enabled
macros in this workbook? I suggest you use the revised code below. Enter the
code exactly as described and save and close the workbook. Open it again and
when prompted enable macros. If you change A1 then the code should execute.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo getmeout
MyName = "Daughtersname" ' Cahnge to suit
Set MyTable = Range("A4:K14") ' Change to suit
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
TheAnswer = Evaluate(Range("A1").Value)
For Each c In MyTable
If c.Value = TheAnswer Then
c.Select
MsgBox MyName & " The correct answer is " & c.Value
Application.EnableEvents = True
Exit Sub
End If
Next

getmeout:
Application.EnableEvents = True
MsgBox "Sorry " & MyDaughter & " The answer to that isn't in the table"
End Sub

Mike

 

[jannie]

TOO COOL! LOL I've taken a VBA class but I have never gotten to use it
before, so I'm trying to force myself to learn it! When the cursor goes to
the cell on the table how can I make the cell change color to like red or
bright yellow?

 

[jannie]

Oh yeh and a couple other things. I create several tables and increased my
range and they work fine. I included some division ones too. the only thing
is I have to put a ' before the first number that I enter in a1 cell like
'12/2 otherwise it thinks it's a date. Also, I have 6 decimal places set in
my division tables but the answer comes back as 2 decimal places. How can I
change that so that the correct 6 decimal answer appears. This too fun!