Having problems with adding input from combobox

[stevem]

Ok, been working on this one, and my last thread went dead, so gonna tr
again (trying to get this working tonight so I can finish thi
project)

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


This works just about 100% .. takes cbName (the combobox input) an
places it to the first empty row in column A on the Master sheet, the
places it in the first empty column on row 1 for the Light Clas
sheet.

The problem I have is this only works if there are at least 2 cell
filled .. I need this to work even if there are no cells input(startin
with A3 and going down on Master sheet and starting at A1 and goin
right on the Light Class sheet)

Any Ideas?

Thanks in Advance..
Stev

 

[Bernie Deitrick]

Steve,

Change

Set Rng = Worksheets("Master").Range("A3").End(xlDown)(2)
to
Set Rng = Worksheets("Master").Range("A65536").End(xlUp)(2)

You might also consider changing
Set rng1 = Worksheets("Light Class").Range("A1").End(xlToRight)(1, 2)
to
Set rng1 = Worksheets("Light Class").Range("IV1").End(xlToLeft)(1, 2)

HTH,
Bernie
MS Excel MVP