How can I simplify this code and get it to work correctly

[buffyiscool]

On my worksheet I use five ranges for data measurements as follows B6:B35, D6:D35, F6:F35, H6:H35 and J6:J35.

sht and ac are used to call the sub and the default value of ac in this case is "B6".

The code below is supposed to scan through each range until it finds the next available empty cell. My problem is that it always stops at cell J6 even if B6 is empty.

This is probably an easy fix however I can't for the life of me see it.

Also can the code be simplified.

Any help greatly appreciated as this problem is doing my head in.

Thanks
Colin

Sub findemptycell(ByVal sht As String, ByVal ac As String)
    If sht <> "Sheet1" Then
        Sheets(sht).Activate: Sheets(sht).Select: Sheets(sht).Range(ac).Select
        With ActiveSheet
            Do Until IsEmpty(ActiveCell) = True
                ActiveCell.Offset(1, 0).Select
                If ActiveCell.Row = 36 Then
                    Exit Do
                End If
            Loop
        End With
    Else


        Sheets(sht).Activate: Sheets(sht).Select
        With ActiveSheet
            If IsEmpty(.Range(ac)) = False Then
                ActiveSheet.Range(ac).End(xlDown).Offset(1, 0).Select
            Else
                .Range(ac).Select
            End If
            If Selection.Row > 35 Then
                GoTo 1
            End If
1:
            ac = "D6"
            If IsEmpty(.Range(ac)) = False Then
                ActiveSheet.Range(ac).End(xlDown).Offset(1, 0).Select
            Else
                .Range(ac).Select
            End If
            If Selection.Row > 35 Then
              GoTo 2
            End If
2:
            ac = "F6"
            If IsEmpty(.Range(ac)) = False Then
                ActiveSheet.Range(ac).End(xlDown).Offset(1, 0).Select
            Else
                .Range(ac).Select
            End If
            If Selection.Row > 35 Then
                GoTo 3
            End If
3:
            ac = "H6"
            If IsEmpty(.Range(ac)) = False Then
                ActiveSheet.Range(ac).End(xlDown).Offset(1, 0).Select
            Else
                .Range(ac).Select
            End If
            If Selection.Row > 35 Then
                GoTo 4
            End If
4:
            ac = "J6"
            If IsEmpty(.Range(ac)) = False Then
                ActiveSheet.Range(ac).End(xlDown).Offset(1, 0).Select
            Else
                .Range(ac).Select
            End If
            If Selection.Row > 35 Then
                filefull = True
                Exit Sub
            End If
        End With
    End If
End Sub

 

[Trebor76]

Hi buffyiscool,

See how this goes:

Option Explicit
Sub FindEmptyCell(strSheet As String)

    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In Sheets(strSheet).Range("B6:B35,D6:D35,F6:F35,H6:H35,J6:J35")
        If Len(rngMyCell) = 0 Then
            rngMyCell.Select
            Exit For
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub
Sub Macro1()

    Call FindEmptyCell("Sheet1")

End Sub

Regards,

Robert

 

[dmt32]

Hi,
try following & see if helps

Sub findemptycell(ByVal sht As String)
    Dim ScanRange As Range, Cell As Range


    With Worksheets(sht)
        .Select
    Set ScanRange = .Range("B6:B35, D6:D35, F6:F35, H6:H35,J6:J35")
    End With
    
    For Each Cell In ScanRange.Cells
        If Len(Cell.Value) = 0 Then Cell.Offset(1, 0).Select: Exit Sub
    Next Cell
End Sub

Dave

 

[buffyiscool]

Thanks guys.

Now got an issue with selecting the cell on the worksheet but think this is due to the fact that I have saved the sheet under a different filename before calling a second userform. Should be an easy fix.

Regards
Colin