finding next cell

[buzz71023]

I am trying to come up with a code to find the next cell in a column and the one I have works prefectly but it needs a little tweaking. What I am trying to do is to find the first empty cell in column B, within range B2:B13 and if there is not an empty cell, then to check for the next empty cell in "C" (range C2:C13) and so on until column F

This is what I have so far.

If Application.WorksheetFunction.CountA("B2:B13") = 0 Then
            MsgBox ("TEST CAN NOT FIND EMPTY CELL")
            Else
            On Error Resume Next
            Columns(2).SpecialCells(xlCellTypeBlanks)(1, 1).Select
            If Err <> 0 Then
            On Error GoTo 0
            [B13].End(xlUp)(2, 1).Select
            End If
            On Error GoTo 0
            End If
        ActiveCell.Value = TextBox6.Value * -1
      End If

 

[mvptomlinson]

Perhaps use:

Sub findblank()
Dim i As Long, ca As Long
For i = 2 To 6
    ca = Application.WorksheetFunction.CountA(Range(Cells(2, i), Cells(13, i)))
    If ca < 12 Then
        Cells(13, i).End(xlUp).Offset(-1, 0).Select
        Exit For
    End If
Next i
End Sub

It will scan each column from 2 to 6 (B to F) and stop when it finds a blank. If you need to do additional math on that blank cell, add the code you need.

 

[mikerickson]

Perhaps this function might help. It is used as in the test sub.

Function NextEmptyCell(SearchRange As Range) As Range
    Dim oneCol As Range
    With SearchRange
        .Parent.Cells(Rows.Count, Columns.Count).Value = "x"
        For Each oneCol In .Columns
            On Error Resume Next
            Set NextEmptyCell = oneCol.SpecialCells(xlCellTypeBlanks).Cells(1, 1)
            On Error GoTo 0
            If Not NextEmptyCell Is Nothing Then Exit For
        Next oneCol
        .Parent.Cells(Rows.Count, Columns.Count).Clear
    End With
End Function

Sub test()
    Dim myCell As Range
    Set myCell = NextEmptyCell(Range("B2:F13"))
    MsgBox myCell.Address
End Sub

 

[buzz71023]

it gave me a runtime error 1004 "application-defined or object-defined error

Private Sub CommandButton3_Click()
Dim i As Long, ca As Long
For i = 2 To 6
    ca = Application.WorksheetFunction.CountA(Range(Cells(2, i), Cells(13, i)))
    If ca < 12 Then
        Cells(13, i).End(xlUp).Offset(-1, 0).Select '<-----HERE
        Exit For
    End If
Next i
ActiveCell.Value = TextBox1.Value
End Sub

 

[mvptomlinson]

Can you post a sample spreadsheet with data in the format like you actually have? I can't get it to error on my sample worksheet.

One issue I did find was that if the only blank was in row 13, it will select row 11 in that column instead of 13. That can easily be handled, though.

 

[buzz71023]

mikerickson,
how would i implement this code into a userform cmd button? b/c i see two codes there and I wouldnt know how to even start adding a second code to a button. I know I didnt specify that earlier, sorry.

 

[buzz71023]

mvptomlinson,
right now I am in a black workbook troubleshooting the code before I add it to my actual spread sheet. If i can get it to work on a black sheet I can get it to work on my workbook.

 

[mvptomlinson]

Soooo.. upload the "black sheet"?

 

[buzz71023]

i meant blank. how do i upload it? Nvr had to do that before. I just now learned how to wrap my code in a box. or do you mean just a screen shot?

 

[mvptomlinson]

This site doesn't allow you to upload files. My signature has links to file upload sites. (MediaFire tends to work best, although some firewalls block it.)