Compare input from ComboBox

[stevem]

Sorry if this is a simple problem, but rather new to VBA programming an
haven't found something similar to what I want to do.

I have a list of names in Sheet1 from A3:A20 ..
I have the same list of names on Sheet2 from A1:Q:1

I have a combo box that has rowsource set to a named range coverin
that area..

What I need to do, is that if someone types a name, it first checks i
the name exists, if it does exists, then need to find the first empt
row below their name on Sheet2, if the name doesn't exist, then adds i
to the first empty row on Sheet1 and the first empty column on Sheet2

Thanks in advance,
Stev

 

[Tom Ogilvy]

Check the listindex property. If it isn't -1, then the entry is a match.
You can then use this to offset your range in Sheet2 to get the right
column. Otherwise, as you said, add the item and reset the rowsource.

set rng = Worksheets("Sheet1").Range("A3").End(xldown)(2)

set rng1 = Worksheets(Sheet2").Range("A1").End(xltoRight)(1,2)

rng = Combobox1.value
rng1 = Combobox1.value

rng1.offset(1,0) gives you the blank cell below the new value.

 

[stevem]

Ok, using the listindex property and that's working well, however,having
problems with setting the rng value.

I copy and pasted as shown and it does nothing.
put it as in as you listed and it didn't add the text from the combobox
to the cell, so then I copy/pasted what you had down and then added a
MsgBox call after that to show me what it had in rng and got back
nothing.


QUOTE]-Originally posted by Tom Ogilvy -
*Check the listindex property. If it isn't -1, then the entry is a
match.
You can then use this to offset your range in Sheet2 to get the right
column. Otherwise, as you said, add the item and reset the rowsource.

set rng = Worksheets("Sheet1").Range("A3").End(xldown)(2)

set rng1 = Worksheets(Sheet2").Range("A1").End(xltoRight)(1,2)

rng = Combobox1.value
rng1 = Combobox1.value

rng1.offset(1,0) gives you the blank cell below the new value.

 

[Tom Ogilvy]

if rng and rng1 are empty cells, then I would expect the msgbox to show
nothing if you did

msgbox rng & " " & rng1

if you do

msgbox rng.address(external:=True) & " " & rng1.Address(external:=True)

that would be an indication if they are finding the correct locations.

With the little information you provided, I am making my best guess at what
will work, but I don't have your data and your code sitting in front of me
nor do I know how you implemented my suggestions.

--
Regards,
Tom Ogilvy

Ok, using the listindex property and that's working well, however,having
problems with setting the rng value.

I copy and pasted as shown and it does nothing.
put it as in as you listed and it didn't add the text from the combobox
to the cell, so then I copy/pasted what you had down and then added a
MsgBox call after that to show me what it had in rng and got back
nothing.


QUOTE]-Originally posted by Tom Ogilvy -
*Check the listindex property. If it isn't -1, then the entry is a
match.
You can then use this to offset your range in Sheet2 to get the right
column. Otherwise, as you said, add the item and reset the rowsource.

set rng = Worksheets("Sheet1").Range("A3").End(xldown)(2)

set rng1 = Worksheets(Sheet2").Range("A1").End(xltoRight)(1,2)

rng = Combobox1.value
rng1 = Combobox1.value

rng1.offset(1,0) gives you the blank cell below the new value.

--
Regards,
Tom Ogilvy

Sorry if this is a simple problem, but rather new to VBA programming and
haven't found something similar to what I want to do.

I have a list of names in Sheet1 from A3:A20 ..
I have the same list of names on Sheet2 from A1:Q:1

I have a combo box that has rowsource set to a named range covering
that area..

What I need to do, is that if someone types a name, it first checks if
the name exists, if it does exists, then need to find the first empty
row below their name on Sheet2, if the name doesn't exist, then adds it
to the first empty row on Sheet1 and the first empty column on Sheet2

Thanks in advance,
Steve

 

[stevem]

Sorry for the delay in reply, but I got it to work.. With a littl
caveat.

If cbName.ListIndex < 0 Then
MsgBox cbName & " not on list, adding now"
Set Rng = Worksheets("Master").Range("A3").End(xlDown)(2)
Set rng1 = Worksheets("Light Class").Range("A1").End(xlToRight)(1
2)
Rng = cbName
rng1 = cbName
rng1.Offset(1, 0) = CDbl(tbScore)
Rng.Offset(0, 1) = "=AVERAGE('Light Class'!C2:C5000)"
MsgBox Format(Rng) & " Added with a score of " & Format(tbScore)
Exit Sub
End If

This is the code as I have used it. The only thing I've noticed is tha
it needs at least 2 entries before it starts to work. If I only have
entry, it gives me Compile Error 1004 - Application-defined o
Object-defined error and highlights the first Set Rng line.

Thanks,
Stev

 

[stevem]

Still working on this problem. Here's the code so far

If cbName.ListIndex < 0 Then
MsgBox cbName & " not on list, adding now"
Set Rng = Worksheets("Master").Range("A3").End(xlDown)(2)
Set rng1 = Worksheets("Light Class").Range("A1").End(xlToRight)(1
2)
rng1 = cbName
rng1.Offset(1, 0) = CDbl(tbScore)
Rng = cbName
Rng.Offset(0, 1) = "=AVERAGE('Light Class'!" & rng1.Offset(1
0).Resize(799).Address & ")"
Rng.Offset(0, 2) = "=SUM('Light Class'!" & rng1.Offset(1
0).Resize(799).Address & ")"
Rng.Offset(0, 3) = "=COUNT('Light Class'!" & rng1.Offset(1
0).Resize(799).Address & ")"
MsgBox Format(Rng) & " Added"
Exit Sub
End If

The problem I'm faced with is that on the sheets I must have at least
entries (A3:A4) already there for this to work. I need for this t
place cbName.

Basically this code is for placing new names and works great if
already have manually entered 2, but fails out if my sheets are blan
or only have 1 entry in them so far..

Any ideas on this one?

Stev