Using Find method and on error add string to range

[Tobbes]

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>

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...

 

[AlphaFrog]

<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]

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]

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]

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]

That means it cant find a control named "Combobox" & i

Check the names of your comboboxes.

 

[Tobbes]

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]

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.

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.

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

 

[Tobbes]

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]

If a is suppose to be an actual column letter, this will put the cb.value in the next empty cell in column "A"

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