Help for VBA NooB

[wknehans]

Hi,

I have a workbook where the results of multiple vlookups across multiple
sheets depend upon the value of one cell on a master or summary page. I
would like to have a way to increment or decrement the numerical value in
that "master" cell, even if I'm currently looking at a different worksheet.
For example, no matter where I am in the workbook (say...Sheet 9):

Pressing Ctrl-Shift-A causes Sheet1!$A$3 = Sheet1!$A$3+1
Pressing Ctrl-Shift-Z causes Sheet1!$A$3 = Sheet1!$A$3-1

I'm certain there's a fairly simple VBA script that would do that, but I
have no idea where to start.

Any help would be very...ummmm...helpful.

Bill

 

[Bernie Deitrick]

Bill,

Copy the macros below and put them into a module in your workbook.

Then, from Excel, choose Tools / Macro / Macros..

Select Macros In: This Workbook...

Then select the first one, click "Options" and assign a Ctrl-Key combo -
Select the box, then hold down Shift and a, then you will get Ctrl-Shift-A
as your key combo... and on for the second.

HTH,
Bernie
MS Excel MVP

Sub IncrementMyCell()
Worksheets("Sheet1").Range("$A$3").Value = _
Worksheets("Sheet1").Range("$A$3").Value + 1
End Sub

Sub DecrementMyCell()
Worksheets("Sheet1").Range("$A$3").Value = _
Worksheets("Sheet1").Range("$A$3").Value - 1
End Sub

 

[wknehans]

Arrrggghhh!!! A one-line macro, and I still can't make it work! Here's the
code I put in Module 1:

Sub Increment()
Worksheets("Sheet1").Range("$A$3").Value =
Worksheets("Sheet1").Range("$A$3").Value + 1
End Sub


Sub Decrement()
Worksheets("Sheet1").Range("$A$3").Value =
Worksheets("Sheet1").Range("$A$3").Value - 1
End Sub

Note that this newsgroup software wraps the middle line after the equal
sign, but in the VB editor, it's all one long statement with a space on
either side of the equal sign.

Sub Increment is assigned Ctrl-Shift-I and Decrement is assigned to
Ctrl-Shift-D via the Options dialog. But whenever I try to run either macro,
I get a "Run-time error '9': Subscript out of range" error box.

Couple of questions:

Do I need the quotes around 'Sheet1' and '$A$3' inside the parentheses?

Your sample had an underscore after each equal sign, but Excel's VB editor
didn't like that at all. Are they an artifact of this newsgroup s/w or are
they a necessary part of the macro command?

What else am I doing wrong? (Could be lots of things!)

Thanks for your help, Bernie, I appreciate it.

BD

 

[Bernie Deitrick]

The underscore is a continuation character and is needed, since the code
really needs to be on one line - you can put the continuation character
back, or you can put all the code on one line by removing the line feed at
the end.

The double quotes are needed since they are string parameters.

HTH,
Bernie

 

[wknehans]

OK, I understand the syntax rules as you laid them out, but I can't figure
out why the macro won't run. The ever-so-helpful "Subscript out of range"
isn't telling me much. I thought maybe I was spelling something wrong per
the rules of VB, but it looks like I've got everything right.

It is something in my install of VB perhaps?

I'm stuck.

BD

 

[Bernie Deitrick]

Actually, if your sheet name isn't literally Sheet1 (the text on the sheet's
tab), then you need to change

Worksheets("Sheet1").

to one of these

Worksheets("Actual Sheet Name").
Worksheets(1). 'this will work if the sheet is always first
Sheet1. (if that is the actual object name of the sheet, as seen in the
project explorer in the VBE)

HTH,
Bernie
MS Excel MVP

 

[wknehans]

Thanks, Bernie, that was exactly what did the trick - I had given Sheet1 a
different name on the tab, but had substituted "Sheet1" for the proper name
when I was having earlier syntax problems.

Outsmarted myself again....

Appreciate the guidance!

BD