Help with code for macro

[angus1]

This is the first time I have wrote a macro so please keep it simple for me!

I have created buttons and want to assign a macro to each of them which will, in the cell above it, add 1 each time the button is clicked. It's to record information from a survey.

I realise that i will need a different macro for each button as it will have to refer to the particular cell above the button.

I think the macro code I need is something like:

ActiveCell.Value("A12")=ActiveCell.Value("A12")+1

but it keeps coming up with message saying VBA error 13 type mismatch and I've tried all sorts of variations but can't get it right.

Please help. thank you

 

[Airfix9]

Hi angus1, welcome to the board

Try this:

Range("A12").FormulaR1C1 = Range("A12").Value + 1

Hope that helps.

 

[Peter_SSs]

angus1

Welcome to the Mr Excel board!

I hope that I have understood correctly what you are trying to achieve. Instead of having lots of buttons and lots of macros, what about using the double click event? Try the code below. When you double-click a cell the value in that cell will be increased by 1 each time. If the cell contains text, nothing will happen.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    Target.Value = Target.Value + 1
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[angus1]

Thanks Airfix and Peter. I will have a go with both of those suggestions. Have only used excel in the past for basic things and looking forward to making much more use of it.