Find Empty Cell

[charllie]

Hi Folks,

I have searched the forum but cannot find anything that does what i want, or anything that i could amend to suit.

I am looking for help with a code that will do the following.

Find the first empty cell in "sheet1" range ("B5:B16") and then place the text of ComboBox1 in the empty cell and the text of ComboBox2 in the opposite cell in column "C".

Example:
If B10 was the first empty cell in the range then:
B10 would = ComboBox1 text
C10 would = ComboBox2 text

If all cells full in the range B5:B16 then a msgbox appears.

I would appreciate any help at all with this.

Thanks

Charllie

 

[Andrew Poulsom]

Example:

Private Sub CommandButton1_Click()
    Dim Sh As Worksheet
    Dim Cell As Range
    Set Sh = Worksheets("Sheet1")
    With Sh
        For Each Cell In .Range("B5:B16")
            If IsEmpty(Cell) Then
                Cell.Value = ComboBox1.Value
                Cell.Offset(0, 1).Value = ComboBox2.Value
                Exit Sub
            End If
        Next Cell
    End With
    MsgBox "No empty cells"
End Sub

 

[charllie]

Hi Andrew,

Thanks you very much for your reply and help, it is very much appreciated.

The code did as required, thanks again.

Charllie

 

[charllie]

Hi Andrew,

Thought i would be able to do this myself but cant get the code to work.

Using you code above which i have only amended to include the names of my comboxes and worksheet, i have tried to input the following code. However it does not work.

For Each Cell In .Range("E5:E16")
If IsEmpty(Cell) Then
Cell.Value = StockBagsTextBox.Value
Cell.Offset(0, 1).Value = StockTimeTextBox.Value

Unload Me
JobBagInfo.Hide
MsgBox "All Information has Now Been Entered"
Exit Sub
End If

What i am trying to do is the same as you original code does only for a second seperate time checking another range.

I have a feeling the problem has to do with the "exit sub" but been on this for over 2 hours now and can't figure it out.

Example of what i want to do:

Check range B5:B16 and place info into the first empty cell etc as your code.
Next, once the the above has been carried out i want to repeat the procedure for range E5:E16.
Next, once that is complete i want to do the following:

Unload Me
JobBagInfo.Hide
MsgBox "All Information has Now Been Entered"

Would appreciate your help again.

Thanks

Charllie

 

[Andrew Poulsom]

You seem to be missing an End If and a Next Cell. If you want to deal with 2 ranges you will need to remove the Exit Sub from my code (and the MsgBox).

 

[charllie]

Hi Andrew,

Thanks for the response.

I already tried that way but an issues whereby the information was filled in but all cells in B5:B16 were filled in rather that just the required cells.

Here is the code i used, can you see anything i have done wrong?

If Me.DayOfWeekComboBox = "Monday" Then
    Dim Sh As Worksheet
    Dim Cell As Range
    Set Sh = Worksheets("Saved Information")
    
    If Me.ProdBagsTextBox.Value > "" Then
    With Sh
        For Each Cell In .Range("B5:B16")
            If IsEmpty(Cell) Then
                Cell.Value = ProdBagsTextBox.Value
                Cell.Offset(0, 1).Value = ProdTimeTextBox.Value
                'Cell.Offset(0, 3).Value = StockBagsTextBox.Value
                'Cell.Offset(0, 4).Value = StockTimeTextBox.Value
                
                'Unload Me
                'JobBagInfo.Hide
                'MsgBox "All Information has Now Been Entered"
                'Exit Sub
            End If
        Next Cell
                For Each Cell In .Range("E5:E16")
            If IsEmpty(Cell) Then
                Cell.Value = StockBagsTextBox.Value
                Cell.Offset(0, 1).Value = StockTimeTextBox.Value
                'Cell.Offset(0, 3).Value = StockBagsTextBox.Value
                'Cell.Offset(0, 4).Value = StockTimeTextBox.Value
                
                Unload Me
                JobBagInfo.Hide
                MsgBox "All Information has Now Been Entered"
                Exit Sub
            End If
        Next Cell
    End With
    End If
End If

Thanks

Mike

 

[Gettingbetter]

You need to add an 'Exit For' after you have found your first space to stop the code looping (this was correctly doen as an 'Exit Sub' in Andrews code but you have since added further code to the bottom so now you need to change it to an 'Exit For')

eg

Cell.Value = ProdBagsTextBox.Value
Cell.Offset(0, 1).Value = ProdTimeTextBox.Value
Exit For

Cheers
GB

 

[charllie]

Hi GB,

Thanks for the response, that worked brilliantly.

Thanks agian to both for your help.

Charllie