Relative addressing from named cell in VBA

[JenniferMurphy]

I have a little macro that I have assigned to a command button. It's basically working except for one detail. There are several named cells in the worksheet. Each one is a column header. I need to perform arithmetic on the cell immediately below each header. If the header is in B4, I need to do arithmetic on B5.

I need something like this:

Public Sub Tally()
Dim row as integer, col as integer
row=activesheet.cell("HdrA").row+1
col =activesheet.cell("HdrA").column
activesheet.cell(row, col) = activesheet.cell(row, col) + 1
End Sub

The above doesn't work. Can anyone help me with the correct syntax?

 

[VoG]

I can't immediately think of a way to do that.

 

[JenniferMurphy]

One new final question, then I'll post the working macros.

Is there a way to assign a hotkey character (not sure if that's the right term)? Menu options usually have one letter underlined. That letter can be used with the Alt key to invoke that option. Can I designate one of the letters of the title as a hotkey?

Aternatively, is there a way to assign a keyboard shortcut to the buttons?

BTW: I am using Excel 2007.

 

[VoG]

Yes. Press ALT + F8, click on the name of the macro, click Options, Enter a key combination then click OK.

 

[JenniferMurphy]

Here's the working code. Many thanks to Peter.

'***********************************************************************************
'            Macro Set to Tally High/Low Results
'
'   Called from command buttons. Since there is no way to pass a parameter from
'   the command button, I have each button call a pre-macro, which then calls the
'   main macro with a parameter.
'
' The command button assignment strings are:
'
'    High Button: Tally.xlsm!Sheet1.TallyHigh
'     Low Button: Tally.xlsm!Sheet1.TallyLow
'
' The High button increments the High tally.
' The Low button increments the Low tally.
' Both buttons increment the Trials tally.
'
' 07/30/11  Created with help from the MrExcel forum.
'***********************************************************************************
 
' Called from the High button
Public Sub TallyHigh()
Call Tally(True)
End Sub
 
' Called from the Low button
Public Sub TallyLow()
Call Tally(False)
End Sub
 
' Main Macro, called by one of the above
Public Sub Tally(Result As Boolean)
 
If Result Then
  If vbYes <> MsgBox("Tally High?", vbYesNo, "Tally Macro") Then Exit Sub
Else
  If vbYes <> MsgBox("Tally Low?", vbYesNo, "Tally Macro") Then Exit Sub
End If
 
' If it's a high result, increment the high counter
If Result Then
  With Range("High").Offset(1)
    .Value = .Value + 1
  End With
' Else it's a low result, increment the low counter
Else
  With Range("Low").Offset(1)
    .Value = .Value + 1
  End With
End If
 
' Always increment the Trials counter
With Range("Trials").Offset(1)
  .Value = .Value + 1
End With
 
End Sub

The macros are called by command buttons. Thjese are placed on the worksheet by clicking on the Developer tab, then Insert. From the Form Controls, select the command button. Place it on the sheet, then right-click to edit.