Using Macro to add 1 to cell that is looked up

[curtryan45]

I have a table that will be frequently sorted. I would like to add a macro that will add 1 to a certain row. For example, say I had a macro button that was labeled "apple". When I click that macro, I want it to find the row in my table called apple and add 1 to it.

 

[Scott Huish]

You didn't say what to do with the row number + 1 so this just shows the value in a msgbox if it finds it. This is set to look in Column J, change as necessary:

Private Sub CommandButton1_Click()
Dim c As Range
Set c = Range("J:J").Find("apple")
If Not c Is Nothing Then
    MsgBox c.Row + 1
Else
    MsgBox "Apple not found"
End If
End Sub

 

[curtryan45]

Oh I meant add 1 to a certain cell, not row. Let me make an easier example.
I am doing a basketball standings. My relevant columns are Team, W, and L. Say I have a macro button labeled "Houston Rockets". When I click that button, I want it to add one to the Rockets wins, no matter where in the table it is.

 

[Peter_SSs]

Oh I meant add 1 to a certain cell, not row. Let me make an easier example.
I am doing a basketball standings. My relevant columns are Team, W, and L. Say I have a macro button labeled "Houston Rockets". When I click that button, I want it to add one to the Rockets wins, no matter where in the table it is.

Assuming the team name will appear no more than once in the column, try this modification to HOTPEPPER's code. Adjust the column in the code to match your Team Name column.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Set</SPAN> c = Range("J:J").Find(CommandButton1.Caption)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        c.Offset(, 1).Value = c.Offset(, 1).Value + 1<br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox CommandButton1.Caption & " not found"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Priit]

Assuming the team name will appear no more than once in the column, try this modification to HOTPEPPER's code. Adjust the column in the code to match your Team Name column.


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br> <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br> <SPAN style="color:#00007F">Set</SPAN> c = Range("J:J").Find(CommandButton1.Caption)<br> <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> c.Offset(, 1).Value = c.Offset(, 1).Value + 1<br> <SPAN style="color:#00007F">Else</SPAN><br> MsgBox CommandButton1.Caption & " not found"<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Thank you for this good posting as I am counting different events and the code seems to be perfect. Anyway when I pasted the code, adjusted the column and started adding buttons assigning the same code to each button (luckily only two for testing ) I get Error 424 Object needed. Can anyone point out whats the problem.

TIA
Priit

 

[Peter_SSs]

Thank you for this good posting as I am counting different events and the code seems to be perfect. Anyway when I pasted the code, adjusted the column and started adding buttons assigning the same code to each button (luckily only two for testing ) I get Error 424 Object needed. Can anyone point out whats the problem.

TIA
Priit

I tried the code with up to three buttons and it seems to work fine. Perhaps you should post your modified code for the two buttons you have tried and also tell us about your sheet layout (which columns are used for what? What rows are used? etc). Also, are you just working on the one worksheet?

If you have a lot of buttons, maybe something like this would be better:

In a standard Module:

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> IncreaseNumber(<SPAN style="color:#00007F">ByVal</SPAN> Team <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Set</SPAN> c = Range("J:J").Find(Team)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        c.Offset(, 1).Value = c.Offset(, 1).Value + 1<br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox Team & " not found"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Then in the Sheet Module:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    IncreaseNumber (CommandButton1.Caption)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()<br>    IncreaseNumber (CommandButton2.Caption)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton3_Click()<br>    IncreaseNumber (CommandButton3.Caption)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Priit]

Thanks Peter,

for your answer. The general idea is that I have to count different events happening. So I want to click the button corresponding to the event and when I have counted all of them I will have the Finish button which will add counting results into the next sheet and clear the previous results on the first sheet for the next counting.

Your answer clarified to me the parameter passing. I thought naively that the language can recognize which button was pressed and passes the parameter itself.

But now I am still stuck with modules. I put the IncreaseNumber function into Module1 and CommandButton1_Click() into the Sheet1 under the Excel Objects. Apparently thats wrong as I get the Object needed message.

As I did my last programming in Foxpro 20 years ago I dont get that object thing clearly

TIA
Priit

 

[Peter_SSs]

But now I am still stuck with modules. I put the IncreaseNumber function into Module1 and CommandButton1_Click() into the Sheet1 under the Excel Objects. Apparently thats wrong as I get the Object needed message.

Assuming CommandButton1 etc is on Sheet1, then it sounds like you have put everything in the right place. So...

1. What version of Excel are you using?

2. I suggest you try setting up a new file with just one sheet with...
a) Team names (say 3 of them) in column J of that sheet
b) Three command buttons with captions matching those 3 team names
c) Code as I suggested
... and see if that works (it should add 1 to the value in column K next to the correct team each time the corresponding button is clicked)
Depending on that outcome, we might be able to work out how to proceed.

Here is my sheet after the button with caption "Houston Rockets" has been clicked 7 times, "LA Lakers" button clicked once and "Utah Jazz" button clicked 13 times.

Excel Workbook
	I	J	K	L	M
1		Team	W	L
2		Houston Rockets	7
3		LA Lakers	1
4		Phoenix Suns
5		Utah Jazz	13
6
Teams

 

[Priit]

Thanks Peter again,

My problem seems to lie not in the code but somewhere with the objects.
I am using Excel 2000. Now I tried this and got similar results.
I create a button and assign a new macro to it which writes a number to the cell. It works fine, I press the button and the number appears. Now I replace writing the number with writing the button caption. I press the button and get "Object needed" message. I move the macro to the Sheet module and get "Macro not found" message.

Regards
Priit

 

[Peter_SSs]

I create a button and assign a new macro to it ...

Does this mean you are using buttons from the 'Forms' toolbar? I was using buttons from the 'Control Tolbox' toolbar and providing code relating to that type of Command Button.