Finding Column based on input, then first empty row

[stevem]

Ok, I have a combobox on Sheet1 which asks for a name. I have to fin
that name on Sheet2 (one name per column) then find the first empty ro
in that column.. I was trying to piece together what I could from th
little knowledge I have and heres a piece of code that does not work
but you might get some idea of what I'm attempting

Sheets("Light Class").Activate
With frmPilot
On Error Resume Next
Cells.Find(What:=cbName.Text, lookat:=xlPart).Select
Range(ActiveCell).End(xlDown).Select
End With


I'd rather not use the Sheets().Activate

It does go to the correct sheet and selects the right name, but doesn'
move down to the first empty row.

Thanks in advance,
Stev

 

[Charles]

Stevem,

Try this

Sheets("sheet1").Activate
With frmPilot
On Error Resume Next
Cells.Find(What:=cbName.Text, lookat:=xlPart).Select
With ActiveCell
.End(xlDown).Offset(1, 0).Select
End With
End With

HIH

Charle

 

[stevem]

Charles,

Thanks, that works, however since I'm going to have to use this o
quite a few hidden sheets, is there anyway to do this without usin
Sheets().Activate.

 

[Charles]

Stevem,

To be honest with you I'm not sure. Maybe someone else could answe
that. I tried using:

with worksheets("sheet1")
cells.find>>>>
end with

it didn't work, but it works when the sheet is active. Sooo I'
guessing it has to be active. Even though hidden the code you have wil
work.

If I can help in any other matter let me know.

Charle

 

[stevem]

The code works fine with hidden sheets, the only thing I added wa
putting Application.ScreenUpdating = False above the code and the
turning it to true after the code, so the person doesn't even see th
selction box moving around. Accomplishes exactly what I wanted.

Thanks again for the help

 

[stevem]

Ok, have one problem, first here's the code as I have it.

pplication.ScreenUpdating = False
Sheets("Light Class").Activate
With frmPilot
On Error Resume Next
Cells.Find(What:=cbName.Text, lookat:=xlPart).Select
With ActiveCell
.End(xlDown).Offset(1, 0).Select
End With
ActiveCell = CDbl(tbScore)
End With
Sheets("Master").Activate
Application.ScreenUpdating = True

Ok, I have names from A1:A100 and scores for each person going down th
rows. This works just fine except if there is no score for that perso
yet.

Example. Steve is in A1 .. I do this and it will put the score in A
overwritting the name, where it should put the score in A2. If
manually enter a score in A2 then run this, it will find the name i
A1, skip the score in A2 and place the new score in A3 as it should.

Any ideas on this one?

Stev

 

[Charles]

stevem,


Sorry been under the weather. Try this if you want the score to be
placed in cell a2


Application.ScreenUpdating = False
Sheets("Light Class").Activate
With frmPilot
On Error Resume Next
Cells.Find(What:=cbName.Text, lookat:=xlPart).Select
With ActiveCell
End(xlDown).Offset(1, 0).Select
End With
ActiveCell.offset(0,1)= CDbl(tbScore) <<<<<<<make change here
End With
Sheets("Master").Activate
Application.ScreenUpdating = True

HTH
Charles

 

[stevem]

I modified the code a little and still no luck.. Here's what I have
now.

Sheets("Light Class").Activate
With frmPilot
On Error Resume Next
Cells.Find(What:=cbName.Text, lookat:=xlPart).Select
Set rng1 = ActiveCell.End(xlDown)
rng1.Offset(1, 0) = CDbl(tbScore)
End With

Behaves almost exactly the same except it won't overwrite cbName if not
tbScore exists already


Steve

 

[stevem]

Great, works..

Once I changed it to
ActiveCell.offset(1,0)= CDbl(tbScore)

Thanks for all the help, and hope you feel better soon.

 

[Charles]

stevem,

Glad it works for you. the cell you selected was the blank cell fo
input. sorry i didn't understnd what you wanted.
if i can be of help again let me know.

Charle