Using Find method and on error add string to range

Tobbes

New Member
Joined
Feb 26, 2009
Messages
47
To whom it be that can help me,

I have an interesting situation.

I run a tipping comp at work. Every week I update points for various sports.
For example I let people pick an NFL team for the month. Every time that team wins during the month they get 5 points. I use a userform to update the winning teams each week and need some help getting the macros to work.

The userform has 16 combo boxes. They have the 32 NFL teams populated in them. I also have another 16 text boxes that I use to input the points I'm giving the team for winning. I have a button called "Update" which triggers the macro.

When I click the button to run the macro I need Excel to search a range containing a list of teams that have won over the past month. Find the team in the first combobox and then add the score from the textbox to the adjacent cell. However, if that team hasn't won this month, their name wont be in the list. So they now need to be added to the list and the score next to that adjacent cell. Also if this is the first week, no teams will be in the list so I need to add the teams from scratch with careful use of the end(xldown) function.

example:
<TABLE style="WIDTH: 113pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=150 border=0 x:str><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><TBODY><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 83pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" width=110 height=24>TEXANS</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 30pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=40 x:num>15</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>TITANS</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>10</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>VIKINGS</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>10</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>PANTHERS</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>BROWNS</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>BRONCOS</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>COWBOYS</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>JAGUARS</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>RAMS</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>5</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
f54e817cf909395493e1354815e0ddc4.gif

If the Browns win this week, find their name and add 5 points to their score. In this case making the Browns on 10 points.
If the Packers win this week, look for their name, see that it's not there, and then add it to the bottom of the list and give them 5 points.

I'm having problems trying to get the Find method to work when the team isn't there. The "On Error" method isn't working for me.
I have no problems adding the points to cells.

Thanks in advance...
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
<font face=Courier New>    <SPAN style="color:#00007F">Dim</SPAN> Found <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Found = Range("A1:A10").Find("Packers", , , xlWhole)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'Match found</SPAN><br>        Found.Offset(, 1).Value = 5<br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#007F00">'Match not found</SPAN><br>        MsgBox "Packers not found. Yeah right! They're 7-0"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>
 

Tobbes

New Member
Joined
Feb 26, 2009
Messages
47
That's awesome...
As is the Packers run...almost as good as the Dolphins, NOT!

Do you know if there is a way too loop through comboboxes?

i.e.
For a = 1 to 10
Me.ComboBox(a).Value = "Put this value in"
Next

Is there a better way to cheat the system?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
This assumes the comoboxes are named Combobox1, Combobox2, ...Combobox10

<font face=Courier New>    <SPAN style="color:#00007F">Dim</SPAN> cb <SPAN style="color:#00007F">As</SPAN> ComboBox, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 10<br>        <SPAN style="color:#00007F">Set</SPAN> cb = Me.Controls("Combobox" & i)<br>        cb.Value = "Put this value in"<br>    <SPAN style="color:#00007F">Next</SPAN> i</FONT>
 

Tobbes

New Member
Joined
Feb 26, 2009
Messages
47

ADVERTISEMENT

I get runtime error "2147024809 (90070057)"
Could not find the specified object...
It then points to the Set cb = Me.Controls("Combobox" & i) line...
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
That means it cant find a control named "Combobox" & i

Check the names of your comboboxes.
 

Tobbes

New Member
Joined
Feb 26, 2009
Messages
47

ADVERTISEMENT

I think it was either the fact that I had the For loop starting after the Set statement or that it was spelt Combobox instead of ComboBox. Works now...

Can you help me with this statement...
Range(Cells(99, a)).End(xlUp).Offset(1, 0).Value = cb.Value

I tried with just Cells(x,y) and have had no luck. It needs to contain the a as part of a loop...

Thanks in advance.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
Can you help me with this statement...
Range(Cells(99, a)).End(xlUp).Offset(1, 0).Value = cb.Value

I tried with just Cells(x,y) and have had no luck. It needs to contain the a as part of a loop...

I don't fully understand what you want.

This should functionally work if a is a column number or a valid column letter reference.

Code:
Cells(Rows.Count, [I][B]a[/B][/I]).End(xlUp).Offset(1, 0).Value = cb.Value

It may be necessary to include a sheet reference as well.
Code:
Sheets("Sheet1").Cells(Rows.Count, [I][B]a[/B][/I]).End(xlUp).Offset(1, 0).Value = cb.Value
 

Tobbes

New Member
Joined
Feb 26, 2009
Messages
47
I'm trying to go from Row 99, Column a (which is part of a For statement) and go up to find the bottom of the list. The list starts at Row 56 so, I could go down from there.
Its just that I can't seem to work out where the end of the list might be in the range a56:a99
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
If a is suppose to be an actual column letter, this will put the cb.value in the next empty cell in column "A"

Code:
Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = cb.Value
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,637
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top